本站承诺永不接任何虚假欺骗、联盟广告、弹窗广告、病毒广告、诱导充值等影响用户体验的广告,广告屏蔽插件会影响本站部分功能,还请不要屏蔽本站广告,感谢支持!

当前位置:首页 / 正文

2021-04-16 | 编程技术 | 2083 次阅读 | 等你评论 | 0 次点赞 | 繁体

窗口函数是数据库查询中的一个经典场景,在解决某些特定问题时甚至是必须的。个人认为,在单纯的数据库查询语句层面【即不考虑 DML、SQL 调优、索引等进阶】,窗口函数可看作是考察求职者 SQL 功底的一个重要方面。

模拟问题描述

给定一组中学生的历次语文月考成绩表(每名学生含有 4 次成绩),需要实现以下 3 个需求:
对每名学生的 4 次成绩表分别进行排序,排序后每人的成绩排名 1-2-3-4
求每名学生历次月考成绩的变化幅度,即本月较上个月的成绩差值
求每名学生历次月考成绩中近 3 次平均分

数据表样例如下:

图片alt

窗口函数介绍

在分析上述需求之前,首先对窗口函数进行介绍。何为窗口函数呢?既然窗口函数这个名字源于数据库,那么我们就援引其在数据库中的定义。下图源于 MySQL8.0 的官方文档,从标黄高亮的一句介绍可知:窗口函数是用与当前行有关的数据行参与计算。这个翻译可能有些蹩脚,但若能感性理解窗口函数的话,其实反而会觉得其概括的比较传神。

图片alt

当然,为了形象表达上述定义所言何物,这里还是进一步给出一些配套插图以便于理解。在给出具体配图之前,首先要介绍与窗口函数相关的 3 个关键词:

  • partition by:用于对全量数据表进行切分(与 SQL 中的 group by 功能类似,但功能完全不同),直接体现的是前面窗口函数定义中的“有关”,即切分到同一组的即为有关,否则就是无关;
  • order by:用于指定对 partition 后各组内的数据进行排序;
  • rows between:用于对切分后的数据进一步限定“有关”行的数量,此种情景下即使 partition 后分到一组,也可能是跟当前行的计算无关。

相应的,这 3 个关键字在前面的数据样表中可作如下配套解释:

图片alt

当然,到这里还不是很理解窗口函数以及相应的 3 个关键字也问题不大,后续结合前述的三个实际需求再返过来看此图多半会豁然开朗。

上面是窗口函数的逻辑解释,那么具体能用于实现什么功能呢?其实,窗口函数能实现什么功能则要取决于能搭配什么函数。仍然引用 MySQL8.0 官方文档中的一幅图例:

图片alt

其中,上表所述的窗口函数主要分为两大类:
排序类,包括 row_number、rank、dense_rank 等,也包括 percent_rank、cume_dist 等分布排序类
相对引用类,如 lag、lead、first_value、last_value、nth_value 等
除了这两类专用窗口函数之外,还有广义的聚合函数也可配套窗口函数使用,例如 sum、avg、max、min 等。

所以,现在来看前面提到的三个需求,就刚好是分别应用这三类窗口函数的例子。【哪有什么刚好,不过是特意设计而已】
围绕这三个需求,下面分别应用 SQL、Pandas 和 Spark 三个工具予以实现。

SQL实现

既然窗口函数起源于数据库,那么下面就应用 SQL 予以实现。
注:以下所有 SQL 查询语句实现均基于 MySQL8.0。

Q1:求解每名同学历次成绩的排名。
A1:由于是区分每名同学进行排序,所以需要依据 uid 字段进行 partition;进一步地,按照成绩进行排序,所以 order by 字段即为 score;最后,由于是要进行排名,所以配套函数选择 row_number 即可。
注:row_number、rank 和 dense_rank 的具体区别可参考历史文章:https://blog.wangtwothree.com/database/145.html

查询语句及查询结果如下:

SELECT *, row_number() over(partition by uid order by score desc) as `rank` from score

图片alt

Q2:求解每名同学历次月考成绩的差值,即本月成绩-上月成绩。
A2:首先,仍然是依据 uid 字段进行 partition;而后由于是要计算本月成绩与上月成绩的差值,所以此次的排序依据应该是 date;进一步地,由于要计算差值,所以需要对每次月考成绩计算其前一行的成绩(在按照 uid 进行切分并按照 date 排序后,上月成绩即为当前行的前一条记录),所以配套函数即为 lag。

给出具体实现 SQL 语句及查询结果如下:

SELECT *, score - lag(score) over(partition by uid order by date) as score_diff from score

图片alt

Q3:求解每名学生近 3 次月考成绩的平均分。
A3:在前两个需求的基础上,易见,仍然是依据 uid 进行 partition、依据 date 进行排序,并选用 avg 聚合函数作为配套窗口函数。进一步地,由于此处限定计算近 3 次成绩的平均分,所以除了 partition 和 order by 两个关键字外,还需增加 rows between 的限定。

具体 SQL 语句和查询结果如下:

SELECT *, avg(score) over(partition by uid order by date rows between 2 preceding and current row) as avg_score3 from score

图片alt

值得指出的是,对于每名学生,当切分窗口不足指定窗口大小(即目标行数)时会按实际的数据进行聚合,例如学生 A,1 月 31 日对应的近 3 次平均分即为本月成绩自身;2 月 28 日对应近 3 次平均分即为本月成绩和上月成绩的平均分,而 3 月 31 日和 4 月 30 日计算的近 3 次平均分则为真正意义上的 3 次成绩均值。

via:
SQL、Pandas、Spark:窗口函数的3种实现
https://mp.weixin.qq.com/s/GUzwvCRkahRxCcOQ-mYV8g

标签: mysql数据库

猜你喜欢
如何定位Mysql中CPU占用高的查询语句
今天 mysql 服务器突然 CPU 告警,记录一下问题查找的过程第一步查看具体是哪个线程占用CPU最高1、在 Linux 中使用 top 命令找到 mysql 进程 PID2、指定进程 PID...
mysql8利用CTE特性实现递归查询
递归查询分为父子查询和子父查询。父子查询: 根据父 id 查询下面所有子节点数据;子父查询: 根据子 id 查询上面所有父节点数据;下边就利用 mysql8 新增语法实现递归查询,表结构及数...
mysql数据库删除重复的数据只保留一条
问题引入假设一个场景,一张用户表,包含 3 个字段:id,identity_id,name。现在身份证号 identity_id 和姓名 name 有很多重复的数据,需要删除多余数据只保留一条有...
三种方式修改 MySQL 数据库名
在 Innodb 数据库引擎下修改数据库名的方式与 MyISAM 引擎下修改数据库的方式完全不一样,如果是 MyISAM 可以直接去数据库目录中 mv 就可以,Innodb 如果用同样的方法修改...
别再用PS了,教你用五行Python代码实现批量抠图
对于会 PhotoShop 的人来说,抠图是非常简单的操作了,有时候几秒钟就能扣好一张图。不过对于一些比较复杂的图,有时候还是需要花点时间的,今天就给大家带了一个非常快速简单的办法,用 Python
(首次提交评论需审核通过才会显示,请勿重复提交)