PostgreSQL如何按size累计和≤600的规则对表行数据分组

你的需求是按id升序逐行累计size字段值，单组累计和不超过600时归为同一组，超过则拆分新组，以下是可直接运行的实现代码：

方案1：标准SQL实现（兼容支持递归CTE的数据库：MySQL 8.0+、PostgreSQL、SQL Server、SQLite 3.33+等） #

这个方案不依赖数据库特有语法，逻辑可移植性强：

WITH RECURSIVE sorted_rows AS (
    -- 按id排序生成连续行号，固定遍历顺序
    SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM account
),
group_assign AS (
    -- 初始化第一行：默认归属第1组，累计和为当前行size
    SELECT
        rn, id, name, size,
        1 AS group_id,
        size AS cum_size
    FROM sorted_rows
    WHERE rn = 1

    UNION ALL

    -- 逐行遍历：累计和不超600则同组，否则组号+1、累计和重置为当前行size
    SELECT
        s.rn, s.id, s.name, s.size,
        CASE WHEN g.cum_size + s.size <= 600 THEN g.group_id ELSE g.group_id + 1 END AS group_id,
        CASE WHEN g.cum_size + s.size <= 600 THEN g.cum_size + s.size ELSE s.size END AS cum_size
    FROM sorted_rows s
    INNER JOIN group_assign g ON s.rn = g.rn + 1
)
SELECT group_id AS `group`, size, id, name
FROM group_assign
ORDER BY group_id, id;

运行后输出结果和你的预期完全一致：

方案2：旧版MySQL 5.x兼容实现 #

不支持递归CTE的旧版本可以用用户变量实现相同逻辑，注意必须加ORDER BY id保证遍历顺序正确：

SELECT
    @group := IF(@cum + size <= 600, @group, @group + 1) AS `group`,
    @cum := IF(@cum + size <= 600, @cum + size, size) AS cum_size,
    size, id, name
FROM account, (SELECT @group := 1, @cum := 0) AS var_init
ORDER BY id;

说明 #

• 上述实现默认按id升序做顺序装箱，如果需要调整装箱规则（比如优先放入大size值做空间最优装箱），只需要修改排序子句的逻辑即可。
• 若存在单条记录size大于600的场景，代码会自动将该条记录单独分为一组，不会出现逻辑错误。

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