按小时拆分计算用户Work状态秒级时长的SQL问题求助

问题背景 #

存在一张名为time的表，结构包含user、state字段，以及start_time和end_time两个timestamp类型字段，表中数据如下：

user  state      start_time                end_time
1     Work    2022-08-15 11:00:38     2022-08-15 14:11:03
1     Break   2022-08-15 14:11:03     2022-08-15 14:25:25
1     Work    2022-08-15 14:25:25     2022-08-15 15:09:10
1     Work    2022-08-15 15:09:10     2022-08-15 15:14:15
1     Break   2022-08-15 15:14:15     2022-08-15 18:07:50
1     Work    2022-08-15 18:07:50     2022-08-15 19:25:31
1     Work    2022-08-15 19:25:31     2022-08-15 19:34:57
1     Work    2022-08-15 19:34:57     2022-08-15 20:10:57
1     Work    2022-08-15 20:10:57     

需求为按小时拆分统计用户处于Work状态的总时长（单位：秒），例如19:00-20:00的Work时长应为3593秒。现有SQL仅能处理部分时段，无法覆盖跨时段的统计场景（如20:00-21:00这类跨整点的Work时长）。

正确解决方案 #

以下SQL基于PostgreSQL编写，可自动生成所有需统计的小时区间，并精确计算每条Work记录与各小时区间的重叠时长：

WITH hourly_intervals AS (
  -- 生成所有需要统计的小时区间，从最早Work记录的整点开始，到最晚Work记录（含未结束的）的整点结束
  SELECT 
    hour_start,
    hour_start + INTERVAL '1 hour' AS hour_end
  FROM generate_series(
    (SELECT date_trunc('hour', MIN(start_time)) FROM time WHERE state = 'Work'),
    (SELECT date_trunc('hour', COALESCE(MAX(end_time), CURRENT_TIMESTAMP)) FROM time WHERE state = 'Work'),
    INTERVAL '1 hour'
  ) AS hour_start
),
work_records AS (
  -- 过滤Work状态记录，将空的end_time替换为当前时间（可根据需求改为当天结束时间等）
  SELECT 
    "user",
    start_time,
    COALESCE(end_time, CURRENT_TIMESTAMP) AS end_time
  FROM time
  WHERE state = 'Work'
)
-- 关联计算每个小时区间的Work时长
SELECT 
  wr."user",
  TO_CHAR(hi.hour_start, 'YYYY-MM-DD HH24:00:00') AS hour_period,
  EXTRACT(EPOCH FROM GREATEST(0, LEAST(wr.end_time, hi.hour_end) - GREATEST(wr.start_time, hi.hour_start))) AS work_seconds
FROM work_records wr
JOIN hourly_intervals hi 
  ON wr.start_time < hi.hour_end AND wr.end_time > hi.hour_start -- 只保留有时间重叠的记录
ORDER BY wr."user", hi.hour_start;

逻辑说明 #

1. hourly_intervals：通过generate_series自动生成所有覆盖Work记录时间范围的小时区间，确保不会遗漏任何时段。
2. work_records：预处理Work状态的记录，用COALESCE处理end_time为空的情况（未结束的工作记录）。
3. 关联计算：通过时间重叠条件关联两个数据集，用LEAST和GREATEST计算每条Work记录与小时区间的重叠时长，确保结果为非负并转换为秒数。

现有SQL的问题 #

• 硬编码了固定时间区间，无法通用到所有时段；
• 字段名错误（如start_timestamp应为start_time），条件逻辑冗余；
• 仅处理了部分跨时段场景，未覆盖所有可能的时间重叠情况。

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