2 sinat 32635107 sinat_32635107 于 2016.03.17 12:11 提问

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

3个回答

ch21st
ch21st   2016.03.23 17:05
已采纳

试下下面的

 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

enpterexpress
enpterexpress   2016.03.17 13:10
sinat_32635107
sinat_32635107 前两个都是用nvarchar(10)来分割日期的,做不到三分钟分哪。自定义函数没学过看不懂。
接近 2 年之前 回复
sinat_32635107
sinat_32635107 前两个都是用nvarchar(10)来分割日期的,做不到三分钟分哪。自定义函数没学过看不懂。
接近 2 年之前 回复
sinat_32635107
sinat_32635107 前两个都是用nvarchar(10)来分割日期的,做不到三分钟分哪。自定义函数没学过看不懂。
接近 2 年之前 回复
sinat_32635107
sinat_32635107 前两个都是用nvarchar(10)来分割日期的,做不到三分钟分哪。自定义函数没学过看不懂。
接近 2 年之前 回复
sinat_32635107
sinat_32635107 前两个都是用nvarchar(10)来分割日期的,做不到三分钟分哪。自定义函数没学过看不懂。
接近 2 年之前 回复
sinat_32635107
sinat_32635107 前两个都是用nvarchar(10)来分割日期的,做不到三分钟分哪。自定义函数没学过看不懂。
接近 2 年之前 回复
ch21st
ch21st   2016.03.23 17:08

原理是,先取得最小和最大时间,然后将最小和最大四件按照每3分钟分成时间段,然后统计每个时间段的值,我为了测试,最后增加了时间范围,所有结果会很多,没有数据的时间段也列出了

Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!