发布于2024-11-02 12:28 阅读(215) 评论(0) 点赞(29) 收藏(5)
2023年12月下旬,广东终于冷了!回想直到12月15那天,依然穿着短袖上班,吹着风扇空调睡觉… 哈哈,这是截至发文时的一些感受与题外话。天气是冷了,但心中依然热情似火,一是工作业务上又有稍微复杂的业务,有挑战;二是虽然有挑战,但想起牛逼的兄弟@CaptinKoo两个月前教了我一招:SQL窗口函数,业务难题迎刃而解!趁着这次解决难题的热度,将本次学到的窗口函数知识点以及项目实战记录下来,供各位分享。
我个人学习窗口函数主要有两个用处:一是对现有SQL知识的拓展,二是能使用窗口函数对一些特定场景做SQL简化,解决复杂问题。
但在正式开始之前,得事先说明一个前提:
前提
学习目标
下面我们开始!
这一小节我们介绍窗口函数的一些概念。
概念
窗口函数,也叫OLAP函数(Online Anallytical Processing,联机分析处理),可以对数据库数据进行实时分析处理。
窗口函数在MySQL 8中引入,是Mysql 8的新特性。是一种主要用于数据分析、特定字段分组等的一种特殊的函数。
常见使用场景
窗口函数的语法如下:
窗口函数([参数]) OVER (
[PARTITION BY <分组列>]
[ORDER BY <排序列 ASC/DESC>]
[ROWS BETWEEN 开始行 AND 结束行]
)
PARTITION BY
子句用于指定分组列,关键字:PARTITION BY
。ORDER BY
子句用于指定排序列,关键字ORDER BY
。ROWS BETWEEN
子句用于指定窗口的范围,关键字ROWS BETWEEN
即[开始行]、[结束行](这部分在“补充与总结”小节中作补充详细说明)。其中,ROWS BETWEEN
子句在实际中可能用得相对少一些,因此有部分参考资料的语法描述省略了ROWS BETWEEN
子句,主要侧重于PARTITION BY
分组与ORDER BY
排序:
窗口函数([参数]) OVER (
[PARTITION BY <分组列>]
[ORDER BY <排序列 ASC/DESC>]
也正因此,本文将ROWS BETWEEN
子句相关关键字知识点将会以补充的形式说明,而侧重常用窗口函数的学习与练习,侧重PARTITION BY
子句与ORDER BY
子句的使用。
语法举例,设有Order表,查询销售数量总和及其当前行前两行和后两行的销售数量总和:
SELECT product_id, order_date, quantity,
SUM(quantity) OVER (PARTITION BY product_id ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS sum_surrounding_quantities
FROM orders
这个例子暂时看不懂个没关系,接下来,我们会详细介绍常见窗口函数,并在介绍的过程中举例。之后,上述例子就很好理解了。
本小节介绍常见窗口函数。
若要跟着本文进行练习,则可以参考着创建如下表,本文的例子均基于下表:
设计一个销售数据表。该表包含以下字段:
以下是创建表的DDL
以及 插入模拟数据的DML
CREATE TABLE sales ( id INT PRIMARY KEY, product VARCHAR(50), category VARCHAR(50), sale_date DATE, quantity INT, revenue DECIMAL(10, 2) ); INSERT INTO sales (id, product, category, sale_date, quantity, revenue) VALUES (1, 'Product A', 'Category 1', '2022-01-01', 10, 100.00), (2, 'Product B', 'Category 1', '2022-01-01', 5, 50.00), (3, 'Product A', 'Category 2', '2022-01-02', 8, 80.00), (4, 'Product B', 'Category 2', '2022-01-02', 3, 30.00), (5, 'Product A', 'Category 1', '2022-01-03', 12, 120.00), (6, 'Product B', 'Category 1', '2022-01-03', 7, 70.00), (7, 'Product A', 'Category 2', '2022-01-04', 6, 60.00), (8, 'Product B', 'Category 2', '2022-01-04', 4, 40.00);
好的,准备工作完成,下面我们一边学习具体窗口函数并练习吧!
许多窗口函数的教程,通常将常用的窗口函数分为两大类:聚合窗口函数 与 专用窗口函数。聚合窗口函数的函数名与普通常用聚合函数一致,功能也一致。从使用的角度来讲,与普通聚合函数的区别在于提供了窗口函数的专属子句,来使得数据的分析与获取更简便。主要有如下几个:
函数名 | 作用 |
---|---|
SUM | 求和 |
AVG | 求平均值 |
COUNT | 求数量 |
MAX | 求最大值 |
MIN | 求最小值 |
区别
这个例子演示与普通聚合函数的区别。设我们要求使用一条查询语句,在sales表每行最后一列都加上这一行的产品类别 category
的 平均 销售收入revenue
,并且以category
顺序排序,即如下图所示:
SELECT
t1.*,
t2.avg_revenue FROM sales t1
LEFT JOIN (
SELECT category, AVG(revenue) AS avg_revenue
FROM sales
GROUP BY category
) t2 ON t1.category = t2.category ORDER BY t1.category
SELECT
sales.*,
AVG( revenue ) OVER ( PARTITION BY category ) AS avg_revenue
FROM
sales
这么一对比,窗口聚合函数简单不少!
常见的专用窗口函数
函数名 | 分类 | 说明 |
---|---|---|
RANK | 排序函数 | 类似于排名,并列的结果序号可以重复,序号不连续 |
DENSE_RANK | 排序函数 | 类似于排名,并列的结果序号可以重复,序号连续 |
ROW_NUMBER | 排序函数 | 对该分组下的所有结果作一个排序,基于该分组给一个行数 |
PERCENT_RANK | 分布函数 | 每行按照公式 (rank-1) / (rows-1) 进行计算 |
CUME_DIST | 分布函数 | 分组内小于、等于当前 rank 值的行数 / 分组内总行数 |
练习
分别对上述表格常见的专用窗口函数进行调用,查看结果。
SELECT
*,
RANK() OVER(PARTITION BY category ORDER BY quantity DESC) AS `quantity_rank`,
DENSE_RANK() OVER(PARTITION BY category ORDER BY product DESC) AS `product_dense_rank`,
ROW_NUMBER() OVER(PARTITION BY category ORDER BY product DESC) AS `product_row_number`,
PERCENT_RANK() OVER(PARTITION BY category ORDER BY quantity DESC) AS `quantity_percent_rank`,
CUME_DIST() OVER(PARTITION BY category ORDER BY quantity DESC) AS `quantity_cume_dist`
FROM sales
至于其它专用窗口函数,请读者自行查阅其它资料做拓展。
通过对上面我们对窗口函数的学习与练习,我们一来明白了窗口函数的相关概念、常见窗口函数的使用以及这些窗口函数的作用与效果。也通过窗口函数与一般函数子查询作了一个简单的对比,体现了窗口函数在一些特定需求的强大。那么既然窗口函数如此强大,那么窗口函数的性能对比传统函数、传统子查询与分组的性能相比如何呢?
窗口函数的性能和其它SQL语句一样,受数据量大小、分区复杂度情况等影响。同等数量级的一般情况下:
当然,上述只是理论上的性能初步分析,实际还得视具体的情况而定。
至于窗口函数优化方案,可以以影响窗口函数性能的原因为切入点由因到果进行优化,例如缩小窗口大小限制。篇幅有限,不作详解。详情可参考文末推荐的优秀参考文章。
上一小节,我们学习了 SQL 窗口函数的概念,从本小节开始,就是做题练习与实战了!
接下来要列举例题,是 @CaptinKoo 两个月前教我们窗口函数时提供的练习题。让我们跟随 @CaptinKoo 老师的脚步,进行窗口函数练习吧!
题目描述
题解
根据题目描述,我们得知,返回结果序号可重复,连续,因此我们使用DENSE_RANK()函数。
SELECT
score,
DENSE_RANK() OVER(ORDER BY score DESC) AS `rank`
FROM Scores
ORDER BY score DESC
题目描述
题解
根据描述,我们可以通过 RANK 窗口函数对 Employee 表进行排序,获取 rank 值为1 的 员工并关联到部门表。
SELECT
d.name AS Department,
e.name AS Employee,
e.salary AS Salary
FROM (
SELECT
name,
salary,
departmentId,
RANK() OVER(PARTITION BY departmentId ORDER BY salary DESC) AS `rank`
FROM Employee
) e
LEFT JOIN Department d
ON e.departmentId = d.id
WHERE e.`rank` = 1
题目链接
LeetCode-SQL185部门工资前三高的所有员工
题目描述
题解
有了上面两道题的解题练习,这道题也迎刃而解:
SELECT
d.name AS Department,
e.name AS Employee,
e.salary AS Salary
FROM (
SELECT
name,
salary,
departmentId,
DENSE_RANK() OVER(PARTITION BY departmentId ORDER BY salary DESC) AS `rank`
FROM Employee
) e
LEFT JOIN Department d
ON e.departmentId = d.id
WHERE e.`rank` <= 3
太棒了!我们一下就完成了三道包括中等、困难难度的LeetCode题目,接下来,我们可以将我们学习并练习过的知识点用于项目实战了!
本小节是我个人用窗口函数解决实际工作问题的实战记录。涉及的表、字段均已做描述更换,脱敏处理。
已知用户订单评价表order_evaluate
有如下字段:
字段名 | 类型 | 说明 |
---|---|---|
id | bigint | 评价表主键id |
evaluate | text | 评价内容 |
user_id | bigint | 用户id |
update_time | datetime | 更新时间 |
order_id | bigint | 订单id |
其中,每个订单可以有多个评价,每个评价都可以修改。业务需要,需要获取当前用户所有订单最近一次评价内容,并返回订单id、最近一次评价的内容。
此次实战业务需要根据update_time
获取最近一次评论并根据order_id
进行分组。
在尚未系统学习窗口函数时,我们第一时间会想到的是传统子查询。
但毕竟实际业务远比这里的脱敏描述要复杂,一时间难以实现,于是我第一时间回顾了@CaptinKoo大佬教我的窗口函数并解决:
# 毕竟是Demo,忽略 user_id 条件,实际业务会补充齐全其它条件
SELECT
t1.order_id,
evaluate
FROM
order_evaluate t1
INNER JOIN (
SELECT id,
order_id,
ROW_NUMBER() OVER ( PARTITION BY order_id ORDER BY update_time DESC ) AS row_num FROM order_evaluate) t2 ON t1.id = t2.id
WHERE
t2.row_num = 1
这条SQL是通过ROW_NUM()函数将工单评价表根据工单分组,更新时间倒序并给它一个行序号。行序号1的就是我们要求的结果。
你能想到用传统子查询实现相同功能的SQL吗?
ROWS BETWEEN
子句常见关键字含义关键字及其含义表
关键字 | 含义 |
---|---|
PRECEDING | 当前行数往前 |
FOLLOWING | 当前行数往后 |
CURRENT ROW | 当前行 |
UNBOUNDED | 起点(一般结合PRECEDING,FOLLOWING使用) |
UNBOUNDED PRECEDING | 表示该窗口最前面的行(起点) |
UNBOUNDED FOLLOWING | 表示该窗口最后面的行(终点) |
此表的知识内容来自于参考文章
根据这个关键字含义表,读者可以理解文初提到的例子了吗?
可选挑战题目
这里提供一题可选的挑战题目链接,是LeetCode困难题目,依然来自@CaptinKoo大佬的推荐,此题的一种解法用到ROWS BETWEEN
子句。
既然这种函数叫"窗口函数",那么它应该可以像"窗口"一样,通过滚动的方式,获取一定范围内的视图。
而滚动的方式恰恰就是ROWS BETWEEN
子句。通过ROWS BETWEEN
子句,获取窗口函数结果的范围,从而有给用户"窗口"的感觉。
用术语表达,则是:通过定义帧,决定窗口的大小。
窗口函数定义帧通常有两种方式:RANGE
和ROWS
, 两者决定窗口帧的边界如何计算。
由于两者用法相似,且一般ROWS BETWEEN
子句会用得多一些,因此本文的语法概述忽略了RANGE
子句。此处作为补充,供读者参考。
本文借由好兄弟@CaptinKoo两个月前教过我的窗口函数知识,截至发文日期顺利解决一个相对比较复杂的业务的故事,记录我从CaptinKoo学到的窗口函数相关知识,以及CaptinKoo大佬推荐的相关习题,以及我个人本次实战的脱敏描述。
通过本文,我们学习到了:
ROWS BETWEEN
子句相关补充知识点再次感谢@CaptinKoo的指导!
原文链接:https://blog.csdn.net/CoderSharry/article/details/135063960
作者:Nxnndn
链接:http://www.phpheidong.com/blog/article/554254/bde911e3b5949006ebbd/
来源:php黑洞网
任何形式的转载都请注明出处,如有侵权 一经发现 必将追究其法律责任
昵称:
评论内容:(最多支持255个字符)
---无人问津也好,技不如人也罢,你都要试着安静下来,去做自己该做的事,而不是让内心的烦躁、焦虑,坏掉你本来就不多的热情和定力
Copyright © 2018-2021 php黑洞网 All Rights Reserved 版权所有,并保留所有权利。 京ICP备18063182号-4
投诉与举报,广告合作请联系vgs_info@163.com或QQ3083709327
免责声明:网站文章均由用户上传,仅供读者学习交流使用,禁止用做商业用途。若文章涉及色情,反动,侵权等违法信息,请向我们举报,一经核实我们会立即删除!