SQL实现分组计算balance与avg列（基于前序记录）

原始数据表结构及数据：

name    seq side    price   qnt
groupA  1   B       30      100
groupA  2   B       36      200
groupA  3   S       23      300
groupA  4   B       30      100
groupA  5   B       54      400
                
groupB  1   B       70      300
groupB  2   B       84      300
groupB  3   B       74      600
groupB  4   S       90      100

计算列规则 #

balance列 #

• 每组（按name分组）首行（seq=1）取值等于该行的qnt
• 后续行：若side="B"，则为前一行balance + 当前qnt；若side="S"，则为前一行balance - 当前qnt

avg列 #

• 每组首行（seq=1）取值等于该行的price
• 后续行：若side="B"，则按公式 ((price * qnt) + (前一行balance * 前一行avg)) / (qnt + 前一行balance) 计算；若side="S"，则直接继承前一行avg

示例计算（groupA第二行） #

balance: 100 + 200 = 300
avg: ((36 * 200) + (100 * 30)) / (200 + 100) = 34

期望输出 #

name    seq side    price   qnt     balance avg
groupA  1   B         30    100     100     30
groupA  2   B         36    200     300     34
groupA  3   S         23    300     0       34
groupA  4   B         30    100     100     30
groupA  5   B         54    400     500     49.2
                            
groupB  1   B         70    300     300     70
groupB  2   B         84    300     600     77
groupB  3   B         74    600     1200    75.5
groupB  4   S         90    100     1100    75.5

实现代码（递归CTE） #

由于计算依赖前序记录的结果，使用递归CTE可以完美解决这个需求，代码如下：

WITH RECURSIVE calculated AS (
    -- 初始化：取出每组的第一条记录作为起始值
    SELECT 
        name, seq, side, price, qnt,
        qnt AS balance,
        price AS avg
    FROM your_table
    WHERE seq = 1
    
    UNION ALL
    
    -- 递归计算后续行
    SELECT 
        t.name, t.seq, t.side, t.price, t.qnt,
        -- 计算balance
        CASE 
            WHEN t.side = 'B' THEN c.balance + t.qnt
            ELSE c.balance - t.qnt
        END AS balance,
        -- 计算avg
        CASE 
            WHEN t.side = 'B' THEN ((t.price * t.qnt) + (c.balance * c.avg)) / (t.qnt + c.balance)
            ELSE c.avg
        END AS avg
    FROM your_table t
    JOIN calculated c 
        ON t.name = c.name 
        AND t.seq = c.seq + 1
)
-- 按分组和序号排序输出所有记录
SELECT * FROM calculated ORDER BY name, seq;

代码说明 #

1. 递归CTE分为两部分：
   • 基础部分：筛选出每组的第一条记录，直接初始化balance为qnt，avg为price
   • 递归部分：通过自连接关联当前组的上一条记录（seq = c.seq +1），按照规则计算当前行的balance和avg
2. 最终结果按name和seq排序，确保输出顺序与原始数据一致

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