如何用SQL按累计金额最大值拆分行？实现单页金额不超限

需求背景 #

需要按累计金额最大值拆分数据行，确保单页累计金额不超过MAX_PAGE_AMOUNT。

原始数据 #

+---------+--------+---------+---------+---------------+
|ORDER_ID|DENOM_ID|DENOM_VAL|   AMOUNT|MAX_PAGE_AMOUNT|
+--------+--------+---------+---------+---------------+
|AAAAAAAA|EUR100  |      100|2,800,000|     1,000,000 |
|AAAAAAAA|EUR050  |       50|  700,000|     1,000,000 |
|AAAAAAAA|EUR010  |       10|  150,000|     1,000,000 |
+--------+--------+---------+---------+---------------+

字段说明：

• ORDER_ID：订单编号
• DENOM_ID：货币面额ID
• DENOM_VAL：实际货币面额值
• AMOUNT：该面额货币的总金额
• MAX_PAGE_AMOUNT：报表单页最大金额上限

期望结果 #

生成带页码的拆分结果，单页累计金额不超过MAX_PAGE_AMOUNT：

+-------+--------+--------+---------+---------+----------+---------------+
|PAG_NUM|ORDER_ID|DENOM_ID|DENOM_VAL|   AMOUNT|NEW_AMOUNT|MAX_PAGE_AMOUNT|
+-------+--------+--------+---------+---------+----------+---------------+
|      1|AAAAAAAA|EUR100  |      100|2,800,000| 1,000,000|      1,000,000|
|      2|AAAAAAAA|EUR100  |      100|2,800,000| 1,000,000|      1,000,000|
|      3|AAAAAAAA|EUR100  |      100|2,800,000|   800,000|      1,000,000|
|      3|AAAAAAAA|EUR050  |       50|  700,000|   200,000|      1,000,000|
|      4|AAAAAAAA|EUR050  |       50|  700,000|   300,000|      1,000,000|
|      4|AAAAAAAA|EUR010  |       10|  150,000|   150,000|      1,000,000|
+-------+--------+--------+---------+---------+----------+---------------+

字段说明：

• PAG_NUM：页码
• NEW_AMOUNT：单页分配的金额，保证单页累计不超限

现有SQL问题 #

当前SQL仅按单个面额拆分，未实现跨面额的累计分页：

WITH cte_dat AS (
 SELECT order_id, denom_id, denom_val, amount, max_page_amount
 FROM my_table
),
cte_rec (order_id, denom_id, denom_val, amount, max_page_amount) AS (
 SELECT order_id, denom_id, denom_val, amount, max_page_amount
 FROM cte_dat
 UNION ALL
 SELECT order_id, denom_id, denom_val, amount, amount - max_page_amount AS amount
 FROM cte_rec
 WHERE (amount - max_page_amount) > 0
)
SELECT order_id, denom_id, denom_val, LEAST(amount, max_page_amount) AS amount, max_page_amount
FROM cte_rec
ORDER BY order_id, denom_val DESC, amount DESC

得到的结果未合并跨面额的金额到同一页码，不符合需求。

正确SQL实现方案 #

核心思路是先计算每个面额的累积金额区间，再生成所有需要的页码并匹配对应的金额区间，最后计算单页分配的金额。以下是适配PostgreSQL、MySQL 8.0+、SQL Server等主流数据库的实现：

WITH cte_denoms AS (
    -- 计算每个面额的累计金额区间
    SELECT 
        order_id,
        denom_id,
        denom_val,
        amount,
        max_page_amount,
        SUM(amount) OVER (PARTITION BY order_id ORDER BY denom_val DESC) AS total_cum,
        SUM(amount) OVER (PARTITION BY order_id ORDER BY denom_val DESC) - amount AS prev_cum
    FROM my_table
),
cte_pages AS (
    -- 生成订单所需的所有页码
    SELECT 
        order_id,
        max_page_amount,
        GENERATE_SERIES(1, CEIL(total_amount / max_page_amount)::INT) AS pag_num
    FROM (
        SELECT 
            order_id,
            max_page_amount,
            SUM(amount) AS total_amount
        FROM my_table
        GROUP BY order_id, max_page_amount
    ) t
),
cte_page_ranges AS (
    -- 计算每个页码对应的金额区间
    SELECT 
        order_id,
        pag_num,
        max_page_amount,
        (pag_num - 1) * max_page_amount + 1 AS page_start,
        pag_num * max_page_amount AS page_end
    FROM cte_pages
)
-- 匹配面额与页码，计算单页分配金额
SELECT 
    pr.pag_num,
    d.order_id,
    d.denom_id,
    d.denom_val,
    d.amount,
    LEAST(d.total_cum, pr.page_end) - GREATEST(d.prev_cum, pr.page_start - 1) AS new_amount,
    d.max_page_amount
FROM cte_denoms d
JOIN cte_page_ranges pr ON d.order_id = pr.order_id
WHERE d.prev_cum < pr.page_end AND d.total_cum > pr.page_start - 1
ORDER BY pr.pag_num, d.denom_val DESC;

代码解释 #

1. cte_denoms：计算每个面额的累计金额区间，prev_cum是该面额之前的总累计金额，total_cum是包含当前面额的总累计金额，用于判断该面额属于哪些页码。
2. cte_pages：基于订单总金额和单页上限，生成所有需要的页码。
3. cte_page_ranges：计算每个页码对应的金额范围（比如页码1对应1-1000000，页码2对应1000001-2000000）。
4. 最终关联查询：匹配面额的累计区间和页码的金额区间，计算每个面额在对应页码的分配金额，确保单页累计不超限。

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