MySQL8.0版本如何正確的使用窗口函數詳解

更新時間：2024年11月07日 08:10:14 作者：一葉飛舟

MySQL?8.0引入的窗口函數,增強了數據分析能力,窗口函數允許對數據集（窗口）進行操作,與GROUPBY類似,但每個查詢行生成獨立結果,包括聚合函數如SUM、AVG,專用窗口函數如ROW_NUMBER等,窗口函數應用于數據分組、排序、排名,并支持復雜分析場景,需要的朋友可以參考下

前言

MySQL數據庫從8.0開始支持窗口函數了，它是一種強大的數據分析工具，旨在幫助你快速獲得場景數據。在正式介紹這類函數前，博主還是解釋一下為什么這么稱呼這類函數為“窗口”函數。窗口——一個數據記錄的集合，也就是你的數據操作范圍只限于這個數據集，再無其他。它與group by類似，但是最大的區(qū)別是窗口函數會為每個查詢行生成一個結果（add column）。

恭喜你，有這個認知后，博主可以正式介紹它了，請緊隨博主，以防迷路。

窗口函數必學必會

既然窗口函數是服務于數據分析的，那么先來看看它長什么樣，有句話說得好：“沒吃過豬肉，還沒見過豬跑么”。當我們Get一個新知時，也要懷著同樣的預期和先行一步的姿態(tài)去對待它。那咱們先看看它的語法結構吧。

1. 基本語法

1.1 匿名窗口

SELECT 
	<窗口函數名> over (partition by <分組列名> order by <排序列名>)
FROM `你的表名`

1.2 顯式窗口

SELECT 
	<窗口函數名> OVER win
FROM `你的表名` 
WINDOW win AS (partition by <分組列名> order by <排序列名>)

其中，窗口函數名必須指定，partition by（可選），order by（可選）。

2. 包括哪些

窗口函數主要包含兩大類：常見的聚合函數（count、sum、avg等）和專用的窗口函數（比如排序等）。

2.1 聚合函數

大多數的聚合函數皆可用作窗口函數，通常與GROUP BY子句使用，將統(tǒng)計值分組到子集中。

聚合函數	用途說明
AVG()	返回平均值
BIT_AND()	按位 AND 運算，代表邏輯與
BIT_OR()	按位 OR 運算，代表邏輯或
BIT_XOR()	按位 XOR 運算，代表邏輯異或
COUNT()	返回行數
COUNT(DISTINCT)	返回去重后的行數
GROUP_CONCAT()	分組后，返回一個自動連接的字符串
JSON_ARRAYAGG()	返回一個json數組
JSON_OBJECTAGG()	返回一個json對象
MAX()	返回最大值
MIN()	返回最小值
STD()	返回整體標準偏差
STDDEV()	返回整體標準偏差
STDDEV_POP()	返回整體標準偏差
STDDEV_SAMP()	返回樣本標準偏差
SUM()	返回總和
VAR_POP()	返回整體標準方差
VAR_SAMP()	返回樣本方差
VARIANCE()	返回整體標準方差

提示：除非另有說明，否則聚合函數會忽略NULL值。

如果在不包含GROUP BY子句的SQL中使用聚合函數，則相當于對所有行進行分組。對于數值參數，方差和標準偏差函數返回一個DOUBLE值。SUM（）和AVG（）函數如果為精確值參數（整數或DECIMAL）返回DECIMAL值，如果為近似值參數（FLOAT或DOUBLE）返回DOUBLE值。

如使一個聚合函數轉換為一個窗口函數執(zhí)行，需按如下格式執(zhí)行（over子句）：

# 添加over子句
SUM([DISTINCT] expr) [over_clause]

示例1-普通聚合：這是一個普通聚合函數寫法（來自官網）：

SELECT 
     country, 
     SUM(profit) AS country_profit
FROM sales
GROUP BY country
ORDER BY country;

示例2-窗口函數：這是一個轉為窗口函數寫法（來自官網）：

SELECT
     year, country, product, profit,
     SUM(profit) OVER() AS total_profit,
     SUM(profit) OVER(PARTITION BY country) AS country_profit
FROM sales
ORDER BY country, year, product, profit;

是不是很簡單？

2.2 專用窗口函數

我們已知窗口函數是對一個記錄集執(zhí)行類似聚合的操作。然而，雖然聚合操作將查詢行分組為單個結果行，但窗口函數會為每個查詢行生成一個結果。

窗口函數	用途說明
ROW_NUMBER()	為結果集中的每行記錄分配唯一的連續(xù)整數序號
RANK()	為結果集中的每行記錄分配一個排名
DENSE_RANK()	為結果集中的每行分配一個排名，但不會跳過相同的排名
PERCENT_RANK()	用于計算某行在結果集中的相對排名比，其值介于0-1間，表示相對位置
CUME_DIST()	用于計算某行在結果集中的累積分布值，其值介于0-1間，表示累計分布比例
LAG(expr,n)	返回當前行的前 n 行的expr值
LEAD(expr,n)	返回當前行的后 n 行的expr的值
FIRST_VALUE(expr)	返回第一個expr的值
LAST_VALUE(expr)	返回最后一個expr的值
NTILE()	返回當前行在其分區(qū)內的桶數
NTH_VALUE()	返回窗口內第N行的參數值

over_clause表示over子句。

某些窗口函數允許使用null_treation子句，該子句指定在計算結果時如何處理null值，本選項款為可選。它是SQL標準的一部分，但MySQL實現只允許RESPECT NULL（這也是默認值）。這意味著在計算結果時會考慮NULL值。

博主這里提供5個示例（來自官網）。請注意SQL中的OVER子句。

示例1：

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);

示例2：

 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);

示例3：

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);

示例4：

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);

示例5：

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);