SQL进阶-3-排序与窗口函数

SQL进阶-3-排序和窗口函数

在使用数据库制作各种统计数据的时候，需要对数据进行排序，比如按照分数、销量、人数等数值进行排序，通常排序的方法有两种：

跳过之后的位次排序
不跳过之后的位次排序

窗口函数

窗口函数只在最新的MySQL版本中才支持的！

参考资料：https://zhuanlan.zhihu.com/p/92654574

什么是窗口函数

窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据库数据进行实时分析处理。

语法

窗口函数的基本语法：

1 2	<窗口函数> 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函数的功能

1
2
3

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()函数实现

1
2
3

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;