开窗函数和排名函数


*开窗函数必须结合排名或者聚合函数一起使用
	窗口是用户指定的一组行。开窗函数计算从窗口派生的结果集中各行的值
	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



  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值