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

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

先初始化数据:

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;

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

1    1    10    2015    1    1    10
2    1    15    2015    1    2    5
3    1    15    2015    1    5    0
4    1    20    2015    1    6    5
5    1    26    2015    1    9    6
6    1    30    2015    1    10    4
7    1    35    2015    1    11    5
1    2    20    2015    1    5    20
2    2    22    2015    1    8    2
3    2    40    2015    1    10    18
4    2    45    2015    1    11    5
1    3    50    2015    1    11    50

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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏c#开发者

Paging of Large Resultsets in ASP.NET

The paging of a large database resultset in Web applications is a well known pro...

448110
来自专栏「3306 Pai」社区

不用MariaDB/Percona也能查看DDL的进度

使用MariaDB/Percona版本的一个便利之处就是可以及时查看DDL的进度,进而预估DDL耗时。 其实,在官方版本里也是可以查看DDL进度的,认真看手册的...

22500
来自专栏一个会写诗的程序员的博客

JPA 执行update/delete query 需要加上事务

Caused by: org.springframework.dao.InvalidDataAccessApiUsageException: Executing...

15220
来自专栏kwcode

SQL语句帮助大全

--删除约束 Status:字段名 alter table Table_1 drop constraint Status; --添加约束 --Status :字...

356110
来自专栏james大数据架构

你真的会玩SQL吗?和平大使 内连接、外连接

你真的会玩SQL吗?系列目录 你真的会玩SQL吗?之逻辑查询处理阶段 你真的会玩SQL吗?和平大使 内连接、外连接 你真的会玩SQL吗?三范式、数据完整性 你真...

19680
来自专栏杨建荣的学习笔记

关于索引扫描的极速调优实战(第一篇) (r3笔记第81天)

一般在生产环境中,如果某个查询中涉及一个大表,走索引扫描是显然是最值得推荐的方式,但是索引扫描有unique index scan, range scan,sk...

25740
来自专栏ASP.NET MVC5 后台权限管理系统

ASP.NET MVC5+EF6+EasyUI 后台管理系统(41)-组织架构

本节开始我们要实现工作流,此工作流可以和之前的所有章节脱离关系,也可以紧密合并。 我们当初设计的项目解决方案就是可伸缩可以拆离,可共享的项目解决方案。所以我们同...

22270
来自专栏跟着阿笨一起玩NET

SQL将本地图片文件插入到数据库

45120
来自专栏杨建荣的学习笔记

生产环境大型sql语句调优实战第一篇(一) (r2笔记第31天)

在生产环境中有一条sql语句的性能极差,在早晨非高峰时段运行抽取数据,平均要花费40分钟,有时候竟然要跑10个多小时。 sql语句比较长,需要点耐心往下看。我对...

42040
来自专栏互联网开发者交流社区

SQL一次查出相关类容避免长时间占用表(上)

14620

扫码关注云+社区

领取腾讯云代金券