关于SQL的over partition by 开窗语句在分页和统计中的使用总结

sql中有一个非常有用的开窗语句over (partition....)，我们可以利用这个over很容易的实现分页和复杂的统计查询，下面我就从什么是over开窗语句说起，然后谈谈分页查询的over语句的实现方法，最后列举一些开窗语句在统计中的常用场景。

要用到的表及数据：

1. CREATE TABLE OrderInfo(
2. ID INT IDENTITY(1,1) PRIMARY KEY,
3. CustomerID INT NULL,
4. TotalMoney DECIMAL(18,2) NULL,
5. OrderStatus TINYINT NULL DEFAULT 0,
6. CreateTime DATETIME DEFAULT GETDATE() NOT NULL
7. )

插入数据

1. INSERT INTO OrderInfo
2. (
3. CustomerID,
4. TotalMoney,
5. OrderStatus,
6. CreateTime
7. )
8. SELECT 1,100,1,'2015-03-21' UNION ALL
9. SELECT 2,50,1,'2015-03-22' UNION ALL
10. SELECT 1,300,1,'2015-03-23' UNION ALL
11. SELECT 3,1000,3,'2015-03-24' UNION ALL
12. SELECT 2,20,1,'2015-03-24' UNION ALL
13. SELECT 5,50,4,'2015-03-20' UNION ALL
14. SELECT 8,600,2,'2015-03-21' UNION ALL
15. SELECT 6,80,1,'2015-03-22' UNION ALL
16. SELECT 2,70,1,'2015-03-23' UNION ALL
17. SELECT 1,40,0,'2015-03-23' UNION ALL
18. SELECT 9,20,1,'2015-03-20' UNION ALL
19. SELECT 10,100,1,'2015-03-21' UNION ALL
20. SELECT 6,99,1,'2015-03-24' UNION ALL
21. SELECT 4,78,2,'2015-03-25' UNION ALL
22. SELECT 2,100,1,'2015-03-24'

一、什么是over开窗语句

开窗我是按照字面意思理解：就是把要满足条件的数据分成几部分，每一部分数据可以通过像现实中的”窗口“来观察统计这这些数据。比如：下面的语句：

1. select * from OrderInfo

没有名over开窗的就可以这些数据理解成被分为一个窗口。而下面的语句：

1. SELECT *,ROW_NUMBER() OVER (PARTITION BY customerID ORDER BY TotalMoney) AS num
2. FROM OrderInfo

执行结果如下图：

从上图可以看出用partition把数据分成了多个窗口，我用红框圈出来了。CustomerID相同的记录被分到同一个窗口，用ROW_NUMBER()为记录编行号。这个编号在每个窗口是都从1开始的，这样就实现了小组内部编号。可能有人会问，如果要统一编行号？这也很简单。

1. SELECT *,ROW_NUMBER() OVER (ORDER BY ID) AS num
2. FROM OrderInfo

 

可以看到这样记录就统一从1开始，依次加1（字段num）,这个时候由于ID是连续的，所以ID列和num是相同。如果删除了一条件记录了，这两列就不全相等了，因为ID不再是连续的了。

二、分页查询的over语句的实现方法

上面我们实现了不连续了ID可以通过ROW_NUMBER() OVER (ORDER BY ID)来为记录编出从1开始的连续行号，利用这个我们就可以实现分页查询。比如我们每页展示10条记录，要返回第2页的记录。

1. SELECT TOP 10 * FROM (
2. SELECT *,ROW_NUMBER() OVER (ORDER BY ID) AS num
3. FROM OrderInfo) as t
4. WHERE t.num>10

封装一个通用方法：

1. DECLARE @PageSize INT
2. DECLARE @PageNum INT
3. DECLARE @strSql NVARCHAR(4000)
4. SET @PageSize=10
5. SET @PageNum=2
6. SET @strSql='SELECT TOP '+cast(@PageSize AS NVARCHAR(10)) +'* FROM (
7. SELECT *,ROW_NUMBER() OVER (ORDER BY ID) AS num
8. FROM OrderInfo) as t
9. WHERE t.num>'+CAST((@PageNum-1)*@PageSize AS NVARCHAR(1000))
10. exec sp_executesql @strSql

三、row_number和partition开窗在统计中的常用场景

3.1、找出每个顾客第一次下单的时间及金额

1. SELECT t.CustomerID,t.TotalMoney,t.CreateTime FROM (
2. SELECT CustomerID,TotalMoney,CreateTime,ROW_NUMBER() OVER (PARTITION BY customerID ORDER BY createTime) AS num
3. FROM OrderInfo) t
4. WHERE t.num=1

3.2、找出每个顾客金额最大的订单金额

1. SELECT t.CustomerID,t.TotalMoney FROM (
2. SELECT CustomerID,TotalMoney,ROW_NUMBER() OVER (PARTITION BY customerID ORDER BY TotalMoney desc) AS num
3. FROM OrderInfo) t
4. WHERE t.num=1

3.3、统计每一个客户最近下的订单是第几次下的订单

1. SELECT t.CustomerID,MAX(t.num) FROM (
2. SELECT CustomerID,CreateTime,ROW_NUMBER() OVER (PARTITION BY customerID ORDER BY CreateTime) AS num
3. FROM OrderInfo) t
4. GROUP BY t.CustomerID
5. --其实上面也可以不用patition实现：
6. SELECT customerID,COUNT(1) FROM OrderInfo
7. GROUP BY customerID

3.4、统计所有客户第3次下单订单信息

1. WITH cet AS(
2. SELECT *,ROW_NUMBER() OVER (PARTITION BY customerID ORDER BY CreateTime) AS num
3. FROM OrderInfo)
4. SELECT * FROM cet WHERE num=3

 

这样可以num传不同的值，就可以统计所有客户第n次下单订单信息。上面的语句用到了SQL SERVER公用表表达式 (CTE)。

3.5、partition by 开窗语句不用group算分组的平均值

开窗语句不只是可以和ROW_NUMBER结合使用、还可以和聚合函数（MAX,AVG,COUNT,MIN,SUM）使用。比如：

1. SELECT *,AVG(TotalMoney) OVER (PARTITION BY customerID) AS AvgTotal
2. FROM OrderInfo

执行结果：

这样在原有的基础上加了一列，值为每个顾客订单平均金额。这样就不需要group by就能实现分组统计。

注意：只有ROW_NUMBER+Over的时候才可以不加PARTITION BY，是聚合函数的时候PARTITION是必须有的，不然要报错。

3.6、在使用over等开窗函数时，over里头的分组及排序的执行晚于“where，group by，order by”的执行。 

如下代码：

1. SELECT CustomerID,CreateTime,ROW_NUMBER() OVER (PARTITION BY customerID ORDER BY CreateTime) AS num
2. FROM OrderInfo
3. WHERE CustomerID>2

以上代码是先执行where子句，执行完后，再给每一条记录进行编号。

参考资料：https://msdn.microsoft.com/zh-cn/library/ms189461.aspx