sinat_32635107 2016-03-17 04:11 采纳率: 60%
浏览 4918
已采纳

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

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(2条)

报告相同问题?

悬赏问题

  • ¥100 有人会搭建GPT-J-6B框架吗?有偿
  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名