SqlServer 案例:已有汽车每日行驶里程数据,计算其每日增量

需求说明

某公司某项业务,需要获得用户每日行车里程数。已知能获得该车每日提交的总里程数,如何通过 T-SQL 来获得其每日增量里程?


解决方案

  1. 首选需要对数据进行编号,利用开窗函数 OVER() 实现隔离(PARTITION)并排序(ORDER BY)车辆数据;
  2. 利用公共表表达式(CTE)将上一步虚拟表缓存在本次会话的内存之中;
  3. 外部查询语句查询 CTE,将所获记录与前一条记录的里程数进行相减,获得的即为结果。

数据准备

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)

方法一(限 MSSQL2012+)

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),One.Mileage) AS '增量'
FROM ONE

结果截图:

SqlServer 案例:已有汽车每日行驶里程数据,计算其每日增量


方法二

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;

结果截图:

SqlServer 案例:已有汽车每日行驶里程数据,计算其每日增量

上一篇:[HDOJ5950]Recursive sequence(递推,二项展开,矩阵快速幂)


下一篇:linux 命令 more