Oracle实现分组排序逐行累计相减 关联表填充首行值

涉及表结构 #

• 表1：主键为ID、Item，存储业务流水数据，字段包含ID、Item、Qty1、Qty2、Date，示例数据如下：

ID | Item | Qty1 | Qty2 | Date 
1  | I1   | 10   | 100  | 5-Jul
1  | I1   | 20   | 90   | 6-Jul
1  | I1   | 15   | 70   | 7-Jul 
2  | I2   | 10   | 50   | 5-Jul 
2  | I2   | 50   | 40   | 6-Jul 
2  | I2   | 10   | -10  | 7-Jul

• 表2：存储每个ID、Item组合的初始数量，字段包含ID、Item、Qty，示例数据如下：

| ID | Item | Qty | 
| 1  | I1   |100  |
| 2  | I2   |50   |

计算规则 #

• 表1数据按ID、Item分组，组内按Date字段升序排序
• 每个分组首行的Qty2字段，取表2中对应ID、Item匹配的初始Qty值填充
• 分组内除最后一行外，每行计算Qty2 - Qty1的结果，赋值给紧邻下一行的Qty2字段
• 分组最后一行无需生成下一行的递推值

实现SQL #

该递推逻辑本质是初始值逐行累计扣减之前行的Qty1，直接用Oracle窗口函数即可实现，不需要写递归逻辑，性能更优：

SELECT 
    t1.ID,
    t1.Item,
    t1.Qty1,
    t2.init_qty - NVL(
        SUM(t1.Qty1) OVER (
            PARTITION BY t1.ID, t1.Item
            ORDER BY t1.Date
            -- 窗口范围：分组首行到当前行的上一行
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ), 0
    ) AS calc_qty2,
    t1.Date
FROM table1 t1
LEFT JOIN (
    SELECT ID, Item, Qty AS init_qty FROM table2
) t2 ON t1.ID = t2.ID AND t1.Item = t2.Item
ORDER BY t1.ID, t1.Item, t1.Date;

查询结果验证 #

执行上述SQL后返回结果完全符合规则要求：

ID | Item | Qty1 | calc_qty2 | Date 
1  | I1   | 10   | 100       | 5-Jul
1  | I1   | 20   | 90        | 6-Jul
1  | I1   | 15   | 70        | 7-Jul 
2  | I2   | 10   | 50        | 5-Jul 
2  | I2   | 50   | 40        | 6-Jul 
2  | I2   | 10   | -10       | 7-Jul

可选：回写计算结果到原表 #

如果需要把计算得到的Qty2更新到表1的原字段中，可以使用MERGE语句实现：

MERGE INTO table1 tgt
USING (
    SELECT 
        t1.ID,
        t1.Item,
        t1.Date,
        t2.init_qty - NVL(
            SUM(t1.Qty1) OVER (
                PARTITION BY t1.ID, t1.Item
                ORDER BY t1.Date
                ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
            ), 0
        ) AS new_qty2
    FROM table1 t1
    LEFT JOIN (SELECT ID, Item, Qty AS init_qty FROM table2) t2 
        ON t1.ID = t2.ID AND t1.Item = t2.Item
) src
ON (tgt.ID = src.ID AND tgt.Item = src.Item AND tgt.Date = src.Date)
WHEN MATCHED THEN UPDATE SET tgt.Qty2 = src.new_qty2;

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