PostgreSQL如何按无间隔日期分组数据并构建视图

数据老陈

2026-9-26

PostgreSQL连续无间隔日期分组视图实现方案

这个需求属于典型的日期区间孤岛合并场景，最高效的实现方式是使用PostgreSQL内置窗口函数，仅需单次扫描表即可完成计算，时间复杂度为O(n)，远优于自连接、递归CTE等实现方案，非常适合封装为视图高频查询。

实现逻辑

按identifier分组排序，用窗口函数获取同分组下前一行的end_date
判断当前行与前一行是否日期间隔连续（前一行end_date + 1 = 当前行start_date），不连续则标记为新分组的起点
对分组起点标记做累加求和，生成连续区间的唯一分组ID
按identifier和分组ID聚合，取每组最小start_date、最大end_date即得到合并后的无间隔日期区间

完整视图创建代码

CREATE OR REPLACE VIEW merged_date_ranges AS
WITH ranked_intervals AS (
    SELECT
        identifier,
        start_date,
        end_date,
        -- 标记新分组起点：前一行日期间隔不连续/是分组内第一行则标记为1
        CASE
            WHEN LAG(end_date) OVER (PARTITION BY identifier ORDER BY start_date) + INTERVAL '1 day' = start_date
            THEN 0
            ELSE 1
        END AS is_new_group
    FROM your_table -- 替换为实际业务表名
),
grouped_intervals AS (
    SELECT
        identifier,
        start_date,
        end_date,
        -- 累加生成连续区间的唯一分组ID
        SUM(is_new_group) OVER (PARTITION BY identifier ORDER BY start_date) AS group_id
    FROM ranked_intervals
)
SELECT
    identifier,
    MIN(start_date) AS start_date,
    MAX(end_date) AS end_date
FROM grouped_intervals
GROUP BY identifier, group_id
ORDER BY identifier, start_date;