TSQL分组最大值问题：LEAD函数分组最后一行异常处理

我完全懂你的困扰——当用LEAD()计算分组内连续值的中点时，分组的最后一行因为没有下一行数据，要么返回NULL，要么得到不符合预期的错误结果，你希望在每个分组的最大UserID行停止这个计算。

问题根源 #

LEAD()窗口函数会为每行返回分组内排序后的下一行数据，当处理到分组最后一行时，没有下一行可以取，所以会返回NULL。直接用(Value + LEAD(Value))/2计算的话，结果会是NULL（或者如果你的数据库对NULL运算有特殊处理，可能得到错误值）。

解决方案：用CASE语句判断是否为最后一行 #

这里提供两种可靠的实现方式，你可以根据自己的场景选择：

方法1：直接判断LEAD()的结果是否存在

这种方法最简单，通过检查LEAD(UserID)是否为NULL，来确定当前行是不是分组的最后一行：

SELECT 
    GroupID,
    UserID,
    Value,
    -- 只有当下一行存在时才计算中点，否则返回NULL（或你需要的默认值）
    CASE 
        WHEN LEAD(UserID) OVER (PARTITION BY GroupID ORDER BY UserID) IS NOT NULL 
        THEN (Value + LEAD(Value) OVER (PARTITION BY GroupID ORDER BY UserID)) / 2.0
        ELSE NULL 
    END AS Midpoint
FROM your_table
ORDER BY GroupID, UserID;

注意：除以2.0是为了确保结果是浮点型，避免整数除法导致的精度丢失（比如两个整数相加除以2得到整数，而不是小数）。

方法2：用行号和总行数判断

如果你的UserID可能不连续，但你需要严格按排序后的最后一行来判断，可以先给每行标记行号，同时计算分组的总行数：

WITH RankedData AS (
    SELECT 
        GroupID,
        UserID,
        Value,
        -- 给每个分组内的行按UserID排序编号
        ROW_NUMBER() OVER (PARTITION BY GroupID ORDER BY UserID) AS RowNum,
        -- 计算每个分组的总行数
        COUNT(*) OVER (PARTITION BY GroupID) AS TotalRows
    FROM your_table
)
SELECT 
    GroupID,
    UserID,
    Value,
    -- 行号小于总行数时，说明不是最后一行，计算中点
    CASE 
        WHEN RowNum < TotalRows 
        THEN (Value + LEAD(Value) OVER (PARTITION BY GroupID ORDER BY UserID)) / 2.0
        ELSE NULL
    END AS Midpoint
FROM RankedData
ORDER BY GroupID, UserID;

效果说明 #

两种方法都会在分组的最后一行（比如GroupID=1且UserID=8的行）返回NULL（或者你可以把ELSE改成你需要的默认值，比如空字符串，但一般NULL更能明确表示“无有效中点”），其他行正常计算当前值和下一行值的中点。

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