SQLSERVER 2012计算上一条，下一条数据的函数

时间：2021-06-19 15:52:42

实际需求很普遍，比如求销售数据的每天与头一天的销售增长量。这里用一个汽车行驶数据来做例子：

先初始化数据：

CREATE TABLE [dbo].[CarData](

    [CarID] [int] NULL,

    [Mileage] [int] NULL,

    [M_year] [int] NULL,

    [M_Month] [int] NULL,

    [M_Day] [int] NULL

) ON [PRIMARY]

GO

INSERT [dbo].[CarData] ([CarID], [Mileage], [M_year], [M_Month], [M_Day]) VALUES (1, 10, 2015, 1, 1)

INSERT [dbo].[CarData] ([CarID], [Mileage], [M_year], [M_Month], [M_Day]) VALUES (1, 15, 2015, 1, 2)

INSERT [dbo].[CarData] ([CarID], [Mileage], [M_year], [M_Month], [M_Day]) VALUES (1, 15, 2015, 1, 5)

INSERT [dbo].[CarData] ([CarID], [Mileage], [M_year], [M_Month], [M_Day]) VALUES (1, 20, 2015, 1, 6)

INSERT [dbo].[CarData] ([CarID], [Mileage], [M_year], [M_Month], [M_Day]) VALUES (1, 26, 2015, 1, 9)

INSERT [dbo].[CarData] ([CarID], [Mileage], [M_year], [M_Month], [M_Day]) VALUES (1, 30, 2015, 1, 10)

INSERT [dbo].[CarData] ([CarID], [Mileage], [M_year], [M_Month], [M_Day]) VALUES (1, 35, 2015, 1, 11)

INSERT [dbo].[CarData] ([CarID], [Mileage], [M_year], [M_Month], [M_Day]) VALUES (2, 20, 2015, 1, 5)

INSERT [dbo].[CarData] ([CarID], [Mileage], [M_year], [M_Month], [M_Day]) VALUES (2, 22, 2015, 1, 8)

INSERT [dbo].[CarData] ([CarID], [Mileage], [M_year], [M_Month], [M_Day]) VALUES (2, 40, 2015, 1, 10)

INSERT [dbo].[CarData] ([CarID], [Mileage], [M_year], [M_Month], [M_Day]) VALUES (2, 45, 2015, 1, 11)

INSERT [dbo].[CarData] ([CarID], [Mileage], [M_year], [M_Month], [M_Day]) VALUES (3, 50, 2015, 1, 11)

然后，使用下面的SQL来统计：

WITH ONE AS(

    SELECT ROW_NUMBER() OVER(PARTITION BY CarId ORDER BY CarId, M_Year, M_Month, M_Day) AS NodeId

          ,C.CarId

          ,C.Mileage

          ,C.M_Year

          ,C.M_Month

          ,C.M_Day

    FROM  dbo.CarData AS C

)

SELECT *

      ,COALESCE(One.Mileage - LAG(One.Mileage) over(PARTITION BY CarId order by One.NodeId),0) AS '增量'

FROM ONE

这里使用LAG函数来计算。

注意，这个查询只有在SQLSERVER 2012以上才支持，2008不支持，所以采用下面的方法实现：

WITH TWO AS(

SELECT ROW_NUMBER() OVER(PARTITION BY CarId ORDER BY CarId, M_Year, M_Month, M_Day) AS NodeId

          ,C.CarId

          ,C.Mileage

          ,C.M_Year

          ,C.M_Month

          ,C.M_Day

    FROM [dbo].[CarData] AS C

)

SELECT A.*

     , A.Mileage -  COALESCE(B.NextMileage, 0) AS '增量'

FROM TWO AS A

    OUTER APPLY (SELECT Mileage AS NextMileage FROM TWO AS B WHERE B.NodeId = A.NodeId - 1 AND B.CarId = A.CarId ) AS B;

执行查询，将得到下面的结果：





感谢 SOD开发技术群（PWMIS开发框架-SOD会员群 43109929）朋友提供的程序。
相关文章