前言

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

恭喜你，有這個(gè)認(rèn)知后，博主可以正式介紹它了，請(qǐng)緊隨博主，以防迷路。

窗口函數(shù)必學(xué)必會(huì)

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

1. 基本語(yǔ)法

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)計(jì)值分組到子集中。

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

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

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

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

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

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

示例2-窗口函數(shù)：這是一個(gè)轉(zhuǎn)為窗口函數(shù)寫法（來(lái)自官網(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;

是不是很簡(jiǎn)單？

2.2 專用窗口函數(shù)

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

over_clause表示over子句。

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

博主這里提供5個(gè)示例（來(lái)自官網(wǎng)）。請(qǐ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é)語(yǔ)

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

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

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

最新評(píng)論