sql server-SQLServer 中如何按一定間隔時間分組統計時間段內出現次數

SQLServer 中如何按一定間隔時間分組統計時間段內出現次數

我的原始數據是這樣的

|ID |time |Status
|215CSZ00 |2015/11/30 23:53:41 |2
|21CUG0HB |2015/11/30 23:54:17 |1
|21CUG0HB |2015/11/30 23:54:22 |1
|21UA75CS |2015/11/30 23:54:54 |1
|215CSZ00 |2015/11/30 23:56:17 |2
|21ZCYPUY |2015/11/30 23:57:06 |2
|2102200A |2015/11/30 23:57:45 |1
|21NNACHH|2015/11/30 23:58:20 |1
|21UA75CS |2015/11/30 23:58:27 |2

每三分鐘一間隔統計狀態1的出現次數

|count |start |end
|3 |2015/11/30 23:53:00 |2015/11/30 23:56:00
|2 |2015/11/30 23:56:00 |2015/11/30 23:59:00
|0 |2015/11/30 23:59:00 |2015/12/1 0:02:00

最佳回答：

試下下面的

 WITH tb(ID ,[time],[Status]) AS (
    select '215CSZ00','2015/11/30 23:53:41','2' union all
    select '21CUG0HB','2015/11/30 23:54:17','1' union all
    select '21CUG0HB','2015/11/30 23:54:22','1' union all
    select '21UA75CS','2015/11/30 23:54:54','1' union all
    select '215CSZ00','2015/11/30 23:56:17','2' union all
    select '21ZCYPUY','2015/11/30 23:57:06','2' union all
    select '2102200A','2015/11/30 23:57:45','1' union all
    select '21NNACHH','2015/11/30 23:58:20','1' union all
    select '21UA75CS','2015/11/30 23:58:27','2' union ALL
    select '21UA75CS','2015/12/1 1:58:27','1'
)
SELECT sv.number, DATEADD(minute, sv.number*3,max(t.mintime)) AS starttime,DATEADD(minute, (sv.number+1)*3,max(t.mintime))  AS EndTime
               ,SUM(CASE WHEN t.[time] BETWEEN DATEADD(minute, sv.number*3,t.mintime) AND DATEADD(minute, (sv.number+1)*3,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 WHERE [Status]='1'
) t ON  sv.[type]='P'
WHERE  DATEADD(minute, sv.number*3,t.mintime)<=t.maxtime
GROUP BY sv.number

number starttime EndTime (No column name)
0 2015-11-30 23:54:17.000 2015-11-30 23:57:17.000 3
23 2015-12-01 01:03:17.000 2015-12-01 01:06:17.000 0
3 2015-12-01 00:03:17.000 2015-12-01 00:06:17.000 0
26 2015-12-01 01:12:17.000 2015-12-01 01:15:17.000 0
6 2015-12-01 00:12:17.000 2015-12-01 00:15:17.000 0
29 2015-12-01 01:21:17.000 2015-12-01 01:24:17.000 0
9 2015-12-01 00:21:17.000 2015-12-01 00:24:17.000 0
15 2015-12-01 00:39:17.000 2015-12-01 00:42:17.000 0
32 2015-12-01 01:30:17.000 2015-12-01 01:33:17.000 0
12 2015-12-01 00:30:17.000 2015-12-01 00:33:17.000 0
35 2015-12-01 01:39:17.000 2015-12-01 01:42:17.000 0
21 2015-12-01 00:57:17.000 2015-12-01 01:00:17.000 0
38 2015-12-01 01:48:17.000 2015-12-01 01:51:17.000 0
1 2015-11-30 23:57:17.000 2015-12-01 00:00:17.000 2
18 2015-12-01 00:48:17.000 2015-12-01 00:51:17.000 0
41 2015-12-01 01:57:17.000 2015-12-01 02:00:17.000 1
27 2015-12-01 01:15:17.000 2015-12-01 01:18:17.000 0
7 2015-12-01 00:15:17.000 2015-12-01 00:18:17.000 0
24 2015-12-01 01:06:17.000 2015-12-01 01:09:17.000 0
30 2015-12-01 01:24:17.000 2015-12-01 01:27:17.000 0
10 2015-12-01 00:24:17.000 2015-12-01 00:27:17.000 0
4 2015-12-01 00:06:17.000 2015-12-01 00:09:17.000 0
36 2015-12-01 01:42:17.000 2015-12-01 01:45:17.000 0
13 2015-12-01 00:33:17.000 2015-12-01 00:36:17.000 0
33 2015-12-01 01:33:17.000 2015-12-01 01:36:17.000 0
39 2015-12-01 01:51:17.000 2015-12-01 01:54:17.000 0
16 2015-12-01 00:42:17.000 2015-12-01 00:45:17.000 0
19 2015-12-01 00:51:17.000 2015-12-01 00:54:17.000 0
25 2015-12-01 01:09:17.000 2015-12-01 01:12:17.000 0
5 2015-12-01 00:09:17.000 2015-12-01 00:12:17.000 0
22 2015-12-01 01:00:17.000 2015-12-01 01:03:17.000 0
2 2015-12-01 00:00:17.000 2015-12-01 00:03:17.000 0
31 2015-12-01 01:27:17.000 2015-12-01 01:30:17.000 0
11 2015-12-01 00:27:17.000 2015-12-01 00:30:17.000 0
28 2015-12-01 01:18:17.000 2015-12-01 01:21:17.000 0
8 2015-12-01 00:18:17.000 2015-12-01 00:21:17.000 0
17 2015-12-01 00:45:17.000 2015-12-01 00:48:17.000 0
34 2015-12-01 01:36:17.000 2015-12-01 01:39:17.000 0
40 2015-12-01 01:54:17.000 2015-12-01 01:57:17.000 0
20 2015-12-01 00:54:17.000 2015-12-01 00:57:17.000 0
14 2015-12-01 00:36:17.000 2015-12-01 00:39:17.000 0
37 2015-12-01 01:45:17.000 2015-12-01 01:48:17.000 0