PostgreSQL中如何基于累积和对连续数字动态分组（组和不超阈值）

需求 #

• 输入范围：1到max_num（示例为100）的连续整数，max_num与组和阈值sum_threshold（示例为20）为动态参数，禁止硬编码
• 分组规则：
  • 尽可能将连续数字归为一组，组内数字之和不得超过阈值
  • 若单个数字本身超过阈值，则单独成组
• 示例输出（阈值20，max_num=100）：
  • 第1组：1,2,3,4,5 → 和15（≤20）
  • 第2组：6,7 → 和13（≤20）
  • 第3组：8,9 → 和17（≤20）
  • 第4组：10 → 和10（≤20）
  • ...
  • 第n-1组：99 → 和99（>20，单独成组）
  • 第n组：100 → 和100（>20，单独成组）

解决方案 #

方法1：窗口函数实现 #

通过计算组内累积和与分组标识完成分组，适合大数据量场景：

WITH params AS (
    SELECT 
        100 AS max_num,  -- 可修改的最大值参数
        20 AS sum_threshold  -- 可修改的组和阈值参数
)
SELECT 
    array_agg(n ORDER BY n) AS group_numbers,
    sum(n) AS group_sum
FROM (
    SELECT 
        n,
        -- 生成分组ID：当前组累积和超过阈值时，分组编号递增
        SUM(CASE WHEN group_running_sum > (SELECT sum_threshold FROM params) THEN 1 ELSE 0 END) OVER (ORDER BY n) AS group_id
    FROM (
        SELECT 
            n,
            -- 计算当前组的累积和：减去之前已完成分组的总和，得到当前组累计值
            n + COALESCE(
                SUM(n) OVER (ORDER BY n ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) 
                - SUM(CASE WHEN global_running_sum > (SELECT sum_threshold FROM params) THEN global_running_sum ELSE 0 END) OVER (ORDER BY n),
                0
            ) AS group_running_sum
        FROM (
            SELECT 
                n,
                -- 计算全局累积和，用于判断是否需开启新分组
                SUM(n) OVER (ORDER BY n) AS global_running_sum
            FROM generate_series(1, (SELECT max_num FROM params)) AS n
        ) AS t1
    ) AS t2
) AS t3
GROUP BY group_id
ORDER BY group_id;

方法2：递归CTE实现（逻辑更直观） #

逐行判断是否需要开启新分组，适合小数据量或需要直观调试的场景：

WITH RECURSIVE params AS (
    SELECT 100 AS max_num, 20 AS sum_threshold  -- 动态参数
),
grouped_data AS (
    -- 初始行：第一个数字，初始分组ID和组和
    SELECT 
        1 AS n,
        1 AS group_id,
        1 AS current_group_sum
    FROM params
    WHERE 1 <= max_num
    UNION ALL
    -- 递归迭代后续数字
    SELECT 
        gd.n + 1,
        -- 判断是否开启新分组
        CASE 
            WHEN gd.current_group_sum + (gd.n + 1) > (SELECT sum_threshold FROM params) 
            THEN gd.group_id + 1 
            ELSE gd.group_id 
        END,
        -- 更新当前组和
        CASE 
            WHEN gd.current_group_sum + (gd.n + 1) > (SELECT sum_threshold FROM params) 
            THEN gd.n + 1 
            ELSE gd.current_group_sum + (gd.n + 1) 
        END
    FROM grouped_data gd
    JOIN params p ON gd.n < p.max_num
)
-- 聚合分组结果
SELECT 
    array_agg(n ORDER BY n) AS group_numbers,
    sum(n) AS group_sum
FROM grouped_data
GROUP BY group_id
ORDER BY group_id;

核心特点 #

两种方案均支持动态修改max_num和sum_threshold参数，无需改动核心逻辑，完全符合“不硬编码”的要求。

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