PostgreSQL中generate_series展开后含空值的分组重聚问题

核心思路 #

问题本质是要把连续日期中属性完全一致的行合并成一个时段（start_date到end_date），空值的干扰在于SQL默认不把NULL视为相等，导致本该合并的连续空值行被拆分，或者不该合并的行因为空值被错误合并。解决的关键是：

1. 正确识别连续日期中属性（包括空值）的变化断点
2. 基于断点对数据分组，再聚合得到起止日期

通用处理方案（支持任意多列空值） #

以下以PostgreSQL为例，假设你通过generate_series展开后的数据集名为expanded_data，包含字段：primary_key（主键）、current_date（展开后的每日日期）、col1~col6（6个可能为空的业务列）。

步骤1：标记属性变化断点 #

用窗口函数LAG()对比当前行与前一行的所有业务列，当属性发生变化（包括空值的变化）时标记为断点：

WITH expanded_with_break AS (
    SELECT
        primary_key,
        current_date,
        col1, col2, col3, col4, col5, col6,
        -- 当当前行与前一行的所有业务列不完全一致时，标记为断点
        CASE
            WHEN LAG((col1, col2, col3, col4, col5, col6)) OVER (
                PARTITION BY primary_key ORDER BY current_date
            ) IS NOT DISTINCT FROM (col1, col2, col3, col4, col5, col6)
            THEN 0
            ELSE 1
        END AS is_break
    FROM expanded_data
)

步骤2：生成分组ID #

对每个主键的断点进行累加，相同连续时段的行将得到同一个分组ID：

, grouped_data AS (
    SELECT
        *,
        SUM(is_break) OVER (
            PARTITION BY primary_key ORDER BY current_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS group_id
    FROM expanded_with_break
)

步骤3：聚合得到起止日期 #

按主键和分组ID聚合，提取每个分组的首尾日期，同时保留业务列：

SELECT
    primary_key,
    MIN(current_date) AS start_date,
    MAX(current_date) AS end_date,
    col1, col2, col3, col4, col5, col6
FROM grouped_data
GROUP BY primary_key, group_id, col1, col2, col3, col4, col5, col6
ORDER BY primary_key, start_date;

关键细节说明 #

• IS NOT DISTINCT FROM的作用：SQL中NULL = NULL返回UNKNOWN，这个运算符会把两个NULL视为相等，非空值则按常规规则比较，完美解决空值的分组判断问题。
• 元组对比简化代码：用(col1, col2,...)元组一次性对比所有业务列，避免写冗长的(col1 = LAG(col1) OR (col1 IS NULL AND LAG(col1) IS NULL)) AND ...条件。
• 缺失日期的自动处理：如果generate_series展开的日期中有缺失（比如原时段中间断了），断点会自动把前后分成不同分组，符合业务逻辑。

特殊场景调整 #

如果业务规则要求空值不能与任何值（包括其他空值）合并，只需将IS NOT DISTINCT FROM替换为常规的相等判断，并通过COALESCE给空值分配唯一标识（需保证标识不会和业务值冲突），示例：

-- 调整断点判断逻辑
CASE
    WHEN COALESCE(col1, '___NULL___') = COALESCE(LAG(col1) OVER (PARTITION BY primary_key ORDER BY current_date), '___NULL___')
         AND COALESCE(col2, '___NULL___') = COALESCE(LAG(col2) OVER (PARTITION BY primary_key ORDER BY current_date), '___NULL___')
         AND COALESCE(col3, '___NULL___') = COALESCE(LAG(col3) OVER (PARTITION BY primary_key ORDER BY current_date), '___NULL___')
         AND COALESCE(col4, '___NULL___') = COALESCE(LAG(col4) OVER (PARTITION BY primary_key ORDER BY current_date), '___NULL___')
         AND COALESCE(col5, '___NULL___') = COALESCE(LAG(col5) OVER (PARTITION BY primary_key ORDER BY current_date), '___NULL___')
         AND COALESCE(col6, '___NULL___') = COALESCE(LAG(col6) OVER (PARTITION BY primary_key ORDER BY current_date), '___NULL___')
         THEN 0
    ELSE 1
END AS is_break

内容的提问来源于stack exchange，提问作者Estêvão de Oliveira