SQL Server 窗体函数主要用来处理由 OVER 子句定义的行集, 主要用来分析和处理
- Running totals
- Moving averages
- Gaps and islands
先看一个简单的应用 - 按照订单额从高到低对订单表信息做一个排名
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.grp
Start_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 = ***, 那就应该是整个查询结果集.
用下面的这个例子来说明这个问题
第一个 RANK() 通过 OVER() 定义了一个窗口集, 这个窗口相对于查询中的每一行都应该是一样的,都是基于 SELECT 的结果集
第二个 RANK() 通过 OVER() 定义了一个通过 Custid 分区的窗口集, 那么这个窗口相对于查询中的每一行都有可能不同, 因为这个窗口只选择基于 SELECT 的结果集中与当前行的 custid 相同的那一部分数据.
SELECT custid,
orderid,
val,
RANK() OVER(ORDER BY val DESC) AS rnk_all,
RANK() OVER(PARTITION BY custid
ORDER BY val DESC)AS rnk_cust
FROM Sales.OrderValues;
Ordering 排序
排序在窗口函数中的使用也非常的直观，但是在和不同的元素搭配起来，理解起来有所不同. 仍然使用上面的示例，结合下图来理解.
对于 Rnk_All 来说, 在 OVER 中只有 ORDER BY 子句, 那么这个窗体就是查询本身. RANK()的计算就是找到在这个结果集中比当前行val值大的总行数然后 +1.对于 Rnk_cust 来说, 首先根据 Custid 分区,然后在这个区中找到比 val 值大的总行数然后 +1.
Framing 框架
Framing 能够进一步限制分区上的行, 可选用的选项有 ROWS 和 RANGE，它们能够定义框架集中的起始行和结束行.下面的示例中计算的是 Running Total, 这个原型在实际业务中使用的非常的多, 通常都是基于某条件下的累加计算.
SUM 聚合函数聚合的是 qty (Quantity), 每一行对应的窗体是基于 empid 分区, 按照 ordermonth 默认从小到大排序. 最后 ROWS 开始定位进一步的划定当前行所对应的窗体集. UNBOUNDED PRECEDING 表明在当前行之前的没有最低边界, CURRENT ROW 就是当前行.
以 qty = 143 为例, 当前行的计算范围就是在当前行之前的所有行(UNBOUNDED PRECEDING) 到当前行的所有 qty的总和就是 runqty.
SELECT empid,
ordermonth,
qty,
SUM(qty) OVER(PARTITION BY empid
ORDER BY ordermonth
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) AS runqty
FROM Sales.EmpOrders;
empid order month qty runqty
1 2006-07-01 00:00:00.000 121 121
1 2006-08-01 00:00:00.000 247 368
1 2006-09-01 00:00:00.000 255 623
1 2006-10-01 00:00:00.000 143 766
1 2006-11-01 00:00:00.000 318 1084
另外要记住, Window Functions 只能用在 SELECT 和 ORDER 子句中, 像下面的这种查询是错误的, 书本中通过一些示例解释了为什么, 这里不详细描述.
SELECT col1
FROM dbo.T1
WHERE col1 > 'B'
AND ROW_NUMBER() OVER(ORDER BY col1) <= 3
Msg 4108, Level 15, State 1, Line 4
Windowed functions can only appear in the SELECT or ORDER BY clauses.