我有一个MySQL 5.7表,每行都有时间戳.我的目标是计算some_id列的值并根据指定的30分钟间隔对其进行分组.并仅显示计数大于0的间隔.
输入:
timestamp some_id
-------------- ------
2019-01-19 05:30:12 4
2019-01-19 05:40:12 8
2019-01-19 15:37:40 2
2019-01-20 01:57:38 2
2019-01-20 07:10:07 4
2019-01-20 22:10:38 2
2019-01-21 08:35:55 4
预期:
interval COUNT(some_id)
------------- -----------
05:30:00 - 06:00:00 2
07:00:00 - 07:30:00 1
08:30:00 - 09:00:00 1
22:00:00 - 22:30:00 1
etc..........
我的尝试
我不确定这是正确的解决方案
SELECT CONCAT(
DATE_FORMAT(table.timestamp, "%H:"),
IF("30">MINUTE(table.timestamp),
(CONCAT("00-",DATE_FORMAT(DATE_ADD(table.timestamp, INTERVAL 60 MINUTE), "%H:"),"30")),
(CONCAT("30-",DATE_FORMAT(DATE_ADD(table.timestamp, INTERVAL 30 MINUTE), "%H:"),"00")))) AS time_period,
COUNT(*)
FROM table
GROUP BY time_period;