编写查询以显示日志文件中最近 15 分钟的会话记录

To write a query to display records for last 15 minutes of sessions in the log file(编写查询以显示日志文件中最近 15 分钟的会话记录)
本文介绍了编写查询以显示日志文件中最近 15 分钟的会话记录的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

问题描述

表(日志分析器)结构是:

The Table(Log Analyzer) structure is :


我想绘制一个折线图,显示过去 15 分钟到当前时间(每分钟)的会话数.我想编写一个查询,显示包含以下信息的两列:


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:

  1. 日期:小时:分钟
  2. 会话数

我尝试编写一个显示每小时请求的示例查询:

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;

我尝试创建一个类似于上面的查询,但它显示错误的结果.它应该每 15 分钟显示一些会话.并且 group by 子句将保持不变(即每分钟)例如:在 10:00:00 它应该代表过去 15 分钟到 10:00:00 的会话数(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

推荐答案

这个有点复杂.首先,您需要创建一个 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

然后,对于丑陋的 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;

试试看.我没有检查它在多个日期的工作情况,但它确实适用于单个日期.

Try it out. I didn't check how well it works across multiple dates but it does would for a single date.

这篇关于编写查询以显示日志文件中最近 15 分钟的会话记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!

相关文档推荐

Query with t(n) and multiple cross joins(使用 t(n) 和多个交叉连接进行查询)
Unpacking a binary string with TSQL(使用 TSQL 解包二进制字符串)
Max rows in SQL table where PK is INT 32 when seed starts at max negative value?(当种子以最大负值开始时,SQL 表中的最大行数其中 PK 为 INT 32?)
Inner Join and Group By in SQL with out an aggregate function.(SQL 中的内部连接和分组依据,没有聚合函数.)
Add a default constraint to an existing field with values(向具有值的现有字段添加默认约束)
SQL remove from running total(SQL 从运行总数中删除)