按brand分组、bill_id排序，单查询计算worth列行差及均值

需求说明 #

按brand列分组、bill_id列排序，计算worth列的连续行差值（net_diff）与累计平均差值（avg_diff），要求用单条SQL完成。

原始数据 #

brand  bill_id  worth
Moto    1       2550
Samsung 1       3430
Samsung 2       3450
Moto    2       2500
Moto    3       2530

预期输出 #

brand  bill_id  worth  net_diff  avg_diff
Moto    1       2550    0         0
Moto    2       2560    10        5
Moto    3       2540   -20       -5
Samsung 1       3430    0         0
Samsung 2       3450    20        10

解决方案 #

利用窗口函数LAG()和AVG()可以直接实现需求，兼容MySQL 8+、PostgreSQL、SQL Server等支持窗口函数的数据库：

SELECT
    brand,
    bill_id,
    worth,
    -- 计算连续行差值：当前worth减去同组前一行的worth，首行差值设为0
    COALESCE(worth - LAG(worth) OVER (PARTITION BY brand ORDER BY bill_id), 0) AS net_diff,
    -- 计算累计平均差值：同组内从首行到当前行的net_diff平均值，取整数结果
    ROUND(
        AVG(COALESCE(worth - LAG(worth) OVER (PARTITION BY brand ORDER BY bill_id), 0))
        OVER (PARTITION BY brand ORDER BY bill_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    ) AS avg_diff
FROM your_table_name
ORDER BY brand, bill_id;

关键逻辑解释 #

• PARTITION BY brand：限定所有计算仅在同一品牌分组内进行，避免跨品牌干扰
• ORDER BY bill_id：确保分组内按账单ID顺序计算连续行差值
• LAG(worth)：获取同组内上一行的worth值，首行无前置行，用COALESCE将差值设为0
• AVG(...) OVER (...)：计算分组内从第一行到当前行的所有net_diff的平均值，ROUND用于将结果取整（如需保留小数可移除该函数）
• 最终ORDER BY保证输出结果与预期格式一致

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