精华内容
下载资源
问答
  • MySQL的窗口函数

    2020-11-04 20:42:52
    1. 应用场景 比如:医院看病,怎样知道上次就医距现在时间?环比如何计算?怎么样得到各部门工资排名前N名员工列表?查找各部门每人工资占部门总工资...要解决此类问题,最方便就是使用窗口函数。 2. 窗口简介

    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)
    
    展开全文
  • 本文将介绍一些常用的窗口函数的用法。窗口函数按照实现方式分成两种:一种是非聚合窗口函数,另外一种是聚合窗口函数。 非聚合窗口函数是相对于聚合窗口函数来说的。聚合函数是对一组数据计算后返回单个值(即分组...

    窗口函数在统计类的需求中很常见,稍微复杂一点的查询需求就有可能用到它,使用窗口函数可以极大的简化我们的 SQL 语句。像 Oracle、SQL Server 这些数据库在较早的版本就支持窗口函数了,MySQL 直到 8.0 版本后才支持它。

    本文将介绍一些常用的窗口函数的用法。窗口函数按照实现方式分成两种:一种是非聚合窗口函数,另外一种是聚合窗口函数。

    非聚合窗口函数是相对于聚合窗口函数来说的。聚合函数是对一组数据计算后返回单个值(即分组),非聚合函数一次只会处理一行数据。窗口聚合函数在行记录上计算某个字段的结果时,可将窗口范围内的数据输入到聚合函数中,并不改变行数。

    1 非聚合窗口函数

    MySQL 支持的非聚合窗口函数见表1。

    名称 描述
    CUME_DIST() 累积分配值
    DENSE_RANK() 当前行在其分区中的排名,稠密排序
    FIRST_VALUE() 指定区间范围内的第一行的值
    LAG() 取排在当前行之前的值
    LAST_VALUE() 指定区间范围内的最后一行的值
    LEAD() 取排在当前行之后的值
    NTH_VALUE() 指定区间范围内第N行的值
    NTILE() 将数据分到N个桶,当前行所在的桶号
    PERCENT_RANK() 排名值的百分比
    RANK() 当前行在其分区中的排名,稀疏排序
    ROW_NUMBER() 分区内当前行的行号
    表1 非聚合窗口函数列表

    常用的函数有:ROW_NUMBER()RANK()DENSE_RANK()LEAD()LAG()NTH_VALUE()FIRST_VALUE()LAST_VALUE()

    • ROW_NUMBER()RANK()DENSE_RANK()

      ROW_NUMBER()RANK()DENSE_RANK()都是排序函数,都可以给区间内的数生成序号。如果区间内不存在重复值,它们的计算结果一样。

      当出现重复值时,ROW_NUMBER()不考虑重复值,它会给相同的两个值分配不同的编号,编号的范围是从 1 到分区的行数。RANK()DENSE_RANK() 给重复的值生成相同的编号。不同的是,RANK()生成的序号有间隙,即重复值的下一项的编号和重复值的编号并不连续(下一项的值的编号 \neq 当前重复值项的编号+1),而DENSE_RANK()就不是这样。

      这几个函数的区别可结合下面的例子分析。

      SELECT 
        sal,
        ROW_NUMBER() OVER(ORDER BY sal) AS 'rn',
        RANK() OVER(ORDER BY sal) AS 'rk',
        DENSE_RANK() OVER(ORDER BY sal) AS 'dk' 
      FROM
        emp;
        
      sal          rn      rk      dk  
      -------  ------  ------  --------
      800.00        1       1         1
      950.00        2       2         2
      1100.00       3       3         3
      1250.00       4       4         4
      1250.00       5       4         4
      1300.00       6       6         5
      1500.00       7       7         6
      1600.00       8       8         7
      2450.00       9       9         8
      2850.00      10      10         9
      2975.00      11      11        10
      3000.00      12      12        11
      3000.00      13      12        11
      5000.00      14      14        12
      

      如果没有指定 partition by 分区字段,那么窗口函数操作的区间就是全部数据。我们可以让函数只作用在 deptno 分区。

      SELECT 
        sal,
        deptno,
        ROW_NUMBER() OVER(
            PARTITION BY deptno 
            ORDER BY sal) AS 'rn'
      FROM
        emp;
        
      sal      deptno      rn  
      -------  ------  --------
      1300.00      10         1
      2450.00      10         2
      5000.00      10         3
      800.00       20         1
      1100.00      20         2
      2975.00      20         3
      3000.00      20         4
      3000.00      20         5
      950.00       30         1
      1250.00      30         2
      1250.00      30         3
      1500.00      30         4
      1600.00      30         5
      2850.00      30         6
      

      如果我们要获取 emp 表中每个部门工资最高的前两名员工的信息,使用 ROW_NUMBER() 就可以这么写。

      SELECT 
        * 
      FROM
        (SELECT 
          empno,
          ename,
          deptno,
          sal,
          ROW_NUMBER() OVER(
            PARTITION BY deptno 
        ORDER BY sal DESC
        ) AS rn 
        FROM
          emp) t 
      WHERE rn <= 2; 
      
      
       empno  ename   deptno  sal          rn  
      ------  ------  ------  -------  --------
        7839  KING        10  5000.00         1
        7782  CLARK       10  2450.00         2
        7788  SCOTT       20  3000.00         1
        7902  FORD        20  3000.00         2
        7698  BLAKE       30  2850.00         1
        7499  ALLEN       30  1600.00         2
      

      注意,如果在OVER() 中没有ORDER 子句,那么,ROW_NUMBER()生成的编号是不确定的,而RANK()DENSE_RANK() 生成的编号都是 1 。

    • LAG()LEAD()

      LAG() 可以获得位于当前行之前的数据,如果指定了分区,则获取数据的范围只能在分区内。默认是获取上一条的记录,如果没有获取到,则返回 NULL。

      LAG() 的表达式是LAG(expr [, N[, default]]) [null_treatment]over_clause ,我们可以指定向后获取第 N 行,以及在获取不到数据时指定默认值。

      LEAD() 的表达式和 LAG() 是一样的,因此在 LEAD() 中也可以指定获取的行数 N 及默认值。

      SELECT 
        empno,
        sal,
        LAG(sal) OVER(ORDER BY empno)AS 'lag',
        LEAD(sal) OVER(ORDER BY empno)AS 'lead',
        LAG(sal,2,0) OVER(ORDER BY empno)AS 'lag_2'
      FROM
        emp LIMIT 6;
      
      
       empno  sal      lag      lead     lag_2    
      ------  -------  -------  -------  ---------
        7369  800.00   (NULL)   1600.00  0.00     
        7499  1600.00  800.00   1250.00  0.00     
        7521  1250.00  1600.00  2975.00  800.00   
        7566  2975.00  1250.00  1250.00  1600.00  
        7654  1250.00  2975.00  2850.00  1250.00  
        7698  2850.00  1250.00  2450.00  2975.00  
      

      我们使用 LAG(sal,2,0) 获取当前行向前偏移 2 行的值,在当前行的编号是 1 和 2 时,由于偏移的行不存在,只能返回默认值 0 。

    • FIRST_VALUE()LAST_VALUE()NTH_VALUE()

      这几个函数可以分别获取区间范围内第一行、最后一行、第 N 行的值。

      SELECT 
        empno,
        deptno,
        FIRST_VALUE(empno) OVER(
            PARTITION BY deptno 
            ORDER BY empno)AS 'first',
        LAST_VALUE(empno) OVER(
            PARTITION BY deptno ORDER BY empno 
            ROWS BETWEEN unbounded preceding 
            AND unbounded following)AS 'last',
        NTH_VALUE(empno,2) OVER(
            PARTITION BY deptno 
            ORDER BY empno)AS 'nth_2'
      FROM
        emp;
        
        
       empno  deptno   first    last   nth_2  
      ------  ------  ------  ------  --------
        7782      10    7782    7934    (NULL)
        7839      10    7782    7934      7839
        7934      10    7782    7934      7839
        7369      20    7369    7902    (NULL)
        7566      20    7369    7902      7566
        7788      20    7369    7902      7566
        7876      20    7369    7902      7566
        7902      20    7369    7902      7566
        7499      30    7499    7900    (NULL)
        7521      30    7499    7900      7521
        7654      30    7499    7900      7521
        7698      30    7499    7900      7521
        7844      30    7499    7900      7521
        7900      30    7499    7900      7521
      

      当在OVER() 中指定了排序字段, FIRST_VALUE()LAST_VALUE()NTH_VALUE() 这几个函数的滑动窗口范围是从第一行到当前行(即 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),直接使用 LAST_VALUE() 得到的结果并不是我们直觉上想看到的那样。因此,需要把LAST_VALUE()的窗口范围改成 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

      关于滑动窗口更加详细的描述,在后面有讲到。

    • NTILE()

      NTILE(N)将一个分区划分为N个组(桶),给分区中的每一行分配其桶号,并返回其分区中当前行的桶号。

      SELECT 
        empno,
        deptno,
        NTILE(2) OVER(
            PARTITION BY deptno 
            ORDER BY empno) AS ntile2
      FROM
        emp;
        
      
       empno  deptno  ntile2  
      ------  ------  --------
        7782      10         1
        7839      10         1
        7934      10         2
        7369      20         1
        7566      20         1
        7788      20         1
        7876      20         2
        7902      20         2
        7499      30         1
        7521      30         1
        7654      30         1
        7698      30         2
        7844      30         2
        7900      30         2
      
    • CUME_DIST()

      统计一组数据中小于等于(或大于等于,和 OVER() 中指定的排序行为有关系)当前行的值的百分比。

      SELECT 
        sal,
        ROW_NUMBER() OVER(
      ORDER BY sal) AS rn,
        CUME_DIST() OVER(
      ORDER BY sal) AS dist
      FROM
        emp;
        
      
      sal          rn                 dist  
      -------  ------  ---------------------
      800.00        1    0.07142857142857142
      950.00        2    0.14285714285714285
      1100.00       3    0.21428571428571427
      1250.00       4    0.35714285714285715
      1250.00       5    0.35714285714285715
      1300.00       6    0.42857142857142855
      1500.00       7                    0.5
      1600.00       8     0.5714285714285714
      2450.00       9     0.6428571428571429
      2850.00      10     0.7142857142857143
      2975.00      11     0.7857142857142857
      3000.00      12     0.9285714285714286
      3000.00      13     0.9285714285714286
      5000.00      14                      1
      

      当我们在OVER() 指定排序的行为是 ORDER BY sal DESC时,看到的却是另一番景象。

      SELECT 
        sal,
        ROW_NUMBER() OVER(
      ORDER BY sal DESC) AS rn,
        CUME_DIST() OVER(
      ORDER BY sal DESC) AS dist
      FROM
        emp;
        
      
      sal          rn                 dist  
      -------  ------  ---------------------
      5000.00       1    0.07142857142857142
      3000.00       2    0.21428571428571427
      3000.00       3    0.21428571428571427
      2975.00       4     0.2857142857142857
      2850.00       5    0.35714285714285715
      2450.00       6    0.42857142857142855
      1600.00       7                    0.5
      1500.00       8     0.5714285714285714
      1300.00       9     0.6428571428571429
      1250.00      10     0.7857142857142857
      1250.00      11     0.7857142857142857
      1100.00      12     0.8571428571428571
      950.00       13     0.9285714285714286
      800.00       14                      1
      

      PERCENT_RANK()CUME_DIST() 一样,也是统计某个值的分配情况,只是它们的算法不一样。

      PERCENT_RANK() 的计算公式:

      (rank - 1) / (rows - 1)
      

      其中,rank 表示行的等级(如果出现重复值,则用最小的那个编号),rows 表示分区的行数。具体请看下面这个例子。

      SELECT 
        empno,
        sal,
        ROW_NUMBER() OVER(ORDER BY sal) AS rn,
        PERCENT_RANK() OVER(ORDER BY sal) AS percent
      FROM
        emp;
        
      
      sal          rn              percent  
      -------  ------  ---------------------
      800.00        1                      0
      950.00        2    0.07692307692307693
      1100.00       3    0.15384615384615385
      1250.00       4    0.23076923076923078
      1250.00       5    0.23076923076923078
      1300.00       6    0.38461538461538464
      1500.00       7    0.46153846153846156
      1600.00       8     0.5384615384615384
      2450.00       9     0.6153846153846154
      2850.00      10     0.6923076923076923
      2975.00      11     0.7692307692307693
      3000.00      12     0.8461538461538461
      3000.00      13     0.8461538461538461
      5000.00      14                      1
      

      当 sal = 800 时,percent = (1 -1 ) / (14 - 1) = 0;

      当 sal = 1250 时,存在两个编号:4 跟 5。取最小的编号,则 percent = (4 -1 ) / (14 - 1) = 0.230769;

      当 sal = 5000 时, percent = (14 -1 ) / (14 - 1) = 1。

      注意,如果在OVER()中没有指定 ORDER 子句,那么 PERCENT_RANK() 计算的结果都是一样的。

    2 聚合窗口函数

    在下面这些聚合窗口函数后面加上 OVER() 子句,它们就变成了聚合窗口函数。

    AVG()
    BIT_AND()
    BIT_OR()
    BIT_XOR()
    COUNT()
    JSON_ARRAYAGG()
    JSON_OBJECTAGG()
    MAX()
    MIN()
    STDDEV_POP(), STDDEV(), STD()
    STDDEV_SAMP()
    SUM()
    VAR_POP(), VARIANCE()
    VAR_SAMP()
    

    常用的聚合窗口函数有:AVG() OVER()COUNT() OVER()MAX() OVER()MIN() OVER()SUM() OVER()

    下面这个例子,它利用窗口函数只查一次 emp 表完成了这些需求:

    • 统计所有员工的薪资;
    • 统计每个部门的人数;
    • 计算每个部门的平均薪资;
    • 获取公司里面的最高薪资;
    • 获取最早入职的员工的入职时间。
    SELECT 
      empno AS '编号',
      ename AS '姓名',
      sal AS '薪资',
      deptno AS '部门编号',
      SUM(sal) OVER() AS '薪资总额',
      COUNT(*) OVER(PARTITION BY deptno) AS '部门人数',
      AVG(sal) OVER(PARTITION BY deptno) AS '部门平均薪资',
      MAX(sal) OVER() AS '最高薪资',
      MIN(hiredate) OVER() '最早入职时间'
    FROM
      emp;
    

    输出结果>>>

    窗口函数示例

    3 命名窗口函数

    我们可以用 WINDOWS 关键字给窗口起别名,并在OVER() 中引用它。命名窗口子句位于 HAVING 子句和 ORDER 子句的位置之间,其语法如下:

    WINDOW window_name AS (window_spec)
        [, window_name AS (window_spec)]
    

    我们可以同时定义多个窗口名字。

    再来看下 window_spec 的定义:

    window_spec:
        [window_name] [partition_clause] [order_clause] [frame_clause]
    

    也就是说,我们在定义一个窗口函数时,可以指定下面这些内容:

    • 引用的窗口别名,比如先定义了窗口 w1,再在窗口 w2 中引用 w1,就像这样WINDOW w1 AS (w2), w2 AS ()。但是,不能循环引用。
    • PARTITION 子句;
    • ORDER 子句;
    • 滑动窗口范围。

    对于在 SQL 中使用了多个窗口函数,且这些窗口函数中的 OVER() 的内容都相同,使用命名窗口函数就很合适。

    比如,对于下面这条 SQL。

    SELECT
      sal,
      ROW_NUMBER() OVER (ORDER BY sal) AS 'row_number',
      RANK()       OVER (ORDER BY sal) AS 'rank',
      DENSE_RANK() OVER (ORDER BY sal) AS 'dense_rank'
    FROM emp;
    

    可以改造成命名窗口的形式。一旦想改变 OVER() 里面的内容,只需改动命名窗口里面的内容,而不用像原来的 SQL 那样要改动每个窗口函数的内容。

    SELECT
      sal,
      ROW_NUMBER() OVER w AS 'row_number',
      RANK()       OVER w AS 'rank',
      DENSE_RANK() OVER w AS 'dense_rank'
    FROM emp
    WINDOW w AS (ORDER BY sal);
    

    我们再来看一个复杂一点的例子。

    SELECT
      deptno,
      sal,
      ROW_NUMBER() OVER w2 AS 'row_number',
      RANK()       OVER (w1 ORDER BY sal) AS 'rank',
      DENSE_RANK() OVER w3 AS 'dense_rank'
    FROM emp
    WINDOW w1 AS(PARTITION BY deptno),
    	   w2 AS(ORDER BY sal),
    	   w3 AS(w2)
    ORDER BY sal;
    

    我们在 RANK() OVER() 的子句里面引用了窗口 w1,并在其后面接入了 ORDER 子句;在定义窗口 w3 时,我们直接引用了窗口 w2,即窗口 w3 所表现的行为和 w2 一致。

    实际上,上面这条 SQL 等价于下面这条 SQL。

    SELECT
      deptno,
      sal,
      ROW_NUMBER() OVER (
          ORDER BY sal) AS 'row_number',
      RANK()       OVER (
          PARTITION BY deptno ORDER BY sal) AS 'rank',
      DENSE_RANK() OVER (ORDER BY sal) AS 'dense_rank'
    FROM emp
    ORDER BY sal;
    

    4 动态窗口

    我们来看下 OVER()的语法结构:

    over_clause:
        {OVER (window_spec) | OVER window_name}
        
    window_spec:
        [window_name] [partition_clause] [order_clause] [frame_clause]
    

    PARTITION 子句和 ORDER 子句大家都比较熟悉了,接下来给大家介绍 FRAME 子句。

    FRAME 子句的就是用来实现动态窗口。窗口函数在每行记录上执行,有的函数的窗口不会发生变化,这种就属于静态窗口;有的函数随着记录不同,窗口大小也在不断变化,这种就属于动态窗口。

    看下面这个例子,我们通过滑动窗口实现随着时间的变化累加部门的薪资,以及计算当前行和上下行记录的平均薪资。

    SELECT 
      empno,
      deptno,
      sal,
      SUM(sal) OVER(PARTITION BY deptno ORDER BY hiredate ROWS UNBOUNDED PRECEDING) AS total,
      AVG(sal) OVER(PARTITION BY deptno ORDER BY hiredate ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS average
    FROM
      emp;
      
    
     empno  deptno  sal      total     average        
    ------  ------  -------  --------  -------------
      7782      10  2450.00  2450.00   3725.000000  
      7839      10  5000.00  7450.00   2916.666667  
      7934      10  1300.00  8750.00   3150.000000  
      7369      20  800.00   800.00    1887.500000  
      7566      20  2975.00  3775.00   2258.333333  
      7902      20  3000.00  6775.00   2991.666667  
      7788      20  3000.00  9775.00   2366.666667  
      7876      20  1100.00  10875.00  2050.000000  
      7499      30  1600.00  1600.00   1425.000000  
      7521      30  1250.00  2850.00   1900.000000  
      7698      30  2850.00  5700.00   1866.666667  
      7844      30  1500.00  7200.00   1866.666667  
      7654      30  1250.00  8450.00   1233.333333  
      7900      30  950.00   9400.00   1100.000000  
    

    当计算 empno = 7782 这行记录时,total = 2450,average = (2450 + 5000)/ 2 = 3725;

    当计算 empno = 7839 这行记录时,total = 2450 + 5000 = 7450,average = (2450 + 5000 + 1300)/ 3 = 2916.66;

    当计算 empno = 7934 这行记录时,total = 2450 + 5000 + 1300 = 8750,average = (5000 + 1300)/ 2 = 3150;

    可以通过基于行或者基于范围的方式指定窗口的大小:

    • 基于行:选择当前行的前后几行。比如范围是当前行的往前两行和往后三行,就可以这么写语句 ROWS BETWEEN 2 PRECEDING AND 3 FOLLOWING
    • 基于范围:选择数据范围。例如获取值在区间 [c-2,c+3]的数据,语句就是 RANGE BETWEEN 2 PRECEDING AND 3 FOLLOWING,c 表示当前行的值。典型的应用场景是统计每天的日活、月活,这些用基于行的方式不好表示。

    我们再来看关于指定窗口大小的表达式:

    CURRENT ROW 边界是当前行,一般和其他范围关键字一起使用
    
    UNBOUNDED PRECEDING 边界是分区中的第一行
    
    UNBOUNDED FOLLOWING 边界是分区中的最后一行
    
    expr PRECEDING  边界是当前行减去expr的值
    
    expr FOLLOWING  边界是当前行加上expr的值
    

    关于 expr 的有效的表达式可以是:

    10 PRECEDING
    INTERVAL 5 DAY PRECEDING
    5 FOLLOWING
    INTERVAL '2:30' MINUTE_SECOND FOLLOWING
    

    注意,有些窗口函数即使指定了FRAME 子句,在计算的时候仍然选择的全分区的数据。这些函数包括:

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

    FRAME 子句的默认值取决于是否有 ORDER 子句。

    • 当存在于 ORDER BY 时,默认值为分区起始行到当前行,包含和当前行的值相等的其它行。语句相当于是 ROW UNBOUNDED PRECEDING 或者 ROW BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    • 当不存在 ORDER BY 时,默认是分区的所有行,即 ROW BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

    5 窗口函数的限制

    • 窗口函数不能直接用在 UPDATEDELETE 语句中,但可以用在子查询中;
    • 不支持嵌套窗口函数;
    • 聚合窗口函数中不能使用 DISTINCT
    • 依赖于当前行的值的滑动窗口端点。
    展开全文
  • 窗口函数在统计类的需求中很常见,稍微复杂一点的...本文将介绍一些常用的窗口函数的用法。窗口函数按照实现方式分成两种:一种是非聚合窗口函数,另外一种是聚合窗口函数。非聚合窗口函数是相对于聚合窗口函数来说...

    窗口函数在统计类的需求中很常见,稍微复杂一点的查询需求就有可能用到它,使用窗口函数可以极大的简化我们的 SQL 语句。像 Oracle、SQL Server 这些数据库在较早的版本就支持窗口函数了,MySQL 直到 8.0 版本后才支持它。

    本文将介绍一些常用的窗口函数的用法。窗口函数按照实现方式分成两种:一种是非聚合窗口函数,另外一种是聚合窗口函数。

    非聚合窗口函数是相对于聚合窗口函数来说的。聚合函数是对一组数据计算后返回单个值(即分组),非聚合函数一次只会处理一行数据。窗口聚合函数在行记录上计算某个字段的结果时,可将窗口范围内的数据输入到聚合函数中,并不改变行数。

    1 非聚合窗口函数

    MySQL 支持的非聚合窗口函数见表1。

    名称描述
    CUME_DIST()累积分配值
    DENSE_RANK()当前行在其分区中的排名,稠密排序
    FIRST_VALUE()指定区间范围内的第一行的值
    LAG()取排在当前行之前的值
    LAST_VALUE()指定区间范围内的最后一行的值
    LEAD()取排在当前行之后的值
    NTH_VALUE()指定区间范围内第N行的值
    NTILE()将数据分到N个桶,当前行所在的桶号
    PERCENT_RANK()排名值的百分比
    RANK()当前行在其分区中的排名,稀疏排序
    ROW_NUMBER()分区内当前行的行号
    表1 非聚合窗口函数列表

    常用的函数有:ROW_NUMBER()RANK()DENSE_RANK()LEAD()LAG()NTH_VALUE()FIRST_VALUE()LAST_VALUE()

    • ROW_NUMBER()RANK()DENSE_RANK()

      ROW_NUMBER()RANK()DENSE_RANK()都是排序函数,都可以给区间内的数生成序号。如果区间内不存在重复值,它们的计算结果一样。

      当出现重复值时,ROW_NUMBER()不考虑重复值,它会给相同的两个值分配不同的编号,编号的范围是从 1 到分区的行数。RANK()DENSE_RANK() 给重复的值生成相同的编号。不同的是,RANK()生成的序号有间隙,即重复值的下一项的编号和重复值的编号并不连续(下一项的值的编号 当前重复值项的编号+1),而DENSE_RANK()就不是这样。

      这几个函数的区别可结合下面的例子分析。

      SELECT 
        sal,
        ROW_NUMBER() OVER(ORDER BY sal) AS 'rn',
        RANK() OVER(ORDER BY sal) AS 'rk',
        DENSE_RANK() OVER(ORDER BY sal) AS 'dk' 
      FROM
        emp;
        
      sal          rn      rk      dk  
      -------  ------  ------  --------
      800.00        1       1         1
      950.00        2       2         2
      1100.00       3       3         3
      1250.00       4       4         4
      1250.00       5       4         4
      1300.00       6       6         5
      1500.00       7       7         6
      1600.00       8       8         7
      2450.00       9       9         8
      2850.00      10      10         9
      2975.00      11      11        10
      3000.00      12      12        11
      3000.00      13      12        11
      5000.00      14      14        12

      如果没有指定 partition by 分区字段,那么窗口函数操作的区间就是全部数据。我们可以让函数只作用在 deptno 分区。

      SELECT 
        sal,
        deptno,
        ROW_NUMBER() OVER(PARTITION BY 
        deptno ORDER BY sal) AS 'rn'
      FROM
        emp;
        
      sal      deptno      rn  
      -------  ------  --------
      1300.00      10         1
      2450.00      10         2
      5000.00      10         3
      800.00       20         1
      1100.00      20         2
      2975.00      20         3
      3000.00      20         4
      3000.00      20         5
      950.00       30         1
      1250.00      30         2
      1250.00      30         3
      1500.00      30         4
      1600.00      30         5
      2850.00      30         6

      如果我们要获取 emp 表中每个部门工资最高的前两名员工的信息,使用 ROW_NUMBER() 就可以这么写。

      SELECT 
        * 
      FROM
        (SELECT 
          empno,
          ename,
          deptno,
          sal,
          ROW_NUMBER() OVER(
            PARTITION BY deptno 
            ORDER BY sal DESC
            ) AS rn 
        FROM
          emp) t 
      WHERE rn <= 2


       empno  ename   deptno  sal          rn  
      ------  ------  ------  -------  --------
        7839  KING        10  5000.00         1
        7782  CLARK       10  2450.00         2
        7788  SCOTT       20  3000.00         1
        7902  FORD        20  3000.00         2
        7698  BLAKE       30  2850.00         1
        7499  ALLEN       30  1600.00         2

      注意,如果在OVER() 中没有ORDER 子句,那么,ROW_NUMBER()生成的编号是不确定的,而RANK()DENSE_RANK() 生成的编号都是 1 。

    • LAG()LEAD()

      LAG() 可以获得位于当前行之前的数据,如果指定了分区,则获取数据的范围只能在分区内。默认是获取上一条的记录,如果没有获取到,则返回 NULL。

      LAG() 的表达式是LAG(expr [, N[, default]]) [null_treatment]over_clause ,我们可以指定向后获取第 N 行,以及在获取不到数据时指定默认值。

      LEAD() 的表达式和 LAG() 是一样的,因此在 LEAD() 中也可以指定获取的行数 N 及默认值。

      SELECT 
        empno,
        sal,
        LAG(sal) OVER(ORDER BY empno)AS 'lag',
        LEAD(sal) OVER(ORDER BY empno)AS 'lead',
        LAG(sal,2,0OVER(ORDER BY empno)AS 'lag_2'
      FROM
        emp LIMIT 6;


       empno  sal      lag      lead     lag_2    
      ------  -------  -------  -------  ---------
        7369  800.00   (NULL)   1600.00  0.00     
        7499  1600.00  800.00   1250.00  0.00     
        7521  1250.00  1600.00  2975.00  800.00   
        7566  2975.00  1250.00  1250.00  1600.00  
        7654  1250.00  2975.00  2850.00  1250.00  
        7698  2850.00  1250.00  2450.00  2975.00  

      我们使用 LAG(sal,2,0) 获取当前行向前偏移 2 行的值,在当前行的编号是 1 和 2 时,由于偏移的行不存在,只能返回默认值 0 。

    • FIRST_VALUE()LAST_VALUE()NTH_VALUE()

      这几个函数可以分别获取区间范围内第一行、最后一行、第 N 行的值。

      SELECT 
        empno,
        deptno,
        FIRST_VALUE(empno) OVER(
            PARTITION BY deptno 
            ORDER BY empno)AS 'first',
        LAST_VALUE(empno) OVER(
            PARTITION BY deptno ORDER BY empno 
            ROWS BETWEEN UNBOUNDED PRECEDING 
            AND UNBOUNDED FOLLOWING)AS 'last',
        NTH_VALUE(empno,2OVER(
            PARTITION BY deptno 
            ORDER BY empno)AS 'nth_2'
      FROM
        emp;
        
        
       empno  deptno   first    last   nth_2  
      ------  ------  ------  ------  --------
        7782      10    7782    7934    (NULL)
        7839      10    7782    7934      7839
        7934      10    7782    7934      7839
        7369      20    7369    7902    (NULL)
        7566      20    7369    7902      7566
        7788      20    7369    7902      7566
        7876      20    7369    7902      7566
        7902      20    7369    7902      7566
        7499      30    7499    7900    (NULL)
        7521      30    7499    7900      7521
        7654      30    7499    7900      7521
        7698      30    7499    7900      7521
        7844      30    7499    7900      7521
        7900      30    7499    7900      7521

      当在OVER() 中指定了排序字段, FIRST_VALUE()LAST_VALUE()NTH_VALUE() 这几个函数的滑动窗口范围是从第一行到当前行(即 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),直接使用 LAST_VALUE() 得到的结果并不是我们直觉上想看到的那样。因此,需要把LAST_VALUE()的窗口范围改成 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

      关于滑动窗口更加详细的描述,在后面有讲到。

    • NTILE()

      NTILE(N)将一个分区划分为N个组(桶),给分区中的每一行分配其桶号,并返回其分区中当前行的桶号。

      SELECT 
        empno,
        deptno,
        NTILE(2OVER(
            PARTITION BY deptno 
            ORDER BY empno) AS ntile2
      FROM
        emp;
        

       empno  deptno  ntile2  
      ------  ------  --------
        7782      10         1
        7839      10         1
        7934      10         2
        7369      20         1
        7566      20         1
        7788      20         1
        7876      20         2
        7902      20         2
        7499      30         1
        7521      30         1
        7654      30         1
        7698      30         2
        7844      30         2
        7900      30         2
    • CUME_DIST()

      统计一组数据中小于等于(或大于等于,和 OVER() 中指定的排序行为有关系)当前行的值的百分比。

      SELECT 
        sal,
        ROW_NUMBER() OVER(ORDER BY sal) AS rn,
        CUME_DIST() OVER(ORDER BY sal) AS dist
      FROM
        emp;
        

      sal          rn                 dist  
      -------  ------  ---------------------
      800.00        1    0.07142857142857142
      950.00        2    0.14285714285714285
      1100.00       3    0.21428571428571427
      1250.00       4    0.35714285714285715
      1250.00       5    0.35714285714285715
      1300.00       6    0.42857142857142855
      1500.00       7                    0.5
      1600.00       8     0.5714285714285714
      2450.00       9     0.6428571428571429
      2850.00      10     0.7142857142857143
      2975.00      11     0.7857142857142857
      3000.00      12     0.9285714285714286
      3000.00      13     0.9285714285714286
      5000.00      14                      1

      当我们在OVER() 指定排序的行为是 ORDER BY sal DESC时,看到的却是另一番景象。

      SELECT 
        sal,
        ROW_NUMBER() OVER(ORDER BY sal DESCAS rn,
        CUME_DIST() OVER(ORDER BY sal DESCAS dist
      FROM
        emp;
        

      sal          rn                 dist  
      -------  ------  ---------------------
      5000.00       1    0.07142857142857142
      3000.00       2    0.21428571428571427
      3000.00       3    0.21428571428571427
      2975.00       4     0.2857142857142857
      2850.00       5    0.35714285714285715
      2450.00       6    0.42857142857142855
      1600.00       7                    0.5
      1500.00       8     0.5714285714285714
      1300.00       9     0.6428571428571429
      1250.00      10     0.7857142857142857
      1250.00      11     0.7857142857142857
      1100.00      12     0.8571428571428571
      950.00       13     0.9285714285714286
      800.00       14                      1

      PERCENT_RANK()CUME_DIST() 一样,也是统计某个值的分配情况,只是它们的算法不一样。

      PERCENT_RANK() 的计算公式:

      (rank - 1) / (rows - 1)

      其中,rank 表示行的等级(如果出现重复值,则用最小的那个编号),rows 表示分区的行数。具体请看下面这个例子。

      SELECT 
        empno,
        sal,
        ROW_NUMBER() OVER(
          ORDER BY sal) AS rn,
        PERCENT_RANK() OVER(
          ORDER BY sal) AS percent
      FROM
        emp;
        

      sal          rn              percent  
      -------  ------  ---------------------
      800.00        1                      0
      950.00        2    0.07692307692307693
      1100.00       3    0.15384615384615385
      1250.00       4    0.23076923076923078
      1250.00       5    0.23076923076923078
      1300.00       6    0.38461538461538464
      1500.00       7    0.46153846153846156
      1600.00       8     0.5384615384615384
      2450.00       9     0.6153846153846154
      2850.00      10     0.6923076923076923
      2975.00      11     0.7692307692307693
      3000.00      12     0.8461538461538461
      3000.00      13     0.8461538461538461
      5000.00      14                      1

      当 sal = 800 时,percent = (1 -1 ) / (14 - 1) = 0;

      当 sal = 1250 时,存在两个编号:4 跟 5。取最小的编号,则 percent = (4 -1 ) / (14 - 1) = 0.230769;

      当 sal = 5000 时, percent = (14 -1 ) / (14 - 1) = 1。

      注意,如果在OVER()中没有指定 ORDER 子句,那么 PERCENT_RANK() 计算的结果都是一样的。

    2 聚合窗口函数

    在下面这些聚合窗口函数后面加上 OVER() 子句,它们就变成了聚合窗口函数。

    AVG()
    BIT_AND()
    BIT_OR()
    BIT_XOR()
    COUNT()
    JSON_ARRAYAGG()
    JSON_OBJECTAGG()
    MAX()
    MIN()
    STDDEV_POP(), STDDEV(), STD()
    STDDEV_SAMP()
    SUM()
    VAR_POP(), VARIANCE()
    VAR_SAMP()

    常用的聚合窗口函数有:AVG() OVER()COUNT() OVER()MAX() OVER()MIN() OVER()SUM() OVER()

    下面这个例子,它利用窗口函数只查一次 emp 表完成了这些需求:

    • 统计所有员工的薪资;
    • 统计每个部门的人数;
    • 计算每个部门的平均薪资;
    • 获取公司里面的最高薪资;
    • 获取最早入职的员工的入职时间。
    SELECT 
      empno AS '编号',
      ename AS '姓名',
      sal AS '薪资',
      deptno AS '部门编号',
      SUM(sal) OVER() AS '薪资总额',
      COUNT(*) OVER(PARTITION BY deptno) AS '部门人数',
      AVG(sal) OVER(PARTITION BY deptno) AS '部门平均薪资',
      MAX(sal) OVER() AS '最高薪资',
      MIN(hiredate) OVER() '最早入职时间'
    FROM
      emp;

    输出结果>>>

    309d89ce27f6f1a698c2fd88741d24cc.png

    3 命名窗口函数

    我们可以用 WINDOWS  关键字给窗口起别名,并在OVER() 中引用它。命名窗口子句位于 HAVING 子句和 ORDER 子句的位置之间,其语法如下:

    WINDOW window_name AS (window_spec)
        [, window_name AS (window_spec)]

    我们可以同时定义多个窗口名字。

    再来看下 window_spec 的定义:

    window_spec:
        [window_name][partition_clause][order_clause][frame_clause]

    也就是说,我们在定义一个窗口函数时,可以指定下面这些内容:

    • 引用的窗口别名,比如先定义了窗口 w1,再在窗口 w2 中引用 w1,就像这样WINDOW w1 AS (w2), w2 AS ()。但是,不能循环引用。
    • PARTITION 子句;
    • ORDER 子句;
    • 滑动窗口范围。

    对于在 SQL 中使用了多个窗口函数,且这些窗口函数中的 OVER() 的内容都相同,使用命名窗口函数就很合适。

    比如,对于下面这条 SQL。

    SELECT
      sal,
      ROW_NUMBER() OVER (ORDER BY sal) AS 'row_number',
      RANK()       OVER (ORDER BY sal) AS 'rank',
      DENSE_RANK() OVER (ORDER BY sal) AS 'dense_rank'
    FROM emp;

    可以改造成命名窗口的形式。一旦想改变 OVER() 里面的内容,只需改动命名窗口里面的内容,而不用像原来的 SQL 那样要改动每个窗口函数的内容。

    SELECT
      sal,
      ROW_NUMBER() OVER w AS 'row_number',
      RANK()       OVER w AS 'rank',
      DENSE_RANK() OVER w AS 'dense_rank'
    FROM emp
    WINDOW w AS (ORDER BY sal);

    我们再来看一个复杂一点的例子。

    SELECT
      deptno,
      sal,
      ROW_NUMBER() OVER w2 AS 'row_number',
      RANK() OVER (w1 ORDER BY sal) AS 'rank',
      DENSE_RANK() OVER w3 AS 'dense_rank'
    FROM emp
    WINDOW w1 AS(PARTITION BY deptno),
        w2 AS(ORDER BY sal),
        w3 AS(w2)
    ORDER BY sal;

    我们在 RANK() OVER() 的子句里面引用了窗口 w1,并在其后面接入了 ORDER 子句;在定义窗口 w3 时,我们直接引用了窗口 w2,即窗口 w3 所表现的行为和 w2 一致。

    实际上,上面这条 SQL 等价于下面这条 SQL。

    SELECT
      deptno,
      sal,
      ROW_NUMBER() OVER (
          ORDER BY sal) AS 'row_number',
      RANK() OVER (
          PARTITION BY deptno ORDER BY sal) AS 'rank',
      DENSE_RANK() OVER (ORDER BY sal) AS 'dense_rank'
    FROM emp
    ORDER BY sal;

    4 动态窗口

    我们来看下 OVER()的语法结构:

    over_clause:
        {OVER (window_spec) | OVER window_name}
        
    window_spec:
        [window_name][partition_clause][order_clause][frame_clause]

    PARTITION 子句和 ORDER 子句大家都比较熟悉了,接下来给大家介绍 FRAME 子句。

    FRAME 子句的就是用来实现动态窗口。窗口函数在每行记录上执行,有的函数的窗口不会发生变化,这种就属于静态窗口;有的函数随着记录不同,窗口大小也在不断变化,这种就属于动态窗口。

    看下面这个例子,我们通过滑动窗口实现随着时间的变化累加部门的薪资,以及计算当前行和上下行记录的平均薪资。

    SELECT 
      empno,
      deptno,
      sal,
      SUM(sal) OVER(PARTITION BY deptno 
      ORDER BY hiredate 
      ROWS UNBOUNDED PRECEDINGAS total,
      AVG(sal) OVER(
      PARTITION BY deptno 
      ORDER BY hiredate 
      ROWS BETWEEN 1 PRECEDING 
      AND 1 FOLLOWINGAS average
    FROM
      emp;
      

     empno  deptno  sal      total     average        
    ------  ------  -------  --------  -------------
      7782      10  2450.00  2450.00   3725.000000  
      7839      10  5000.00  7450.00   2916.666667  
      7934      10  1300.00  8750.00   3150.000000  
      7369      20  800.00   800.00    1887.500000  
      7566      20  2975.00  3775.00   2258.333333  
      7902      20  3000.00  6775.00   2991.666667  
      7788      20  3000.00  9775.00   2366.666667  
      7876      20  1100.00  10875.00  2050.000000  
      7499      30  1600.00  1600.00   1425.000000  
      7521      30  1250.00  2850.00   1900.000000  
      7698      30  2850.00  5700.00   1866.666667  
      7844      30  1500.00  7200.00   1866.666667  
      7654      30  1250.00  8450.00   1233.333333  
      7900      30  950.00   9400.00   1100.000000  

    当计算 empno = 7782 这行记录时,total =  2450,average = (2450 + 5000)/ 2 = 3725;

    当计算 empno = 7839 这行记录时,total =  2450 + 5000 = 7450,average = (2450 + 5000 + 1300)/ 3 = 2916.66;

    当计算 empno = 7934 这行记录时,total =  2450 + 5000 + 1300 = 8750,average = (5000 + 1300)/ 2 = 3150;

    可以通过基于行或者基于范围的方式指定窗口的大小:

    • 基于行:选择当前行的前后几行。比如范围是当前行的往前两行和往后三行,就可以这么写语句 ROWS BETWEEN 2 PRECEDING AND 3 FOLLOWING
    • 基于范围:选择数据范围。例如获取值在区间 [c-2,c+3]的数据,语句就是 RANGE BETWEEN 2 PRECEDING AND 3 FOLLOWING,c 表示当前行的值。典型的应用场景是统计每天的日活、月活,这些用基于行的方式不好表示。

    我们再来看关于指定窗口大小的表达式:

    CURRENT ROW 边界是当前行,一般和其他范围关键字一起使用

    UNBOUNDED PRECEDING 边界是分区中的第一行

    UNBOUNDED FOLLOWING 边界是分区中的最后一行

    expr PRECEDING  边界是当前行减去expr的值

    expr FOLLOWING  边界是当前行加上expr的值

    关于 expr 的有效的表达式可以是:

    10 PRECEDING
    INTERVAL 5 DAY PRECEDING
    5 FOLLOWING
    INTERVAL '2:30' MINUTE_SECOND FOLLOWING

    注意,有些窗口函数即使指定了FRAME 子句,在计算的时候仍然选择的全分区的数据。这些函数包括:

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

    FRAME 子句的默认值取决于是否有 ORDER 子句。

    • 当存在于 ORDER BY 时,默认值为分区起始行到当前行,包含和当前行的值相等的其它行。语句相当于是 ROW UNBOUNDED PRECEDING 或者 ROW BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    • 当不存在  ORDER BY 时,默认是分区的所有行,即 ROW BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

    5 窗口函数的限制

    • 窗口函数不能直接用在 UPDATEDELETE 语句中,但可以用在子查询中;
    • 不支持嵌套窗口函数;
    • 聚合窗口函数中不能使用 DISTINCT
    • 滑动窗口的端点不能依赖于当前行的值。
    封面图片由agnessse3Pixabay上发布
    展开全文
  • 这两天在学习 MySQL 窗口函数,看到一篇国外文章 How Window Functions Work ,用动图方式讲解窗口函数,帮助我更好地理解了,现分享给大家。下面我只是简单地翻译了一下,并对内容做了些许删减,如有错误,请大家...

    这两天在学习 MySQL 窗口函数,看到一篇国外文章 How Window Functions Work ,用动图的方式讲解窗口函数,帮助我更好地理解了,现分享给大家。下面我只是简单地翻译了一下,并对内容做了些许删减,如有错误,请大家海涵,也烦请指正。

    什么是窗口函数?

    窗口函数基于对数据的子集或 "窗口 "进行的计算创建一个新列。这个窗口从特定列上的第一行开始,除非你限制窗口的大小,否则窗口的大小会增加。

    SELECT 'Day', 'Mile Driving',SUM('Miles Driving')

    OVER(ORDER BY 'Day') AS 'Running Total'FROM 'Running total mileage visual';

    在这里,对窗口中的数据进行聚合。窗口的行数在增加,所以它聚合更多的数据。

    如果我们将窗口限制为3行高,我们可以得到一个连续3天平均收入。

    SELECT 'Day', 'Daily Revenue',AVG('Daily Revenue')

    OVER(ORDER BY 'Day' ROWS 2 PRECEDING)AS '3 Day Average'

    FROM 'Running Average Example';

    窗口从第一行开始,然后增长到它的固定大小,然后整个窗口也随之移动。

    窗口函数也可以用 partition by 对数据分组。它首先对数据进行分组,然后在这些分组上应用聚合函数,将结果放在该组中每一行的新列中。

    SELECT 'Day', 'Weekend', 'Daily Revenue',SUM('Daily Revenue')

    OVER(PARTITION BY 'Weekend') AS 'Total'FROM 'Partitioned Total Example';

    可以看到某家店在周末和工作日两个时间段的收入对比。

    窗口函数的聚合函数的区别

    窗口函数与聚合函数非常相似,实际上每个窗口函数都在其中应用了一个聚合函数。他们不同之处在于:

    输出:(窗口函数不会减少输出的行,而是创建一个完整的输出列。)聚合函数输出单行,减少原表行数

    窗口函数产生一个新的数据列,该列的行数与原表相同。

    对数据进行子集化:聚类函数应用于分组数据或整个数据集中的数据。

    窗口函数应用于一个窗口内的数据。窗口可以很灵活,可以控制在特定的行数上,也可以适用于分组。

    让我们来看看两者的区别。

    窗口函数

    SELECT 'Day', 'Mile Driving',SUM('Miles Driving')

    OVER(ORDER BY 'Day') AS 'Running Total'FROM 'Running total mileage visual';

    聚合函数

    SELECT SUM('Miles Driving') AS 'Sum of Miles Driving'

    FROM 'Running total mileage visual';

    创建窗口函数的关键字OVER - 表示窗口函数的开始,这将使聚合的结果作为一个列添加到输出表中。

    PARTITION BY 在表中创建数据组,聚合结果将被执行。

    ORDER BY - 根据给定的列对数据进行排序。

    语法如下:

    SELECT '(Optional: The data you want to select)',

    [aggregate function]'(The column to perform the aggregate function on) '

    OVER(Optional: PARTITION BY and/or ORDER BY)

    AS'(Descriptive name)'

    FROM '(corresponding table)';

    对窗口行数进行控制

    使用关键字指定窗口的行数。ROW n PRECEDING - 定义当前行之前要包括的行数

    ROW n FOLLOWING - 定义当前行之后要包括的行数

    SELECT *,AVG('Daily Revenue')

    OVER(ROWS 2 PRECEDING)

    AS '3 Day Average'

    FROM 'Running Average Example'

    SELECT *,AVG('Daily Revenue')

    OVER(ROWS 2 FOLLOWING)

    AS '3 Day Average'

    FROM 'Running Average Example'

    使用 Partition by 和 Order by

    数据如下

    只使用 order by, 会创建移动平均(moving average)

    SELECT 'Date', 'Steps Taken',AVG('Steps Taken')

    OVER(ORDER BY Date)

    AS 'Average Steps Taken'

    FROM 'Steps Taken Daily';

    使用 order by 和 partition by

    SELECT 'Date', 'Weekend', 'Steps Taken',AVG('Steps Taken')

    OVER(PARTITION BY 'Weekend' ORDER BY Date)

    AS 'Average Steps Taken'

    FROM 'Steps Taken Daily';

    可以看到,当与PARTITION BY一起使用时,ORDER BY并没有创建移动平均(moving average)。

    展开全文
  • 这两天在学习 MySQL 窗口函数,看到一篇国外文章 How Window Functions Work ,用动图方式讲解窗口函数,帮助我更好地理解了,现分享给大家。下面我只是简单地翻译了一下,并对内容做了些许删减,如有错误,请大家...
  • 这两天在学习 MySQL 窗口函数,看到一篇国外文章 How Window Functions Work ,用动图方式讲解窗口函数,帮助我更好地理解了,现分享给大家。下面我只是简单地翻译了一下,并对内容做了些许删减,如有错误,请大家...
  • 本文介绍几个MySQL常用的窗口函数 下面以这个简单的数据表为例,对常见的几种窗口函数进行说明 1、排序函数 (1)Rank() 功能:求出每个员工在它所属部门中的工资排名 select *, rank() over(partition by ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,244
精华内容 497
关键字:

mysql的窗口函数

mysql 订阅