开篇介绍
SQL Server 窗体函数主要用来处理由 OVER 子句定义的行集, 主要用来分析和处理
- Running totals
- Moving averages
- Gaps and islands
初步了解 OVER 关键字
先看一个简单的应用 - 按照订单额从高到低对订单表信息做一个排名。
USE TSQL2012
GO
SELECT orderid,
orderdate,
val,
RANK() OVER(ORDER BY val DESC) AS rnk
FROM Sales.OrderValues
ORDER BY rnk 查询结果 -
OrderID OrderDate Val Rnk
10865 2008-02-02 00:00:00.000 16387.50 1
10981 2008-03-27 00:00:00.000 15810.00 2
11030 2008-04-17 00:00:00.000 12615.05 3
10889 2008-02-16 00:00:00.000 11380.00 4
10417 2007-01-16 00:00:00.000 11188.40 5
10817 2008-01-06 00:00:00.000 10952.85 6
10897 2008-02-19 00:00:00.000 10835.24 7
10479 2007-03-19 00:00:00.000 10495.60 8
OVER 的作用
OVER 的作用就是定义了行集窗体，这个窗体的集合为当前行提供了一个上下文环境。RANK 函数根据指定的集合以及行集的排序顺序计算出当前行的排名，以 Rnk = 5 为例，排序后这条数据的前面有4条数据，所以它的排名就是 4 + 1 = 5。
再总结简单一点就是：OVER 定义了一个行的集合，它是一个函数，每次向当前行返回一个唯一的值。如何返回? 在这个例子中就使用 RANK 函数返回了当前行的一个排名。
与OVER搭配使用的其它函数
聚合的函数 - SUM, COUNT, MIN, MAX
排名的函数 - RANK， DENSE_RANK, ROW_NUMBER, NTILE
Distribution 函数 - PERCENT_RANK, CUME_DIST, PERCENTILE_CONT, PERCENTILE_DISC
Offset 函数 - LAG, LEAD, FIRST_VALUE, LAST_VALUE
SQL Server Window Function 的应用
窗体函数的应用非常广泛 - 像分页、去重、分组的基础上返回 Top N 的行、计算 Running Totals、Gaps and islands、百分率, Hierarchy 排序、Pivoting 等等。
使用 Windows 窗体函数的原因一方面是因为 SQL Server 的优化器不够完美，尽管足够强大，但是并不会涵盖所有的优化规则。
第二, 在执行计划的选择上，SQL Server 并不会真正执行所有有可能的计划来获取一个最优的选择，对于 SQL 本身这种指令性语言的解析和优化优化器只能说是在最短时间里尽量做到足够好，选择一个好的执行计划。而 Window 窗体函数本身就经历过了很好的调优处理，所以性能会更加好一些。
集的整体与顺序的概念
对于集的理解，两个方面“整体”与“排序。把集当作一个整体来看，不要关心集中的某一条数据，排序、集是没有顺序的。相对于迭代性的像游标操作，首先遍历的是一条一条的数据，其次是基于一定的顺序的情况下来执行遍历操作的。迭代行的思考方式就是一次一条，有某种顺序，而窗体函数思考方式是整体的，无序的。
用迭代方式来解释窗体函数工作的过程
- 首先通过 OVER 定义了一个根据 val 排序的集。
- 遍历查询中的每一行时, 用当前遍历到的行到这个集中从头开始去比较。
- 如果当前行是这个集中的第一行, 那么就返回一个1。
- 如果当前行的 val 等于这个集中之前的某一个val的值，就返回找到的排名。
- 否则就返回在之后找到的这个排名。
但是实际上并不是这样来工作的，实际上窗体函数逻辑性的为 SELECT 查询结果中的每一行都创建了一个独立的窗体环境，默认没有任何限制和规则的窗体。在这个例子中，只是定义了一个排序规则，每一行对应的窗体都由 SELECT 查询结果集的所有行组成，并且所有窗体此时也都共存.对于查询中的每一行, RANK()的计算就是去当前行关联的窗体中找出比当前行 val 值大的集中所有行的条数然后加1。
我的疑惑是如果为每一行都创建一个窗体环境，那需要多大的开销啊？
使用窗体函数 (Window Function) 与 Group, 子查询语句的比较
对于Group来说，SELECT语句中的列必须是Group子句中出现的列或者是聚合列，那么如果需要同时在 SELECT 语句中查询其它的非 Group 或者非聚合列, 那么就需要额外的子查询。
可以非常直观的通过几个例子来比较使用窗体函数和 Group, 子查询解决实际问题的难易程度。
比如要查询每个客户的每个订单的值，以及这个订单于这个订单客户的所有订单总和比，以及这个订单与这个客户所有订单平均值的差。
分析以下代码，发现没有办法直接在一个 SELECT 语句中完成查询。
SELECT custid,
orderid,
val
FROM Sales.OrderValues
ORDER BY custid,
orderid
SELECT custid,
SUM(val) AS TotalVal,
AVG(val) AS AvgVal
FROM Sales.OrderValues
GROUP BY custid 这里不得不使用关联两个查询，因为没有办法在一个Group查询中同时显示 Detail和汇总的信息，Order 是细节，Val 总和和平均值是基于 Customer ID 的汇总。
查询如下 -
WITH Aggregates AS
(
SELECT custid,
SUM(val) AS sumval,
AVG(val) AS avgval
FROM Sales.OrderValues
GROUP BY custid
)
SELECT O.orderid,
O.custid,
O.val,
CAST(100. * O.val / A.sumval AS NUMERIC(5, 2)) AS pctcust,
O.val - A.avgval AS diffcust
FROM Sales.OrderValues AS O
JOIN Aggregates AS A
ON O.custid = A.custid;结果显示如下
/**---------------------------------------------------------
10835 1 845.80 19.79 133.633334
10952 1 471.20 11.03 -240.966666
10643 1 814.50 19.06 102.333334
10692 1 878.00 20.55 165.833334
11011 1 933.50 21.85 221.333334
10702 1 330.00 7.72 -382.166666
10625 2 479.75 34.20 129.012500
10759 2 320.00 22.81 -30.737500
10308 2 88.80 6.33 -261.937500
10926 2 514.40 36.67 163.662500
--------------------------------------------------------------**/
但如果这时再加一个比 - 单个订单与总订单额/平均额比，这时汇总的级别又不相同了， 需要单独再汇总一次。
WITH CustAggregates AS
(
SELECT custid,
SUM(val) AS sumval,
AVG(val) AS avgval
FROM Sales.OrderValues
GROUP BY custid
),
GrandAggregates AS
(
SELECT SUM(val) AS sumval,
AVG(val) AS avgval
FROM Sales.OrderValues
)
SELECT O.orderid,
O.custid,
O.val,
CAST(100. * O.val / CA.sumval AS NUMERIC(5, 2)) AS pctcust,
O.val - CA.avgval AS diffcust,
CAST(100. * O.val / GA.sumval AS NUMERIC(5, 2)) AS pctall,
O.val - GA.avgval AS diffall
FROM Sales.OrderValues AS O
JOIN CustAggregates AS CA
ON O.custid = CA.custid
CROSS JOIN GrandAggregates AS GA;查询结果
/**--------------------------------------------------------------
10835 1 845.80 19.79 133.633334 0.07 -679.252072
10952 1 471.20 11.03 -240.966666 0.04 -1053.852072
10643 1 814.50 19.06 102.333334 0.06 -710.552072
10692 1 878.00 20.55 165.833334 0.07 -647.052072
11011 1 933.50 21.85 221.333334 0.07 -591.552072
10702 1 330.00 7.72 -382.166666 0.03 -1195.052072
10625 2 479.75 34.20 129.012500 0.04 -1045.302072
10759 2 320.00 22.81 -30.737500 0.03 -1205.052072
10308 2 88.80 6.33 -261.937500 0.01 -1436.252072
----------------------------------------------------------------**/
如果提出更多的聚合和比较，查询语句会越来越复杂，并且查询优化器也不能确定每次是否都访问的是同一个数据集，因此需要分别访问数据集，造成性能下降。
通过使用窗体函数可以很容易解决这些问题，因为可以为每一种聚合定义一个窗体上下文。
SELECT orderid,
custid,
val,
CAST(100.* val/ SUM(val) OVER(PARTITION BY custid) AS NUMERIC(5,2)) AS pctcut,
val - AVG(val) OVER(PARTITION BY custid) AS diffcust,
CAST(100.* val/ SUM(val) OVER() AS NUMERIC(5,2)) AS pctall,
val - AVG(val) OVER() AS diffall
FROM Sales.OrderValues窗体函数中的窗体初始状态是 SELECT 后的查询结果集，在子查询中可能要过滤很多条件，因为各个子查询各自独立，各自基于一个查询来过滤，但是通过窗体函数只需要在 SELECT 的集合中限制一遍就可以了。
SELECT orderid,
custid,
val,
CAST(100. * val /
(
SELECT SUM(O2.val)
FROM Sales.OrderValues AS O2
WHERE O2.custid = O1.custid
AND orderdate >= '20070101'
AND orderdate < '20080101'
) AS NUMERIC(5, 2)) AS pctcust,
val - (
SELECT AVG(O2.val)
FROM Sales.OrderValues AS O2
WHERE O2.custid = O1.custid
AND orderdate >= '20070101'
AND orderdate < '20080101') AS diffcust,
CAST(100. * val /
(
SELECT SUM(O2.val)
FROM Sales.OrderValues AS O2
WHERE orderdate >= '20070101'
AND orderdate < '20080101') AS NUMERIC(5, 2)) AS pctall,
val - ( SELECT AVG(O2.val)
FROM Sales.OrderValues AS O2
WHERE orderdate >= '20070101'
AND orderdate < '20080101') AS diffall
FROM Sales.OrderValues AS O1
WHERE orderdate >= '20070101'
AND orderdate < '20080101';这里的 WHERE 条件保证了在这个查询中应用的窗体就已经是介于20070101 ~ 20080101 之间的行集。
SELECT orderid,
custid,
val,
CAST(100.* val/ SUM(val) OVER(PARTITION BY custid) AS NUMERIC(5,2)) AS pctcut,
val - AVG(val) OVER(PARTITION BY custid) AS diffcust,
CAST(100.* val/ SUM(val) OVER() AS NUMERIC(5,2)) AS pctall,
val - AVG(val) OVER() AS diffall
FROM Sales.OrderValues
WHERE orderdate >= '20070101'
AND orderdate < '20080101'Gaps and islands 的问题
在一组可排序的值中找到连续的数据或者间隙，这个在实际的应用比较广泛，比如查询某个产品有连续下载或者购买的时段，发生的频率或者连续达到某个KPI的时段有哪些等等。
比如这个例子 -
用户ID 有效期起始 有效期结束
1 2012-10-01 2012-10-31
2 2012-10-01 2012-10-31
1 2012-12-01 2012-12-31
2 2012-11-01 2012-11-30
合并有效期 -
用户ID 有效期起始 有效期结束
1 2012-10-01 2012-10-31
1 2012-12-01 2012-12-31
2 2012-10-01 2012-11-30
下面用一个实际的代码来说明这个过程。
SET NOCOUNT ON;
USE TSQL2012;
IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1;
GO
CREATE TABLE dbo.T1
(
col1 INT NOT NULL
CONSTRAINT PK_T1 PRIMARY KEY
);
INSERT INTO dbo.T1(col1)
VALUES(2),(3),(11),(12),(13),(27),(33),(34),(35),(42);
GO
SELECT *
FROM dbo.T1原数据查询
2
3
11
12
13
27
33
34
35
42
需要实现的查询结果
start_range end_range
----------- -----------
2 3
11 13
27 27
33 35
42 42
SELECT col1,
(
-- 在 T1 中找出比 Col1 大的并且加1后在T1中不存在的最小值
SELECT MIN(B.col1)
FROM dbo.T1 AS B
WHERE B.col1 >= A.col1
AND NOT EXISTS
(
SELECT *
FROM dbo.T1 AS C
WHERE C.col1 = B.col1 + 1
)
)
AS grp
FROM dbo.T1 AS A;-- 查询结果
2 3
3 3
11 13
12 13
13 13
27 27
33 35
34 35
35 35
42 42
再分组查询
SELECT MIN(T.col1) AS start_range,
MAX(T.col1) AS end_range
FROM(
SELECT col1,
(
-- 在 T1 中找出比 Col1 大的并且加1后在T1中不存在的最小值
SELECT MIN(B.col1)
FROM dbo.T1 AS B
WHERE B.col1 >= A.col1
AND NOT EXISTS
(
SELECT *
FROM dbo.T1 AS C
WHERE C.col1 = B.col1 + 1
)
) AS grp
FROM dbo.T1 AS A
)AS T
-- 分组找最大和最小值
GROUP BY T.grp Start_range end_range
2 3
11 13
27 27
33 35
42 42
再来看使用窗口函数。
SELECT col1,
(col1 - ROW_NUMBER() OVER(ORDER BY col1)) AS grp
FROM dbo.T1查询结果
2 1
3 1
11 8
12 8
13 8
27 21
33 26
34 26
35 26
42 32
聚合查询
SELECT MIN(T.col1) AS start_range,
MAX(T.col1) AS end_range
FROM
(
SELECT col1,
(col1 - ROW_NUMBER() OVER(ORDER BY col1)) AS grp
FROM dbo.T1
)AS T
GROUP BY T.grpStart_range end_range
2 3
11 13
27 27
33 35
42 42
Window Function 的元素
Window Function 的OVER语句中有三个非常重要的元素 - Partitioning, Ordering, Framing。
Partitioning 分区 - PARTITION BY 支持所有的窗口函数
通过PARTITION BY 得到的窗体集是基于当前查询结果的当前行的一个集，比如说 PARTITION BY CustomerID，当前行的 CustomerID = 1，那么对于当前行的这个 Window 集就是在当前查询结果之上再加上 CustomerID = 1 的一个查询结果。如果当前行的 CustomerID = 2，那么它的窗体就是在查询结果上所有 CustomerID = 2 的集。
如果没有指定 PARTITION BY，那么窗体集将不会在查询结果之上去过滤 CustomerID = ***，那就应该是整个查询结果集。