关于SQL的over partition by 开窗语句在分页和统计中的使用总结
sql中有一个非常有用的开窗语句over (partition....),我们可以利用这个over很容易的实现分页和复杂的统计查询,下面我就从什么是over开窗语句说起,然后谈谈分页查询的over语句的实现方法,最后列举一些开窗语句在统计中的常用场景。
要用到的表及数据:
- CREATE TABLE OrderInfo(
- ID INT IDENTITY(1,1) PRIMARY KEY,
- CustomerID INT NULL,
- TotalMoney DECIMAL(18,2) NULL,
- OrderStatus TINYINT NULL DEFAULT 0,
- CreateTime DATETIME DEFAULT GETDATE() NOT NULL
- )
插入数据
- INSERT INTO OrderInfo
- (
- CustomerID,
- TotalMoney,
- OrderStatus,
- CreateTime
- )
- SELECT 1,100,1,'2015-03-21' UNION ALL
- SELECT 2,50,1,'2015-03-22' UNION ALL
- SELECT 1,300,1,'2015-03-23' UNION ALL
- SELECT 3,1000,3,'2015-03-24' UNION ALL
- SELECT 2,20,1,'2015-03-24' UNION ALL
- SELECT 5,50,4,'2015-03-20' UNION ALL
- SELECT 8,600,2,'2015-03-21' UNION ALL
- SELECT 6,80,1,'2015-03-22' UNION ALL
- SELECT 2,70,1,'2015-03-23' UNION ALL
- SELECT 1,40,0,'2015-03-23' UNION ALL
- SELECT 9,20,1,'2015-03-20' UNION ALL
- SELECT 10,100,1,'2015-03-21' UNION ALL
- SELECT 6,99,1,'2015-03-24' UNION ALL
- SELECT 4,78,2,'2015-03-25' UNION ALL
- SELECT 2,100,1,'2015-03-24'
一、什么是over开窗语句
开窗我是按照字面意思理解:就是把要满足条件的数据分成几部分,每一部分数据可以通过像现实中的”窗口“来观察统计这这些数据。比如:下面的语句:
- select * from OrderInfo
- SELECT *,ROW_NUMBER() OVER (PARTITION BY customerID ORDER BY TotalMoney) AS num
- FROM OrderInfo
执行结果如下图:
![](https://www.lanhusoft.com/attached/image/20150324/20150324212925_8400.gif)
从上图可以看出用partition把数据分成了多个窗口,我用红框圈出来了。CustomerID相同的记录被分到同一个窗口,用ROW_NUMBER()为记录编行号。这个编号在每个窗口是都从1开始的,这样就实现了小组内部编号。可能有人会问,如果要统一编行号?这也很简单。
- SELECT *,ROW_NUMBER() OVER (ORDER BY ID) AS num
- FROM OrderInfo
![](https://www.lanhusoft.com/attached/image/20150324/20150324213015_7462.gif)
可以看到这样记录就统一从1开始,依次加1(字段num),这个时候由于ID是连续的,所以ID列和num是相同。如果删除了一条件记录了,这两列就不全相等了,因为ID不再是连续的了。
二、分页查询的over语句的实现方法
上面我们实现了不连续了ID可以通过ROW_NUMBER() OVER (ORDER BY ID)来为记录编出从1开始的连续行号,利用这个我们就可以实现分页查询。比如我们每页展示10条记录,要返回第2页的记录。
- SELECT TOP 10 * FROM (
- SELECT *,ROW_NUMBER() OVER (ORDER BY ID) AS num
- FROM OrderInfo) as t
- WHERE t.num>10
封装一个通用方法:
- DECLARE @PageSize INT
- DECLARE @PageNum INT
- DECLARE @strSql NVARCHAR(4000)
- SET @PageSize=10
- SET @PageNum=2
- SET @strSql='SELECT TOP '+cast(@PageSize AS NVARCHAR(10)) +'* FROM (
- SELECT *,ROW_NUMBER() OVER (ORDER BY ID) AS num
- FROM OrderInfo) as t
- WHERE t.num>'+CAST((@PageNum-1)*@PageSize AS NVARCHAR(1000))
- exec sp_executesql @strSql
三、row_number和partition开窗在统计中的常用场景
3.1、找出每个顾客第一次下单的时间及金额
- SELECT t.CustomerID,t.TotalMoney,t.CreateTime FROM (
- SELECT CustomerID,TotalMoney,CreateTime,ROW_NUMBER() OVER (PARTITION BY customerID ORDER BY createTime) AS num
- FROM OrderInfo) t
- WHERE t.num=1
3.2、找出每个顾客金额最大的订单金额
- SELECT t.CustomerID,t.TotalMoney FROM (
- SELECT CustomerID,TotalMoney,ROW_NUMBER() OVER (PARTITION BY customerID ORDER BY TotalMoney desc) AS num
- FROM OrderInfo) t
- WHERE t.num=1
3.3、统计每一个客户最近下的订单是第几次下的订单
- SELECT t.CustomerID,MAX(t.num) FROM (
- SELECT CustomerID,CreateTime,ROW_NUMBER() OVER (PARTITION BY customerID ORDER BY CreateTime) AS num
- FROM OrderInfo) t
- GROUP BY t.CustomerID
- --其实上面也可以不用patition实现:
- SELECT customerID,COUNT(1) FROM OrderInfo
- GROUP BY customerID
3.4、统计所有客户第3次下单订单信息
- WITH cet AS(
- SELECT *,ROW_NUMBER() OVER (PARTITION BY customerID ORDER BY CreateTime) AS num
- FROM OrderInfo)
- SELECT * FROM cet WHERE num=3
这样可以num传不同的值,就可以统计所有客户第n次下单订单信息。上面的语句用到了SQL SERVER公用表表达式 (CTE)。
3.5、partition by 开窗语句不用group算分组的平均值
开窗语句不只是可以和ROW_NUMBER结合使用、还可以和聚合函数(MAX,AVG,COUNT,MIN,SUM)使用。比如:
- SELECT *,AVG(TotalMoney) OVER (PARTITION BY customerID) AS AvgTotal
- FROM OrderInfo
执行结果:
![](https://www.lanhusoft.com/attached/image/20150324/20150324213132_3400.gif)
这样在原有的基础上加了一列,值为每个顾客订单平均金额。这样就不需要group by就能实现分组统计。
注意:只有ROW_NUMBER+Over的时候才可以不加PARTITION BY,是聚合函数的时候PARTITION是必须有的,不然要报错。
3.6、在使用over等开窗函数时,over里头的分组及排序的执行晚于“where,group by,order by”的执行。
如下代码:
- SELECT CustomerID,CreateTime,ROW_NUMBER() OVER (PARTITION BY customerID ORDER BY CreateTime) AS num
- FROM OrderInfo
- WHERE CustomerID>2
参考资料:https://msdn.microsoft.com/zh-cn/library/ms189461.aspx