如何在PostgreSQL中检测连续活动时段并聚合数据

你的核心需求是将连续日期的记录划分为同一时段（period），再按时段聚合求和。原SQL的问题在于累加逻辑错误，导致无法正确分段，以下是修正方案：

1. 生成中间时段标记结果 #

使用以下SQL可以得到你期望的中间输出：

SELECT 
    t, 
    data,
    SUM(is_new_period) OVER (ORDER BY t) AS period
FROM (
    SELECT 
        t, 
        data,
        -- 标记是否为新时段的起始行
        CASE 
            WHEN LAG(t) OVER (ORDER BY t) IS NULL 
                OR t - LAG(t) OVER (ORDER BY t) > 1 
            THEN 1 
            ELSE 0 
        END AS is_new_period
    FROM t_series
) AS x;

逻辑说明 #

• 子查询中通过LAG(t)获取上一条记录的日期，判断当前记录是否属于新时段：
  • 第一条记录无前置日期，直接标记为新时段起始（1）
  • 当前日期与上一条日期间隔超过1天时，标记为新时段起始（1）
  • 其余连续日期的记录标记为0
• 外层通过累加这些起始标记，生成连续的时段编号（period），相同连续日期的记录会得到同一个period值。

2. 生成最终聚合结果 #

基于上述中间结果，按period分组求和即可得到最终输出：

SELECT 
    period,
    SUM(data) AS sum
FROM (
    SELECT 
        t, 
        data,
        SUM(is_new_period) OVER (ORDER BY t) AS period
    FROM (
        SELECT 
            t, 
            data,
            CASE 
                WHEN LAG(t) OVER (ORDER BY t) IS NULL 
                    OR t - LAG(t) OVER (ORDER BY t) > 1 
                THEN 1 
                ELSE 0 
            END AS is_new_period
        FROM t_series
    ) AS x
) AS y
GROUP BY period
ORDER BY period;

你也可以简化嵌套层级，直接在聚合时计算period：

SELECT 
    period,
    SUM(data) AS sum
FROM (
    SELECT 
        data,
        SUM(
            CASE 
                WHEN LAG(t) OVER (ORDER BY t) IS NULL 
                    OR t - LAG(t) OVER (ORDER BY t) > 1 
                THEN 1 
                ELSE 0 
            END
        ) OVER (ORDER BY t) AS period
    FROM t_series
) AS x
GROUP BY period
ORDER BY period;

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