SQL进阶-3-排序和窗口函数
在使用数据库制作各种统计数据的时候，需要对数据进行排序，比如按照分数、销量、人数等数值进行排序，通常排序的方法有两种：
- 跳过之后的位次排序
- 不跳过之后的位次排序
窗口函数
窗口函数只在最新的MySQL版本中才支持的！
窗口函数只在最新的MySQL版本中才支持的！
窗口函数只在最新的MySQL版本中才支持的！
参考资料：https://zhuanlan.zhihu.com/p/92654574
什么是窗口函数
窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据库数据进行实时分析处理。
语法
窗口函数的基本语法：
<窗口函数> over (partition by <用于分组的字段名> -- partition子句可省略，不指定分组
order by <用于排序的列名>)
<窗口函数>的位置上可以放两种函数：
- 专用窗口函数，
rank、dense_rank、row_number等 - 聚合函数，如
sum、avg、count、max、min等
因为窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中。
功能
- 同时具有分组和排序的功能
- 不改变原有表的行数
- 窗口函数原则上只能写在
select子句中
实际场景
- 排名问题：每个部门按业绩来排名
topN问题：找出每个部门排名前N的员工进行奖励
rank/dense_rank/row_number
实例
rank：并列跳跃排名dense_rank：并列连续排名row_number：连续排名
这3个函数的区别通过一个列子可以清楚地看到：
select
name,price,
rank() over (order by price desc) as rank_1,
dense_rank() (order by price desc) as rank_2,
row_number() (order by price desc) as rank_3
from products;
结论
name price rank_1 rank_2 rank_3
橘子 100 1 1 1
西瓜 80 2 2 2
苹果 50 3 3 3
香蕉 50 3 3 4
葡萄 50 3 3 5
柠檬 30 6 4 6
总结：
rank()在出现了相同位次之后，跳过了相同的位次dense_rank()则没有跳过相同的位次row_number()按照自然数的顺序进行排列
在上述的这三个专用窗口函数中，函数后面的括号不需要任何参数，保持()空着就可以。
知乎例子
实现rank()
select *,
rank() over (partition by 班级 -- 先分组
order by 成绩 desc) as ranking -- 再排序
from 班级表
不改变行数
非等值连接实现rank()
select p1.name,p1.price,
(select count(p2.price)
from products p2
where p2.price > p1.price) + 1
as rank_1
from products
order by rank_1;
- 子查询的功能是计算出比自己
(p1)高的记录，并将其作为自己的位次 - 比如对去重之后的价格
{100,80,50}进行分析和排序，比100大的个数是0，比80大的个数是1，比50大的个数是2 +1之后的排名实际上就是下面将会提到的dense_rank()函数的排名
| 价格 | 排名 | +1 |
|---|---|---|
| 100 | 0 | 1 |
| 80 | 1 | 2 |
| 50 | 2 | 3 |
如果希望排序从0开始，则去掉加1：
非等值连接实现dense_rank()
mysql> select p1.name, p1.price,
(select count(distinct p2.price) from products p2 where p2.price > p1.price) + 1 as rank_1
from products p1
order by rank_1;
使用变量实现row_number()
在MySQL5.7.28中实现变量实现row_number函数的功能
mysql> select p.name, p.price, (@pro_rank := @pro_rank + 1) row_Number
-> from products p,(select @pro_rank := 0) r
-> order by price desc;
如果是更高级的MySQL版本，直接使用row_number()函数实现
select name, price,
row_number() over (partition by name order by price desc) as rowNumber
from products
聚合函数作为窗口函数
聚合窗口函数和上面提到的专用窗口函数用法完全相同，只需要把聚合函数写在窗口函数的位置即可
- 函数后面括号里面不能为空
- 需要指定聚合的列名
需要在高级的MySQL版本或者hive中实现
mysql> select *,
-> sum(price) over (order by name) as rank_sum,
-> avg(price) over (order by name) as rank_avg,
-> max(price) over (order by name) as rank_max,
-> count(price) over (order by name) as rank_count
-> from products;
本文使用 mdnice 排版
