如何使用SQL查询/PL/pgSQL无循环按指定范围连续分组？

纯SQL解决方案（推荐，无需循环/过程代码） #

这个需求的核心是识别按行顺序连续的同分组范围记录，再对每个连续子组做聚合统计。我们可以用窗口函数全程实现，完全不需要循环：

WITH groups AS (
    -- 定义分组范围，后续修改只需调整这里，灵活度拉满
    SELECT 'Group-1' AS group_name, 0 AS start_range, 5 AS end_range UNION ALL
    SELECT 'Group-2', 6, 10 UNION ALL
    SELECT 'Group-3', 11, 15 UNION ALL
    SELECT 'Group-4', 16, 20
),
tagged_rows AS (
    -- 给每行标记所属分组，同时标记分组变化的节点
    SELECT 
        t.id, 
        t.values, 
        g.group_name,
        -- 如果当前行分组和前一行不同，标记为新组的起点
        CASE WHEN LAG(g.group_name) OVER (ORDER BY t.id) = g.group_name THEN 0 ELSE 1 END AS new_group
    FROM your_table t  -- 替换成你的实际表名
    JOIN groups g ON t.values BETWEEN g.start_range AND g.end_range
),
grouped_rows AS (
    -- 通过累积求和生成连续分组的唯一ID，同连续分组的行会拿到同一个ID
    SELECT 
        *,
        SUM(new_group) OVER (ORDER BY id) AS continuous_group_id
    FROM tagged_rows
)
-- 最终聚合统计出每个连续子组的信息
SELECT 
    ROW_NUMBER() OVER (ORDER BY continuous_group_id) AS id,
    group_name AS values_group,
    MIN(values) AS start_value,
    MAX(values) AS end_value,
    AVG(values) AS average_value
FROM grouped_rows
GROUP BY continuous_group_id, group_name
ORDER BY continuous_group_id;

代码拆解：

1. groups CTE：把分组规则独立出来，后续要调整范围或新增分组，直接改这里就行。
2. tagged_rows CTE：将原始数据和分组规则关联，给每行分配对应的分组名称；用LAG函数对比前一行的分组，标记出新分组的起始点。
3. grouped_rows CTE：对new_group列做累积求和，生成每个连续子组的唯一标识——这样同一段连续的同分组行就会被归为一组。
4. 最后一步：按连续分组ID聚合，计算每组的起止值、平均值，并用ROW_NUMBER生成结果的ID列。

PL/pgSQL函数封装方案 #

如果需要把逻辑封装成可复用的工具，可以用PL/pgSQL把上面的SQL包裹起来，同样不需要循环：

CREATE OR REPLACE FUNCTION get_continuous_value_groups()
RETURNS TABLE (
    id INT,
    values_group VARCHAR(20),
    start_value INT,
    end_value INT,
    average_value NUMERIC
) AS $$
BEGIN
    RETURN QUERY
    WITH groups AS (
        SELECT 'Group-1' AS group_name, 0 AS start_range, 5 AS end_range UNION ALL
        SELECT 'Group-2', 6, 10 UNION ALL
        SELECT 'Group-3', 11, 15 UNION ALL
        SELECT 'Group-4', 16, 20
    ),
    tagged_rows AS (
        SELECT 
            t.id, 
            t.values, 
            g.group_name,
            CASE WHEN LAG(g.group_name) OVER (ORDER BY t.id) = g.group_name THEN 0 ELSE 1 END AS new_group
        FROM your_table t  -- 替换成你的实际表名
        JOIN groups g ON t.values BETWEEN g.start_range AND g.end_range
    ),
    grouped_rows AS (
        SELECT 
            *,
            SUM(new_group) OVER (ORDER BY id) AS continuous_group_id
        FROM tagged_rows
    )
    SELECT 
        ROW_NUMBER() OVER (ORDER BY continuous_group_id)::INT,
        group_name,
        MIN(values)::INT,
        MAX(values)::INT,
        AVG(values)::NUMERIC
    FROM grouped_rows
    GROUP BY continuous_group_id, group_name
    ORDER BY continuous_group_id;
END;
$$ LANGUAGE plpgsql;

调用函数就能直接得到结果：

SELECT * FROM get_continuous_value_groups();

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