阅读 444

Mysql窗口函数

什么是窗口函数

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

窗口函数的基本语法如下:

<窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>)
复制代码

1)PARTITION BY:你只需将它看成GROUP BY子句,但是在窗口函数中,你要写PARTITION BY。group by分组汇总后改变了表的行数,一行只有一个类别。而partiition by和rank函数不会减少原表中的行数,如图:

img

2)ORDER BY:ORDER BY和普通查询语句中的ORDER BY没什么不同。

窗口函数有哪些

1) 专用窗口函数,包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。

2) 聚合函数,如sum. avg, count, max, min等

窗口函数功能

1)同时具有分组(partition by)和排序(order by)的功能

2)不减少原表的行数,所以经常用来在每组内排名

注意事项

窗口函数原则上只能写在select子句中

窗口函数使用场景

业务需求“在每组内排名”,比如:

排名问题:每个部门按业绩来排名 topN问题:找出每个部门排名前N的员工进行奖励

实例

编写一个 SQL 查询来实现分数排名。

如果两个分数相同,则两个分数排名(Rank)相同。请注意,平分后的下一个名次应该是下一个连续的整数值。换句话说,名次之间不应该有“间隔”。

+----+-------+ | Id | Score | +----+-------+ | 1 | 3.50 | | 2 | 3.65 | | 3 | 4.00 | | 4 | 3.85 | | 5 | 4.00 | | 6 | 3.65 | +----+-------+ 例如,根据上述给定的 Scores 表,你的查询应该返回(按分数从高到低排列):

+-------+------+ | Score | Rank | +-------+------+ | 4.00 | 1 | | 4.00 | 1 | | 3.85 | 2 | | 3.65 | 3 | | 3.65 | 3 | | 3.50 | 4 | +-------+------+

来源:力扣(LeetCode) 链接:leetcode-cn.com/problems/ra… 著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。

以上题为例:

不使用窗口函数可能需要这样写:

SELECT Score, (SELECT count(DISTINCT score) FROM Scores WHERE score >= s.score) AS 'Rank' FROM Scores s ORDER BY Score DESC ;

使用窗口函数:

select Score, dense_rank() over (order by Score desc) as Rank from Scores order by Score desc;

窗口函数的优点

简单

窗口函数更易于使用。在上面的示例中,与使用聚合函数然后合并结果相比,使用窗口函数仅需要多一行就可以获得所需要的结果。

快速

这一点与上一点相关,使用窗口函数比使用替代方法要快得多。当你处理成百上千个千兆字节的数据时,这非常有用。

多功能性

最重要的是,窗口函数具有多种功能,本文并没有提及这个功能,比如,包括添加移动平均线,添加行号和滞后数据,等等。

文章分类
后端
文章标签