This website requires JavaScript.

MSSQL:窗口函数小结

样本数据库下载:

http://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
0条评论
avatar