如何在SQL Server中筛选与上一条记录存在指定差值的记录

嘿，我来帮你解决这个冷库传感器数据的筛选问题！要找出每5分钟上传的温度里，比上一条低2°F及以上的记录，用SQL Server的窗口函数就能轻松搞定。

核心思路 #

咱们需要拿到每条记录的上一条温度值，然后计算两者的差值，筛选出下降幅度≥2°F的记录。SQL Server 2012及以后版本支持的LAG()窗口函数正好干这个活——它能按时间顺序获取上一行的温度数据，完美匹配你的需求。

完整查询代码 #

假设你的数据表叫SensorReadings，直接用下面的语句就行（记得把表名换成你实际的表名）：

SELECT 
    CurrentTemperature,
    CurrentTime,
    PreviousTemperature,
    TemperatureDrop
FROM (
    SELECT 
        Value AS CurrentTemperature,
        Timestamp AS CurrentTime,
        -- 按时间排序，获取上一条记录的温度
        LAG(Value) OVER (ORDER BY Timestamp) AS PreviousTemperature,
        -- 计算温度下降的幅度（上一条温度 - 当前温度）
        LAG(Value) OVER (ORDER BY Timestamp) - Value AS TemperatureDrop
    FROM SensorReadings
    -- 要是需要限定时间范围，比如最近7天，就加下面这行
    -- WHERE Timestamp >= DATEADD(DAY, -7, GETDATE())
) AS TempCalculations
-- 筛选出下降2°F及以上的记录，同时排除第一条没有上一条数据的记录
WHERE TemperatureDrop >= 2
  AND PreviousTemperature IS NOT NULL
ORDER BY CurrentTime;

细节说明 #

• 如果你的传感器不止一个，需要按传感器分组计算的话，把LAG()里的OVER子句改成OVER (PARTITION BY SensorId ORDER BY Timestamp)，把SensorId换成你表中标识不同传感器的列名就行。
• 子查询里计算的TemperatureDrop是正数，因为用上一条温度减当前温度，这样筛选>=2就代表下降了至少2°F，逻辑更直观。
• 最后的WHERE条件排除了第一条记录，因为它没有上一条数据，PreviousTemperature会是NULL，没必要保留。

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