MySQL的窗口函数

最新推荐文章于 2024-06-17 15:36:01 发布
最新推荐文章于 2024-06-17 15:36:01 发布
阅读量277 收藏 1
点赞数 1
分类专栏： 数据库 文章标签： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/nixiang_888/article/details/109488197
版权

1. 应用场景

比如：医院看病，怎样知道上次就医距现在的时间？环比如何计算？怎么样得到各部门工资排名前N名员工列表?查找各部门每人工资占部门总工资的百分比？

对于这样的需求，使用传统的SQL实现起来比较困难。这类需求都有一个共同的特点，需要在单表中满足某些条件的记录集内部做一些函数操作，不是简单的表连接，也不是简单的聚合可以实现的，通常会让写SQL的同学焦头烂额、绞尽脑汁，费了大半天时间写出来一堆长长的晦涩难懂的自连接SQL，且性能低下，难以维护。

要解决此类问题，最方便的就是使用窗口函数。

2. 窗口简介

MySQL从8.0开始支持窗口函数，这个功能在大多商业数据库和部分开源数据库中早已支持，有的也叫分析函数。

什么叫窗口?

  • 它可以理解为记录集合，窗口函数也就是在满足某种条件的记录集合上执行的特殊函数。对于每条记录都要在此窗口内执行函数，有的函数随着记录不同，窗口大小都是固定的，这种属于静态窗口；有的函数则相反，不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口。

窗口函数和普通聚合函数也很容易混淆，二者区别如下：

  1. 聚合函数是将多条记录聚合为一条
  2. 而窗口函数是每条记录都会执行，有几条记录执行完还是几条。
  3. 聚合函数也可以用于窗口函数中。
  • 我们现已一个示例开始：
-- 数据结构
mysql> select * from order_tb;
+----------+---------+--------+---------------------+
| order_id | user_no | amount | create_date         |
+----------+---------+--------+---------------------+
|        1 | 0001    |    100 | 2020-10-01 00:00:00 |
|        2 | 0001    |    300 | 2020-10-02 00:00:00 |
|        3 | 0001    |    500 | 2020-10-03 00:00:00 |
|        4 | 0001    |    800 | 2020-10-04 00:00:00 |
|        5 | 0001    |    900 | 2020-10-05 00:00:00 |
|        6 | 0002    |    500 | 2020-10-06 00:00:00 |
|        7 | 0002    |    600 | 2020-10-07 00:00:00 |
|        8 | 0002    |    300 | 2020-10-08 00:00:00 |
|        9 | 0002    |    800 | 2020-10-09 00:00:00 |
|       10 | 0002    |    800 | 2020-10-10 00:00:00 |
+----------+---------+--------+---------------------+
10 rows in set (0.00 sec)

窗口函数应用实例

mysql> select * from
    -> (
    ->     select row_number() over (partition by user_no order by amount desc) as row_num,
    ->     order_id, user_no, amount, create_date
    ->     from order_tb
    -> ) as t;
+---------+----------+---------+--------+---------------------+
| row_num | order_id | user_no | amount | create_date         |
+---------+----------+---------+--------+---------------------+
|       1 |        5 | 0001    |    900 | 2020-10-05 00:00:00 |
|       2 |        4 | 0001    |    800 | 2020-10-04 00:00:00 |
|       3 |        3 | 0001    |    500 | 2020-10-03 00:00:00 |
|       4 |        2 | 0001    |    300 | 2020-10-02 00:00:00 |
|       5 |        1 | 0001    |    100 | 2020-10-01 00:00:00 |
|       1 |        9 | 0002    |    800 | 2020-10-09 00:00:00 |
|       2 |       10 | 0002    |    800 | 2020-10-10 00:00:00 |
|       3 |        7 | 0002    |    600 | 2020-10-07 00:00:00 |
|       4 |        6 | 0002    |    500 | 2020-10-06 00:00:00 |
|       5 |        8 | 0002    |    300 | 2020-10-08 00:00:00 |
+---------+----------+---------+--------+---------------------+
10 rows in set (0.00 sec)

上面例子中，row_number()over(partition by user_no order by amount desc)这部分都属于窗口函数，它的功能是显示每个用户按照订单金额从大到小排序的序号。

按照功能划分，可以把MySQL支持的窗口函数分为如下几类：

  • 序号函数：row_number() / rank() / dense_rank()
  • 分布函数：percent_rank() / cume_dist()
  • 前后函数：lag() / lead()
  • 头尾函数：first_val() / last_val()
  • 其他函数：nth_value() / nfile()

3.窗口函数的应用

窗口函数的基本用法如下：函数名（[expr]） over子句

其中，

  • over是关键字，用来指定函数执行的窗口范围，
  • 如果后面括号中什么都不写，则意味着窗口包含满足where条件的所有行，窗口函数基于所有行进行计算；
  • 如果不为空，则支持以下四种语法来设置窗口：
  1. window_name：给窗口指定一个别名，如果SQL中涉及的窗口较多，采用别名可以看起来更清晰易读。上面例子中如果指定一个别名w，则改写如下：
select * from
(
	select row_number()over w as row_num,
	order_id,user_no,amount,create_date
	from order_tab
	WINDOW w AS (partition by user_no order by amount desc)
) as t ;
  1. partition子句：窗口按照那些字段进行分组，窗口函数在不同的分组上分别执行。上面的例子就按照用户id进行了分组。在每个用户id上，按照order by的顺序分别生成从1开始的顺序编号。
  2. order by子句：按照哪些字段进行排序，窗口函数将按照排序后的记录顺序进行编号。可以和partition子句配合使用，也可以单独使用。上例中二者同时使用，如果没有partition子句，则会按照所有用户的订单金额排序来生成序号。
  3. frame子句：frame是当前分区的一个子集，frame子句用来定义子集的规则，通常用来作为滑动窗口使用。比如要根据每个订单动态计算包括本订单和按时间顺序前后两个订单的平均订单金额，则可以设置如下frame子句来创建滑动窗口。
滑动窗口说明
mysql> select * from (
    ->     select order_id, user_no, amount, avg(amount) over w as avg_num,
    ->     create_date
    ->     from order_tb
    ->     window w as (partition by user_no order by create_date desc rows between 1 preceding and 1 following)
    -> ) as t;
+----------+---------+--------+----------+---------------------+
| order_id | user_no | amount | avg_num  | create_date         |
+----------+---------+--------+----------+---------------------+
|        5 | 0001    |    900 | 850.0000 | 2020-10-05 00:00:00 |
|        4 | 0001    |    800 | 733.3333 | 2020-10-04 00:00:00 |
|        3 | 0001    |    500 | 533.3333 | 2020-10-03 00:00:00 |
|        2 | 0001    |    300 | 300.0000 | 2020-10-02 00:00:00 |
|        1 | 0001    |    100 | 200.0000 | 2020-10-01 00:00:00 |
|       10 | 0002    |    800 | 800.0000 | 2020-10-10 00:00:00 |
|        9 | 0002    |    800 | 633.3333 | 2020-10-09 00:00:00 |
|        8 | 0002    |    300 | 566.6667 | 2020-10-08 00:00:00 |
|        7 | 0002    |    600 | 466.6667 | 2020-10-07 00:00:00 |
|        6 | 0002    |    500 | 550.0000 | 2020-10-06 00:00:00 |
+----------+---------+--------+----------+---------------------+
10 rows in set (0.00 sec)

从结果可以看出，order_id为5订单属于边界值，没有前一行，因此平均订单金额为(900+800)/2=850；order_id为4的订单前后都有订单，所以平均订单金额为（900+800+300）/3=666.6667，以此类推就可以得到一个基于滑动窗口的动态平均订单值。此例中，窗口函数用到了传统的聚合函数avg()，用来计算动态的平均值。

对于滑动窗口的范围指定，有两种方式，基于行和基于范围，具体区别如下

基于行：

  • 通常使用BETWEEN frame_start AND frame_end语法来表示行范围，frame_start和frame_end可以支持如下关键字，来确定不同的动态行记录：
  1. CURRENT ROW 边界是当前行，一般和其他范围关键字一起使用
  2. UNBOUNDED PRECEDING 边界是分区中的第一行
  3. UNBOUNDED FOLLOWING 边界是分区中的最后一行
  4. expr PRECEDING 边界是当前行减去expr的值
  5. expr FOLLOWING 边界是当前行加上expr的值

比如，下面都是合法的范围：

  1. rows BETWEEN 1 PRECEDING AND 1 FOLLOWING 窗口范围是当前行、前一行、后一行一共三行记录。
  2. rows UNBOUNDED FOLLOWING 窗口范围是当前行到分区中的最后一行。
  3. rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 窗口范围是当前分区中所有行，等同于不写。

基于范围：

  • 和基于行类似，但有些范围不是直接可以用行数来表示的，比如希望窗口范围是一周前的订单开始，截止到当前行，则无法使用rows来直接表示，此时就可以使用范围来表示窗口：INTERVAL 7 DAY PRECEDING。Linux中常见的最近1分钟、5分钟负载是一个典型的应用场景。
静态窗口

有的函数不管有没有frame子句，它的窗口都是固定的，也就是前面介绍的静态窗口，这些函数包括如下：

CUME_DIST()
DENSE_RANK()
LAG()
LEAD()
NTILE()
PERCENT_RANK()
RANK()
ROW_NUMBER()

接下来我们以上例的订单表为例，来介绍每个函数的使用方法。表中各字段含义按顺序分别为订单号、用户id、订单金额、订单创建日期。

4. 常见窗口函数应用

4.1 序号函数

序号函数：row_number() / rank() / dense_rank()
用途：显示分区中的当前行号
使用场景：希望查询每个用户订单金额最高的前三个订单

mysql> select * from
    -> (
    ->     select row_number() over (partition by user_no order by amount desc) as row_num, order_id, user_no, amount, create_date
    ->     from order_tb
    -> ) as t where t.row_num <=3;
+---------+----------+---------+--------+---------------------+
| row_num | order_id | user_no | amount | create_date         |
+---------+----------+---------+--------+---------------------+
|       1 |        5 | 0001    |    900 | 2020-10-05 00:00:00 |
|       2 |        4 | 0001    |    800 | 2020-10-04 00:00:00 |
|       3 |        3 | 0001    |    500 | 2020-10-03 00:00:00 |
|       1 |        9 | 0002    |    800 | 2020-10-09 00:00:00 |
|       2 |       10 | 0002    |    800 | 2020-10-10 00:00:00 |
|       3 |        7 | 0002    |    600 | 2020-10-07 00:00:00 |
+---------+----------+---------+--------+---------------------+
6 rows in set (0.00 sec)

此时可以使用ROW_NUMBER()函数按照用户进行分组并按照订单金额进行由大到小排序，最后查找每组中序号<=3的记录。

对于用户‘002’的订单，大家发现订单金额为800的有两条，序号随机排了1和2，但很多情况下二者应该是并列第一，而订单为600的序号则可能是第二名，也可能为第三名，这时候，row_number就不能满足需求，需要rank()和dense_rank()出场。

  • 这两个函数和row_number()非常类似，只是在出现重复值时处理逻辑有所不同。

上面例子我们稍微改一下，需要查询不同用户的订单中，按照订单金额进行排序，显示出相应的排名序号，SQL中用row_number() / rank() / dense_rank()分别显示序号，我们看一下有什么差别：

mysql> SELECT * FROM
    -> (
    ->      SELECT
    ->           ROW_NUMBER() OVER(PARTITION BY user_no ORDER BY amount DESC) AS row_num_func,
    ->           RANK() OVER(PARTITION BY user_no ORDER BY amount DESC) AS rank_func,
    ->           DENSE_RANK() OVER(PARTITION BY user_no ORDER BY amount DESC) AS dese_rank_func,
    ->           order_id, user_no, amount, create_date
    ->      FROM order_tb
    -> ) AS t;
+--------------+-----------+----------------+----------+---------+--------+---------------------+
| row_num_func | rank_func | dese_rank_func | order_id | user_no | amount | create_date         |
+--------------+-----------+----------------+----------+---------+--------+---------------------+
|            1 |         1 |              1 |        5 | 0001    |    900 | 2020-10-05 00:00:00 |
|            2 |         2 |              2 |        4 | 0001    |    800 | 2020-10-04 00:00:00 |
|            3 |         3 |              3 |        3 | 0001    |    500 | 2020-10-03 00:00:00 |
|            4 |         4 |              4 |        2 | 0001    |    300 | 2020-10-02 00:00:00 |
|            5 |         5 |              5 |        1 | 0001    |    100 | 2020-10-01 00:00:00 |
|            1 |         1 |              1 |        9 | 0002    |    800 | 2020-10-09 00:00:00 |
|            2 |         1 |              1 |       10 | 0002    |    800 | 2020-10-10 00:00:00 |
|            3 |         3 |              2 |        7 | 0002    |    600 | 2020-10-07 00:00:00 |
|            4 |         4 |              3 |        6 | 0002    |    500 | 2020-10-06 00:00:00 |
|            5 |         5 |              4 |        8 | 0002    |    300 | 2020-10-08 00:00:00 |
+--------------+-----------+----------------+----------+---------+--------+---------------------+
10 rows in set (0.00 sec)

上面显示了三个函数的区别，row_number()在amount都是800的两条记录上随机排序，但序号按照1、2递增，后面amount为600的的序号继续递增为3，中间不会产生序号间隙；rank()/dense_rank()则把amount为800的两条记录序号都设置为1，但后续amount为600的分别设置为3（rank）和2（dense_rank）。即rank（）会产生序号相同的记录，同时可能产生序号间隙 ；而dense_rank（）也会产生序号相同的记录，但 不会产生序号间隙。

4.2 分布函数

分布函数：percent_rank()/cume_dist()。

① percent_rank()

用途：和之前的RANK()函数相关，每行按照如下公式进行计算：(rank - 1) / (rows - 1)

其中，rank为RANK()函数产生的秩，rows为当前窗口的记录总行数。

应用场景：求分位数位点

mysql> SELECT t.* FROM
    -> (
    ->      SELECT
    ->           RANK() OVER w AS Rank_num,
    ->           PERCENT_RANK() OVER w AS percent,
    ->           order_id, user_no, amount
    ->      FROM order_tb
    ->      WINDOW w AS (PARTITION BY user_no ORDER BY amount DESC)
    -> ) AS t;
+----------+---------+----------+---------+--------+
| Rank_num | percent | order_id | user_no | amount |
+----------+---------+----------+---------+--------+
|        1 |       0 |        5 | 0001    |    900 |
|        2 |    0.25 |        4 | 0001    |    800 |
|        3 |     0.5 |        3 | 0001    |    500 |
|        4 |    0.75 |        2 | 0001    |    300 |
|        5 |       1 |        1 | 0001    |    100 |
|        1 |       0 |        9 | 0002    |    800 |
|        1 |       0 |       10 | 0002    |    800 |
|        3 |     0.5 |        7 | 0002    |    600 |
|        4 |    0.75 |        6 | 0002    |    500 |
|        5 |       1 |        8 | 0002    |    300 |
+----------+---------+----------+---------+--------+
10 rows in set (0.00 sec)

从结果看出，percent列按照公式(rank - 1) / (rows - 1)带入rank值（row_num列）和rows值（user_no为‘001’和‘002’的值均为5）。

②cume_dist()

解释：分组内小于等于当前rank值的行数 / 分组内总行数的比值。

计算公式：等于某一rank()值的行数 / 分组内总行数；取值在（0, 1]之间

应用场景：大于等于当前订单金额的订单比例有多少。

mysql> SELECT t.* FROM
    -> (
    ->      SELECT
    ->           RANK() OVER w AS Rank_num,
    ->           PERCENT_RANK() OVER w AS percent,
    ->           CUME_DIST() OVER w AS cume,
    ->           order_id, user_no, amount
    ->      FROM order_tb
    ->      WINDOW w AS (PARTITION BY user_no ORDER BY amount DESC)
    -> ) AS t;
+----------+---------+------+----------+---------+--------+
| Rank_num | percent | cume | order_id | user_no | amount |
+----------+---------+------+----------+---------+--------+
|        1 |       0 |  0.2 |        5 | 0001    |    900 |
|        2 |    0.25 |  0.4 |        4 | 0001    |    800 |
|        3 |     0.5 |  0.6 |        3 | 0001    |    500 |
|        4 |    0.75 |  0.8 |        2 | 0001    |    300 |
|        5 |       1 |    1 |        1 | 0001    |    100 |
|        1 |       0 |  0.4 |        9 | 0002    |    800 |
|        1 |       0 |  0.4 |       10 | 0002    |    800 |
|        3 |     0.5 |  0.6 |        7 | 0002    |    600 |
|        4 |    0.75 |  0.8 |        6 | 0002    |    500 |
|        5 |       1 |    1 |        8 | 0002    |    300 |
+----------+---------+------+----------+---------+--------+
10 rows in set (0.00 sec)

列cume显示了预期的数据分布结果。

4.3 前后函数

前后函数：lead(字段名, n) / lag(字段名, n)。
用途：分区中位于当前行的下n行（lead）/ 上n行(lag)的记录值。
使用场景：查询上一个订单距离当前订单的时间间隔。

mysql> SELECT order_id, user_no, amount, create_date, last_date, DATEDIFF(create_date, last_date) AS diff FROM
    -> (
    ->      SELECT
    ->   order_id, user_no, amount, create_date,
    ->   LAG(create_date, 1) OVER w AS last_date
    ->      FROM order_tb
    ->      WINDOW w AS (PARTITION BY user_no ORDER BY create_date)
    -> ) AS t;
+----------+---------+--------+---------------------+---------------------+------+
| order_id | user_no | amount | create_date         | last_date           | diff |
+----------+---------+--------+---------------------+---------------------+------+
|        1 | 0001    |    100 | 2020-10-01 00:00:00 | NULL                | NULL |
|        2 | 0001    |    300 | 2020-10-02 00:00:00 | 2020-10-01 00:00:00 |    1 |
|        3 | 0001    |    500 | 2020-10-03 00:00:00 | 2020-10-02 00:00:00 |    1 |
|        4 | 0001    |    800 | 2020-10-04 00:00:00 | 2020-10-03 00:00:00 |    1 |
|        5 | 0001    |    900 | 2020-10-05 00:00:00 | 2020-10-04 00:00:00 |    1 |
|        6 | 0002    |    500 | 2020-10-06 00:00:00 | NULL                | NULL |
|        7 | 0002    |    600 | 2020-10-07 00:00:00 | 2020-10-06 00:00:00 |    1 |
|        8 | 0002    |    300 | 2020-10-08 00:00:00 | 2020-10-07 00:00:00 |    1 |
|        9 | 0002    |    800 | 2020-10-09 00:00:00 | 2020-10-08 00:00:00 |    1 |
|       10 | 0002    |    800 | 2020-10-10 00:00:00 | 2020-10-09 00:00:00 |    1 |
+----------+---------+--------+---------------------+---------------------+------+
10 rows in set (0.00 sec)

内层SQL先通过lag函数得到上一次订单的日期，外层SQL再将本次订单和上次订单日期做差得到时间间隔diff。

4.4 头尾函数

头尾函数：first_val(expr)/last_val(expr)。

expr：可以是表达式，也可以是列明

用途：得到分区中的第一个/最后一个指定参数的值。
使用场景：查询截止到当前订单，按照日期排序第一个订单和最后一个订单的订单金额。

mysql> SELECT * FROM
    -> (
    ->      SELECT
    ->   order_id, user_no, amount, create_date,
    ->   FIRST_VALUE(amount) OVER w AS first_mount,
    ->   LAST_VALUE(amount) OVER w AS last_mount
    ->      FROM order_tb
    ->      WINDOW w AS (PARTITION BY user_no ORDER BY create_date)
    -> ) AS t;
+----------+---------+--------+---------------------+-------------+------------+
| order_id | user_no | amount | create_date         | first_mount | last_mount |
+----------+---------+--------+---------------------+-------------+------------+
|        1 | 0001    |    100 | 2020-10-01 00:00:00 |         100 |        100 |
|        2 | 0001    |    300 | 2020-10-02 00:00:00 |         100 |        300 |
|        3 | 0001    |    500 | 2020-10-03 00:00:00 |         100 |        500 |
|        4 | 0001    |    800 | 2020-10-04 00:00:00 |         100 |        800 |
|        5 | 0001    |    900 | 2020-10-05 00:00:00 |         100 |        900 |
|        6 | 0002    |    500 | 2020-10-06 00:00:00 |         500 |        500 |
|        7 | 0002    |    600 | 2020-10-07 00:00:00 |         500 |        600 |
|        8 | 0002    |    300 | 2020-10-08 00:00:00 |         500 |        300 |
|        9 | 0002    |    800 | 2020-10-09 00:00:00 |         500 |        800 |
|       10 | 0002    |    800 | 2020-10-10 00:00:00 |         500 |        800 |
+----------+---------+-----

结果和预期一致，比如order_id为4的记录，first_amount和last_amount分别记录了用户‘001’截止到时间2018-01-03 00:00:00为止，第一条订单金额100和最后一条订单金额800，注意这里是按时间排序的最早订单和最晚订单，并不是最小金额和最大金额订单。

4.5 其他函数

其他函数：nth_value(expr,n)/nfile(n）。

① nth_value(expr,n)

用途：返回窗口中第N个expr的值，expr可以是表达式，也可以是列名。
应用场景：每个用户订单中显示本用户金额排名第二和第三的订单金额。

mysql> SELECT * FROM
    -> (
    ->      SELECT
    ->   order_id, user_no, amount, create_date,
    ->   NTH_VALUE(amount, 2) OVER w AS second_mount,
    ->   NTH_VALUE(amount, 3) OVER w AS third_mount
    ->      FROM order_tb
    ->      WINDOW w AS (PARTITION BY user_no ORDER BY create_date)
    -> ) AS t;
+----------+---------+--------+---------------------+--------------+-------------+
| order_id | user_no | amount | create_date         | second_mount | third_mount |
+----------+---------+--------+---------------------+--------------+-------------+
|        1 | 0001    |    100 | 2020-10-01 00:00:00 |         NULL |        NULL |
|        2 | 0001    |    300 | 2020-10-02 00:00:00 |          300 |        NULL |
|        3 | 0001    |    500 | 2020-10-03 00:00:00 |          300 |         500 |
|        4 | 0001    |    800 | 2020-10-04 00:00:00 |          300 |         500 |
|        5 | 0001    |    900 | 2020-10-05 00:00:00 |          300 |         500 |
|        6 | 0002    |    500 | 2020-10-06 00:00:00 |         NULL |        NULL |
|        7 | 0002    |    600 | 2020-10-07 00:00:00 |          600 |        NULL |
|        8 | 0002    |    300 | 2020-10-08 00:00:00 |          600 |         300 |
|        9 | 0002    |    800 | 2020-10-09 00:00:00 |          600 |         300 |
|       10 | 0002    |    800 | 2020-10-10 00:00:00 |          600 |         300 |
+----------+---------+--------+---------------------+--------------+-------------+
10 rows in set (0.00 sec)
②ntile(n)

用途：将分区中的有序数据分为n个桶，记录桶号。
应用场景：将每个用户的订单按照订单金额分成3组。

mysql> SELECT * FROM
    -> (
    ->      SELECT
    ->           NTILE(3) OVER w AS nf,
    ->           order_id, user_no, amount, create_date
    ->      FROM order_tb
    ->      WINDOW w AS (PARTITION BY user_no ORDER BY amount DESC)
    -> ) AS t;
+------+----------+---------+--------+---------------------+
| nf   | order_id | user_no | amount | create_date         |
+------+----------+---------+--------+---------------------+
|    1 |        5 | 0001    |    900 | 2020-10-05 00:00:00 |
|    1 |        4 | 0001    |    800 | 2020-10-04 00:00:00 |
|    2 |        3 | 0001    |    500 | 2020-10-03 00:00:00 |
|    2 |        2 | 0001    |    300 | 2020-10-02 00:00:00 |
|    3 |        1 | 0001    |    100 | 2020-10-01 00:00:00 |
|    1 |        9 | 0002    |    800 | 2020-10-09 00:00:00 |
|    1 |       10 | 0002    |    800 | 2020-10-10 00:00:00 |
|    2 |        7 | 0002    |    600 | 2020-10-07 00:00:00 |
|    2 |        6 | 0002    |    500 | 2020-10-06 00:00:00 |
|    3 |        8 | 0002    |    300 | 2020-10-08 00:00:00 |
+------+----------+---------+--------+---------------------+
10 rows in set (0.00 sec)

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

4.6 聚合函数作为窗口函数

用途：在窗口中每条记录动态应用聚合函数(sum/avg/max/min/count)，可以动态计算在指定的窗口内的各种聚合函数值。

应用场景：每个用户按照订单id，截止到当前的累计订单金额/平均订单金额/最大订单金额/最小订单金额/订单数是多少？

mysql> SELECT * FROM
    -> (
    ->      SELECT
    ->   order_id, user_no, amount, create_date,
    ->   SUM(amount) OVER w AS sum1,
    ->   AVG(amount) OVER w AS avg1,
    ->   MAX(amount) OVER w AS max1,
    ->   MIN(amount) OVER w AS min1,
    ->   COUNT(amount) OVER w AS count1
    ->      FROM order_tb
    ->      WINDOW w AS (PARTITION BY user_no ORDER BY order_id)
    -> ) AS t;
+----------+---------+--------+---------------------+------+----------+------+------+--------+
| order_id | user_no | amount | create_date         | sum1 | avg1     | max1 | min1 | count1 |
+----------+---------+--------+---------------------+------+----------+------+------+--------+
|        1 | 0001    |    100 | 2020-10-01 00:00:00 |  100 | 100.0000 |  100 |  100 |      1 |
|        2 | 0001    |    300 | 2020-10-02 00:00:00 |  400 | 200.0000 |  300 |  100 |      2 |
|        3 | 0001    |    500 | 2020-10-03 00:00:00 |  900 | 300.0000 |  500 |  100 |      3 |
|        4 | 0001    |    800 | 2020-10-04 00:00:00 | 1700 | 425.0000 |  800 |  100 |      4 |
|        5 | 0001    |    900 | 2020-10-05 00:00:00 | 2600 | 520.0000 |  900 |  100 |      5 |
|        6 | 0002    |    500 | 2020-10-06 00:00:00 |  500 | 500.0000 |  500 |  500 |      1 |
|        7 | 0002    |    600 | 2020-10-07 00:00:00 | 1100 | 550.0000 |  600 |  500 |      2 |
|        8 | 0002    |    300 | 2020-10-08 00:00:00 | 1400 | 466.6667 |  600 |  300 |      3 |
|        9 | 0002    |    800 | 2020-10-09 00:00:00 | 2200 | 550.0000 |  800 |  300 |      4 |
|       10 | 0002    |    800 | 2020-10-10 00:00:00 | 3000 | 600.0000 |  800 |  300 |      5 |
+----------+---------+--------+---------------------+------+----------+------+------+--------+
10 rows in set (0.00 sec)
Xiaofei@IDO
关注
  • 1
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
MySQL窗口函数
weixin_48352526的博客
04-20 204
第5行：由于整数除整数得到的还是整数，而增长率是一个小数，所以要对其中一个数据类型进行修改，cast（gmv as double）就是讲gmv改成double类型的数据。{range|rows} between frame_start and frame_end：从指定行开始到指定行结束。{range|rows} frame_start：range、rows二选一，从指定行开始到当前行结束。lag(expression,n)：返回当前行的前n行。row_number()：序号不重复，序号连续。
MySQL开窗函数
asdfadafd的博客
07-28 973
*窗口函数**窗口+函数窗口函数运行时计算的数据集的范围函数运行时的函数聚合函数内置窗口函数取值取窗口第一个值；LAST_VALUE取窗口最后一个值；串行LEAD窗口内向下第n行的值；LAG窗口内向上第n行的值；排序NTILE把数据平均分配指定N个桶，如果不能平均分配，优先分配到编号小的里面；RANK从1开始，按照顺序相同会重复名次会留下空的位置生成组内的记录编号；ROW_NUMBERDENSE_RANK。...
窗口函数简介与总结
kiritobryant的博客
11-24 1万+
窗口函数是 SQL 中一类特别的函数。和聚合函数相似，窗口函数的输入也是多行记录。不 同的是，聚合函数的作用于由 GROUP BY 子句聚合的组，而窗口函数则作用于一个窗口， 这里，窗口是由一个 OVER 子句 定义的多行记录。聚合函数对其所作用的每一组记录输 出一条结果，而窗口函数对其所作用的窗口中的每一行记录输出一条结果。一些聚合函 数，如 sum, max, min, avg,count 等也可以当作窗口函数使用。
窗口函数概述
hsabrina的博客
08-04 7626
窗口函数，也叫OLAP(Online Anallytical Processing,联机分析处理）,可以对数据库数据进行实时分析处理窗口函数有以下功能：1）同时具有分组（partiton by）和排序（order by） 的功能2）不减少原表的行数，所有经常用来在每组内排名注意事项：窗口函数原则上只能写在select 子句中窗口函数使用场景：1）经典top N问题，比如找出每个部门排名前N的员工进行奖励2）经典排名问题，比如每个部门按业绩来排名。...
深入理解窗口函数及其应用
最新发布
weixin_43977307的博客
06-17 669
窗口函数（Window Function）是一种在关系型数据库中执行聚合、分析和排序操作的高级工具。与常规的聚合函数（如SUM、AVG、COUNT等）不同，窗口函数允许在结果集中的每一行上进行计算，而不需要将数据分组。窗口函数提供了对每一行的子集（称为窗口）执行计算的能力，同时保留原始数据行的上下文信息。想象一家咖啡馆，里面有一堵特殊的墙，我们称之为“洞察之墙”。这堵墙背后有一个魔法窗户，这就是窗口函数。
含泪整理的超全窗口函数：数据开发必备
涤生大数据
11-08 2273
我们可以使用last_value(col,true/false) over()，作用是：取分组内排序后，截止到当前行，最后一个值。所以，如果使用 order by 排序的时候，想要取最后一个值，需要与 rows between unbounded preceding and unbounded following 连用。1.当第二个参数为 true 的时候，会跳过空值；2.当 over() 中不指定排序的时候，会默认使用表中数据的原排序。
MySQL窗口函数（MySQL Window Functions）
Vincent的博客
05-17 1万+
百分比排序，返回当前行在组内的百分比位置，返回值范围为[0, 1]，可以用 当前行排序/(行数-1) 计算得出，但与rank不同，这里排序是从0开始而不是从1（相当于rank-1），因此第1行的百分比位置是0%，相当于：（rank-1）/(rows-1)。没有参数，返回当前行在组内的位置编号，从1开始，order by子句会影响行的编号顺序，如果没有order by，那么行的编号是不确定的。另外，即使行完全相同，它们的编号也是不同的，这点和后面的 rank() 和 dense_rank() 不同。
常用的窗口函数
简之的通向技术之路
07-11 2万+
1、什么是窗口函数 含义：窗口函数也叫OLAP函数（Online Anallytical Processing,联机分析处理），可以对数据进行实时分析处理。 作用： 解决排名问题，e.g.每个班级按成绩排名 解决TOPN问题，e.g.每个班级前两名的学生 语法： select 窗口函数 over (partition by 用于分组的列名， order by 用于排序的列名 分类： 排名函数：row_number()，rank()，dense_rank() 聚合函数：max()，min()，count
32-视频33MySQL窗口函数
09-13
MySQL窗口函数是数据库查询中的一个重要概念，它在SQL语句中提供了一种处理分组数据的新方式，使得在不使用子查询或者自连接的情况下，也能实现复杂的数据分析和计算。在本节视频“32-视频33MySQL窗口函数”中，我们...
MySQL窗口函数：展开数据的魔法.md
08-15
MySQL窗口函数：展开数据的魔法.md
mysql-窗口函数概述
12-21
mysql中窗口函数的概述以及一些数据练习，可以更好的对窗口函数理解。 什么是窗口函数：可以像聚合函数一样对一组数据进行分析并返回结果，二者的不同之处在于，窗口函数不是将一组数据汇总成单个结果，而是为每一行...
Mysql8.0使用窗口函数解决排序问题
09-08
MySQL 8.0 引入了窗口函数，这是一种强大的数据处理工具，能够在满足特定条件的记录集合（窗口）上执行分析操作，而不仅仅是简单的聚合。窗口函数与传统的聚合函数（如 SUM, AVG, COUNT 等）不同，它们在处理数据时...
MySQL8.0窗口函数入门实践及总结
09-08
MySQL8.0窗口函数是数据库查询中的一个重要特性，它使得在处理复杂的数据分析和统计时变得更加便捷。在MySQL8.0之前，如果要进行类似的数据排名或统计，通常需要借助子查询或自连接等复杂操作，而窗口函数的引入极大...
深入理解窗口函数和使用介绍
wt334502157的博客
08-23 2971
想象一家咖啡馆，里面有一堵特殊的墙，我们称之为“洞察之墙”。这堵墙背后有一个魔法窗户，这就是窗口函数。这个窗户可以让你透过它去看到一系列的数据，但是你可以使用特殊的工具，比如望远镜和魔法棒，来查看和分析这些数据。 现在，让我们来看看这些工具的具体用途： 1. **望远镜 - RANK、DENSE_RANK、ROW_NUMBER：** 想象你站在窗户前，你可以通过使用望远镜，看到墙上的标识。这些标识告诉你每个数据在排序中的位置，就像在一个比赛中知道运动员的排名一样。RANK表示前几名的排名，DENSE_
【必看】最全开窗函数讲解和实战指南
赵英超的博客
03-20 4725
窗口函数
还不会窗口函数？进来看看
weixin_46111235的博客
11-12 325
窗口函数（Window Function）是一种在数据库中执行实时分析处理的函数。它可以用于排序并生成序列号，对一组相关的数据行（称为窗口）进行计算，并生成每个窗口内的聚合值。窗口函数允许用户对数据进行更灵活的分析和处理，例如计算排名、分组和计算移动平均等。窗口函数的使用需要使用OVER子句来定义窗口的规则，并可以结合聚合函数（如sum、avg、count、max、min等）对数据进行计算。常见的窗口函数包括RANK()NTILE()LAG()和LEAD()等。
SQL窗口函数
热门推荐
每天学习一点点
04-18 4万+
SQL窗口函数为在线分析处理（OLAP）和商业智能（BI）提供了复杂分析和报表统计的功能，例如产品的累计销售额统计、分类排名、同比/环比分析等。这些功能通常很难通过聚合函数和分组操作来实现。 窗口函数（Window Function）可以像聚合函数一样对一组数据进行分析并返回结果，二者的不同之处在于，窗口函数不是将一组数据汇总成单个结果，而是为每一行数据都返回一个结果。聚合函数和窗口函数的区别如下图所示。 以SUM函数为例演示这两种函数的差异，以下语句中的SUM()是一个聚合函数： S.
窗口函数
kkill_youokk_的博客
05-17 3427
窗口函数一、窗口函数二、hive窗口函数1.over()窗口函数的语法结构1.1 over()函数中的三个函数讲解1.order by2.partition by3.rows between 开始位置 and 结束位置2.常与over()一起使用的分析函数2.1 聚合类2.2 排名类2.3 其他 一、窗口函数 窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据库数据进行实时分析处理。 窗口函数的基本语法如下： <窗口函数> over
窗口函数简介
我是一名保安
02-22 1257
窗口函数（Window Function）是一种在关系型数据库中用于。窗口函数可以对查询结果集中的行进行分组、排序，并在每个分组内进行聚合、排名、计算等操作，而不会改变原始查询结果的行数或顺序。
mysql 窗口函数
07-27
MySQL 窗口函数是一种在查询结果集中进行计算和排序的功能。它们能够对查询结果集中的每一行进行计算，而不依赖于单独的聚合函数或分组操作。以下是一些常见的 MySQL 窗口函数： 1. ROW_NUMBER(): 为结果集中的每一行分配一个唯一的整数值。 2. RANK(): 计算结果集中每一行的排名，如果出现相同的值，将会有相同的排名。 3. DENSE_RANK(): 与 RANK() 类似，但是在出现相同值时不会跳过排名。 4. LAG(): 获取当前行之前指定偏移量的行的值。 5. LEAD(): 获取当前行之后指定偏移量的行的值。 6. FIRST_VALUE(): 返回结果集中第一行的指定列的值。 7. LAST_VALUE(): 返回结果集中最后一行的指定列的值。 8. NTILE(n): 将结果集划分为 n 个等大小的桶，并为每个桶分配一个编号。 这些是只是一些常见的 MySQL 窗口函数，还有其他更多的函数可用。你可以在 MySQL 官方文档中找到更多关于窗口函数的详细信息和用法示例。
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值