1. 窗口函數概念和語法
窗口函數對一組查詢行執行類似聚合的操作 。然而，聚合操作將查詢行分組到單個結果行，而窗口函數為每個查詢行產生一個結果:
- 函數求值發生的行稱為當前行
- 與發生函數求值的當前行相關的查詢行組成了當前行的窗口
SELECTmanufacturer, product, profit,SUM(profit) OVER() AS total_profit,SUM(profit) OVER(PARTITION BY manufacturer) AS manufacturer_profitFROM sales;
文章插圖
查詢中的每個窗口操作都通過包含一個 OVER 子句來表示，該子句指定如何將查詢行劃分為組以供窗口函數處理：
- 第一個 OVER 子句是空的，它將整個查詢行集視為一個分區 。窗口函數因此產生一個全局和，但對每一行都這樣做 。
- 第二個 OVER 子句按 manufacturer 劃分行，產生每個分區（每個manufacturer）的總和 。該函數為每個分區行生成此總和 。
查詢結果行由 FROM 子句確定，在 WHERE、GROUP BY 和 HAVING 處理之后，窗口執行發生在 ORDER BY、LIMIT 和 SELECT DISTINCT 之前 。
OVER子句被允許用于許多聚合函數 ， 因此 ， 這些聚合函數可以用作窗口函數或非窗口函數，具體取決于是否存在 OVER 子句：
AVG()BIT_AND()BIT_OR()BIT_XOR()COUNT()JSON_ARRAYAGG()JSON_OBJECTAGG()MAX()MIN()STDDEV_POP(), STDDEV(), STD()STDDEV_SAMP()SUM()VAR_POP(), VARIANCE()VAR_SAMP()MySQL還支持只能作為窗口函數使用的非聚合函數 。對于這些，OVER子句是必須的CUME_DIST()DENSE_RANK()FIRST_VALUE()LAG()LAST_VALUE()LEAD()NTH_VALUE()NTILE()PERCENT_RANK()RANK()ROW_NUMBER()ROW_NUMBER() 它生成其分區內每一行的行號 。默認情況下，分區行是無序的，行編號是不確定的 。若要對分區行進行排序 ， 請在窗口定義中包含一個ORDER BY子句 。下面的示例中，查詢使用無序分區和有序分區(row_num1和row_num2列)來說明省略和包含ORDER BY之間的區別:SELECTmanufacturer, product, profit,ROW_NUMBER() OVER(PARTITION BY manufacturer) AS row_num1,ROW_NUMBER() OVER(PARTITION BY manufacturer ORDER BY profit) AS row_num2FROM sales;
文章插圖
如前所述，要使用窗口函數（或將聚合函數視為窗口函數），需要在函數調用后包含OVER子句 。OVER子句有兩種形式:
over_clause:{OVER (window_spec) | OVER window_name}這兩種形式都定義了窗口函數應該如何處理查詢行 。它們的區別在于窗口是直接在OVER子句中定義的，還是通過對查詢中其他地方定義的命名窗口的引用提供的:- 在第一種情況下，窗口規范直接出現在 OVER 子句中的括號之間 。
- 在第二種情況下，window_name 是由查詢中其他地方的 WINDOW 子句定義的窗口規范的名稱 。
window_spec:[window_name] [partition_clause] [order_clause] [frame_clause]如果 OVER() 為空，則窗口由所有查詢行組成 ， 窗口函數使用所有行計算結果 。否則，括號中的子句決定了使用哪些查詢行來計算函數結果，以及它們是如何分區和排序的：- window_name: 由查詢中其他地方的window子句定義的窗口的名稱 。如果window_name單獨出現在OVER子句中，則它完全定義了窗口 。如果分區、排序或分幀子句也給出了，它們會修改被命名窗口的解釋 。
- partition_clause: PARTITION BY 子句指示如何將查詢行分組 。給定行的窗口函數結果基于包含該行的分區的行 。如果省略 PARTITION BY ， 則有一個由所有查詢行組成的分區 。
partition_clause:PARTITION BY expr [, expr] ... - order_clause: ORDER BY 子句指示如何對每個分區中的行進行排序 。根據 ORDER BY 子句相等的分區行被視為對等 。如果省略 ORDER BY，則分區行是無序的，沒有隱含的處理順序，并且所有分區行都是對等的 。
order_clause:ORDER BY expr [ASC|DESC] [, expr [ASC|DESC]] ...
窗口定義中的 ORDER BY 適用于各個分區 。要將結果集作為一個整體進行排序，請在查詢頂層包含 ORDER BY 。
