MySQL窗口函数简介

深藏阁楼爱情的钟 2022-03-27 11:12 254阅读 0赞

原文地址：[https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html\#function\_last-value][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”][Section_12.20.1_ _Aggregate _GROUP BY_ Function Descriptions]。

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

**      Table 12.27 Window Functions**

<table> 
 <thead> 
  <tr> 
   <th>Name</th> 
   <th>Description</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_cume-dist" rel="nofollow"><code>CUME_DIST()</code></a></td> 
   <td>Cumulative distribution value</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_dense-rank" rel="nofollow"><code>DENSE_RANK()</code></a></td> 
   <td>Rank of current row within its partition, without gaps</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_first-value" rel="nofollow"><code>FIRST_VALUE()</code></a></td> 
   <td>Value of argument from first row of window frame</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_lag" rel="nofollow"><code>LAG()</code></a></td> 
   <td>Value of argument from row lagging current row within partition</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_last-value" rel="nofollow"><code>LAST_VALUE()</code></a></td> 
   <td>Value of argument from last row of window frame</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_lead" rel="nofollow"><code>LEAD()</code></a></td> 
   <td>Value of argument from row leading current row within partition</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_nth-value" rel="nofollow"><code>NTH_VALUE()</code></a></td> 
   <td>Value of argument from N-th row of window frame</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_ntile" rel="nofollow"><code>NTILE()</code></a></td> 
   <td>Bucket number of current row within its partition.</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_percent-rank" rel="nofollow"><code>PERCENT_RANK()</code></a></td> 
   <td>Percentage rank value</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_rank" rel="nofollow"><code>RANK()</code></a></td> 
   <td>Rank of current row within its partition, with gaps</td> 
  </tr> 
  <tr> 
   <td><a href="https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_row-number" rel="nofollow"><code>ROW_NUMBER()</code></a></td> 
   <td>Number of current row within its partition</td> 
  </tr> 
 </tbody> 
</table>

在下面的函数描述中，over\_clause即[Section 12.21.2, “Window Function Concepts and Syntax”][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”][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”][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”][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”][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”][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”][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”][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”][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”][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”][Section_12.21.2_ _Window Function Concepts and Syntax]中有over\_clause的描述。

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

下一篇：[https://blog.csdn.net/qq\_41080850/article/details/86416215][https_blog.csdn.net_qq_41080850_article_details_86416215](MySQL窗口函数概念和语法)

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

[https_dev.mysql.com_doc_refman_8.0_en_window-function-descriptions.html_function_last-value]: https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_last-value
[Section_12.20.1_ _Aggregate _GROUP BY_ Function Descriptions]: https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html
[Section_12.21.2_ _Window Function Concepts and Syntax]: https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html
[Fibonacci Series Generation]: https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursive-fibonacci-series
[https_blog.csdn.net_qq_41080850_article_details_86416215]: https://blog.csdn.net/qq_41080850/article/details/86416215