窗口函数小结
样本数据库下载：
https://files.cnblogs.com/files/haseo/TSQL2012.rar
1.窗口聚合函数
除了多个OVER子句其实和分组聚合函数一样。
SELECT custid , orderid , val , SUM(val) OVER ( PARTITION BY custid ) AS custtotal , SUM(val) OVER ( ) AS grandtotal FROM Sales.OrderValues;
| custid | orderid | val | custtotal | grandtotal |
| 1 | 10643 | 814.5 | 4273 | 1265793 |
| 1 | 10692 | 878 | 4273 | 1265793 |
| 1 | 10702 | 330 | 4273 | 1265793 |
| 1 | 10835 | 845.8 | 4273 | 1265793 |
| 1 | 10952 | 471.2 | 4273 | 1265793 |
| 1 | 11011 | 933.5 | 4273 | 1265793 |
| 2 | 10926 | 514.4 | 1402.95 | 1265793 |
| 2 | 10759 | 320 | 1402.95 | 1265793 |
| 2 | 10625 | 479.75 | 1402.95 | 1265793 |
| 2 | 10308 | 88.8 | 1402.95 | 1265793 |
| 3 | 10365 | 403.2 | 7023.98 | 1265793 |
| 3 | 10507 | 749.06 | 7023.98 | 1265793 |
SELECT custid , orderid , val , CAST(100.0 * val / SUM(val) OVER ( PARTITION BY custid ) AS NUMERIC(5,2)) AS pctcust , CAST(100.0 * val / SUM(val) OVER ( ) AS NUMERIC(5, 2)) AS pcttotal FROM Sales.OrderValues;
| custid | orderid | val | pctcust | pcttotal |
| 1 | 10643 | 814.5 | 19.06 | 0.06 |
| 1 | 10692 | 878 | 20.55 | 0.07 |
| 1 | 10702 | 330 | 7.72 | 0.03 |
| 1 | 10835 | 845.8 | 19.79 | 0.07 |
| 1 | 10952 | 471.2 | 11.03 | 0.04 |
| 1 | 11011 | 933.5 | 21.85 | 0.07 |
| 2 | 10926 | 514.4 | 36.67 | 0.04 |
| 2 | 10759 | 320 | 22.81 | 0.03 |
| 2 | 10625 | 479.75 | 34.2 | 0.04 |
| 2 | 10308 | 88.8 | 6.33 | 0.01 |
| 3 | 10365 | 403.2 | 5.74 | 0.03 |
| 3 | 10507 | 749.06 | 10.66 | 0.06 |
框架限定（必须要排序）
UNBOUNDED PRECEDING 统计之前所有记录
<n> ROWS PRECEDING 统计前面几行
CURRENT ROW 当前行
UNBOUNDED FOLLOWING 本组下一行
SELECT custid , orderid , orderdate , val , SUM(val) OVER ( PARTITION BY custid ORDER BY orderdate, orderid ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW --可以直接简写为 ROWS UNBOUNDED PRECEDING --ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS runningtotal FROM Sales.OrderValues;
| custid | orderid | orderdate | val | runningtotal |
| 1 | 10643 | 2007/8/25 | 814.5 | 814.5 |
| 1 | 10692 | 2007/10/3 | 878 | 1692.5 |
| 1 | 10702 | 2007/10/13 | 330 | 2022.5 |
| 1 | 10835 | 2008/1/15 | 845.8 | 2868.3 |
| 1 | 10952 | 2008/3/16 | 471.2 | 3339.5 |
| 1 | 11011 | 2008/4/9 | 933.5 | 4273 |
| 2 | 10308 | 2006/9/18 | 88.8 | 88.8 |
| 2 | 10625 | 2007/8/8 | 479.75 | 568.55 |
| 2 | 10759 | 2007/11/28 | 320 | 888.55 |
| 2 | 10926 | 2008/3/4 | 514.4 | 1402.95 |
| 3 | 10365 | 2006/11/27 | 403.2 | 403.2 |
| 3 | 10507 | 2007/4/15 | 749.06 | 1152.26 |
| 3 | 10535 | 2007/5/13 | 1940.85 | 3093.11 |
| 3 | 10573 | 2007/6/19 | 2082 | 5175.11 |
2.排名函数
SELECT custid , orderid , val , ROW_NUMBER() OVER ( ORDER BY val ) AS rownum , RANK() OVER ( ORDER BY val ) AS rnk , DENSE_RANK() OVER ( ORDER BY val ) AS densernk , NTILE(100) OVER ( ORDER BY val ) AS ntile100 --分割结果，列子中分了一百组 FROM Sales.OrderValues;
| custid | orderid | val | rownum | rnk | densernk | ntile100 |
| 12 | 10782 | 12.5 | 1 | 1 | 1 | 1 |
| 27 | 10807 | 18.4 | 2 | 2 | 2 | 1 |
| 66 | 10586 | 23.8 | 3 | 3 | 3 | 1 |
| 76 | 10767 | 28 | 4 | 4 | 4 | 1 |
| 54 | 10898 | 30 | 5 | 5 | 5 | 1 |
| 88 | 10900 | 33.75 | 6 | 6 | 6 | 1 |
| 48 | 10883 | 36 | 7 | 7 | 7 | 1 |
| 41 | 11051 | 36 | 8 | 7 | 7 | 1 |
| 71 | 10815 | 40 | 9 | 9 | 8 | 1 |
| 38 | 10674 | 45 | 10 | 10 | 9 | 2 |
| 53 | 11057 | 45 | 11 | 10 | 9 | 2 |
| 75 | 10271 | 48 | 12 | 12 | 10 | 2 |
3.偏移函数
SELECT custid , orderid , orderdate , val , LAG(val) OVER ( PARTITION BY custid ORDER BY orderdate, orderid ) AS prev_val ,--显示前一行 LAG(val, 3) OVER ( PARTITION BY custid ORDER BY orderdate, orderid ) AS prev_val1 ,--往前显示第三行 LAG(val, 3, 0) OVER ( PARTITION BY custid ORDER BY orderdate, orderid ) AS prev_val2 ,--往前显示第三行，如过前面每值，则显示0 LEAD(val) OVER ( PARTITION BY custid ORDER BY orderdate, orderid ) AS next_val --显示后一行 FROM Sales.OrderValues;
| custid | orderid | orderdate | val | prev_val | prev_val1 | prev_val2 | next_val |
| 1 | 10643 | 00:00.0 | 814.5 | NULL | NULL | 0 | 878 |
| 1 | 10692 | 00:00.0 | 878 | 814.5 | NULL | 0 | 330 |
| 1 | 10702 | 00:00.0 | 330 | 878 | NULL | 0 | 845.8 |
| 1 | 10835 | 00:00.0 | 845.8 | 330 | 814.5 | 814.5 | 471.2 |
| 1 | 10952 | 00:00.0 | 471.2 | 845.8 | 878 | 878 | 933.5 |
| 1 | 11011 | 00:00.0 | 933.5 | 471.2 | 330 | 330 | NULL |
| 2 | 10308 | 00:00.0 | 88.8 | NULL | NULL | 0 | 479.75 |
| 2 | 10625 | 00:00.0 | 479.75 | 88.8 | NULL | 0 | 320 |
| 2 | 10759 | 00:00.0 | 320 | 479.75 | NULL | 0 | 514.4 |
| 2 | 10926 | 00:00.0 | 514.4 | 320 | 88.8 | 88.8 | NULL |
| 3 | 10365 | 00:00.0 | 403.2 | NULL | NULL | 0 | 749.06 |
| 3 | 10507 | 00:00.0 | 749.06 | 403.2 | NULL | 0 | 1940.85 |
| 3 | 10535 | 00:00.0 | 1940.85 | 749.06 | NULL | 0 | 2082 |
| 3 | 10573 | 00:00.0 | 2082 | 1940.85 | 403.2 | 403.2 | 813.37 |
--FIRST_VALUE 和 LAST_VALUE支持window partition,order, and frame clauses. SELECT custid , orderid , orderdate , val , FIRST_VALUE(val) OVER ( PARTITION BY custid ORDER BY orderdate, orderid ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS first_val , LAST_VALUE(val) OVER ( PARTITION BY custid ORDER BY orderdate, orderid ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) AS last_val FROM Sales.OrderValues;
| custid | orderid | orderdate | val | first_val | last_val |
| 1 | 11011 | 2008/4/9 | 933.5 | 814.5 | 933.5 |
| 1 | 10952 | 2008/3/16 | 471.2 | 814.5 | 933.5 |
| 1 | 10835 | 2008/1/15 | 845.8 | 814.5 | 933.5 |
| 1 | 10702 | 2007/10/13 | 330 | 814.5 | 933.5 |
| 1 | 10692 | 2007/10/3 | 878 | 814.5 | 933.5 |
| 1 | 10643 | 2007/8/25 | 814.5 | 814.5 | 933.5 |
| 2 | 10926 | 2008/3/4 | 514.4 | 88.8 | 514.4 |
| 2 | 10759 | 2007/11/28 | 320 | 88.8 | 514.4 |
| 2 | 10625 | 2007/8/8 | 479.75 | 88.8 | 514.4 |
| 2 | 10308 | 2006/9/18 | 88.8 | 88.8 | 514.4 |
| 3 | 10856 | 2008/1/28 | 660 | 403.2 | 660 |
| 3 | 10682 | 2007/9/25 | 375.5 | 403.2 | 660 |
| 3 | 10677 | 2007/9/22 | 813.37 | 403.2 | 660 |
| 3 | 10573 | 2007/6/19 | 2082 | 403.2 | 660 |
