前言

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

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

窗口函數(shù)必學必會

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

1. 基本語法

1.1 匿名窗口

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

1.2 顯式窗口

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

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

2. 包括哪些

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

2.1 聚合函數(shù)

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

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

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

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

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

示例1-普通聚合：這是一個普通聚合函數(shù)寫法（來自官網(wǎng)）：

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

示例2-窗口函數(shù)：這是一個轉(zhuǎn)為窗口函數(shù)寫法（來自官網(wǎng)）：

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 專用窗口函數(shù)

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

over_clause表示over子句。

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

博主這里提供5個示例（來自官網(wǎng)）。請注意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);

結(jié)語

窗口函數(shù)的主要作用是對查詢結(jié)果集中的行進行分組、排序，并在每個分組內(nèi)進行聚合、排名、計算等操作，但不會改變原始查詢結(jié)果的行數(shù)或順序。‌ 窗口函數(shù)主要用于數(shù)據(jù)分析場景，其最大的特點是輸入值是從SELECT語句結(jié)果集中的一行或多行的“窗口”中獲取的‌。窗口函數(shù)的具體應(yīng)用場景包括：

• 分組排序‌：可以對數(shù)據(jù)進行分組排序，求和、求平均值、計數(shù)等‌；
• 排名計算‌：計算分組內(nèi)的排名或累積求和等‌；
• 數(shù)據(jù)分析‌：提供強大的數(shù)據(jù)分析支持，如計算同比/環(huán)比增長率等‌；

到此這篇關(guān)于MySQL8.0版本如何正確的使用窗口函數(shù)的文章就介紹到這了,更多相關(guān)MySQL8.0正確使用窗口函數(shù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論