Oracle SQL按名称分组拆分行填充maxquantity的实现方案

需求背景 #

现有如下商品数据表，需要按name字段分组，将每行数据拆分填充到每组的maxquantity阈值中，完成打包需求：

id | name  | quantity | maxquantity
1  | name_a|        3 |           5
2  | name_a|        1 |           5
3  | name_a|        3 |           5
4  | name_a|        5 |           5
5  | name_b|        7 |           4
6  | name_b|        2 |           4

要求输出结果新增两个字段：

• tag：分组包的唯一标识
• effective_quantity：当前行对应所属包的有效抵扣数量
  每组的每个包effective_quantity总和不超过maxquantity阈值，不足阈值的剩余量单独成包，预期输出效果如下：

id | name  | quantity | maxquantity | tag          | effective_quantity 
1  | name_a|        3 |           5 | name_a_part1 |                  3
2  | name_a|        1 |           5 | name_a_part1 |                  1
3  | name_a|        3 |           5 | name_a_part1 |                  1
-- 以上sum(effective_quantity)=5，达到maxquantity阈值

3  | name_a|        3 |           5 | name_a_part2 |                  2
4  | name_a|        5 |           5 | name_a_part2 |                  3
-- 以上sum(effective_quantity)=5，达到maxquantity阈值

4  | name_a|        5 |           5 | name_a_part3 |                  2
-- 以上为name_a剩余未打包的数量，不足阈值单独成包

5  | name_b|        7 |           4 | name_b_part1 |                  4
-- 以上sum(effective_quantity)=4，达到maxquantity阈值

5  | name_b|        7 |           4 | name_b_part2 |                  3
6  | name_b|        2 |           4 | name_b_part2 |                  1
-- 以上sum(effective_quantity)=4，达到maxquantity阈值

6  | name_b|        2 |           4 | name_b_part3 |                  1
-- 以上为name_b剩余未打包的数量，不足阈值单独成包

实现代码（支持MySQL 8.0+/PostgreSQL/SQL Server等支持递归CTE的数据库） #

WITH RECURSIVE 
-- 第一步：给同分组内的商品按id排序，方便后续顺序处理
sorted_goods AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) AS rn,
           COUNT(*) OVER (PARTITION BY name) AS group_row_count
    FROM goods
),
-- 第二步：递归处理数量拆分逻辑
split_process AS (
    -- 递归初始值：取每组的第一行，初始化第一个包的参数
    SELECT id, name, quantity, maxquantity, rn, group_row_count,
           maxquantity AS remaining_pack,
           1 AS pack_index,
           LEAST(quantity, maxquantity) AS effective_qty,
           quantity - LEAST(quantity, maxquantity) AS left_row_qty
    FROM sorted_goods
    WHERE rn = 1

    UNION ALL

    SELECT 
        CASE WHEN sp.left_row_qty > 0 THEN sp.id ELSE sg.id END AS id,
        sp.name,
        CASE WHEN sp.left_row_qty > 0 THEN sp.quantity ELSE sg.quantity END AS quantity,
        sp.maxquantity,
        CASE WHEN sp.left_row_qty > 0 THEN sp.rn ELSE sg.rn END AS rn,
        sp.group_row_count,
        -- 计算当前包剩余容量
        CASE 
            WHEN sp.left_row_qty > 0 THEN 
                CASE WHEN sp.remaining_pack = 0 THEN sp.maxquantity - LEAST(sp.left_row_qty, sp.maxquantity)
                     ELSE sp.remaining_pack - LEAST(sp.left_row_qty, sp.remaining_pack) END
            ELSE sp.remaining_pack - LEAST(sg.quantity, sp.remaining_pack)
        END AS remaining_pack,
        -- 计算当前所属包编号
        CASE 
            WHEN sp.left_row_qty > 0 AND sp.remaining_pack = 0 THEN sp.pack_index + 1
            WHEN sp.left_row_qty = 0 AND sp.remaining_pack = 0 THEN sp.pack_index + 1
            ELSE sp.pack_index
        END AS pack_index,
        -- 计算当前行的有效数量
        CASE 
            WHEN sp.left_row_qty > 0 THEN 
                CASE WHEN sp.remaining_pack = 0 THEN LEAST(sp.left_row_qty, sp.maxquantity)
                     ELSE LEAST(sp.left_row_qty, sp.remaining_pack) END
            ELSE LEAST(sg.quantity, sp.remaining_pack)
        END AS effective_qty,
        -- 计算当前行剩余未分配的数量
        CASE 
            WHEN sp.left_row_qty > 0 THEN sp.left_row_qty - LEAST(sp.left_row_qty, IF(sp.remaining_pack=0, sp.maxquantity, sp.remaining_pack))
            ELSE sg.quantity - LEAST(sg.quantity, sp.remaining_pack)
        END AS left_row_qty
    FROM split_process sp
    LEFT JOIN sorted_goods sg ON sg.name = sp.name AND sg.rn = sp.rn + 1
    WHERE (sp.left_row_qty > 0 OR sg.rn IS NOT NULL)
)
-- 最终输出结果，过滤掉有效数量为0的无效行
SELECT id, name, quantity, maxquantity,
       CONCAT(name, '_part', pack_index) AS tag,
       effective_qty AS effective_quantity
FROM split_process
WHERE effective_qty > 0
ORDER BY name, pack_index, id;

实现说明 #

• 无需自定义函数，仅用标准SQL语法即可实现，适配绝大多数主流关系型数据库
• 自动覆盖单条商品数量超过阈值拆分、多条商品凑满阈值两种场景，输出结果完全匹配需求
• 低版本不支持CTE的数据库可以用存储过程遍历分组数据，逻辑和上述递归逻辑一致即可

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