sinat_32635107
2016-03-17 04:11
采纳率: 60%
浏览 4.7k

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条回答 默认 最新

  • 道素 2016-03-23 09: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

    已采纳该答案
    评论
    解决 无用
    打赏 举报
  • 把分全给哥 2016-03-17 05:10
    评论
    解决 无用
    打赏 举报
  • 道素 2016-03-23 09:08

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

    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题