如何在SQL中统计8:00-18:00各半小时时段对应的表行数？

不用写循环，用分组查询就能一次性搞定所有时段的统计，比手动执行一堆SELECT高效得多。核心思路是把dt字段按半小时为单位分组，同时筛选出8:00到18:00的时段，再统计每组的行数。

下面针对几种常见数据库给出具体写法：

MySQL 写法 #

SELECT
  -- 将时间归到所属的半小时整点（比如8:15→8:00，8:40→8:30）
  DATE_FORMAT(dt, '%Y-%m-%d %H:00:00') + INTERVAL FLOOR(MINUTE(dt)/30)*30 MINUTE AS half_hour_slot,
  COUNT(*) AS row_count
FROM my_table
WHERE
  -- 筛选8:00-18:00的记录
  TIME(dt) BETWEEN '08:00:00' AND '18:00:00'
  -- 如果只需要某一天的数据，比如2023-05-08，加上下面这行
  -- AND DATE(dt) = '2023-05-08'
GROUP BY half_hour_slot
ORDER BY half_hour_slot;

PostgreSQL 写法 #

SELECT
  date_trunc('hour', dt) + (floor(date_part('minute', dt)/30) * interval '30 minutes') AS half_hour_slot,
  COUNT(*) AS row_count
FROM my_table
WHERE
  EXTRACT(HOUR FROM dt) BETWEEN 8 AND 18
  -- 仅统计特定日期时添加
  -- AND dt::date = '2023-05-08'
GROUP BY half_hour_slot
ORDER BY half_hour_slot;

SQL Server 写法 #

SELECT
  DATEADD(MINUTE, DATEDIFF(MINUTE, 0, dt)/30*30, 0) AS half_hour_slot,
  COUNT(*) AS row_count
FROM my_table
WHERE
  DATEPART(HOUR, dt) BETWEEN 8 AND 18
  -- 仅统计特定日期时添加
  -- AND CAST(dt AS DATE) = '2023-05-08'
GROUP BY DATEADD(MINUTE, DATEDIFF(MINUTE, 0, dt)/30*30, 0)
ORDER BY half_hour_slot;

说明 #

• 这些语句会自动把每个时间点归类到对应的半小时时段，一次性输出所有8:00到18:00之间的半小时区间及对应的行数。
• 如果只需要某一天的数据，取消注释WHERE里的日期筛选条件即可。
• 分组查询是数据库原生优化的操作，比循环逐时段查询效率高得多，代码也更简洁易维护。

内容的提问来源于stack exchange，提问作者roulette01