*开窗函数必须结合排名或者聚合函数一起使用
窗口是用户指定的一组行。开窗函数计算从窗口派生的结果集中各行的值
1.开窗聚合函数
select *,SUM(score) over() as 汇总,
CONVERT(decimal(18,4),score/SUM(score) over()) as 单个比例,
SUM(score) over(partition by orderId) as 部门汇总,//按部门汇总
CONVERT(decimal(18,4),score/SUM(score) over(PARTITION by orderId)) as 单个占部门比例 from T
查询结果:
id orderId score 汇总 单个比例 部门汇总 单个占部门比例
1 1 2.00 27.20 0.0735 3.00 0.6667
2 1 1.00 27.20 0.0368 3.00 0.3333
3 2 6.00 27.20 0.2206 7.10 0.8451
4 2 1.10 27.20 0.0404 7.10 0.1549
5 3 8.00 27.20 0.2941 16.10 0.4969
6 3 1.10 27.20 0.0404 16.10 0.0683
7 3 7.00 27.20 0.2574 16.10 0.4348
8 4 1.00 27.20 0.0368 1.00 1.0000
2.开窗排名函数
select orderId,score,
rank() over(PARTITION BY orderId order by score) as 分组排名,
rank() over(order by score) as 排名
from orderTable
order by orderId asc
查询结果:
id orderId score 分组排名 总排名
1 1 2.00 1 4
2 1 1.00 2 6
3 2 6.00 1 3
4 2 1.10 2 5
6 3 1.10 3 5
5 3 8.00 1 1
7 3 7.00 2 2
8 4 1.00 1 6
*排名函数
1.row_number
select ROW_NUMBER() over(order by score) as 行号,* from T
行号 id orderId score
1 2 1 1.00
2 8 4 1.00
3 6 3 1.10
4 4 2 1.10
5 1 1 2.00
6 3 2 6.00
7 7 3 7.00
8 5 3 8.00
2.rank //如果出现相同的值的情况
select rank() over(order by score) as 行号,* from T
1 2 1 1.00
1 8 4 1.00
3 6 3 1.10
3 4 2 1.10
5 1 1 2.00
6 3 2 6.00
7 7 3 7.00
8 5 3 8.00
3.dense_rank //也是出现相同的值的情况,这个是继续往下排
select dense_rank() over(order by score) as 行号,* from T
1 2 1 1.00
1 8 4 1.00
2 6 3 1.10
2 4 2 1.10
3 1 1 2.00
4 3 2 6.00
5 7 3 7.00
6 5 3 8.00
4.ntile 函数可以对序号进行分组处理。这就相当于将查询出来的记录集放到指定长度的数组中,每一个数组元素存放一定数量的记录。ntile函数为每条记录生成的序号就是这条记录所有的数组元素的索引(从1开始)。也可以将每一个分配记录的数组元素称为“桶”。ntile函数有一个参数,用来指定桶数
select ntile(4) over(order by score) as 行号,* from T
1 2 1 1.00
1 8 4 1.00
2 6 3 1.10
2 4 2 1.10
3 1 1 2.00
3 3 2 6.00
4 7 3 7.00
4 5 3 8.00