题目

有如下一张记录表，如何查询出每隔15分钟的记录数?
Day02 查询出日志数据每隔15分钟的记录数 - 图1
预期结果:
Day02 查询出日志数据每隔15分钟的记录数 - 图2

数据准备

  1. create table times_log(
  2. id int
  3. ,times datetime
  4. )
  5. GO
  6. insert into times_log values(1,'2020-12-25 11:01:00')
  7. insert into times_log values(2,'2020-12-25 11:03:00')
  8. insert into times_log values(3,'2020-12-25 11:05:00')
  9. insert into times_log values(4,'2020-12-25 11:09:00')
  10. insert into times_log values(5,'2020-12-25 11:17:00')
  11. insert into times_log values(6,'2020-12-25 11:19:00')
  12. insert into times_log values(7,'2020-12-25 11:29:00')
  13. insert into times_log values(8,'2020-12-25 11:37:00')

解题思路

  1. 计算当前时刻与本日0点的分钟时间差,与15分钟取余再乘15,转化成15分钟一个刻度的分钟数

DATEDIFF(mi, convert(varchar(10), times, 120), times) /15 *15

  1. 转化成时刻

    1. CONVERT(varchar(100), DATEADD(mi,(DATEDIFF(mi, convert(varchar(10), times, 120), times)  /15)  *15, CONVERT(varchar(10), times, 120)), 120)

    image.png

  2. 分组统计得出结果

    1. select CONVERT(varchar(100), DATEADD(mi,(DATEDIFF(mi, convert(varchar(10), times, 120),  times) /15) *15, CONVERT(varchar(10), times, 120)), 120)  AS [Time],
    2.     count(0)  Num
    3. from times_log
    4. group by CONVERT(varchar(100), DATEADD(mi,(DATEDIFF(mi, convert(varchar(10), times, 120),  times) /15) *15, CONVERT(varchar(10), times, 120)), 120)

    image.png

    其他思路

    也可以先创建一个时刻段维度表,每15分钟一个刻度把所有刻度跑进去,然后再拼接日期,关联查询
    维度表如下:
    image.png