按日期分组并基于最大容量2的桶生成全局序列化SUB_BATCH_REF_ID的SQL实现问询

我明白你在SQL里实现这个分桶需求的困扰——高级语言里用循环和集合很容易搞定，但SQL是面向集合的语言，得换个思路来实现全局序列化的桶编号。

先再明确下你的核心需求，避免理解偏差：

• 按日期（INSERT_DATE）对交易数据分组
• 每个日期组内的交易最多2条划分为一个桶
• 每个桶分配全局唯一的SUB_BATCH_REF_ID，格式为BATCH_REF{全局桶序号}
• 同一日期的交易必须在所属日期的桶内，不能跨日期分桶

你已经尝试用ROW_NUMBER()按日期分区生成行号，这步是对的，接下来我们要基于这个行号，计算每个交易所属的桶，再生成全局唯一的桶序号。

解决方案思路 #

1. 先给每个交易按日期分区生成行号（你已经完成这步）
2. 计算每个日期组需要多少个桶（用CEIL(交易数/2)，因为每个桶最多2条）
3. 累计每个日期之前所有日期的桶总数，得到当前日期的起始全局桶号
4. 结合当前交易在日期内的桶位置，算出全局桶序号，最终拼接成SUB_BATCH_REF_ID

完整SQL代码 #

WITH date_buckets AS (
    -- 第一步：给每个交易按日期分区生成行号，同时统计当前日期总交易数
    SELECT 
        T.*,
        ROW_NUMBER() OVER (PARTITION BY TRUNC(INSERT_DATE) ORDER BY TRANSACTION_ID) AS rn,
        COUNT(*) OVER (PARTITION BY TRUNC(INSERT_DATE)) AS total_trans
    FROM TRANSACTION T 
    WHERE BATCH_REF = 'XYZ'
),
date_bucket_metadata AS (
    -- 第二步：计算每个日期的桶数量，以及累计前置日期的桶总数（当前日期的起始全局桶号）
    SELECT 
        TRUNC(INSERT_DATE) AS trans_date,
        CEIL(total_trans / 2) AS buckets_needed,
        -- 累计前面所有日期的桶数，第一个日期的前置总数为0
        COALESCE(SUM(CEIL(total_trans / 2)) OVER (ORDER BY TRUNC(INSERT_DATE) ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS prev_total_buckets
    FROM date_buckets
    GROUP BY TRUNC(INSERT_DATE), total_trans
)
-- 第三步：关联计算每个交易的全局桶序号，生成最终的SUB_BATCH_REF_ID
SELECT 
    db.*,
    CONCAT(db.BATCH_REF, dbc.prev_total_buckets + CEIL(db.rn / 2)) AS SUB_BATCH_REF_ID
FROM date_buckets db
JOIN date_bucket_metadata dbc ON TRUNC(db.INSERT_DATE) = dbc.trans_date
ORDER BY db.INSERT_DATE, db.TRANSACTION_ID;

代码解释 #

• date_buckets CTE：给每个交易分配日期内的行号rn，同时算出当前日期的总交易数total_trans，为后续计算桶数量做准备
• date_bucket_metadata CTE：用CEIL(total_trans/2)得到当前日期需要的桶数，再通过SUM() OVER()窗口函数累计前置日期的所有桶数，得到当前日期的起始全局桶号prev_total_buckets
• 最终查询：通过CEIL(rn/2)得到交易在当前日期内的桶位置，加上起始全局桶号就是全局唯一的桶序号，和BATCH_REF拼接后得到符合要求的SUB_BATCH_REF_ID

举个实际例子：

• 2024-01-01有3条交易：行号1、2、3 → 桶1（行1-2）、桶2（行3）
• 2024-01-02有2条交易：行号1、2 → 桶3（行1-2）
  对应的SUB_BATCH_REF_ID就是XYZ1、XYZ1、XYZ2、XYZ3、XYZ3，完全匹配你伪代码里的全局序列化逻辑。

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