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

来源:互联网 时间:1970-01-01

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

先初始化数据:

CREATE TABLE [dbo].[CarData]( [CarID] [int] NULL, [Mileage] [int] NULL, [M_year] [int] NULL, [M_Month] [int] NULL, [M_Day] [int] NULL) ON [PRIMARY]GOINSERT [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;

 执行查询,将得到下面的结果:

1 1 10 2015 1 1 102 1 15 2015 1 2 53 1 15 2015 1 5 04 1 20 2015 1 6 55 1 26 2015 1 9 66 1 30 2015 1 10 47 1 35 2015 1 11 51 2 20 2015 1 5 202 2 22 2015 1 8 23 2 40 2015 1 10 184 2 45 2015 1 11 51 3 50 2015 1 11 50

 

感谢 SOD开发技术群(PWMIS开发框架-SOD会员群 43109929)朋友提供的程序。



相关阅读:
Top