SQL使用窗口函数计算同VIN分组下相邻value正差值的实现方法

修改后的SQL语句 #

SELECT TOP(200)
A.Value as value,
CASE 
    WHEN A.Value - LAG(A.Value, 1) OVER (PARTITION BY VIN ORDER BY readDate) > 0 
    THEN A.Value - LAG(A.Value, 1) OVER (PARTITION BY VIN ORDER BY readDate)
    ELSE NULL 
END as difference,
readDate,
symbol,
description,
VIN
FROM Table1 as V
JOIN Table2 ON Table2.VX_Id=V.VX_Id
JOIN Table3 ON Table3.FX_Id =Table2.FX_Id
JOIN Table4 on Table3.FX_Id=Table4.FX_Id
JOIN Table5 as A on A.RX_Id=Table4.RX_Id
WHERE symbol='SV'
ORDER BY VIN,readDate

改动说明 #

• 给LAG窗口函数新增了PARTITION BY VIN参数：窗口函数会按VIN分组计算，每个VIN分组内的第一行取前一行值的结果为NULL，自动过滤跨VIN的差值计算结果。
• 新增CASE判断逻辑：仅当计算得到的差值为正数时保留结果，差值为负、或者跨VIN分组无前置值的场景统一返回NULL。

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