MySQL 8.0 新特性之窗口函數(shù)

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net


文章目錄

        窗口函數(shù)概述
        窗口函數(shù)語法
            PARTITION BY
            ORDER BY
            窗口選項
            命名窗口
        窗口函數(shù)列表
            聚合窗口函數(shù)
            專用窗口函數(shù)
        窗口函數(shù)限制
        窗口函數(shù)優(yōu)化

大家好！我是只談技術(shù)不剪發(fā)的 Tony 老師。今天給大家分享 MySQL 8.0 中的一個新特性：窗口函數(shù)。

許多關(guān)系型數(shù)據(jù)庫，例如 Oracle、SQL Server、PostgreSQL 以及 SQLite 等，都實現(xiàn)了 SQL 標準定義的窗口函數(shù)；MySQL 8.0 終于也增加了這個功能，今天我們就來詳細介紹一下 MySQL 中的窗口函數(shù)。

這里是一份 SQL 窗口函數(shù)速查表，可以方便我們快速回顧和查找相關(guān)信息。
窗口函數(shù)概述

窗口函數(shù)（Window Function）針對查詢中的每一行數(shù)據(jù)，基于和它相關(guān)的一組數(shù)據(jù)計算出一個結(jié)果。窗口函數(shù)在某些數(shù)據(jù)庫中也叫做分析函數(shù)（Analytic Function）。

為了便于理解，我們可以比較一下聚合函數(shù)和窗口函數(shù)的區(qū)別。首先創(chuàng)建一個銷售數(shù)據(jù)示例表 sales：

CREATE TABLE sales
(
    year    INT,
    country VARCHAR(20),
    product VARCHAR(32),
    profit  INT
);

insert into sales(year, country, product, profit)
values
(2000, 'Finland', 'Computer', 1500),
(2001, 'USA', 'Computer', 1200),
(2001, 'Finland', 'Phone', 10),
(2000, 'India', 'Calculator', 75),
(2001, 'USA', 'TV', 150),
(2000, 'India', 'Computer', 1200),
(2000, 'USA', 'Calculator', 75),
(2000, 'USA', 'Computer', 1500),
(2000, 'Finland', 'Phone', 100),
(2001, 'USA', 'Calculator', 50),
(2001, 'USA', 'Computer', 1500),
(2000, 'India', 'Calculator', 75),
(2001, 'USA', 'TV', 100);

 

以下是 SUM 函數(shù)分別作為聚合函數(shù)和窗口函數(shù)的結(jié)果：

mysql> SELECT SUM(profit) AS total_profit FROM sales;
+--------------+
| total_profit |
+--------------+
|         7535 |
+--------------+
1 row in set (0.00 sec)

SELECT year, country, product, profit,
       SUM(profit) OVER() AS total_profit
FROM sales;
mysql> SELECT year, country, product, profit,
    ->        SUM(profit) OVER() AS total_profit
    -> FROM sales;
+------+---------+------------+--------+--------------+
| year | country | product    | profit | total_profit |
+------+---------+------------+--------+--------------+
| 2000 | Finland | Computer   |   1500 |         7535 |
| 2001 | USA     | Computer   |   1200 |         7535 |
| 2001 | Finland | Phone      |     10 |         7535 |
| 2000 | India   | Calculator |     75 |         7535 |
| 2001 | USA     | TV         |    150 |         7535 |
| 2000 | India   | Computer   |   1200 |         7535 |
| 2000 | USA     | Calculator |     75 |         7535 |
| 2000 | USA     | Computer   |   1500 |         7535 |
| 2000 | Finland | Phone      |    100 |         7535 |
| 2001 | USA     | Calculator |     50 |         7535 |
| 2001 | USA     | Computer   |   1500 |         7535 |
| 2000 | India   | Calculator |     75 |         7535 |
| 2001 | USA     | TV         |    100 |         7535 |
+------+---------+------------+--------+--------------+
13 rows in set (0.00 sec)

 

從查詢的結(jié)果可以看出，窗口函數(shù) SUM(profit) OVER() 執(zhí)行了和聚合函數(shù) SUM(profit) 類似的匯總。不過聚合函數(shù)只返回了一個匯總之后的結(jié)果，而窗口函數(shù)為每一行數(shù)據(jù)都返回了結(jié)果。


窗口函數(shù)只能出現(xiàn)在 SELECT 列表和 ORDER BY 子句中，查詢語句的處理順序依次為 FROM、WHERE、GROUP BY、聚合函數(shù)、HAVING、窗口函數(shù)、SELECT DISTINCT、ORDER BY、LIMIT。

窗口函數(shù)與其他函數(shù)的語法區(qū)別主要在于 OVER 子句，接下來我們介紹它的語法。
窗口函數(shù)語法

窗口函數(shù)的語法如下：

window_function(expr)
OVER (
  PARTITION BY ...
  ORDER BY ...
  frame_clause
  )

 

OVER 子句包含三個可選項：分區(qū)（PARTITION BY）、排序（ORDER BY）以及窗口大?。╢rame_clause）。
PARTITION BY

PARTITION BY 選項用于將數(shù)據(jù)行拆分成多個分區(qū)（組），窗口函數(shù)基于每一行數(shù)據(jù)所在的組進行計算并返回結(jié)果。如果省略了 PARTITION BY，所有的數(shù)據(jù)作為一個組進行計算。在上面的示例中，SUM(profit) OVER() 就是將所有的銷售數(shù)據(jù)看作一個分區(qū)。

下面的查詢按照不同的國家匯總銷量：

mysql> SELECT year, country, product, profit,
    ->        SUM(profit) OVER(PARTITION BY country) AS country_profit
    -> FROM sales;
+------+---------+------------+--------+----------------+
| year | country | product    | profit | country_profit |
+------+---------+------------+--------+----------------+
| 2000 | Finland | Computer   |   1500 |           1610 |
| 2001 | Finland | Phone      |     10 |           1610 |
| 2000 | Finland | Phone      |    100 |           1610 |
| 2000 | India   | Calculator |     75 |           1350 |
| 2000 | India   | Computer   |   1200 |           1350 |
| 2000 | India   | Calculator |     75 |           1350 |
| 2001 | USA     | Computer   |   1200 |           4575 |
| 2001 | USA     | TV         |    150 |           4575 |
| 2000 | USA     | Calculator |     75 |           4575 |
| 2000 | USA     | Computer   |   1500 |           4575 |
| 2001 | USA     | Calculator |     50 |           4575 |
| 2001 | USA     | Computer   |   1500 |           4575 |
| 2001 | USA     | TV         |    100 |           4575 |
+------+---------+------------+--------+----------------+
13 rows in set (0.00 sec)

 

SQL 標準要求 PARTITION BY 之后只能使用字段名，不過 MySQL 允許指定表達式。例如，假設(shè)表中存在一個名為 ts 的 TIMESTAMP 類型字段，SQL 表中允許 PARTITION BY ts 但是不允許 PARTITION BY HOUR(ts)；而 MySQL 兩者都允許。

另外，我們也可以指定多個分組字段：

PARTITION BY expr [, expr] ...

 

ORDER BY

ORDER BY 選項用于指定分區(qū)內(nèi)數(shù)據(jù)的排序，排序字段數(shù)據(jù)相同的行是對等行（peer）。如果省略 ORDER BY ，分區(qū)內(nèi)的數(shù)據(jù)不進行排序，不按照固定順序處理， 而且所有數(shù)據(jù)都是對等行。ORDER BY 通常用于排名分析函數(shù)，參考下文中的專用窗口函數(shù)。

以下查詢按照國家進行分組，按照年份和產(chǎn)品名稱進行排序，然后匯總銷量：

mysql> SELECT year, country, product, profit,
    ->        SUM(profit) OVER(PARTITION BY country ORDER BY year, product) AS country_profit
    -> FROM sales;
+------+---------+------------+--------+----------------+
| year | country | product    | profit | country_profit |
+------+---------+------------+--------+----------------+
| 2000 | Finland | Computer   |   1500 |           1500 |
| 2000 | Finland | Phone      |    100 |           1600 |
| 2001 | Finland | Phone      |     10 |           1610 |
| 2000 | India   | Calculator |     75 |            150 |
| 2000 | India   | Calculator |     75 |            150 |
| 2000 | India   | Computer   |   1200 |           1350 |
| 2000 | USA     | Calculator |     75 |             75 |
| 2000 | USA     | Computer   |   1500 |           1575 |
| 2001 | USA     | Calculator |     50 |           1625 |
| 2001 | USA     | Computer   |   1200 |           4325 |
| 2001 | USA     | Computer   |   1500 |           4325 |
| 2001 | USA     | TV         |    150 |           4575 |
| 2001 | USA     | TV         |    100 |           4575 |
+------+---------+------------+--------+----------------+
13 rows in set (0.00 sec)

 

ORDER BY 之后的表達式也可以使用 ASC 或者 DESC 指定排序方式，默認為 ASC。對應(yīng)升序排序，NULL 排在最前面；對于降序排序，NULL 排在最后面：

ORDER BY expr [ASC|DESC] [, expr [ASC|DESC]] ...

 

OVER 子句中的 ORDER BY 選項只用于分區(qū)內(nèi)的數(shù)據(jù)排序；如果想要對最終的結(jié)果進行排序，可以使用 ORDER BY 子句。
窗口選項

frame_clause 選項用于在當前分區(qū)中指定一個數(shù)據(jù)窗口，也就是一個與當前行相關(guān)的數(shù)據(jù)子集。窗口會隨著當前處理的數(shù)據(jù)行而移動，例如：

    定義一個從分區(qū)開始到當前數(shù)據(jù)行結(jié)束的窗口，可以計算截止到每一行的累計總值。
    定義一個從當前行之前 N 行數(shù)據(jù)到當前行之后 N 行數(shù)據(jù)的窗口，可以計算移動平均值。

frame_clause 的語法如下：

{ROWS | RANGE} frame_start
{ROWS | RANGE} BETWEEN frame_start AND frame_end

 

其中，

    ROWS 表示通過開始行和結(jié)束行的位置指定窗口，偏移量以行數(shù)為單位進行計算。
    RANGE 表示通過開始行和結(jié)束行的數(shù)值指定窗口，偏移量以數(shù)值為單位進行計算。

frame_start 和 frame_end 分別表示窗口的開始行和結(jié)束行。如果只有 frame_start，默認以當前行作為窗口的結(jié)束。

CURRENT ROW
UNBOUNDED PRECEDING
UNBOUNDED FOLLOWING
expr PRECEDING
expr FOLLOWING

 

如果同時指定了兩者，frame_start 不能晚于 frame_end，例如 BETWEEN 1 FOLLOWING AND 1 PRECEDING 就是一個無效的窗口。

frame_start 和 frame_end 的具體意義如下：

    CURRENT ROW：對于 ROWS 方式，代表了當前行；對于 RANGE，代表了當前行的所有對等行。
    UNBOUNDED PRECEDING：代表了分區(qū)中的第一行。
    UNBOUNDED FOLLOWING：代表了分區(qū)中的最后一行。
    expr PRECEDING：對于 ROWS 方式，代表了當前行之前的第 expr 行；對于 RANGE，代表了等于當前行的值減去 expr 的所有行；如果當前行的值為 NULL，代表了當前行的所有對等行。
    expr FOLLOWING：對于 ROWS 方式，代表了當前行之后的第 expr 行；對于 RANGE，代表了等于當前行的值加上 expr 的所有行；如果當前行的值為 NULL，代表了當前行的所有對等行。

對于 expr PRECEDING 和 expr FOLLOWING)，expr 可以是一個 ? 參數(shù)占位符（用于預(yù)編譯語句）、非負的數(shù)值常量或者 INTERVAL val unit 格式的時間間隔。對于 INTERVAL 表達式，val 是一個非負的時間間隔值，unit 是一個時間間隔單位，例如 INTERVAL 1 DAY。關(guān)于時間間隔常量的詳細信息，可以參考官方文檔中的 DATE_ADD() 函數(shù)介紹。

基于數(shù)字或者時間間隔的 RANGE 窗口需要指定一個基于數(shù)字或者時間間隔的 ORDER BY。以下是一些有效的 expr PRECEDING 和 expr FOLLOWING 窗口：

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

 

以下查詢演示了如何實現(xiàn)累計求和以及移動平均值：

mysql> SELECT year, country, product, profit,
    ->        SUM(profit) OVER(PARTITION BY country ORDER BY year, product ROWS UNBOUNDED PRECEDING) AS running_total,
    ->        AVG(profit) OVER(PARTITION BY country ORDER BY year, product ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS running_average
    -> FROM sales;
+------+---------+------------+--------+---------------+-----------------+
| year | country | product    | profit | running_total | running_average |
+------+---------+------------+--------+---------------+-----------------+
| 2000 | Finland | Computer   |   1500 |          1500 |        800.0000 |
| 2000 | Finland | Phone      |    100 |          1600 |        536.6667 |
| 2001 | Finland | Phone      |     10 |          1610 |         55.0000 |
| 2000 | India   | Calculator |     75 |            75 |         75.0000 |
| 2000 | India   | Calculator |     75 |           150 |        450.0000 |
| 2000 | India   | Computer   |   1200 |          1350 |        637.5000 |
| 2000 | USA     | Calculator |     75 |            75 |        787.5000 |
| 2000 | USA     | Computer   |   1500 |          1575 |        541.6667 |
| 2001 | USA     | Calculator |     50 |          1625 |        916.6667 |
| 2001 | USA     | Computer   |   1200 |          2825 |        916.6667 |
| 2001 | USA     | Computer   |   1500 |          4325 |        950.0000 |
| 2001 | USA     | TV         |    150 |          4475 |        583.3333 |
| 2001 | USA     | TV         |    100 |          4575 |        125.0000 |
+------+---------+------------+--------+---------------+-----------------+
13 rows in set (0.01 sec)

 

對于 running_average，每個分區(qū)內(nèi)的第一行數(shù)據(jù)沒有 PRECEDING，結(jié)果是當前行和下一行的平均值；最后一行數(shù)據(jù)沒有 FOLLOWING，結(jié)果是當前行和上一行的平均值。

另外，這篇文章介紹了如何基于時間間隔指定窗口大小，從而實現(xiàn)銀行等金融機構(gòu)可疑支付交易的監(jiān)測。

如果沒有指定 frame_clause，默認的窗口取決于是否存在 ORDER BY 選項：

    如果指定了 ORDER BY：默認窗口從分區(qū)的開始直到當前行，包括當前行的所有對等行。此時默認窗口相當于：RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW；
    如果沒有指定 ORDER BY：默認窗口就是整個分區(qū)，因為此時所有的數(shù)據(jù)都是對等行。此時默認窗口相當于：RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。

由于默認窗口取決于是否包含了 ORDER BY 選項，而增加 ORDER BY 獲得確定結(jié)果的同時又可能改變結(jié)果（例如 SUM() 函數(shù)的結(jié)果就會因此而改變）。所以，為了獲得相同的結(jié)果，但又按照 ORDER BY 排序，可以總是明確指定一個窗口選項。

如果當前行的數(shù)據(jù)為 NULL，窗口的定義可能不太明確。以下示例說明了這種情況下各種窗口選項的作用：

    ORDER BY X ASC RANGE BETWEEN 10 FOLLOWING AND 15 FOLLOWING，窗口從 NULL 開始到 NULL 結(jié)束，也就是說窗口只包含 NULL 數(shù)據(jù)。
    ORDER BY X ASC RANGE BETWEEN 10 FOLLOWING AND UNBOUNDED FOLLOWING，窗口從 NULL 開始到分區(qū)的最后結(jié)束。由于 ASC 排序時 NULL 值出現(xiàn)在最前面，因此窗口就是整個分區(qū)。
    ORDER BY X DESC RANGE BETWEEN 10 FOLLOWING AND UNBOUNDED FOLLOWING，窗口從 NULL 開始到分區(qū)的最后結(jié)束。由于 DESC 排序時 NULL 值出現(xiàn)在最后面，因此窗口只包含 NULL 數(shù)據(jù)。
    ORDER BY X ASC RANGE BETWEEN 10 PRECEDING AND UNBOUNDED FOLLOWING，窗口從 NULL 開始到分區(qū)的最后結(jié)束。由于 ASC 排序時 NULL 值出現(xiàn)在最前面，因此窗口就是整個分區(qū)。
    ORDER BY X ASC RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING，窗口從 NULL 開始到 NULL 結(jié)束，也就是說窗口只包含 NULL 數(shù)據(jù)。
    ORDER BY X ASC RANGE BETWEEN 10 PRECEDING AND 1 PRECEDING，窗口從 NULL 開始到 NULL 結(jié)束，也就是說窗口只包含 NULL 數(shù)據(jù)。
    ORDER BY X ASC RANGE BETWEEN UNBOUNDED PRECEDING AND 10 FOLLOWING，窗口從分區(qū)的第一行開始到 NULL 結(jié)束。由于 ASC 排序時 NULL 值出現(xiàn)在最前面，因此窗口只包含 NULL 數(shù)據(jù)。

命名窗口

OVER 子句除了直接定義各種選項之外，還可以使用一個預(yù)定義的窗口變量。窗口變量使用 WINDOW 子句進行定義，語法上位于 HAVING 和 ORDER BY 之間。

window_function(expr) OVER window_name

WINDOW window_name AS (PARTITION BY ... ORDER BY ... frame_clause)
WINDOW window_name AS (other_window_name)

 

如果查詢中多個窗口函數(shù)的 OVER 子句相同，利用 WINDOW 子句定義一個窗口變量，然后在多個 OVER 子句中使用該變量可以簡化查詢語句：

SELECT year, country, product, profit,
       SUM(profit) OVER w AS country_total,
       AVG(profit) OVER w AS country_average,
       COUNT(profit) OVER w AS country_count
FROM sales
WINDOW w AS (PARTITION BY country);

 

如果需要修改窗口的定義，這種方式只需要修改一次，更加方便。

OVER 子句還可以使用 OVER (window_name …) 基于已有的窗口變量進行修改，但是只能增加其他選項。例如，以下查詢定義了一個窗口變量，指定了分區(qū)選項；然后在 OVER 子句中增加了不同的 ORDER BY 選項：

SELECT year, country, product, profit,
       SUM(profit) OVER (w ORDER BY year, product ROWS UNBOUNDED PRECEDING) AS running_total,
       AVG(profit) OVER (w ORDER BY year, product ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS running_average,
       COUNT(profit) OVER w AS country_count
FROM sales
WINDOW w AS (PARTITION BY country);

 

OVER 子句只能為窗口變量增加選項，而不能修改原來的選項。以下是一個錯誤的示例：

OVER (w PARTITION BY year)
... WINDOW w AS (PARTITION BY country)

 

另外，窗口變量的定義中也可以使用其他窗口變量。例如：

WINDOW w1 AS (w2), w2 AS (), w3 AS (w1)

 

w1 反向引用了 w2，w3 正向引用了 w1。但是注意不要產(chǎn)生循環(huán)引用，例如以下定義將會產(chǎn)生錯誤：

WINDOW w1 AS (w2), w2 AS (w3), w3 AS (w1)

 

w1、w2、w3 之間是一個循環(huán)引用，無法真正定義窗口變量。
窗口函數(shù)列表

MySQL 中的窗口函數(shù)可以分為兩類：聚合窗口函數(shù)和專用窗口函數(shù)。
聚合窗口函數(shù)

許多 MySQL 聚合函數(shù)都支持 OVER 子句，從而作為窗口函數(shù)使用：

    AVG()，計算平均值；
    BIT_AND()，按位與運算；
    BIT_OR()，按位或運算；
    BIT_XOR()，按位異或運算；
    COUNT()，計算行數(shù)；
    JSON_ARRAYAGG()，以 JSON 數(shù)組返回數(shù)據(jù)；
    JSON_OBJECTAGG()，以 JSON 對象返回數(shù)據(jù)；
    MAX()，計算最大值；
    MIN()，計算最小值；
    STDDEV_POP()、STDDEV()、STD()，計算總體標準差；
    STDDEV_SAMP()，計算樣本標準差；
    SUM()，計算和值；
    VAR_POP()、VARIANCE()，計算總體方差；
    VAR_SAMP()，計算樣本方差。

聚合窗口函數(shù)基于當前行所在的窗口進行計算，窗口的定義和默認值可以參考上文中的窗口選項。關(guān)于這些聚合窗口函數(shù)的具體介紹，可以參考官方文檔。
專用窗口函數(shù)

MySQL 提供了以下專用的窗口函數(shù)：

    CUME_DIST()，返回累積分布值，也就是分區(qū)內(nèi)小于等于當前行的數(shù)據(jù)行占比。取值范圍 [0, 1]；
    DENSE_RANK()，返回當前行在分區(qū)內(nèi)的排名，數(shù)據(jù)相同的行排名相同，隨后的排名不會產(chǎn)生跳躍；
    FIRST_VALUE()，返回當前窗口內(nèi)的第一行；
    LAG()，返回分區(qū)內(nèi)當前行上面（之前）的第 N 行；
    LAST_VALUE()，返回當前窗口內(nèi)的最后一行；
    LEAD()，返回分區(qū)內(nèi)當前行下面（之后）的第 N 行；
    NTH_VALUE()，返回當前窗口內(nèi)的第 N 行；
    NTILE()，將當前分區(qū)拆分成 N 個組，返回當前行所在的組編號；
    PERCENT_RANK()，返回分區(qū)內(nèi)排除最高值之后，小于當前行的數(shù)據(jù)行占比，計算方式為 (rank - 1) / (rows - 1)；
    RANK()，返回當前行在分區(qū)內(nèi)的排名，數(shù)據(jù)相同的行排名相同，隨后的排名會產(chǎn)生跳躍；
    ROW_NUMBER()，返回當前行在分區(qū)內(nèi)的編號，從 1 開始。

以下窗口函數(shù)基于當前行所在的窗口進行計算：

FIRST_VALUE()
LAST_VALUE()
NTH_VALUE()

 

以下函數(shù)基于整個分區(qū)進行計算：

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

 

SQL 標準不允許這些函數(shù)指定窗口選項，MySQL 允許為它們指定窗口選項，但是會忽略這些選項。

以下是一些用于排名的窗口函數(shù)示例：

mysql> SELECT year, country, product, profit,
    ->        ROW_NUMBER() OVER(PARTITION BY country ORDER BY profit DESC) AS "row_number",
    ->        RANK() OVER(PARTITION BY country ORDER BY profit DESC) AS "rank",
    ->        DENSE_RANK() OVER(PARTITION BY country ORDER BY profit DESC) AS "dense_rank",
    ->        PERCENT_RANK() OVER(PARTITION BY country ORDER BY profit DESC) AS "percent_rank"
    -> FROM sales;
+------+---------+------------+--------+------------+------+------------+--------------------+
| year | country | product    | profit | row_number | rank | dense_rank | percent_rank       |
+------+---------+------------+--------+------------+------+------------+--------------------+
| 2000 | Finland | Computer   |   1500 |          1 |    1 |          1 |                  0 |
| 2000 | Finland | Phone      |    100 |          2 |    2 |          2 |                0.5 |
| 2001 | Finland | Phone      |     10 |          3 |    3 |          3 |                  1 |
| 2000 | India   | Computer   |   1200 |          1 |    1 |          1 |                  0 |
| 2000 | India   | Calculator |     75 |          2 |    2 |          2 |                0.5 |
| 2000 | India   | Calculator |     75 |          3 |    2 |          2 |                0.5 |
| 2000 | USA     | Computer   |   1500 |          1 |    1 |          1 |                  0 |
| 2001 | USA     | Computer   |   1500 |          2 |    1 |          1 |                  0 |
| 2001 | USA     | Computer   |   1200 |          3 |    3 |          2 | 0.3333333333333333 |
| 2001 | USA     | TV         |    150 |          4 |    4 |          3 |                0.5 |
| 2001 | USA     | TV         |    100 |          5 |    5 |          4 | 0.6666666666666666 |
| 2000 | USA     | Calculator |     75 |          6 |    6 |          5 | 0.8333333333333334 |
| 2001 | USA     | Calculator |     50 |          7 |    7 |          6 |                  1 |
+------+---------+------------+--------+------------+------+------------+--------------------+
13 rows in set (0.00 sec)

 

注意不同函數(shù)的排名方式。

以下是一些用于獲取指定數(shù)據(jù)行值的窗口函數(shù)示例：

mysql> SELECT year, country, product, profit,
    ->        LEAD(profit, 1, 0) OVER(PARTITION BY country ORDER BY profit DESC) AS "lead",
    ->        LAG(profit, 1, 0) OVER(PARTITION BY country ORDER BY profit DESC) AS "lag",
    ->        FIRST_VALUE(profit) OVER(PARTITION BY country ORDER BY profit DESC) AS "first_value",
    ->        LAST_VALUE(profit) OVER(PARTITION BY country ORDER BY profit DESC) AS "last_value",
    ->        NTH_VALUE(profit, 2) OVER(PARTITION BY country ORDER BY profit DESC) AS "nth_value"
    -> FROM sales;
+------+---------+------------+--------+------+------+-------------+------------+-----------+
| year | country | product    | profit | lead | lag  | first_value | last_value | nth_value |
+------+---------+------------+--------+------+------+-------------+------------+-----------+
| 2000 | Finland | Computer   |   1500 |  100 |    0 |        1500 |       1500 |      NULL |
| 2000 | Finland | Phone      |    100 |   10 | 1500 |        1500 |        100 |       100 |
| 2001 | Finland | Phone      |     10 |    0 |  100 |        1500 |         10 |       100 |
| 2000 | India   | Computer   |   1200 |   75 |    0 |        1200 |       1200 |      NULL |
| 2000 | India   | Calculator |     75 |   75 | 1200 |        1200 |         75 |        75 |
| 2000 | India   | Calculator |     75 |    0 |   75 |        1200 |         75 |        75 |
| 2000 | USA     | Computer   |   1500 | 1500 |    0 |        1500 |       1500 |      1500 |
| 2001 | USA     | Computer   |   1500 | 1200 | 1500 |        1500 |       1500 |      1500 |
| 2001 | USA     | Computer   |   1200 |  150 | 1500 |        1500 |       1200 |      1500 |
| 2001 | USA     | TV         |    150 |  100 | 1200 |        1500 |        150 |      1500 |
| 2001 | USA     | TV         |    100 |   75 |  150 |        1500 |        100 |      1500 |
| 2000 | USA     | Calculator |     75 |   50 |  100 |        1500 |         75 |      1500 |
| 2001 | USA     | Calculator |     50 |    0 |   75 |        1500 |         50 |      1500 |
+------+---------+------------+--------+------+------+-------------+------------+-----------+
13 rows in set (0.00 sec)

 

關(guān)于這些窗口函數(shù)的具體介紹，可以參考官方文檔。
窗口函數(shù)限制

SQL 標準不允許在 UPDATE 或者 DELETE 語句中使用窗口函數(shù)更新數(shù)據(jù)，但是允許在子查詢中使用這些函數(shù)。

MySQL 目前不支持 SQL 標準中定義的以下功能：

    聚合創(chuàng)建函數(shù)中的 DISTINCT 關(guān)鍵字；
    嵌套窗口函數(shù)；
    基于當前行數(shù)據(jù)值的動態(tài)窗口。

MySQL 解析器可以接收以下標準 SQL 選項但是不會生效：

    以 GROUPS 為單位的窗口選項會產(chǎn)生錯誤，目前只支持 ROWS 和 RANGE 方式；
    指定窗口時的 EXCLUDE 選項會產(chǎn)生錯誤；
    使用 IGNORE NULLS 選項會產(chǎn)生錯誤，目前只支持 RESPECT NULLS；
    使用 FROM LAST 選項會產(chǎn)生錯誤，目前只支持 FROM FIRST。

窗口函數(shù)優(yōu)化

窗口函數(shù)會對優(yōu)化器產(chǎn)生以下影響：

    如果子查詢中包含窗口函數(shù)，就不會使用派生表合并優(yōu)化。此時子查詢只會使用物化進行優(yōu)化。
    半連接不能用于窗口函數(shù)優(yōu)化，因為半連接只能用于 WHERE 和 JOIN … ON 子查詢，它們不能包含窗口函數(shù)。
    優(yōu)化器按照順序處理多個具有相同排序規(guī)則的窗口，因此除了第一個窗口，其他都可以省略排序操作。
    優(yōu)化器不會嘗試合并多個原本可以一次獲取的窗口，例如多個定義完全相同的 OVER 子句。解決辦法就是通過 WINDOW 子句定義一個窗口變量，然后在多個 OVER 子句中進行引用。

聚合函數(shù)不作為窗口函數(shù)使用時，會在盡可能的最外層查詢執(zhí)行聚合操作。例如，以下查詢中，MySQL 知道 COUNT(t1.b) 不是外部查詢中的聚合函數(shù)，因為它屬于 WHERE 子句：

SELECT * FROM t1 WHERE t1.a = (SELECT COUNT(t1.b) FROM t2);

 

所以，MySQL 在子查詢中進行聚合操作，將 t1.b 看作一個常量并且返回 t2 中的數(shù)據(jù)行數(shù)。

如果將 WHERE 替換成 HAVING 將會導(dǎo)致錯誤：

mysql> SELECT * FROM t1 HAVING t1.a = (SELECT COUNT(t1.b) FROM t2);
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1
of SELECT list contains nonaggregated column 'test.t1.a'; this is
incompatible with sql_mode=only_full_group_by

 

錯誤的原因在于 COUNT(t1.b) 可以出現(xiàn)在 HAVING 中，從而導(dǎo)致外部查詢產(chǎn)生聚合操作。

窗口函數(shù)（包括聚合窗口函數(shù)）的處理沒有那么復(fù)雜，它們總是在當前子查詢中進行聚合操作。

窗口函數(shù)的計算可能會受到系統(tǒng)變量 windowing_use_high_precision 的影響，該變量決定了計算窗口的操作是否確保不會丟失精度。默認啟用了 windowing_use_high_precision。

對于某些移動窗口的聚合操作，可以使用反向聚合功能刪除不需要的數(shù)據(jù)。這種方法可以提高性能，但可能導(dǎo)致精度丟失。例如，將一個很小的浮點數(shù)和一個很大的值相加，會導(dǎo)致這個浮點數(shù)被“遮蓋”；隨后對這個大值進行反向取值時，小浮點數(shù)的效果就會丟失。

反向聚合只會導(dǎo)致浮點數(shù)據(jù)類型的精度丟失；對于其他類型不會有影響，包括 DECIMAL 數(shù)據(jù)類型。

為了性能，MySQL 在安全的情況下總是會使用反向聚合：

    對于浮點型數(shù)據(jù)，反向聚合可能導(dǎo)致精度丟失。默認設(shè)置可以避免反向聚合，以性能的犧牲確保了精度。如果可以丟失一定的精度，可以禁用 windowing_use_high_precision 以支持反向聚合。
    對于非浮點型的數(shù)據(jù)類型，反向聚合不會產(chǎn)生問題，因此無論 windowing_use_high_precision 如何設(shè)置都會使用反向聚合。
    windowing_use_high_precision 對于 MIN() 和 MAX() 聚合函數(shù)沒有影響，因為它們永遠不會使用反向聚合。

對于計算方差的 STDDEV_POP()、STDDEV_SAMP()、VAR_POP()、VAR_SAMP() 等聚合函數(shù)，可以使用優(yōu)化模式或者默認模式。 優(yōu)化模式可能導(dǎo)致最后一位小數(shù)略有不同，如果允許存在這些偏差，經(jīng)用 windowing_use_high_precision 可以利用優(yōu)化模式。

對于 EXPLAIN 命令，傳統(tǒng)輸出格式顯示窗口執(zhí)行信息過于復(fù)雜；如果想要顯示相關(guān)信息，可以使用 EXPLAIN FORMAT=JSON，然后查找 windowing 元素。

如果你點擊了收藏?，請不要忘了關(guān)注??、評論??、點贊??！