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)


相关推荐

  • 基于ArduinoUNO的LD3320语音识别+SYN6288语音合成的智能分类垃圾桶

    基于ArduinoUNO的LD3320语音识别+SYN6288语音合成的智能分类垃圾桶文章目录写在前面器件连接部分代码运行结果小结写在前面接上一篇文章,这次是集合了语音识别+语音合成(就是语音播报实现一种反馈)+SG90舵机实现垃圾桶的开与闭,给出上篇文章链接,如果对LD3320语音识别还有问题的朋友可以看看:LD3320语音识别模块与Arduino软串口通讯实现开关灯功能发现还是有好多人都在做这个,当玩具也好,毕设也好,这个都是一个好玩又有趣的项目。因为这只是现在的一门小课…

  • 的季节里,第京_第四个季节

    的季节里,第京_第四个季节京,柳叶翠青,槐花京,柳叶翠青,槐花馨香。在这个美好的季节里,第京,柳叶翠青,槐花馨香。在这个美好的季节里,第京,柳叶翠青,槐花馨香。在这个美好的季节里,第京,柳叶翠青,槐花馨香。在这个美好的季节里,第京,柳叶翠青,槐花馨香。在这个美好的季节里,第京,柳叶翠青,槐花馨香。在这个美好的季节里,第京,柳叶翠青,槐花馨香。在这个美好的季节里,第京,柳叶翠青,槐花馨香。在这个美好的季节里,第京,柳叶翠青,

  • 新东方网课资源分享_新东方笔记大赛官网

    新东方网课资源分享_新东方笔记大赛官网
    新东方李老师的734条高频词组笔记(实在是太有用了!就转来了,没看过的,赶紧点吧~)

    1.abideby(=befaithfulto;obey)忠于;遵守。
    2.beabsentfrom….缺席,不在
    3.absenceofmind(=beingabsent-minded)心不在焉
    4.absorb(=takeuptheattentionof)吸引…的注意力(被动语态)beabsorbedin

  • 线程、多线程与线程池面试题

    线程、多线程与线程池面试题●概念线程:进程中负责程序执行的执行单元。一个进程中至少有一个线程。多线程:解决多任务同时执行的需求,合理使用CPU资源。多线程的运行是根据CPU切换完成,如何切换由CPU决定,因此多线程运行具有不确定性。线程池:基本思想还是一种对象池的思想,开辟一块内存空间,里面存放了众多(未死亡)的线程,池中线程执行调度由池管理器来处理。当有线程任务时,从池中取一个,执行完成后线程对…

  • springboot线程池的配置使用[通俗易懂]

    springboot线程池的配置使用[通俗易懂]我们都知道,java中有ThreadPoolExecutor提供的线程池服务,非常好用。可以有效的解决了一些异步业务,提高系统性能。但是java中配置和使用线程池有点繁琐,而在springboot中,线程池的配置简直就是轻而易举。下面直接上干货。springboot中主要使用配置类来配置线程池@Async注解可以使用配置好的线程池下面我们来详细看一下配置和使用的过程1、配置线程池主要使用…

  • 空间回归与地理加权_时空地理加权回归对样本量要求

    空间回归与地理加权_时空地理加权回归对样本量要求实际上,除了辅助表以外,GWR还会生成一份全要素的表。对回归的每一个样本都给出相应的信息,今天就来看看这些信息代表了什么内容。生成的新的要素类字段信息如下:实际上,Coeffcient(系数)和StandardErrorConffcient(标准差系数)会对每一个解释变量都给出一个值,所以可以看成是两类值,下面把各个值的情况和意义做个简单描述:其中ArcGIS

发表回复

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

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