MySQL窗口函数简介「建议收藏」

MySQL窗口函数简介「建议收藏」原文地址:https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_last-value译文:12.21.1WindowFunctionDescriptions本节描述非聚合窗口函数,对于查询中的每一行,这些函数使用与该行相关的行执行计算。大多数聚合函数也可以用作窗口函数,…

大家好,又见面了,我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。

Jetbrains全系列IDE稳定放心使用

原文地址:https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_last-value

译文:

12.21.1 Window Function Descriptions

本节描述非聚合窗口函数,对于查询中的每一行,这些函数使用与该行相关的行执行计算。大多数聚合函数也可以用作窗口函数,具体可以参考Section 12.20.1, “Aggregate (GROUP BY) Function Descriptions”

有关窗口函数使用信息和示例,以及术语的定义,如OVER语句、窗口、分区、框架和对等项,请参考Section 12.21.2, “Window Function Concepts and Syntax”.

      Table 12.27 Window Functions

Name Description
CUME_DIST() Cumulative distribution value
DENSE_RANK() Rank of current row within its partition, without gaps
FIRST_VALUE() Value of argument from first row of window frame
LAG() Value of argument from row lagging current row within partition
LAST_VALUE() Value of argument from last row of window frame
LEAD() Value of argument from row leading current row within partition
NTH_VALUE() Value of argument from N-th row of window frame
NTILE() Bucket number of current row within its partition.
PERCENT_RANK() Percentage rank value
RANK() Rank of current row within its partition, with gaps
ROW_NUMBER() Number of current row within its partition

在下面的函数描述中,over_clause即Section 12.21.2, “Window Function Concepts and Syntax”中描述的OVER语句。一些窗口函数允许使用null_treatment子句,该子句指定在计算结果时如何处理NULL值。这个子句是可选的。它是SQL标准的一部分,但是MySQL实现只允许RESPECT NULLS(这也是默认值)。这意味着在计算结果时要考虑NULL值。IGNORE NULLS也可以被解析,但会产生错误。

    1)CUME_DIST over_clause:

返回一组值中某个值的累积分布,即分区值小于或等于当前行的值的百分比。它表示窗口分区的窗口顺序中在当前行之前或与当前行对等的行数,除以窗口分区中的总行数。返回值的范围从0到1。这个函数应该与ORDER BY一起使用,将分区行按所需的顺序排序。如果没有ORDER BY,所有行都是对等的,值N/N = 1,其中N是分区大小。

Section 12.21.2, “Window Function Concepts and Syntax”中有over_clause的描述。

下面的查询显示,对于val列中的值集,每行的CUME_DIST()值,以及类似的PERCENT_RANK()函数返回的百分比级值。作为参考,查询还使用ROW_NUMBER()显示行号:

  • mysql> SELECT
             val,
             ROW_NUMBER()   OVER w AS 'row_number',
             CUME_DIST()    OVER w AS 'cume_dist',
             PERCENT_RANK() OVER w AS 'percent_rank'
           FROM numbers
           WINDOW w AS (ORDER BY val);
    +------+------------+--------------------+--------------+
    | val  | row_number | cume_dist          | percent_rank |
    +------+------------+--------------------+--------------+
    |    1 |          1 | 0.2222222222222222 |            0 |
    |    1 |          2 | 0.2222222222222222 |            0 |
    |    2 |          3 | 0.3333333333333333 |         0.25 |
    |    3 |          4 | 0.6666666666666666 |        0.375 |
    |    3 |          5 | 0.6666666666666666 |        0.375 |
    |    3 |          6 | 0.6666666666666666 |        0.375 |
    |    4 |          7 | 0.8888888888888888 |         0.75 |
    |    4 |          8 | 0.8888888888888888 |         0.75 |
    |    5 |          9 |                  1 |            1 |
    +------+------------+--------------------+--------------+

    2)DENSE_RANk over_clause:

返回分区内当前行的排名,没有间隔。对等项被认为是平起平坐的,得到同样的排名。该函数将连续的排名分配给对等组,结果是,大小大于1的组不会产生不连续的排名。具体示例,请参见RANK()函数描述。这个函数应该与ORDER BY一起使用,将分区行按所需的顺序排序。没有ORDER BY,所有行都是对等的。

Section 12.21.2, “Window Function Concepts and Syntax”中有over_clause的描述。

    3)FIRST_VALUE(expr) [null_treatment] over_clause:

从窗口框架的第一行返回expr的值。

Section 12.21.2, “Window Function Concepts and Syntax”中有over_clause的描述。null_treatment如介绍部分所述。

下面的查询演示了FIRST_VALUE()、LAST_VALUE()和两个NTH_VALUE()实例:

  • mysql> SELECT
             time, subject, val,
             FIRST_VALUE(val)  OVER w AS 'first',
             LAST_VALUE(val)   OVER w AS 'last',
             NTH_VALUE(val, 2) OVER w AS 'second',
             NTH_VALUE(val, 4) OVER w AS 'fourth'
           FROM observations
           WINDOW w AS (PARTITION BY subject ORDER BY time
                        ROWS UNBOUNDED PRECEDING);
    +----------+---------+------+-------+------+--------+--------+
    | time     | subject | val  | first | last | second | fourth |
    +----------+---------+------+-------+------+--------+--------+
    | 07:00:00 | st113   |   10 |    10 |   10 |   NULL |   NULL |
    | 07:15:00 | st113   |    9 |    10 |    9 |      9 |   NULL |
    | 07:30:00 | st113   |   25 |    10 |   25 |      9 |   NULL |
    | 07:45:00 | st113   |   20 |    10 |   20 |      9 |     20 |
    | 07:00:00 | xh458   |    0 |     0 |    0 |   NULL |   NULL |
    | 07:15:00 | xh458   |   10 |     0 |   10 |     10 |   NULL |
    | 07:30:00 | xh458   |    5 |     0 |    5 |     10 |   NULL |
    | 07:45:00 | xh458   |   30 |     0 |   30 |     10 |     30 |
    | 08:00:00 | xh458   |   25 |     0 |   25 |     10 |     30 |
    +----------+---------+------+-------+------+--------+--------+

每个函数都使用当前框架中的行,根据显示的窗口定义,当前框架从第一个分区行扩展到当前行。对于NTH_VALUE()调用,当前框架并不总是包含所请求的行,在这种情况下,返回值为NULL。

    4)LAG(expr [, N[, default]]) [null_treatment] over_clause:

在分区中,从比当前行落后N行的行返回expr的值。如果没有这样的行,则返回值为默认值。例如,如果N是3,则前三行的返回值是默认值(原文说的是如果N是3,则前两行的返回值是默认值,笔者经测试发现是错的。译文中已将其改正)。如果缺少N或default,则默认值分别为1和NULL。N必须是非负整数。如果N为0,则对当前行计算expr。

Section 12.21.2, “Window Function Concepts and Syntax”中有over_clause的描述。null_treatment如介绍部分所述。

LAG()(和类似的LEAD()函数)经常用于计算行之间的差异。下面的查询显示了一组按时间顺序排列的观察值,和对于每一个观察值,来自相邻行中的LAG()和LEAD()值,以及当前行和相邻行之间的差异:

  • mysql> SELECT
             t, val,
             LAG(val)        OVER w AS 'lag',
             LEAD(val)       OVER w AS 'lead',
             val - LAG(val)  OVER w AS 'lag diff',
             val - LEAD(val) OVER w AS 'lead diff'
           FROM series
           WINDOW w AS (ORDER BY t);
    +----------+------+------+------+----------+-----------+
    | t        | val  | lag  | lead | lag diff | lead diff |
    +----------+------+------+------+----------+-----------+
    | 12:00:00 |  100 | NULL |  125 |     NULL |       -25 |
    | 13:00:00 |  125 |  100 |  132 |       25 |        -7 |
    | 14:00:00 |  132 |  125 |  145 |        7 |       -13 |
    | 15:00:00 |  145 |  132 |  140 |       13 |         5 |
    | 16:00:00 |  140 |  145 |  150 |       -5 |       -10 |
    | 17:00:00 |  150 |  140 |  200 |       10 |       -50 |
    | 18:00:00 |  200 |  150 | NULL |       50 |      NULL |
    +----------+------+------+------+----------+-----------+

在本例中,LAG()和LEAD()调用分别使用默认的N和默认值1和NULL。

第一行显示了当当前行没有前一行时LAG()的返回值情况:函数返回默认值(在本例中为NULL)。最后一行显示相同的内容,当当前行没有下一行时LEAD()返回NULL值。

LAG()和LEAD()也用于计算和,而不是差异。考虑下面的数据集,它包含斐波那契数列的前几个数字:

  • mysql> SELECT n FROM fib ORDER BY n;
    +------+
    | n    |
    +------+
    |    1 |
    |    1 |
    |    2 |
    |    3 |
    |    5 |
    |    8 |
    +------+

下面的查询显示了与当前行相邻的行的LAG()和LEAD()值。它还使用这些函数向当前行值添加前一行和后一行的值。其效果是生成斐波那契数列中的下一个数字,然后是该数字的下一个数字:

  • mysql> SELECT
             n,
             LAG(n, 1, 0)      OVER w AS 'lag',
             LEAD(n, 1, 0)     OVER w AS 'lead',
             n + LAG(n, 1, 0)  OVER w AS 'next_n',
             n + LEAD(n, 1, 0) OVER w AS 'next_next_n'
           FROM fib
           WINDOW w AS (ORDER BY n);
    +------+------+------+--------+-------------+
    | n    | lag  | lead | next_n | next_next_n |
    +------+------+------+--------+-------------+
    |    1 |    0 |    1 |      1 |           2 |
    |    1 |    1 |    2 |      2 |           3 |
    |    2 |    1 |    3 |      3 |           5 |
    |    3 |    2 |    5 |      5 |           8 |
    |    5 |    3 |    8 |      8 |          13 |
    |    8 |    5 |    0 |     13 |           8 |
    +------+------+------+--------+-------------+

生成原始斐波那契数集的一种方法是使用递归公共表表达式。具体示例,可以参考Fibonacci Series Generation

    5)LAST_VALUE(expr) [null_treatment] over_clause:

从窗口框架的最后一行返回expr的值。

Section 12.21.2, “Window Function Concepts and Syntax”中有over_clause的描述。null_treatment如介绍部分所述。

具体示例,请参见前文的FIRST_VALUE()函数描述。

    6)LEAD(expr [, N[, default]]) [null_treatment] over_clause:

从在其分区中超前当前行N行的行返回expr的值。如果没有这样的行,则返回值为默认值。例如,如果N是3,则最后三行的返回值是默认值(原文说的是如果N是3,则最后两行的返回值是默认值,笔者经测试发现是错的。译文中已将其改正)。如果缺少N或default,则默认值分别为1和NULL。

N必须是非负整数。如果N为0,则对当前行计算expr。

Section 12.21.2, “Window Function Concepts and Syntax”中有over_clause的描述。null_treatment如介绍部分所述。

具体示例,请参见前文的LAG()函数描述。

    7)NTH_VALUE(expr, N) [from_first_last] [null_treatment] over_clause:

从窗口框架的第n行返回expr的值。如果没有这样的行,则返回值为NULL。

N必须是一个正整数。

from_first_last是SQL标准的一部分,但是MySQL实现只允许FROM FIRST(这也是默认设置)。这意味着计算从窗口的第一行开始。FROM LAST也会被解析,但会产生一个错误。要获得与FROM LAST相同的效果(在窗口的最后一行开始计算),可以使用ORDER BY按相反顺序排序。

Section 12.21.2, “Window Function Concepts and Syntax”中有over_clause的描述。null_treatment如介绍部分所述。

具体示例,请参见前文的FIRST_VALUE()函数描述。

    8)NTILE(N) over_clause:

将一个分区分成N组(bucket),为分区中的每一行分配其bucket号,并返回分区中当前行的bucket号。例如,如果N是4,NTILE()将行分成4个bucket。如果N是100,NTILE()将行分成100个bucket。

N必须是一个正整数。bucket号返回值的范围从1到N。

这个函数应该与ORDER BY一起使用,将分区行按所需的顺序排序。

Section 12.21.2, “Window Function Concepts and Syntax”中有关于over_clause的描述。

下面的查询显示,对于val列中的值集,将行分成两组或四组所得到的百分比值。作为参考,查询还使用ROW_NUMBER()显示行号:

  • mysql> SELECT
             val,
             ROW_NUMBER() OVER w AS 'row_number',
             NTILE(2)     OVER w AS 'ntile2',
             NTILE(4)     OVER w AS 'ntile4'
           FROM numbers
           WINDOW w AS (ORDER BY val);
    +------+------------+--------+--------+
    | val  | row_number | ntile2 | ntile4 |
    +------+------------+--------+--------+
    |    1 |          1 |      1 |      1 |
    |    1 |          2 |      1 |      1 |
    |    2 |          3 |      1 |      1 |
    |    3 |          4 |      1 |      2 |
    |    3 |          5 |      1 |      2 |
    |    3 |          6 |      2 |      3 |
    |    4 |          7 |      2 |      3 |
    |    4 |          8 |      2 |      4 |
    |    5 |          9 |      2 |      4 |
    +------+------------+--------+--------+

    9)PERCENT_RANK() over_clause:

返回分区值小于当前行的值的百分比,不包括最大值。返回值范围为0 ~ 1,表示行相对排名,根据公式(rank – 1) / (rows – 1)计算得到,其中rank为行排名,row为分区行数:

  • (rank - 1) / (rows - 1)

    10)RANK() over_clause:

返回分区中当前行的排名,并带有间隔。对等行被认为是平起平坐的,得到同样的排名。如果存在大小大于一的组,则此函数不为对等组分配连续的排名;结果是不连续的排名。这个函数应该与ORDER BY一起使用,将分区行按所需的顺序排序。没有ORDER BY,所有行都是对等的。

Section 12.21.2, “Window Function Concepts and Syntax”中有over_clause的描述。

下面的查询显示了RANK()和DENSE_RANK()之间的区别,RANK()生成有间隔的排名,DENSE_RANK()生成没有间隔的排名。查询显示val列中一组值的每个成员的排名,其中包含一些重复值。RANK()将相同的排名分配给对等行(值重复的行),下一个更大的值的排名比前一行的排名高出的值是上一个对等行的数量减一。DENSE_RANK()也为对等行分配相同的排名,但是下一个更高的值的排名加一。作为参考,查询还使用ROW_NUMBER()显示行号:

  • mysql> SELECT
             val,
             ROW_NUMBER() OVER w AS 'row_number',
             RANK()       OVER w AS 'rank',
             DENSE_RANK() OVER w AS 'dense_rank'
           FROM numbers
           WINDOW w AS (ORDER BY val);
    +------+------------+------+------------+
    | val  | row_number | rank | dense_rank |
    +------+------------+------+------------+
    |    1 |          1 |    1 |          1 |
    |    1 |          2 |    1 |          1 |
    |    2 |          3 |    3 |          2 |
    |    3 |          4 |    4 |          3 |
    |    3 |          5 |    4 |          3 |
    |    3 |          6 |    4 |          3 |
    |    4 |          7 |    7 |          4 |
    |    4 |          8 |    7 |          4 |
    |    5 |          9 |    9 |          5 |
    +------+------------+------+------------+

    11)ROW_NUMBER() over_clause:

返回分区中当前行的编号。行号的范围从1到分区行数。

ORDER BY影响行编号的顺序。没有ORDER BY,行编号是不确定的。

ROW_NUMBER()为对等行分配不同的行号。若要为对等行分配相同的值,请使用RANK()或DENSE_RANK()。具体示例,请参见前文的RANK()函数描述。

Section 12.21.2, “Window Function Concepts and Syntax”中有over_clause的描述。

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~我是分割线~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

下一篇:https://blog.csdn.net/qq_41080850/article/details/86416215(MySQL窗口函数概念和语法)

PS:水平有限,译文中难免存在谬误,欢迎批评指正。

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/185768.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)


相关推荐

  • 求矩阵的逆的三种方法

    求矩阵的逆的三种方法我们知道求矩阵的逆具有非常重要的意义,本文分享给大家如何针对3阶以内的方阵,求出逆矩阵的3种手算方法:待定系数法、伴随矩阵法、初等变换法(只介绍初等行变换)待定系数法求逆矩阵 1 首先,我们来看如何使用待定系数法,求矩阵的逆。 举例: 矩阵A= 12 -1-3 2 假设所求的逆矩阵为 ab cd 则 3 从而可以得出方程组 a+2c=1 b+2d=0 -a-3c=0 -b-3d=1

  • 本地如何使用phpstudy环境搭建多站点

    本地如何使用phpstudy环境搭建多站点

  • JAVA类加载器_java三个类加载器

    JAVA类加载器_java三个类加载器1.类的加载过程 JVM将类加载过程分为三个步骤:装载(Load),链接(Link)和初始化(Initialize)链接又分为三个步骤,如下图所示:1)装载:查找并加载类的二进制数据;2)链接:验证:确保被加载类的正确性;准备:为类的静态变量分

  • 序列化和反序列化实现

    1.什么是序列化?程序员在编写应用程序的时候往往需要将程序的某些数据存储在内存中,然后将其写入文件或是将其传输到网络中的另一台计算机上以实现通讯。这个将程序数据转换成能被存储并传输的格式的过程被称

    2021年12月28日
  • 呼叫中心FAQ_客服呼叫中心介绍

    呼叫中心FAQ_客服呼叫中心介绍呼叫中心专业术语

  • SpringBoot笔记(五)Linux系统与项目部署

    SpringBoot笔记(五)Linux系统与项目部署Linux操作系统与项目部署**注意:**本版块会涉及到操作系统相关知识。现在,几乎所有智能设备都有一个自己的操作系统,比如我们的家用个人电脑,基本都是预装Windows操作系统,我们的手机也有Android和iOS操作系统,还有程序员比较青睐的MacBook,预装MacOS操作系统,甚至连Macbook的Touchbar都有一个自己的小型操作系统。操作系统是管理计算机硬件与软件资源的计算机程序,操作系统可以对计算机系统的各项资源板块开展调度工作,运用计算机操作系统可以减少人工资源分配的工作强度。.

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号