SQL进阶-3-排序和窗口函数
在使用数据库制作各种统计数据的时候,需要对数据进行排序,比如按照分数、销量、人数等数值进行排序,通常排序的方法有两种:
- 跳过之后的位次排序
- 不跳过之后的位次排序
窗口函数
窗口函数只在最新的MySQL
版本中才支持的!
窗口函数只在最新的MySQL
版本中才支持的!
窗口函数只在最新的MySQL
版本中才支持的!
参考资料:https://zhuanlan.zhihu.com/p/92654574
什么是窗口函数
窗口函数,也叫OLAP
函数(Online Anallytical Processing,联机分析处理),可以对数据库数据进行实时分析处理。
语法
窗口函数的基本语法:
1 | <窗口函数> over (partition by <用于分组的字段名> -- partition子句可省略,不指定分组 |
<窗口函数>
的位置上可以放两种函数:
- 专用窗口函数,
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个函数的区别通过一个列子可以清楚地看到:
1 | select |
结论
1 | name price rank_1 rank_2 rank_3 |
总结:
rank()
在出现了相同位次之后,跳过了相同的位次dense_rank()
则没有跳过相同的位次row_number()
按照自然数的顺序进行排列
在上述的这三个专用窗口函数中,函数后面的括号不需要任何参数,保持()空着就可以。
知乎例子
实现rank()
1 | select *, |
不改变行数
非等值连接实现rank()
1 | select p1.name,p1.price, |
- 子查询的功能是计算出比自己
(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()
1 | mysql> select p1.name, p1.price, |
使用变量实现row_number()
在MySQL5.7.28
中实现变量实现row_number
函数的功能
1 | mysql> select p.name, p.price, (@pro_rank := @pro_rank + 1) row_Number |
如果是更高级的MySQL
版本,直接使用row_number()
函数实现
1 | select name, price, |
聚合函数作为窗口函数
聚合窗口函数和上面提到的专用窗口函数用法完全相同,只需要把聚合函数写在窗口函数的位置即可
- 函数后面括号里面不能为空
- 需要指定聚合的列名
需要在高级的MySQL
版本或者hive
中实现
1 | mysql> select *, |