MySQL中的窗口函數(shù)使用及說明

更新時間：2023年12月27日 10:08:25 作者：m0_38063172

這篇文章主要介紹了MySQL中的窗口函數(shù)使用及說明,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教

MySQL中的窗口函數(shù)

從version 8.0開始，MySQL支持在查詢中使用窗口函數(shù)。

這篇文章是對一篇英文資料的不完全翻譯，加上自己的一些理解。

如果有興趣可以去看看原文章。

文中的示例用到的建表語句和插值語句如下：

CREATE TABLE sales(
    sales_employee VARCHAR(50) NOT NULL,
    fiscal_year INT NOT NULL,
    sale DECIMAL(14,2) NOT NULL,
    PRIMARY KEY(sales_employee,fiscal_year)
);
 
INSERT INTO sales(sales_employee,fiscal_year,sale)
VALUES('Bob',2016,100),
      ('Bob',2017,150),
      ('Bob',2018,200),
      ('Alice',2016,150),
      ('Alice',2017,100),
      ('Alice',2018,200),
       ('John',2016,200),
      ('John',2017,150),
      ('John',2018,250);

先看一個例子：

SELECT 
    fiscal_year, 
    sales_employee,
    sale,
    SUM(sale) OVER (PARTITION BY fiscal_year) total_sales
FROM
    sales;

執(zhí)行后得到的結(jié)果如下：

+-------------+----------------+--------+-------------+
| fiscal_year | sales_employee | sale   | total_sales |
+-------------+----------------+--------+-------------+
|        2016 | Alice          | 150.00 |      450.00 |
|        2016 | Bob            | 100.00 |      450.00 |
|        2016 | John           | 200.00 |      450.00 |
|        2017 | Alice          | 100.00 |      400.00 |
|        2017 | Bob            | 150.00 |      400.00 |
|        2017 | John           | 150.00 |      400.00 |
|        2018 | Alice          | 200.00 |      650.00 |
|        2018 | Bob            | 200.00 |      650.00 |
|        2018 | John           | 250.00 |      650.00 |
+-------------+----------------+--------+-------------+
9 rows in set (0.00 sec)

這里，sum()函數(shù)充當(dāng)了窗口函數(shù)，得到了根據(jù)fiscal_year計算出的sale的總和total_sales列，但是又不像它作為聚合函數(shù)使用時一樣，這里的結(jié)果保留了每一行的信息。

原因就在于窗口函數(shù)的執(zhí)行順序（邏輯上的）是在FROM，JOIN，WHERE，GROUP　BY，HAVING之后，在ORDER　BY，LIMIT，SELECT　DISTINCT之前。

它執(zhí)行時GROUP　BY的聚合過程已經(jīng)完成了，所以不會再產(chǎn)生數(shù)據(jù)聚合。

窗口函數(shù)的語法

window_function_name(expression) 
    OVER (
        [partition_defintion]
        [order_definition]
        [frame_definition]
    )

先指定作為窗口函數(shù)的函數(shù)名，然后是OVER(…)，就算OVER里面沒有內(nèi)容，括號也需要保留。

窗口函數(shù)的一個概念是當(dāng)前行，當(dāng)前行屬于某個窗口，窗口由“[partition_defintion]”，“[order_definition]”，“[frame_definition]“確定。

1.partition_defintion

翻譯過來應(yīng)該是分區(qū)，語法是"PARTITION BY < expression>[{,< expression>…}]"，它會根據(jù)單個或者多個表達(dá)式的計算結(jié)果來分區(qū)（列名也是一種表達(dá)式，它的結(jié)果就是列名本身）。

在前面的例子中，結(jié)果中的每一行都有自己的分區(qū)，total_sales列的值就是它所屬的分區(qū)里面的sum(sale)的結(jié)果。

2.frame_definition

這里先講frame_definition，可能應(yīng)該是叫幀吧。

它的作用是在分區(qū)里面再進(jìn)一步細(xì)分窗口。

語法是"frame_unit {< frame_start>|< frame_between>}"，frame_unit有兩種，分別是ROWS和RANGE，由ROWS定義的frame是由開始和結(jié)束位置的行確定的，由RANGE定義的frame由在某個值區(qū)間的行確定。

如果只指定了frame的開始位置，那么結(jié)束位置就默認(rèn)為當(dāng)前行。

frame_start有三種：

UNBOUNDED PRECEDING: 區(qū)間的第一行
N PRECEDING: 當(dāng)前行之前的N行，N可以是數(shù)字，也可以是一個能計算出數(shù)字的表達(dá)式
CURRENT ROW: 當(dāng)前行

frame_between的可以取的值如下：

frame_start：如前面所列
UNBOUNDED FOLLOWING：區(qū)間的最后一行
N FOLLOWING：當(dāng)前行之后的N行，N可以是數(shù)字，也可以是一個能計算出數(shù)字的表達(dá)式
如果沒顯式指定frame的話，MySQL會認(rèn)為frame是“ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING”

這個有點復(fù)雜，看個例子：

SELECT 
   fiscal_year, 
   sales_employee,
   sale,
   SUM(sale) OVER (PARTITION BY sales_employee 
                               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) total_sales
FROM
   sales;

執(zhí)行結(jié)果如下：

+-------------+----------------+--------+-------------+
| fiscal_year | sales_employee | sale   | total_sales |
+-------------+----------------+--------+-------------+
|        2016 | Alice          | 150.00 |      150.00 |
|        2017 | Alice          | 100.00 |      250.00 |
|        2018 | Alice          | 200.00 |      450.00 |
|        2016 | Bob            | 100.00 |      100.00 |
|        2017 | Bob            | 150.00 |      250.00 |
|        2018 | Bob            | 200.00 |      450.00 |
|        2016 | John           | 200.00 |      200.00 |
|        2017 | John           | 150.00 |      350.00 |
|        2018 | John           | 250.00 |      600.00 |
+-------------+----------------+--------+-------------+
9 rows in set (0.00 sec)

第一行所在的區(qū)間是sales_employee為Alice的區(qū)間，所在的幀是區(qū)間第一行到當(dāng)前行，只有一行，total_sales為150；

第二行所在的區(qū)間是sales_employee為Alice的區(qū)間，所在的幀是區(qū)間第一行到當(dāng)前行，有兩行，total_sales為150+100=250；

第一行所在的區(qū)間是sales_employee為Alice的區(qū)間，所在的幀是區(qū)間第一行到當(dāng)前行，有三行，total_sales為150+100+200=450。