按连续序列分类值分组：PostgreSQL分组需求及问题

嘿，这个需求我刚好处理过很多次！要实现按连续出现的同名记录分组，完全不用折腾PL/pgSQL的循环，纯SQL用窗口函数就能优雅解决，而且性能还更好。

核心思路 #

问题的关键是给每一段连续的同名记录分配一个唯一的「分组ID」——当当前行的name和上一行不同时，就标记为一个新组的起点，然后通过累加这些标记值生成分组ID。有了分组ID之后，常规的GROUP BY就能精准聚合每一段连续组了。

示例演示 #

假设你的表结构是这样的（注意：必须有一个能确定记录顺序的字段，比如自增ID、时间戳，否则「连续」的定义就不存在了）：

CREATE TABLE your_table (
    id SERIAL PRIMARY KEY, -- 用于确定记录顺序的字段
    name VARCHAR(50),
    value_1 INT,
    value_2 INT
);

先插入一些测试数据模拟连续同名的场景：

INSERT INTO your_table (name, value_1, value_2) VALUES
('A', 10, 20),
('A', 15, 25),
('B', 5, 10),
('B', 8, 12),
('B', 3, 15),
('A', 20, 30),
('A', 18, 28);

完整实现SQL #

WITH grouped_data AS (
    SELECT
        name,
        value_1,
        value_2,
        -- 生成连续同名组的唯一ID
        SUM(CASE 
            WHEN prev_name != name OR prev_name IS NULL THEN 1 
            ELSE 0 
        END) OVER (ORDER BY id) AS group_id
    FROM (
        SELECT
            name,
            value_1,
            value_2,
            -- 获取上一行的name值
            LAG(name) OVER (ORDER BY id) AS prev_name
        FROM your_table
    ) AS subquery
)
SELECT
    name,
    MIN(value_1) AS min_value_1,
    MAX(value_2) AS max_value_2
FROM grouped_data
GROUP BY group_id, name
ORDER BY group_id;

代码解释 #

1. 内层子查询：用LAG(name) OVER (ORDER BY id)获取当前行的上一行name值，这里的ORDER BY id必须替换成你实际用来确定顺序的字段（比如时间戳created_at）。
2. 生成分组ID：通过CASE判断当前行和上一行的name是否不同——如果不同（或者是第一行），就返回1，否则返回0。再用SUM()窗口函数按顺序累加这些值，这样每一段连续的同名记录就会被分配同一个group_id。
3. 聚合计算：最后按group_id分组，就能精准得到每一段连续同名组的min(value_1)和max(value_2)了。

注意事项 #

• 一定要保证排序字段的正确性，这是「连续分组」的核心前提，如果没有明确的排序字段，这个需求就无法准确实现。
• 这个方案完全基于原生SQL窗口函数，性能远高于PL/pgSQL的循环方案，适合处理大数据量的场景。

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