問(wèn)題描述
表(日志分析器)結(jié)構(gòu)是:
The Table(Log Analyzer) structure is :
我想繪制一個(gè)折線圖，顯示過(guò)去 15 分鐘到當(dāng)前時(shí)間(每分鐘)的會(huì)話數(shù).我想編寫(xiě)一個(gè)查詢(xún)，顯示包含以下信息的兩列:
I want to draw a line graph displaying the number of Sessions for last 15 minute to the current time (every minute). I want to write a query which displays two columns with the following information:
- 日期:小時(shí):分鐘
- 會(huì)話數(shù)
我嘗試編寫(xiě)一個(gè)顯示每小時(shí)請(qǐng)求的示例查詢(xún):
I have tried to write a sample query displaying requests per hour:
select convert(nvarchar(16), L.TimeLog, 120) requestTime ,(select Count(SessionID) from LogData where TimeLog < convert(nvarchar(16), TimeLog, 120) and TimeLog > DATEADD (mi , -15 , convert(nvarchar(16), TimeLog, 120) ) ) AS Sessions
from LogData L
group by convert(nvarchar(16), TimeLog, 120) order by requestTime;
我嘗試創(chuàng)建一個(gè)類(lèi)似于上面的查詢(xún)，但它顯示錯(cuò)誤的結(jié)果.它應(yīng)該每 15 分鐘顯示一些會(huì)話.并且 group by 子句將保持不變(即每分鐘)例如:在 10:00:00 它應(yīng)該代表過(guò)去 15 分鐘到 10:00:00 的會(huì)話數(shù)(sessionID)，即:9:45:00 到 10:00:00 等
I tried to create a query similar to above but it shows wrong results . It should display a number of sessions every 15 minutes . and the group by clause will remain the same (ie. every minute) For example : at 10:00:00 it should represent the number of sessions (sessionID) in last 15 minutes to 10:00:00 ie: 9:45:00 to 10:00:00 and so on
推薦答案
這個(gè)有點(diǎn)復(fù)雜.首先，您需要?jiǎng)?chuàng)建一個(gè) TimeSlot 表，將一天分成 15 分鐘的塊.
This one gets a little complicated. First you need to create a TimeSlot table that breaks a day into 15 minute chunks.
CREATE TABLE [TimeSlot](
[ID] [int] IDENTITY(1,1) NOT NULL,
[BeginTime] [datetime] NULL,
[EndTime] [datetime] NULL
) ON [PRIMARY]
填表:
DECLARE @Start DATETIME
SET @Start='00:00:00'
WHILE (@Start <'23:59:00')
BEGIN
INSERT INTO TimeSlot(BeginTime, EndTime) values (@Start,dateadd(mi,15,@Start))
SET @Start=dateadd(mi,15,@Start)
END
然后，對(duì)于丑陋的 SQL:
Then, for the ugly SQL:
SELECT convert(nvarchar(8), L.TimeLog,112) + ' ' + convert(nvarchar(8), S.BeginTime, 108) AS SlotBeginTime ,Count(LogID) AS Sessions
FROM LogData L, TimeSlot S
WHERE (convert(nvarchar(8), L.TimeLog,112) + convert(nvarchar(8), L.TimeLog, 108)) >= (convert(nvarchar(8), L.TimeLog,112)+ convert(nvarchar(8), S.BeginTime,108))
AND (convert(nvarchar(8), L.TimeLog,112) + convert(nvarchar(8), L.TimeLog, 108)) < (convert(nvarchar(8), L.TimeLog,112)+ convert(nvarchar(8), S.EndTime,108))
GROUP BY convert(nvarchar(8), L.TimeLog,112) + ' ' + convert(nvarchar(8), S.BeginTime, 108)
ORDER BY SlotBeginTime;
試試看.我沒(méi)有檢查它在多個(gè)日期的工作情況，但它確實(shí)適用于單個(gè)日期.
Try it out. I didn't check how well it works across multiple dates but it does would for a single date.
這篇關(guān)于編寫(xiě)查詢(xún)以顯示日志文件中最近 15 分鐘的會(huì)話記錄的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！