将事件分组为5分钟时间段
将事件分组为5分钟时间段
提问于 2017-04-11 05:04:39
希望有人能够帮助修改下面的SQL，以达到所需输出中显示的结果。我不太懂TSQL，但知道得够多了。
我的目标是将我的事件数据和组划分为15分钟时间段，从午夜开始，但也包括在特定时间段中没有事件数据的0 (0)。
现代查询
;With cte As
(SELECT CONVERT (varchar(5),DATEADD(minute, 15 *
(DATEDIFF(minute, '20000101', I.CreateTimestamp) / 15), '20000101'),108)
AS CreationTime, I.IncidentShortReference AS Ref
FROM Incident I
WHERE i.CreateTimestamp between DATEADD(d,-1,GETDATE()) and GETDATE()
)
SELECT CTE.CreationTime, count(CTE.Ref) As Count
FROM cte CTE
GROUP BY CTE.CreationTime
ORDER BY CTE.CreationTime我的结果
CreationTime count
00:15 2
01:00 1
01:15 1
01:30 1
01:45 2
02:00 1
02:15 1
02:30 4
(Truncated)期望输出
CreationTime count
00:15 2
00:30 0
00:45 0
01:00 1
01:15 1
01:30 1
01:45 2
02:00 1
02:15 1
02:30 4
02:45 0
03:00 0
(Truncated)回答 2
Stack Overflow用户
回答已采纳
发布于 2017-04-11 05:38:33
它使用cte来创建从昨天午夜到现在的每一个时间戳的记录，并使用示例数据对每个范围内的事件数进行计数：
declare @incident table (CreateTimestamp datetime, IncidentShortReference varchar(5))
insert into @incident values ('4/10/2017 11:11:00', 'test')
insert into @incident values ('4/10/2017 11:12:00', 'test')
insert into @incident values ('4/10/2017 11:21:00', 'test')
insert into @incident values ('4/10/2017 11:31:00', 'test')
insert into @incident values ('4/10/2017 13:31:00', 'test')
DECLARE @dt datetime
SELECT @dt = dateadd(d, datediff(d, 0, getdate()), 0) - 1 -- yesterday at midnight
;with cte as
(
select @dt dt
union all
select DATEADD(minute, 15, dt) as Next15
FROM cte
WHERE DATEADD(minute, 15, dt) < GETDATE()
)
select convert(varchar(5), dt, 108) as CreationTime, (select count(*) FROM @incident WHERE CreateTimestamp >= dt and CreateTimestamp < dateadd(mi, 15, dt)) as count
from cte从随机间隔输出的样本：
Stack Overflow用户
发布于 2017-04-11 05:37:52
您可以创建一个时间间隔CTE表，如这。
WITH TIME_CTE
AS(
SELECT
CAST('20170411 00:15:00' AS DATETIME) AS TimePeriod
UNION ALL
SELECT
DATEADD(MINUTE, 15, TimePeriod)
FROM TIME_CTE
WHERE
DATEADD(MINUTE, 15, TimePeriod) < CAST('20170411 23:59:00' AS DATETIME)
)
SELECT
LEFT(CONVERT(VARCHAR(10),TimePeriod,108), 5)
FROM TIME_CTE然后将其与原始查询连接起来。
WITH TIME_CTE
AS(
SELECT
CAST('20170411 00:15:00' AS DATETIME) AS TimePeriod
UNION ALL
SELECT
DATEADD(MINUTE, 15, TimePeriod)
FROM TIME_CTE
WHERE
DATEADD(MINUTE, 15, TimePeriod) < CAST('20170411 23:59:00' AS DATETIME)
),
CTE
AS (
SELECT CONVERT (varchar(5),DATEADD(minute, 15 *
(DATEDIFF(minute, '20000101', I.CreateTimestamp) / 15), '20000101'),108)
AS CreationTime, I.IncidentShortReference AS Ref
FROM Incident I
WHERE i.CreateTimestamp between DATEADD(d,-1,GETDATE()) and GETDATE()
)
SELECT TIME_CTE.TimePeriod, SUM(IIF(CTE.Ref IS NULL, 0, 1)) As Count
FROM TIME_CTE
LEFT JOIN CTE ON CTE.CreationTime = TIME_CTE.TimePeriod
GROUP BY TIME_CTE.TimePeriod
ORDER BY TIME_CTE.TimePeriod页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/43337146
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号