PostgreSQL窗口查询中去除重复版本记录的正确方法

问题背景 #

需要基于上报的集群诊断数据生成软件版本的历史运行周期，由于存在版本升降级的场景，直接使用GROUP BY或按版本分区的窗口函数都会把非连续的同版本记录合并，无法得到正确的独立周期。

实现思路 #

该需求属于典型的**序列孤岛（Gaps and Islands）**问题，通过3步即可实现：

1. 用LAG()窗口函数对比相邻记录的版本，标记版本变化点
2. 对变化标记做累加求和，生成连续同版本的分组ID
3. 按分组聚合计算每个版本周期的起止时间

完整查询SQL #

WITH version_change AS (
    SELECT 
        cluster_id,
        version,
        date,
        -- 对比上一行版本，不同则标记为1
        CASE WHEN version = LAG(version) OVER (PARTITION BY cluster_id ORDER BY date) 
             THEN 0 
             ELSE 1 
        END AS is_changed
    FROM cluster_info
    WHERE cluster_id = 'e2865aec-0ce1-11ec-afda-0242c0a8a003'
),
version_group AS (
    SELECT 
        cluster_id,
        version,
        date,
        -- 累加变化标记生成分组ID，连续相同版本的标记值相同
        SUM(is_changed) OVER (PARTITION BY cluster_id ORDER BY date) AS group_id
    FROM version_change
)
SELECT 
    cluster_id,
    version,
    MIN(date) AS start_date,
    MAX(date) AS end_date
FROM version_group
GROUP BY cluster_id, version, group_id
ORDER BY start_date;

查询结果验证 #

使用提供的测试数据执行后，输出结果如下：

同版本的非连续运行周期已经被拆分为独立条目，完全符合需求。

兼容说明 #

该SQL语法兼容PostgreSQL 10及以上版本，适配当前使用的PostgreSQL 10.14环境。

内容的提问来源于stack exchange，提问作者J.B. Langston