Sql Server:每十分钟计算一次记录(包括零) [英] Sql Server: Count records (including zero) per ten-minute intervals
问题描述
我目前有一个表 (SQL Server 2005) 来记录对我的 Web 应用程序的访问,我想将一些代码放在一起来报告(并显示可视化)该流量.我想要的是显示过去 24 小时内每十分钟间隔内的访问次数.
我有一个查询就是这样做的,但有十分钟的时间间隔没有访问,我想调整我的查询以显示这些时间间隔的零计数.我想我可以想出一些使用游标的东西,但如果可以避免的话,我宁愿不使用它们.
这是到目前为止的查询:
DECLARE @time int声明 @interval int选择@时间=96选择@间隔=10选择间隔,COUNT(*) AS requestCount,DATEDIFF(MINUTE,DATEADD(HOUR,-1*@time-1,getDate()),interval)/@interval AS intervalPos从(SELECT DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, getDate(), requestBegin)/(@interval*1.0)) * @interval,getDate())FROM [track_pageSubmit] WHERE requestBegin IS NOT NULL AND DATEDIFF(HOUR,requestBegin,getDate()) <@time)AS I(间隔)GROUP BY 间隔 ORDER BY 间隔
表结构如下:
创建表 [dbo].[Track_PageSubmit]([id] [int] IDENTITY(1,1) 非空,[popid] [int] 非空,[节] [varchar](30) NULL,[页面] [int] NULL,[requestBegin] [datetime] NULL,[requestEnd] [datetime] NULL,[渲染] [日期时间] NULL,[postBegin] [日期时间] NULL,[postEnd] [日期时间] NULL)
这里是一些记录的样子:<代码><预>INSERT INTO track_pageSubmit (popid,section,page,requestbegin,requestend,rendered,postbegin,postend)SELECT '2393712','Main_Can_Eng','10','2010-01-22 10:22:08.287','2010-01-22 10:22:08.330',NULL,'2010-01-22 10:22:09.503','2010-01-22 10:22:09.627' 联合SELECT '2393712','Main_Can_Eng','11','2010-01-22 10:22:09.660','2010-01-22 10:22:09.770',NULL,'2010-01-22 10:22:10.973','2010-01-22 10:22:11.050' 联合SELECT '2393712','Main_Can_Eng','12','2010-01-22 10:22:11.080','2010-01-22 10:22:11.143',NULL,'2010-01-22 10:22:12.503','2010-01-22 10:22:12.567' 联合选择 '2394478','main','21','2010-01-21 10:38:54.057','2010-01-21 10:38:54.117','2010-01-21 10:38:54.487','2010-01-21 10:38:55.633','2010-01-21 10:38:55.697' 联合选择 '2394478','main','22','2010-01-21 10:38:55.757','2010-01-21 10:38:55.820','2010-01-21 10:38:56.197','2010-01-21 10:38:57.477','2010-01-21 10:38:57.570' 联合选择 '2394478','main','23','2010-01-21 10:38:57.617','2010-01-21 10:38:57.993','2010-01-21 10:38:58.367','2010-01-21 10:38:59.397','2010-01-21 10:38:59.493' 联合选择 '2394478','main','25','2010-01-21 10:38:59.553','2010-01-21 10:38:59.617','2010-01-21 10:38:59.93','2010-01-21 10:39:01.227','2010-01-21 10:39:01.303' 联合选择 '2394478','main','26','2010-01-21 10:39:01.350','2010-01-21 10:39:01.477','2010-01-21 10:39:01.860','2010-01-21 10:39:02.787','2010-01-21 10:39:02.867' 联合选择 '2394478','main','27','2010-01-21 10:39:02.930','2010-01-21 10:39:03.007','2010-01-21 10:39:03.400','2010-01-21 10:39:04.147','2010-01-21 10:39:04.460' 联合选择 '2394478','main','28','2010-01-21 10:39:04.507','2010-01-21 10:39:05.147','2010-01-21 10:39:05.790','2010-01-21 10:39:19.413','2010-01-21 10:39:19.477' 联合SELECT '2393754','exp46_cex','1','2010-01-22 12:40:56.563','2010-01-22 12:40:56.640',NULL,'2010-01-22 12:40:58.657','2010-01-22 12:40:58.733' 联合SELECT '2393754','exp46_cex','2','2010-01-22 12:40:58.750','2010-01-22 12:40:58.780',NULL,'2010-01-22 12:41:15.623','2010-01-22 12:41:15.657' 联合SELECT '2393754','additionalComments','1','2010-01-22 12:41:15.670','2010-01-22 12:41:15.733',NULL,'2010-01-22 12:41:19.000','2010-01-22 12:41:19.030' 联合SELECT '2393802','main','2','2010-01-22 12:44:50.857','2010-01-22 12:44:50.933',NULL,'2010-01-22 12:44:53.497','2010-01-22 12:44:53.557' 联合SELECT '2393802','main','3','2010-01-22 12:44:53.590','2010-01-22 12:44:53.667',NULL,'2010-01-22 12:44:56.370','2010-01-22 12:44:56.730'
奖励积分(以我的额外感谢的形式)给任何可以更改查询的人,以便我还可以报告每个时间间隔的不同 popid(除了总请求数).
谢谢!
-- 因为您的样本数据跨越 27 小时:
DECLARE@hours TINYINT,@minute_interval TINYINT,@开始小日期时间;选择@小时 = 27,@minute_interval = 10,@start = '20100122 13:00';;与 x AS(选择顶部 (@hours * (60/@minute_interval))n = ROW_NUMBER() 结束(按 column_id 排序)从 msdb.sys.columns),间隔(边界)AS(选择转换(小日期时间,DATEADD(分钟,(-n * @minute_interval),@start))发件人)选择i.边界,RequestCount = COUNT(d.id),DistinctPopIDs = COUNT(DISTINCT d.popid)从间隔 AS i左外连接dbo.Track_PageSubmit AS dON d.requestBegin >= i.boundaryAND d.requestBegin <DATEADD(分钟,@minute_interval,i.boundary)GROUP BY i.boundary按 i.boundary 排序;
I currently have a table (SQL Server 2005) that logs the visits against my web app, and I want to put together some code to report (and display a visualization) of that traffic. What I want is to display the number of visits during each ten-minute interval over the last 24 hours.
I have a query that does just that, but there are ten-minute intervals during which there are no visits, and I would like to adjust my query to display a zero count for those intervals. I imagine I could come up with something using cursors, but I'd rather not use them if I can avoid it.
Here is the query so far:
DECLARE @time int
DECLARE @interval int
SELECT @time=96
SELECT @interval=10
SELECT interval,
COUNT(*) AS requestCount,
DATEDIFF(MINUTE,DATEADD(HOUR,-1*@time-1,getDate()),interval)/@interval AS intervalPos
FROM
(SELECT DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, getDate(), requestBegin) / (@interval*1.0)) * @interval,getDate())
FROM [track_pageSubmit] WHERE requestBegin IS NOT NULL AND DATEDIFF(HOUR,requestBegin,getDate()) < @time) AS I(interval)
GROUP BY interval ORDER BY interval
Here is the table structure:
CREATE TABLE [dbo].[Track_PageSubmit](
[id] [int] IDENTITY(1,1) NOT NULL,
[popid] [int] NOT NULL,
[section] [varchar](30) NULL,
[page] [int] NULL,
[requestBegin] [datetime] NULL,
[requestEnd] [datetime] NULL,
[rendered] [datetime] NULL,
[postBegin] [datetime] NULL,
[postEnd] [datetime] NULL
)
And here is what some of the records look like:
INSERT INTO track_pageSubmit (popid,section,page,requestbegin,requestend,rendered,postbegin,postend) SELECT '2393712','Main_Can_Eng','10','2010-01-22 10:22:08.287','2010-01-22 10:22:08.330',NULL,'2010-01-22 10:22:09.503','2010-01-22 10:22:09.627' UNION SELECT '2393712','Main_Can_Eng','11','2010-01-22 10:22:09.660','2010-01-22 10:22:09.770',NULL,'2010-01-22 10:22:10.973','2010-01-22 10:22:11.050' UNION SELECT '2393712','Main_Can_Eng','12','2010-01-22 10:22:11.080','2010-01-22 10:22:11.143',NULL,'2010-01-22 10:22:12.503','2010-01-22 10:22:12.567' UNION SELECT '2394478','main','21','2010-01-21 10:38:54.057','2010-01-21 10:38:54.117','2010-01-21 10:38:54.487','2010-01-21 10:38:55.633','2010-01-21 10:38:55.697' UNION SELECT '2394478','main','22','2010-01-21 10:38:55.757','2010-01-21 10:38:55.820','2010-01-21 10:38:56.197','2010-01-21 10:38:57.477','2010-01-21 10:38:57.570' UNION SELECT '2394478','main','23','2010-01-21 10:38:57.617','2010-01-21 10:38:57.993','2010-01-21 10:38:58.367','2010-01-21 10:38:59.397','2010-01-21 10:38:59.493' UNION SELECT '2394478','main','25','2010-01-21 10:38:59.553','2010-01-21 10:38:59.617','2010-01-21 10:38:59.993','2010-01-21 10:39:01.227','2010-01-21 10:39:01.303' UNION SELECT '2394478','main','26','2010-01-21 10:39:01.350','2010-01-21 10:39:01.477','2010-01-21 10:39:01.860','2010-01-21 10:39:02.787','2010-01-21 10:39:02.867' UNION SELECT '2394478','main','27','2010-01-21 10:39:02.930','2010-01-21 10:39:03.007','2010-01-21 10:39:03.400','2010-01-21 10:39:04.147','2010-01-21 10:39:04.460' UNION SELECT '2394478','main','28','2010-01-21 10:39:04.507','2010-01-21 10:39:05.147','2010-01-21 10:39:05.790','2010-01-21 10:39:19.413','2010-01-21 10:39:19.477' UNION SELECT '2393754','exp46_cex','1','2010-01-22 12:40:56.563','2010-01-22 12:40:56.640',NULL,'2010-01-22 12:40:58.657','2010-01-22 12:40:58.733' UNION SELECT '2393754','exp46_cex','2','2010-01-22 12:40:58.750','2010-01-22 12:40:58.780',NULL,'2010-01-22 12:41:15.623','2010-01-22 12:41:15.657' UNION SELECT '2393754','additionalComments','1','2010-01-22 12:41:15.670','2010-01-22 12:41:15.733',NULL,'2010-01-22 12:41:19.000','2010-01-22 12:41:19.030' UNION SELECT '2393802','main','2','2010-01-22 12:44:50.857','2010-01-22 12:44:50.933',NULL,'2010-01-22 12:44:53.497','2010-01-22 12:44:53.557' UNION SELECT '2393802','main','3','2010-01-22 12:44:53.590','2010-01-22 12:44:53.667',NULL,'2010-01-22 12:44:56.370','2010-01-22 12:44:56.730'
Bonus points (in the form of extra thanks from me) to anyone who can change the query so that I can also report distinct popids per interval (in addition to total requests).
Thanks!
-- Because your sample data spans 27 hours:
DECLARE
@hours TINYINT,
@minute_interval TINYINT,
@start SMALLDATETIME;
SELECT
@hours = 27,
@minute_interval = 10,
@start = '20100122 13:00';
;WITH x AS
(
SELECT TOP (@hours * (60 / @minute_interval))
n = ROW_NUMBER() OVER
(ORDER BY column_id)
FROM msdb.sys.columns
),
intervals(boundary) AS
(
SELECT CONVERT
(
SMALLDATETIME,
DATEADD(MINUTE, (-n * @minute_interval), @start)
)
FROM x
)
SELECT
i.boundary,
RequestCount = COUNT(d.id),
DistinctPopIDs = COUNT(DISTINCT d.popid)
FROM
intervals AS i
LEFT OUTER JOIN
dbo.Track_PageSubmit AS d
ON d.requestBegin >= i.boundary
AND d.requestBegin < DATEADD(MINUTE, @minute_interval, i.boundary)
GROUP BY i.boundary
ORDER BY i.boundary;
这篇关于Sql Server:每十分钟计算一次记录(包括零)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!