SQL计算体重历史记录与上一条记录差值错误排查

问题背景 #

有一张存储体重历史记录的表@tblweight，需要计算每条记录与同组（COLUMN1、COLUMN2、COLUMN3、SEX组合）中上一条记录的体重增量，但当前使用子查询的代码计算结果存在错误（如第7行），需排查问题并修正。

测试表及数据 #

DECLARE @tblweight TABLE 
                   (
                       ID NVARCHAR(12), 
                       COLUMN1 INT, 
                       COLUMN2 INT, 
                       COLUMN3 nvarchar(2), 
                       SEX NVARCHAR(1), 
                       [WEEK] INT, 
                       AGE INT, 
                       [WEIGHT] INT
                   )

INSERT INTO @tblweight (ID, COLUMN1, COLUMN2, COLUMN3, SEX, [WEEK], AGE, [WEIGHT]) 
VALUES 
('20211220201H', 20211, 2202, '01', 'H', 1,1,625),
('20211220201H', 20211, 2202, '01', 'H', 1,7,2830),
('20211220201H', 20211, 2202, '01', 'H', 2,14,7180),
('20211220201H', 20211, 2202, '01', 'H', 3,21,13045),
('20211220201H', 20211, 2202, '01', 'H', 4,28,17795),
('20211220202H', 20211, 2202, '01', 'H', 4,29,2716),
('20211220202H', 20211, 2202, '01', 'H', 5,35,24475),
('20211220202H', 20211, 2202, '01', 'H', 5,38,28240),
('20211220202H', 20211, 2202, '01', 'H', 5,40,32650),
('20211220202H', 20211, 2202, '01', 'H', 6,42,14150);

现有错误代码 #

SELECT
    C.ID,
    C.COLUMN1,
    C.COLUMN2,
    C.COLUMN3,
    C.SEX,
    C.[WEEK],
    C.AGE,
    C.[WEIGHT],
    (C.[WEIGHT] - C.WEIGHT_B) AS mrt
FROM
    (SELECT
         A.ID,
         A.COLUMN1,
         A.COLUMN2,
         A.COLUMN3,
         A.SEX,
         A.[WEEK],
         A.AGE,
         A.[WEIGHT],
         (SELECT MAX(B.[WEIGHT]) AS WEIGHT_B
          FROM @tblweight AS B
          WHERE CAST(A.COLUMN1 AS nvarchar(5)) + CAST(A.COLUMN2 AS nvarchar(4)) + CAST(A.COLUMN3 AS nvarchar(2)) + CAST(A.SEX AS nvarchar(1)) 
             = CAST(B.COLUMN1 AS nvarchar(5)) + CAST(B.COLUMN2 AS nvarchar(4)) + CAST(B.COLUMN3 AS nvarchar(2)) + CAST(B.SEX AS nvarchar(1))
            AND B.AGE < A.AGE) AS WEIGHT_B
     FROM
         @tblweight as A) AS C

错误原因分析 #

1. 分组条件逻辑隐患：通过字符串拼接字段判断同组，极易因字段长度、隐式转换导致分组错误。例如COLUMN1=2021+COLUMN2=12202的拼接结果，与COLUMN1=20211+COLUMN2=2202完全一致，会错误将不同组归为同一组。
2. 上一条记录取值错误：子查询用MAX(B.[WEIGHT])获取上一条体重，逻辑完全偏离需求。该写法会取同组内所有年龄小于当前记录的最大体重，而非按年龄排序后的上一条记录的体重，若同组内存在多个年龄更小的记录，增量计算会完全不符合预期。

修复后的代码 #

使用SQL Server的LAG窗口函数，直接按分组和排序规则获取上一条记录的体重，逻辑清晰且高效：

SELECT
    ID,
    COLUMN1,
    COLUMN2,
    COLUMN3,
    SEX,
    [WEEK],
    AGE,
    [WEIGHT],
    ([WEIGHT] - LAG([WEIGHT]) OVER (PARTITION BY COLUMN1, COLUMN2, COLUMN3, SEX ORDER BY AGE)) AS mrt
FROM @tblweight
ORDER BY COLUMN1, COLUMN2, COLUMN3, SEX, AGE;

修复逻辑说明 #

• PARTITION BY COLUMN1, COLUMN2, COLUMN3, SEX：按指定字段分组，确保仅在同组内计算增量。
• ORDER BY AGE：按年龄排序，保证LAG函数获取的是当前记录的上一条（年龄紧邻更小）的体重值。
• LAG([WEIGHT])：直接返回同组内上一条记录的体重，无匹配时返回NULL，此时增量计算结果也为NULL（符合第一条记录无前置记录的逻辑）。

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