OVER()开窗函数的使用ROW_NUMBER/RANK/DENSE_RANK/NTILE

-- 聚合函数和排名函数都支持OVER()子句

-- 注意区别聚合函数和GROUP BY组合的分组统计

SELECT orderid ,custid, val,SUM (val) OVER() AS totalvalue

FROM Sales .OrderValues;

 

10248     85     440.00      1265793.22
10249     79     1863.40     1265793.22
10250     34     1552.60     1265793.22
10251     84     654.06      1265793.22
10252     76     3597.90     1265793.22
10253     34     1444.80     1265793.22
10254     14     556.62      1265793.22
10255     68     2490.50     1265793.22

 

SELECT orderid ,custid, val,SUM (val) OVER() AS totalvalue ,

       SUM(val )OVER( PARTITION BY custid) AS custtotalvalue

FROM Sales .OrderValues;

 

10643     1     814.50     1265793.22     4273.00
10692     1     878.00     1265793.22     4273.00
10702     1     330.00     1265793.22     4273.00
10835     1     845.80     1265793.22     4273.00
10952     1     471.20     1265793.22     4273.00
11011     1     933.50     1265793.22     4273.00
10926     2     514.40     1265793.22     1402.95
10759     2     320.00     1265793.22     1402.95
10625     2     479.75     1265793.22     1402.95
10308     2     88.80      1265793.22     1402.95
10365     3     403.20     1265793.22     7023.98
10507     3     749.06     1265793.22     7023.98

 

--查询OrderValues 的每一行计算当前价格占总价格的百分比，以及当前价格占客户总价格的百分比

--表达式中使用了十进制实数（后面加个点），而不是直接使用整数，因为这样可以隐式地将整数值 val和SUM(val) 转换成十进制实数值。

SELECT orderid ,custid, val,100. * val /SUM( val) OVER() AS pctall ,

       100. * val /SUM( val) OVER(PARTITION BY custid) AS pctcust

FROM Sales .OrderValues;   

 

10643     1     814.50     0.0643470029014691672941     19.0615492628130119354083
10692     1     878.00     0.0693636200705830925528     20.5476246197051252047741
10702     1     330.00     0.0260706089103558320528     7.7229113035338169904048

 

--OVER()支持种排名函数：ROWNUMBER行号、 RANK排名、DENSE_RANK 密集排名，以及 NTILE

--NTILE(expr) OVER([PARTITION BY]ORDER BY) ：把有序的数据集平均分配到 expr指定的数量的桶中，将桶号分配给每一行；如果不能平均分配，则较小桶号的桶分配额外的行，并且各个桶中能放的行数最多相差。

SELECT orderid ,custid, val,

       ROW_NUMBER() OVER(ORDER BY val)AS rownum,

       RANK() OVER(ORDER BY val)AS rank,

       DENSE_RANK()OVER(ORDER BY val)AS dense_rank,

       NTILE(3) OVER(ORDER BY val)AS ntile

FROM Sales.OrderValues

WHERE val<=48.75

ORDER BY val;

 

10782     12     12.50     1     1     1     1
10807     27     18.40     2     2     2     1
10586     66     23.80     3     3     3     1
10767     76     28.00     4     4     4     1
10898     54     30.00     5     5     5     1
10900     88     33.75     6     6     6     2
11051     41     36.00     7     7     7     2
10883     48     36.00     8     7     7     2
10815     71     40.00     9     9     8     2
10674     38     45.00     10     10     9      3
11057     53     45.00     11     10     9      3
10271     75     48.00     12     12     10     3
10602     83     48.75     13     13     11     3

 

SELECT orderid,custid,val,

       ROW_NUMBER() OVER(PARTITION BY custid ORDER BY val)AS custnum

FROM Sales.OrderValues

ORDER BY custid,val;

10702     1     330.00     1
10952     1     471.20     2
10643     1     814.50     3
10835     1     845.80     4
10692     1     878.00     5
11011     1     933.50     6
10308     2     88.80      1
10759     2     320.00     2
10625     2     479.75     3
10926     2     514.40     4