TSQL 如何计算用户最早与最新记录行的得分差值并输出汇总结果

实现逻辑 #

通过窗口函数对用户分组，分别获取每个用户的最早记录得分、最新记录得分及最新记录时间，计算差值后去重输出单行结果，代码兼容SQL Server 2012及以上版本。

完整实现代码 #

假设你的表名为UserScores，可根据实际表名替换：

WITH RankedScores AS (
    SELECT 
        [User],
        Score,
        [Timestamp],
        -- 标记每个用户的最新记录行
        ROW_NUMBER() OVER (PARTITION BY [User] ORDER BY [Timestamp] DESC) AS rn_latest,
        -- 获取对应用户的最早记录得分
        FIRST_VALUE(Score) OVER (PARTITION BY [User] ORDER BY [Timestamp] ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_score,
        -- 获取对应用户的最新记录得分
        FIRST_VALUE(Score) OVER (PARTITION BY [User] ORDER BY [Timestamp] DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS latest_score,
        -- 获取对应用户的最新记录时间
        FIRST_VALUE([Timestamp]) OVER (PARTITION BY [User] ORDER BY [Timestamp] DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS latest_time
    FROM UserScores
)
SELECT 
    [User],
    latest_score AS CurrentScore,
    latest_score - first_score AS Change,
    latest_time AS [Timestamp]
FROM RankedScores
WHERE rn_latest = 1
ORDER BY [User];

注意：User和Timestamp属于TSQL保留关键字，查询时需要加方括号转义避免语法报错。

低版本兼容方案（SQL Server 2008及以下可用） #

如果数据库版本不支持FIRST_VALUE窗口函数，可使用CROSS APPLY关联查询实现：

SELECT 
    u.[User],
    latest.Score AS CurrentScore,
    latest.Score - earliest.Score AS Change,
    latest.[Timestamp]
FROM (SELECT DISTINCT [User] FROM UserScores) u
CROSS APPLY (
    SELECT TOP 1 Score, [Timestamp] 
    FROM UserScores 
    WHERE [User] = u.[User] 
    ORDER BY [Timestamp] DESC
) latest
CROSS APPLY (
    SELECT TOP 1 Score 
    FROM UserScores 
    WHERE [User] = u.[User] 
    ORDER BY [Timestamp] ASC
) earliest
ORDER BY u.[User];

以上两种方案均可输出你需要的汇总结果，和给出的期望输出完全匹配。

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