SQL每日一题04：按分钟分组问题

题目

有如下一张记录表T20191105，如何查询出每隔15分钟的记录数

预期结果如下：

参考答案

--SQL Server解法
select * from T20191105;
SELECT T.时间段 , T.行数 
FROM (
SELECT 
 DATEADD(mi,(DATEDIFF(
 mi,CONVERT(VARCHAR(10),
 DATEADD(ss,1,Times),120),
 DATEADD(ss,1,Times))/15)*15,
 CONVERT(VARCHAR(10),Times,120)) AS '时间段',
 COUNT(*) AS '行数'
FROM T20191105
GROUP BY DATEADD(mi,
 (DATEDIFF(mi,
 CONVERT(VARCHAR(10),
 DATEADD(ss,1,Times),120),
 DATEADD(ss,1,Times))/15)*15
 ,CONVERT(VARCHAR(10),Times,120))
) T

答案解析

这里主要是分组的这个函数改怎么理解。为了大家看的更加清楚明白，我们把这其中的每个函数都单独拆分开给大家看看

1、SELECT DATEADD(ss,1,times) FROM T20191105

2、SELECT CONVERT(VARCHAR(10),DATEADD(ss,1,times),120) FROM T20191105

3、SELECT (DATEDIFF(mi,CONVERT(VARCHAR(10),DATEADD(ss,1,times),120),DATEADD(ss,1,times))/15)*15 FROM T20191105

4、SELECT CONVERT(VARCHAR(10),times,120) FROM T20191105

5、SELECT DATEADD(mi,(DATEDIFF(mi,CONVERT(VARCHAR(10),DATEADD(ss,1,times),120),DATEADD(ss,1,times))/15)*15,CONVERT(VARCHAR(10),times,120)) FROM T20191105

结果如下：

这里最重要的就是第3个函数转换DATEDIFF，它是首先将times的完整时间DATEADD(ss,1,times)减去times的日期部分CONVERT(VARCHAR(10),DATEADD(ss,1,times)，最后就得到了时间部分，然后将时间按分钟进行换算，得出一个整数。

我们知道在SQL中整数除以15最后还是整数，会自动直接舍弃小数部分。

其实关键就是要舍弃这个小数部分，这样我们再乘以15就可以得到times最接近的15分钟是那一刻了。再将这个时刻对应的整数转换成时间类型，就是第5部我们得到内容了。

--MySQL解法
SELECT 
FROM_UNIXTIME(t.unit_time * 900,
 '%Y-%m-%d %H:%i:%s') Times, 
 t.cnt
FROM
(
	SELECT 
	FLOOR(UNIX_TIMESTAMP(Times)/900) unit_time, 
	count(ID) cnt
	FROM T20191105 
	GROUP BY unit_time
) t;

答案解析

这里是利用对时间格式化后进行分组求个数，其中子查询里面是解题的关键，先将带时间的日期通过UNIX_TIMESTAMP格式化成秒。

然后除以900秒后用FLOOR向下取整得到一个整数，这样就可以得到一个按15分钟分组的时间序列。

再通过FROM_UNIXTIME将unit_time转化成带时间的日期格式即可实现题目中的要求。