sql 间隔段次数

最新推荐文章于 2021-07-12 14:42:32 发布

qq_26228765

最新推荐文章于 2021-07-12 14:42:32 发布

阅读量255

点赞数

分类专栏： SQL 文章标签： SQL

本文链接：https://blog.csdn.net/qq_26228765/article/details/84971380

版权

SQL 专栏收录该内容

0 篇文章 0 订阅

订阅专栏

需求：间隔段次数 20分钟内的数据都算一次

WITH tb([time]) AS (
select ‘2015/11/30 23:53:41’ union all
select ‘2015/11/30 23:54:17’ union all
select ‘2015/11/30 23:54:22’ union all
select ‘2015/11/30 23:54:54’ union all
select ‘2015/11/30 23:56:17’ union all
select ‘2015/11/30 23:57:06’ union all
select ‘2015/11/30 23:57:45’ union all
select ‘2015/11/30 23:58:20’ union all
select ‘2015/11/30 23:58:27’ union ALL
select ‘2015/12/1 1:58:27’
)

SELECT sv.number, DATEADD(minute, sv.number*10,max(t.mintime)) AS starttime,DATEADD(minute, (sv.number+1)10,max(t.mintime)) AS EndTime
,SUM(CASE WHEN t.[time] BETWEEN DATEADD(minute, sv.number10,t.mintime) AND DATEADD(minute, (sv.number+1)10,t.mintime) THEN 1 ELSE 0 END)
FROM MASTER.dbo.spt_values AS sv LEFT JOIN
(
SELECT * ,min([time])OVER(PARTITION BY 1) as mintime, max([time])OVER(PARTITION BY 1) AS maxtime
FROM tb
) t ON sv.[type]=‘P’
WHERE DATEADD(minute, sv.number10,t.mintime)<=t.maxtime
GROUP BY sv.number

SELECT sv.number, DATEADD(minute, sv.number*10,max(t.mintime)) AS starttime,DATEADD(minute, (sv.number+1)10,max(t.mintime)) AS EndTime
,SUM(CASE WHEN t.[FRunTime] BETWEEN DATEADD(minute, sv.number10,t.mintime) AND DATEADD(minute, (sv.number+1)10,t.mintime) THEN 1 ELSE 0 END)
FROM MASTER.dbo.spt_values AS sv LEFT JOIN
(
SELECT * ,min([FRunTime])OVER(PARTITION BY 1) as mintime, max([FRunTime])OVER(PARTITION BY 1) AS maxtime
FROM tRun
) t ON sv.[type]=‘P’
WHERE DATEADD(minute, sv.number10,t.mintime)<=t.maxtime
GROUP BY sv.number

SELECT sv.number, DATEADD(minute, sv.number*10,max(t.mintime)) AS starttime,DATEADD(minute, (sv.number+1)10,max(t.mintime)) AS EndTime
,SUM(CASE WHEN t.[FRunTime] BETWEEN DATEADD(minute, sv.number10,t.mintime) AND DATEADD(minute, (sv.number+1)10,t.mintime) THEN 1 ELSE 0 END)
FROM MASTER.dbo.spt_values AS sv LEFT JOIN
(
SELECT FRunTime ,min([FRunTime]) as mintime, max([FRunTime]) AS maxtime
FROM tRun group by FRunTime
) t ON sv.[type]=‘P’
WHERE DATEADD(minute, sv.number10,t.mintime)<=t.maxtime
GROUP BY sv.number