专栏首页膨胀的面包Mysql 窗口函数学习

Mysql 窗口函数学习

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

模拟问题描述

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

数据表样例如下:

窗口函数介绍

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

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

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

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

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

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

其中,上表所述的窗口函数主要分为两大类: 排序类,包括 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.error.work/database/145.html

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

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

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

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

值得指出的是,对于每名学生,当切分窗口不足指定窗口大小(即目标行数)时会按实际的数据进行聚合,例如学生 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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MySQL 窗口函数之 RANK 函数

    Mysql8.0+ 版本支持窗口函数,该类函数也称为分析函数,对于初学者来说,窗口函数特别容易与分组聚合函数混合。两者的不同之处在于,窗口函数的使用,使得每一行...

    DataScience
  • MySQL 窗口函数之头尾函数

    注意:当函数后无指定分区及排序字段,即 over() 括号内容为空,则会出现上面的结果。

    DataScience
  • 对比Excel,学习Python窗口函数

    对Sql比较了解的同学,应该都听过Sql中的窗口函数,感觉掌握了窗口函数就可以说自己精通Sql了,在Python中也有类似的窗口函数。

    张俊红
  • HIVE窗口函数

    窗口函数的应用场景http://yugouai.iteye.com/blog/1908121

    努力在北京混出人样
  • HIVE窗口函数

    CSDN博客地址:https://mp.csdn.net/mdeditor/81067060

    努力在北京混出人样
  • Hive窗口函数

    Hive官网,点我就进 oracle,sqlserver都提供了窗口函数,但是在mysql5.5和5.6都没有提供窗口函数!

    孙晨c
  • SQL 窗口函数

    MYSQL 从 8.0.2 版本起开始支持窗口函数,那么在窗口函数没出来之前,我们要实现类似的功能该怎么做呢?

    白日梦想家
  • Flink1.4 窗口函数

    在定义窗口分配器之后,我们需要在每个窗口上指定我们要执行的计算。这是窗口函数的责任,一旦系统确定窗口准备好处理数据,窗口函数就处理每个窗口中的元素。

    smartsi
  • MySQL 8.0窗口函数优化SQL一例

    最近在折腾把所有mysql slow query log写入到数据库中,再集中展示,向业务部门开放,也方便业务部门的同学自行查看并优化各自业务内的慢SQL。增加...

    老叶茶馆
  • MySQL——开窗函数

    高级开窗函数/ 排名的实现ROW_NUMBER();rank() ,dense_rank()

    全栈程序员站长
  • hive sql 窗口函数

    1) 窗口函数 Lag, Lead, First_value,Last_value Lag, Lead、这两个函数为常用的窗口函数,可以返回上下数据行的数据....

    用户1217611
  • 5分钟学会SQL SERVER窗口函数

    窗口函数(window function), 也可以被称为 OLAP函数 或 分析函数。

    fireWang
  • hive 窗口分析函数

    0: jdbc:hive2://localhost:10000> select * from t_access; ±---------------±-----...

    曼路
  • postgreSQL窗口函数总结

    1、我们都知道在SQL中有一类函数叫做聚合函数,例如sum()、avg()、max()等等,这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后的行数是要少...

    小徐
  • postgreSQL窗口函数总结

    1、我们都知道在SQL中有一类函数叫做聚合函数,例如sum()、avg()、max()等等,这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后的行数是要少...

    小徐
  • PostgreSQL窗口函数分析

    今天看了一下PostgreSQL row_number的实现过程。之前一直好奇窗口函数是什么,原理是什么,今天稍稍解惑。下面就以row_number为例进行介绍...

    王果壳
  • MySQL基础(六)——MySQL之开窗函数

      上篇文章给大家介绍MySQL中常用的一些函数,其中主要包括数学函数、字符串函数、日期时间函数、分组合并函数、逻辑函数,其实还有开窗函数,只不过开窗函数是My...

    一计之长
  • LeetCode MySQL 569. 员工薪水中位数(over窗口函数)

    Employee 表包含所有员工。Employee 表有三列:员工Id,公司名和薪水。

    Michael阿明
  • Hive窗口函数/分析函数详解

    在sql中有一类函数叫做聚合函数,例如sum()、avg()、max()等等,这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后的行数是要少于聚集前的行数...

    五分钟学大数据

扫码关注云+社区

领取腾讯云代金券