mysql 窗口函数

最新推荐文章于 2024-09-12 16:54:47 发布
最新推荐文章于 2024-09-12 16:54:47 发布
阅读量939 收藏 8
点赞数
文章标签： mysql
原文链接：https://blog.csdn.net/weixin_39010770/article/details/87862407?spm=1001.2014.3001.5506
版权

在这里插入图片描述
窗口：记录集合
窗口函数：在满足某些条件的记录集合上执行的特殊函数，对于每条记录都要在此窗口内执行函数。有的函数随着记录的不同，窗口大小都是固定的，称为静态窗口；有的函数则相反，不同的记录对应着不同的窗口，称为滑动窗口。

1. 窗口函数和普通聚合函数的区别：

①聚合函数是将多条记录聚合为一条；窗口函数是每条记录都会执行，有几条记录执行完还是几条。
②聚合函数也可以用于窗口函数。

2. 窗口函数的基本用法：

函数名 OVER 子句

over关键字用来指定函数执行的窗口范围，若后面括号中什么都不写，则意味着窗口包含满足WHERE条件的所有行，窗口函数基于所有行进行计算；如果不为空，则支持以下4中语法来设置窗口。
①window_name：给窗口指定一个别名。如果SQL中涉及的窗口较多，采用别名可以看起来更清晰易读；
②PARTITION BY 子句：窗口按照哪些字段进行分组，窗口函数在不同的分组上分别执行；
③ORDER BY子句：按照哪些字段进行排序，窗口函数将按照排序后的记录顺序进行编号；
④FRAME子句：FRAME是当前分区的一个子集，子句用来定义子集的规则，通常用来作为滑动窗口使用。

3. 按功能划分可将MySQL支持的窗口函数分为如下几类：
①序号函数：ROW_NUMBER()、RANK()、DENSE_RANK()
  • 用途：显示分区中的当前行号
  • 应用场景：查询每个学生的分数最高的前3门课程

ROW_NUMBER() OVER (PARTITION BY stu_id ORDER BY score)

mysql> SELECT *
    -> FROM(
    ->     SELECT stu_id,
    ->     ROW_NUMBER() OVER (PARTITION BY stu_id ORDER BY score DESC) AS score_
order,
    ->     lesson_id, score
    ->     FROM t_score) t
    -> WHERE score_order <= 3
    -> ;
+--------+-------------+-----------+-------+
| stu_id | score_order | lesson_id | score |
+--------+-------------+-----------+-------+
|      1 |           1 | L005      |    98 |
|      1 |           2 | L001      |    98 |
|      1 |           3 | L004      |    88 |
|      2 |           1 | L002      |    90 |
|      2 |           2 | L003      |    86 |
|      2 |           3 | L001      |    84 |
|      3 |           1 | L001      |   100 |
|      3 |           2 | L002      |    91 |
|      3 |           3 | L003      |    85 |
|      4 |           1 | L001      |    99 |
|      4 |           2 | L005      |    98 |
|      4 |           3 | L002      |    88 |
+--------+-------------+-----------+-------+

对于stu_id=1的同学，有两门课程的成绩均为98，序号随机排了1和2。但很多情况下二者应该是并列第一，则他的成绩为88的这门课的序号可能是第2名，也可能为第3名。
这时候，ROW_NUMBER()就不能满足需求，需要RANK()和DENSE_RANK()出场，它们和ROW_NUMBER()非常类似，只是在出现重复值时处理逻辑有所不同。

mysql> SELECT *
    -> FROM(
    ->     SELECT
    ->     ROW_NUMBER() OVER (PARTITION BY stu_id ORDER BY score DESC) AS score_order1,
    ->     RANK() OVER (PARTITION BY stu_id ORDER BY score DESC) AS score_order2,
    ->     DENSE_RANK() OVER (PARTITION BY stu_id ORDER BY score DESC) AS score_order3,
    ->     stu_id, lesson_id, score
    ->     FROM t_score) t
    -> WHERE stu_id = 1 AND score_order1 <= 3 AND score_order2 <= 3 AND score_order3 <= 3
    -> ;
+--------------+--------------+--------------+--------+-----------+-------+
| score_order1 | score_order2 | score_order3 | stu_id | lesson_id | score |
+--------------+--------------+--------------+--------+-----------+-------+
|            1 |            1 |            1 |      1 | L005      |    98 |
|            2 |            1 |            1 |      1 | L001      |    98 |
|            3 |            3 |            2 |      1 | L004      |    88 |
+--------------+--------------+--------------+--------+-----------+-------+

ROW_NUMBER()：顺序排序——1、2、3
RANK()：并列排序，跳过重复序号——1、1、3
DENSE_RANK()：并列排序，不跳过重复序号——1、1、2

②分布函数：PERCENT_RANK()、CUME_DIST()
PERCENT_RANK()
  • 用途：每行按照公式(rank-1) / (rows-1)进行计算。其中，rank为RANK()函数产生的序号，rows为当前窗口的记录总行数
  • 应用场景：不常用

给窗口指定别名：WINDOW w AS (PARTITION BY stu_id ORDER BY score)
rows = 5

mysql> SELECT
    -> RANK() OVER w AS rk,
    -> PERCENT_RANK() OVER w AS prk,
    -> stu_id, lesson_id, score
    -> FROM t_score
    -> WHERE stu_id = 1
    -> WINDOW w AS (PARTITION BY stu_id ORDER BY score)
    -> ;
+----+------+--------+-----------+-------+
| rk | prk  | stu_id | lesson_id | score |
+----+------+--------+-----------+-------+
|  1 |    0 |      1 | L003      |    79 |
|  2 | 0.25 |      1 | L002      |    86 |
|  3 |  0.5 |      1 | L004      |    88 |
|  4 | 0.75 |      1 | L005      |    98 |
|  4 | 0.75 |      1 | L001      |    98 |
+----+------+--------+-----------+-------+
CUME_DIST()
  • 用途：分组内小于、等于当前rank值的行数 / 分组内总行数
  • 应用场景：查询小于等于当前成绩（score）的比例

cd1：没有分区，则所有数据均为一组，总行数为8
cd2：按照lesson_id分成了两组，行数各为4

mysql> SELECT stu_id, lesson_id, score,
    -> CUME_DIST() OVER (ORDER BY score) AS cd1,
    -> CUME_DIST() OVER (PARTITION BY lesson_id ORDER BY score) AS cd2
    -> FROM t_score
    -> WHERE lesson_id IN ('L001','L002')
    -> ;
+--------+-----------+-------+-------+------+
| stu_id | lesson_id | score | cd1   | cd2  |
+--------+-----------+-------+-------+------+
|      2 | L001      |    84 | 0.125 | 0.25 |
|      1 | L001      |    98 |  0.75 |  0.5 |
|      4 | L001      |    99 | 0.875 | 0.75 |
|      3 | L001      |   100 |     1 |    1 |
|      1 | L002      |    86 |  0.25 | 0.25 |
|      4 | L002      |    88 | 0.375 |  0.5 |
|      2 | L002      |    90 |   0.5 | 0.75 |
|      3 | L002      |    91 | 0.625 |    1 |
+--------+-----------+-------+-------+------+
③前后函数：LAG(expr,n)、LEAD(expr,n)
  • 用途：返回位于当前行的前n行（LAG(expr,n)）或后n行（LEAD(expr,n)）的expr的值
  • 应用场景：查询前1名同学的成绩和当前同学成绩的差值

内层SQL先通过LAG()函数得到前1名同学的成绩，外层SQL再将当前同学和前1名同学的成绩做差得到成绩差值diff。

mysql> SELECT stu_id, lesson_id, score, pre_score,
    -> score-pre_score AS diff
    -> FROM(
    ->     SELECT stu_id, lesson_id, score,
    ->     LAG(score,1) OVER w AS pre_score
    ->     FROM t_score
    ->     WHERE lesson_id IN ('L001','L002')
    ->     WINDOW w AS (PARTITION BY lesson_id ORDER BY score)) t
    -> ;
+--------+-----------+-------+-----------+------+
| stu_id | lesson_id | score | pre_score | diff |
+--------+-----------+-------+-----------+------+
|      2 | L001      |    84 |      NULL | NULL |
|      1 | L001      |    98 |        84 |   14 |
|      4 | L001      |    99 |        98 |    1 |
|      3 | L001      |   100 |        99 |    1 |
|      1 | L002      |    86 |      NULL | NULL |
|      4 | L002      |    88 |        86 |    2 |
|      2 | L002      |    90 |        88 |    2 |
|      3 | L002      |    91 |        90 |    1 |
+--------+-----------+-------+-----------+------+
④头尾函数：FIRST_VALUE(expr)、LAST_VALUE(expr)
  • 用途：返回第一个（FIRST_VALUE(expr)）或最后一个（LAST_VALUE(expr)）expr的值
  • 应用场景：截止到当前成绩，按照日期排序查询第1个和最后1个同学的分数
添加新列：mysql> ALTER TABLE t_score ADD create_time DATE;

 
 

 
 
  • 1
mysql> SELECT stu_id, lesson_id, score, create_time,
    -> FIRST_VALUE(score) OVER w AS first_score,
    -> LAST_VALUE(score) OVER w AS last_score
    -> FROM t_score
    -> WHERE lesson_id IN ('L001','L002')
    -> WINDOW w AS (PARTITION BY lesson_id ORDER BY create_time)
    -> ;
+--------+-----------+-------+-------------+-------------+------------+
| stu_id | lesson_id | score | create_time | first_score | last_score |
+--------+-----------+-------+-------------+-------------+------------+
|      3 | L001      |   100 | 2018-08-07  |         100 |        100 |
|      1 | L001      |    98 | 2018-08-08  |         100 |         98 |
|      2 | L001      |    84 | 2018-08-09  |         100 |         99 |
|      4 | L001      |    99 | 2018-08-09  |         100 |         99 |
|      3 | L002      |    91 | 2018-08-07  |          91 |         91 |
|      1 | L002      |    86 | 2018-08-08  |          91 |         86 |
|      2 | L002      |    90 | 2018-08-09  |          91 |         90 |
|      4 | L002      |    88 | 2018-08-10  |          91 |         88 |
+--------+-----------+-------+-------------+-------------+------------+
⑤其它函数：NTH_VALUE(expr, n)、NTILE(n)
NTH_VALUE(expr,n)
  • 用途：返回窗口中第n个expr的值。expr可以是表达式，也可以是列名
  • 应用场景：截止到当前成绩，显示每个同学的成绩中排名第2和第3的成绩的分数
mysql> SELECT stu_id, lesson_id, score,
    -> NTH_VALUE(score,2) OVER w AS second_score,
    -> NTH_VALUE(score,3) OVER w AS third_score
    -> FROM t_score
    -> WHERE stu_id IN (1,2)
    -> WINDOW w AS (PARTITION BY stu_id ORDER BY score)
    -> ;
+--------+-----------+-------+--------------+-------------+
| stu_id | lesson_id | score | second_score | third_score |
+--------+-----------+-------+--------------+-------------+
|      1 | L003      |    79 |         NULL |        NULL |
|      1 | L002      |    86 |           86 |        NULL |
|      1 | L004      |    88 |           86 |          88 |
|      1 | L001      |    98 |           86 |          88 |
|      1 | L005      |    98 |           86 |          88 |
|      2 | L004      |    75 |         NULL |        NULL |
|      2 | L005      |    77 |           77 |        NULL |
|      2 | L001      |    84 |           77 |          84 |
|      2 | L003      |    86 |           77 |          84 |
|      2 | L002      |    90 |           77 |          84 |
+--------+-----------+-------+--------------+-------------+
NTILE(n)
  • 用途：将分区中的有序数据分为n个等级，记录等级数
  • 应用场景：将每门课程按照成绩分成3组
mysql> SELECT
    -> NTILE(3) OVER w AS nf,
    -> stu_id, lesson_id, score
    -> FROM t_score
    -> WHERE lesson_id IN ('L001','L002')
    -> WINDOW w AS (PARTITION BY lesson_id ORDER BY score)
    -> ;
+------+--------+-----------+-------+
| nf   | stu_id | lesson_id | score |
+------+--------+-----------+-------+
|    1 |      2 | L001      |    84 |
|    1 |      1 | L001      |    98 |
|    2 |      4 | L001      |    99 |
|    3 |      3 | L001      |   100 |
|    1 |      1 | L002      |    86 |
|    1 |      4 | L002      |    88 |
|    2 |      2 | L002      |    90 |
|    3 |      3 | L002      |    91 |
+------+--------+-----------+-------+

NTILE(n)函数在数据分析中应用较多，比如由于数据量大，需要将数据平均分配到n个并行的进程分别计算，此时就可以用NTILE(n)对数据进行分组（由于记录数不一定被n整除，所以数据不一定完全平均），然后将不同桶号的数据再分配。

4. 聚合函数作为窗口函数：
  • 用途：在窗口中每条记录动态地应用聚合函数（SUM()、AVG()、MAX()、MIN()、COUNT()），可以动态计算在指定的窗口内的各种聚合函数值
  • 应用场景：截止到当前时间，查询stu_id=1的学生的累计分数、分数最高的科目、分数最低的科目
mysql> SELECT stu_id, lesson_id, score, create_time,
    -> SUM(score) OVER w AS score_sum,
    -> MAX(score) OVER w AS score_max,
    -> MIN(score) OVER w AS score_min
    -> FROM t_score
    -> WHERE stu_id = 1
    -> WINDOW w AS (PARTITION BY stu_id ORDER BY create_time)
    -> ;
+--------+-----------+-------+-------------+-----------+-----------+-----------+

| stu_id | lesson_id | score | create_time | score_sum | score_max | score_min |

+--------+-----------+-------+-------------+-----------+-----------+-----------+

|      1 | L001      |    98 | 2018-08-08  |       184 |        98 |        86 |

|      1 | L002      |    86 | 2018-08-08  |       184 |        98 |        86 |

|      1 | L003      |    79 | 2018-08-09  |       263 |        98 |        79 |

|      1 | L004      |    88 | 2018-08-10  |       449 |        98 |        79 |

|      1 | L005      |    98 | 2018-08-10  |       449 |        98 |        79 |

+--------+-----------+-------+-------------+-----------+-----------+-----------+
mmdzm
关注
MySQL中的窗口函数
Meteor的笔记
03-27 578
窗口函数允许用户在查询结果集的一个子集上执行计算，这个子集被称为“窗口”。窗口函数通常与OVER()子句一起使用，以定义窗口的范围和顺序。它们可以用于计算聚合值（如总和、平均值等），也可以用于排名和排序。：为窗口中的每一行分配一个唯一的序号。RANK()和：为窗口中的每一行分配一个排名。LEAD()和LAG()：访问结果集中当前行的前一行或后一行的值。SUM()AVG()MIN()MAX()等聚合函数：在窗口上执行聚合计算。
MySQL窗口函数
m0_47581892的博客
01-24 210
MySQL窗口函数 窗口函数，也称为OLAP函数，联机分析处理。能够将表中数据划分一个范围，对范围内的数据进行某种处理，可以是聚合，也可以是排序，也可以是求第一个记录或者最后一个记录等等。对数据进行实时分析处理。 作用： 1.解决排名问题。e.g.每个班级按照成绩排名 2.解决top-n问题。e.g.每个班级前两名学生 1.语法 <窗口函数> over (partition by <列清单> order by <列清单>) partition by(分组)：用来圈定所要
Mysql 窗口函数
热门推荐
菜菜的大数据开发の路
03-04 3万+
一, MySQl 8.0 窗口函数 窗口函数适用场景: 对分组统计结果中的每一条记录进行计算的场景下, 使用窗口函数更好; 可以跟Hive的对比着看: 点我, 特么的花了一晚上整理, 没想到跟Hive 的基本一致, 还不因为好久没复习博客了, 淦 注意: mysql 因为没有array数据结构, 无法像Hive一样 行列进行转换; 1.1 窗口函数分类 MySQL从8.0版本开始支持窗口函数。窗口函数的作用类似于在查询中对数据进行分组，不同的是，分组操作会把分组的结果聚合成一条记录，而窗口函数是
MySQL高级特性窗口函数详解
最新发布
勤于笔记，成于点滴。
09-12 1156
MySQL窗口函数极大地丰富了SQL语言的功能，使得在数据库层面上进行复杂的数据分析成为可能。通过本文的介绍，应该对MySQL中的窗口函数有了更深入的理解，并能够在实际工作中灵活运用这些技巧来提高数据处理的效率和准确性。
MySQL 窗口函数
bt_xxx的博客
06-26 3544
MySQL窗口函数是一种强大的工具，用于在查询中执行复杂的统计分析，而不需要改变表的结构或数据。MySQL从8.0版本开始支持窗口函数，这些函数也被称为分析函数，因为它们能够处理相对复杂的报表统计分析场景。窗口的意思是将数据进行分组，每个分组即是一个窗口，这和使用聚合函数时的group by分组类似，但与聚合函数不同的地方是： 聚合函数（例如：sum/avg/min/max）会针对每个分组（窗口）聚合出一个结果（每一组返回一个结果）。
MYSQL窗口函数
qq_58112820的博客
11-25 376
mysql窗口函数与实例
32-视频33MySQL窗口函数
09-13
MySQL窗口函数是数据库查询中的一个重要概念，它在SQL语句中提供了一种处理分组数据的新方式，使得在不使用子查询或者自连接的情况下，也能实现复杂的数据分析和计算。在本节视频“32-视频33MySQL窗口函数”中，我们...
mysql窗口函数同比环比_MySQL利用窗口函数解决排名问题
weixin_33654339的博客
02-08 966
从MySQL8.0开始，MySQL增加了对窗口函数的支持。本文主要介绍如何利用窗口函数row_number(),dense_rank()和rank()解决相关排名问题。说明：本文中的SQL代码均测试于MySQL8.0.13环境。在总体上进行排名：# 创建学生成绩表：create table grade(id int,name varchar(10),score int (10))engine my...
Mysql 窗口函数rank
qq_22945381的博客
09-16 779
窗口函数 MySQL8.0之后支持窗口函数。窗口指的是记录集合，窗口函数是指在某种条件的记录集合上执行的特殊函数。静态窗口是指不同的记录对应的窗口大小是固定的，而滑动窗口是指随着记录的不同窗口的大小是动态变化的。 窗口函数分类 1） 专用窗口函数，包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。 2） 聚合函数，如sum. avg, count, max, min等 注意事项 窗口函数原则上只能写在select子句中，因为窗口函数是对where或者group by子句
数据分析实训 阶段二 模块二 mysql高级应用 by拉钩教育
qq_32795481的博客
08-30 284
一、窗口函数 1、窗口函数的定义 窗口函数，又称为分析函数，用于处理相对复杂的报表统计分析场景。mysql从8.0之后开始支持窗口函数。 窗口 窗口，又可以理解为记录的集合。 窗口函数也就是在满足某种条件的记录集合上执行的特殊函数。 窗口的分类 静态窗口：对于每条记录都要在此窗口内执行函数，窗口大小都是固定的，这种属于静态窗口； 滑动窗口：不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口。 窗口函数基本用法 函数名（[expr]） over子句 函数() over() over是关键字，用来指
mysql的窗口函数
记录一些工作日常和学习
10-13 253
窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据库数据进行实时分析处理 使用场景：组内排名 窗口函数的基本语法： <窗口函数> over (partition by <分组的列> order by <排序的列>) <窗口函数>的位置，可以放以下两种函数： 1） 专用窗口函数，rank, dense_rank, row_number等专用窗口函数。 2） 聚合函数，如sum. avg,
mysql窗口函数
qq_37627820的博客
08-14 1506
专用的窗口函数 CUME_DIST() CUME_DIST() OVER ( PARTITION BY expr, … ORDER BY expr [ASC | DESC], … ) 计算公式是： ROW_NUMBER() / total_rows 示例： 在这里插入代码片 ROW_NUMBER() / total_rows 3. DENSE_RANK() 4. FIRST_VALUE() 和 LAST_VALUE() 5. LAG() 和 LEAD() 6. NTH_VALUE() 7. N
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值