PostgreSQL查询：将活动时段数据转换为半小时区间分钟统计

假设你的原始表名为activity_log，包含字段：start_time（活动开始时间，time或timestamp类型）、end_time（活动结束时间，time或timestamp类型）、activity_code（活动代码，文本类型）。以下是完整的查询实现：

1. 核心查询代码 #

WITH time_buckets AS (
    -- 生成10:00-19:00之间的所有半小时区间桶
    SELECT
        bucket_start AS period,
        bucket_start + INTERVAL '30 minutes' AS bucket_end
    FROM generate_series(
        TIME '10:00:00',
        TIME '18:30:00',
        INTERVAL '30 minutes'
    ) AS bucket_start
),
activity_overlaps AS (
    -- 计算每个活动在各区间内的实际耗时（分钟数）
    SELECT
        tb.period,
        al.activity_code,
        GREATEST(
            0,
            EXTRACT(EPOCH FROM LEAST(al.end_time, tb.bucket_end) - GREATEST(al.start_time, tb.bucket_start)) / 60
        ) AS duration_minutes
    FROM activity_log al
    CROSS JOIN time_buckets tb
    -- 仅保留活动与区间有重叠的记录
    WHERE GREATEST(al.start_time, tb.bucket_start) < LEAST(al.end_time, tb.bucket_end)
),
activity_summary AS (
    -- 汇总每个区间内各活动的总耗时
    SELECT
        period,
        activity_code,
        SUM(duration_minutes) AS total_minutes
    FROM activity_overlaps
    GROUP BY period, activity_code
),
open_time_summary AS (
    -- 计算每个区间的Open Time时长（30分钟减去其他活动总耗时）
    SELECT
        tb.period,
        'Open Time' AS activity_code,
        30 - COALESCE(SUM(asum.total_minutes), 0) AS total_minutes
    FROM time_buckets tb
    LEFT JOIN activity_summary asum ON tb.period = asum.period
    GROUP BY tb.period
)
-- 合并所有活动与Open Time的结果，按时间区间排序
SELECT period, activity_code, total_minutes
FROM activity_summary
UNION ALL
SELECT period, activity_code, total_minutes
FROM open_time_summary
WHERE total_minutes > 0 -- 可选：过滤无空闲时间的区间
ORDER BY period, activity_code;

2. 关键逻辑说明 #

• 时间桶生成：通过generate_series生成10:00到18:30的半小时起始时间，每个区间覆盖30分钟（如10:00-10:30、10:30-11:00等）。
• 重叠时长计算：用GREATEST取活动与区间的较晚开始时间，LEAST取较早结束时间，两者的时间差转换为分钟数，得到活动在当前区间的实际耗时。
• Open Time计算：每个半小时区间总时长为30分钟，减去该区间内所有活动的耗时总和，即为Open Time的时长；COALESCE处理区间内无活动的情况（此时Open Time为30分钟）。
• 类型适配：如果你的表使用timestamp类型（带日期），只需将generate_series中的TIME改为对应日期的TIMESTAMP即可，逻辑完全兼容。

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