如何在SQL中对TBL_TXN表记录按指定规则进行批量处理

数据表信息 #

表名：TBL_TXN

查询需求 #

需将交易记录按以下规则分批输出：

• 每批最多包含2条交易记录
• 每批所有交易的商品总数不超过4

实现代码（以MySQL为例） #

假设数据表包含TXN_ID（交易ID）、ITEM_COUNT（单条交易商品数）字段，可通过窗口函数实现分批逻辑：

WITH txn_with_running AS (
    SELECT
        TXN_ID,
        ITEM_COUNT,
        -- 累计商品数
        SUM(ITEM_COUNT) OVER (ORDER BY TXN_ID) AS running_total_items,
        -- 交易记录序号
        ROW_NUMBER() OVER (ORDER BY TXN_ID) AS row_num
    FROM TBL_TXN
),
batch_assign AS (
    SELECT
        TXN_ID,
        ITEM_COUNT,
        running_total_items,
        row_num,
        -- 计算批次ID：满足任一条件则开启新批次
        FLOOR(
            SUM(
                CASE
                    -- 前一批累计商品数+当前商品数超过4，或者当前是第2/4...条记录
                    WHEN (running_total_items - ITEM_COUNT) + ITEM_COUNT > 4 OR row_num % 2 = 0 THEN 1
                    ELSE 0
                END
            ) OVER (ORDER BY TXN_ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
        ) + 1 AS batch_id
    FROM txn_with_running
)
SELECT
    batch_id,
    TXN_ID,
    ITEM_COUNT
FROM batch_assign
ORDER BY batch_id, TXN_ID;

逻辑说明 #

1. txn_with_running：为每条交易记录生成序号，并计算从第一条到当前记录的累计商品总数。
2. batch_assign：通过判断两个条件来分配批次：
   • 若当前记录加入后，累计商品数超过4，则开启新批次
   • 若当前记录是第2、4...条（即批次内已达2条），则开启新批次
3. 最终结果按批次ID排序，输出每批的交易记录。

如果使用其他数据库（如PostgreSQL、SQL Server），仅需调整窗口函数的语法细节，核心逻辑保持一致。

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