如何在SQL中按Batch分组计算两行Result差值并填充至同组全行？

需求说明 #

按Batch字段分组，将同组内Run=2的样本Result值减去Run=1的样本Result值，把计算出的差值填充到该组所有行的新列Diff中。

输入示例 #

输出示例 #

方法一：使用窗口函数（推荐，适用于主流数据库） #

如果你的数据库支持窗口函数（如PostgreSQL、MySQL 8.0+、SQL Server、Oracle等），可以直接在分组内提取对应Run值的Result，再计算差值：

SELECT 
    Sample,
    Result,
    Batch,
    Run,
    -- 提取同组Run=2的Result，减去Run=1的Result
    MAX(CASE WHEN Run = 2 THEN Result END) OVER (PARTITION BY Batch) 
    - MAX(CASE WHEN Run = 1 THEN Result END) OVER (PARTITION BY Batch) AS Diff
FROM your_table_name;

方法二：使用自连接（兼容旧版本数据库） #

如果数据库不支持窗口函数，可以通过自连接的方式先计算每个Batch的差值，再关联回原表：

SELECT 
    t.Sample,
    t.Result,
    t.Batch,
    t.Run,
    d.Diff
FROM your_table_name t
-- 关联预先计算好的各Batch差值
JOIN (
    SELECT 
        r2.Batch,
        r2.Result - r1.Result AS Diff
    FROM your_table_name r1
    JOIN your_table_name r2 ON r1.Batch = r2.Batch
    WHERE r1.Run = 1 AND r2.Run = 2
) d ON t.Batch = d.Batch;

注意事项 #

• 确保每个Batch分组内仅存在一条Run=1和一条Run=2的记录，如果有多个对应记录，需要根据业务逻辑调整聚合方式（比如用AVG替代MAX）。
• 替换代码中的your_table_name为实际表名。

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