求PostgreSQL查询语句：基于时间区间生成日期列及告警占比统计

核心思路 #

1. 过滤无效记录：直接排除created_at或recovery_time为NULL的记录
2. 生成日期序列：为每条有效记录，生成其时间覆盖的所有日期（即on_date）
3. 计算每日有效时长：对每个日期，算出该日期落在[created_at, recovery_time]区间内的实际时长
4. 计算占比：将每日有效时长除以24小时，得到当日占比
5. 生成哑变量：按定义设置alarm1字段（因已过滤NULL，此处固定为1，但保留通用逻辑）

实现SQL #

SELECT
    id,
    CASE WHEN created_at IS NOT NULL THEN 1 ELSE 0 END AS alarm1,
    gs.on_date::date,
    ROUND(
        EXTRACT(EPOCH FROM (
            LEAST(recovery_time, gs.on_date::timestamp + INTERVAL '1 day') -
            GREATEST(created_at, gs.on_date::timestamp)
        )) / (3600 * 24),
        2
    ) AS alarm1_day_percentage,
    created_at,
    recovery_time
FROM
    your_table_name
CROSS JOIN LATERAL
    generate_series(
        DATE(created_at),
        DATE(recovery_time),
        INTERVAL '1 day'
    ) AS gs(on_date)
WHERE
    created_at IS NOT NULL
    AND recovery_time IS NOT NULL
ORDER BY
    id, gs.on_date;

代码说明 #

• 过滤逻辑：WHERE子句直接剔除created_at或recovery_time为空的无效记录
• 日期序列生成：generate_series生成告警时间覆盖的所有日期，通过CROSS JOIN LATERAL关联到原表每条记录，实现一行拆多行
• 有效时长计算：
  • GREATEST(created_at, gs.on_date::timestamp)：取当日0点和告警开始时间的较晚值，作为当日有效区间的起始
  • LEAST(recovery_time, gs.on_date::timestamp + INTERVAL '1 day')：取次日0点和告警结束时间的较早值，作为当日有效区间的结束
  • EXTRACT(EPOCH FROM ...)将时间差转换为秒数，除以3600*24（一天的总秒数）得到占比
• 占比格式化：ROUND(..., 2)将占比保留两位小数，匹配示例中的0.33、1.00、0.42格式
• 哑变量：保留CASE逻辑贴合定义，即使过滤后该值恒为1

示例验证 #

对于id=1的记录（created_at='2022-07-13 16:00'，recovery_time='2022-07-15 10:00'）：

• 2022-07-13：有效时长8小时，占比8/24≈0.33
• 2022-07-14：有效时长24小时，占比24/24=1.00
• 2022-07-15：有效时长10小时，占比10/24≈0.42
  与示例结果完全一致

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