MySQL8.0數(shù)據(jù)庫(kù)開(kāi)窗函數(shù)圖文詳解
數(shù)據(jù)庫(kù)開(kāi)窗函數(shù)是一種在SQL中使用的函數(shù)，它可以用來(lái)對(duì)結(jié)果集中的數(shù)據(jù)進(jìn)行分組和排序，以便更好地分析和處理數(shù)據(jù)。開(kāi)窗函數(shù)與聚合函數(shù)不同，它不會(huì)將多行數(shù)據(jù)聚合成一行，而是保留每一行數(shù)據(jù)，并對(duì)其進(jìn)行分組和排序。
常見(jiàn)的開(kāi)窗函數(shù)包括ROW_NUMBER()、RANK()、DENSE_RANK()、NTILE()、LAG()、LEAD()等。這些函數(shù)可以幫助用戶在結(jié)果集中生成分組和排序的結(jié)果，以便更好地理解和分析數(shù)據(jù)。
例如，使用ROW_NUMBER()函數(shù)可以根據(jù)一個(gè)或多個(gè)字段對(duì)結(jié)果集進(jìn)行分組，并在每個(gè)分組內(nèi)生成一個(gè)行號(hào)，以便用戶可以輕松地跟蹤數(shù)據(jù)。使用LAG()和LEAD()函數(shù)可以在結(jié)果集中的每一行之前和之后提取數(shù)據(jù)，以便用戶可以查看當(dāng)前行之前或之后的數(shù)據(jù)。
開(kāi)窗函數(shù)是SQL中非常有用的工具，可以幫助用戶對(duì)結(jié)果集中的數(shù)據(jù)進(jìn)行分組和排序，以便更好地分析和處理數(shù)據(jù)。
MySQL 官方文檔: https://dev.mysql.com/doc/refman/8.0/en/window-functions.html
注意: 官方解釋 開(kāi)窗函數(shù)只有MySQL8.0版本之后才有哦。
一、開(kāi)窗函數(shù) 與 聚合函數(shù) 有什么區(qū)別?數(shù)據(jù)處理范圍：聚合函數(shù)只能對(duì)整個(gè)數(shù)據(jù)表或者數(shù)據(jù)集進(jìn)行操作，計(jì)算結(jié)果為單一值。而開(kāi)窗函數(shù)則可以對(duì)每個(gè)行進(jìn)行操作，計(jì)算結(jié)果會(huì)在每個(gè)行上顯示。
計(jì)算結(jié)果：聚合函數(shù)的計(jì)算結(jié)果只有一個(gè)，通常用于執(zhí)行諸如求和、取平均值、計(jì)算最大值/最小值等的操作。而開(kāi)窗函數(shù)的計(jì)算結(jié)果可以有多個(gè)，它提供給查詢結(jié)果集中每一行的附加列。語(yǔ)法：聚合函數(shù)通常用于SELECT語(yǔ)句中的SELECT子句和HAVING子句，而開(kāi)窗函數(shù)通常在OVER關(guān)鍵字后使用。
二、官方解釋的開(kāi)窗函數(shù)
翻譯
官方說(shuō)的很官方，稍有點(diǎn)難以理解還是。
三、開(kāi)窗函數(shù)細(xì)分3.1、序號(hào)ROW_NUMBER()：該函數(shù)可以根據(jù)一個(gè)或多個(gè)字段對(duì)結(jié)果集進(jìn)行分組，并在每個(gè)分組內(nèi)生成一個(gè)行號(hào)，以便用戶可以輕松地跟蹤數(shù)據(jù)。RANK()：該函數(shù)可以根據(jù)一個(gè)或多個(gè)字段對(duì)結(jié)果集進(jìn)行排序，并在每個(gè)排序中生成一個(gè)排名，以便用戶可以了解數(shù)據(jù)的大小和順序。DENSE_RANK()：該函數(shù)可以根據(jù)一個(gè)或多個(gè)字段對(duì)結(jié)果集進(jìn)行排序，并在每個(gè)排序中生成一個(gè)排名，但跳過(guò)的位次比RANK()函數(shù)少一位。3.2、分布PERCENT RANK()：函數(shù)用于計(jì)算數(shù)據(jù)集中每個(gè)值的百分比排名。CUME_DIST()：函數(shù)用于計(jì)算數(shù)據(jù)集中每個(gè)值的累積密度排名。3.3、前后LAG()：該函數(shù)可以在結(jié)果集中的每一行之前提取數(shù)據(jù)，以便用戶可以查看當(dāng)前行之前的數(shù)據(jù)。LEAD()：該函數(shù)可以在結(jié)果集中的每一行之后提取數(shù)據(jù)，以便用戶可以查看當(dāng)前行之后的數(shù)據(jù)。3.4、首尾FIRST_VALUE()：函數(shù)返回結(jié)果集的有序分區(qū)中的第一個(gè)值。LAST_VALUE()：函數(shù)返回結(jié)果集的有序分區(qū)中的最后一個(gè)值。3.5、其它NTILE()：該函數(shù)可以根據(jù)一個(gè)或多個(gè)字段對(duì)結(jié)果集進(jìn)行分組，并將每個(gè)分組分配到指定數(shù)量的桶中，以便用戶可以更好地分析和分組數(shù)據(jù)。NTH_VALUE()：函數(shù)返回結(jié)果集的有序分區(qū)中第n行的值。四、語(yǔ)法使用4.1、語(yǔ)法結(jié)構(gòu)<窗口函數(shù)> OVER ([PARTITION BY <分組列>] [ORDER BY <排序列> {ASC|DESC}] [<行窗口>|<范圍窗口>] [<開(kāi)始位置>|<結(jié)束位置>|<長(zhǎng)度>])
<窗口函數(shù)>表示要執(zhí)行的聚合函數(shù)，如SUM、AVG、MAX、MIN、COUNT等；<分組列>表示要進(jìn)行分組的列；<排序列>表示按照哪個(gè)列進(jìn)行排序，可以指定多個(gè)排序列，用逗號(hào)分隔；<行窗口>和<范圍窗口>分別表示行級(jí)窗口和范圍級(jí)窗口；<開(kāi)始位置>、<結(jié)束位置>和<長(zhǎng)度>表示窗口的起始位置、結(jié)束位置和長(zhǎng)度。在 MySQL 8.0 中，行窗口是指一組連續(xù)的行，這些行被視為一個(gè)整體，并且可以用于窗口函數(shù)的計(jì)算。
行窗口由以下關(guān)鍵字指定：
ROWS：表示行窗口。BETWEEN：用于指定行窗口的起始位置和結(jié)束位置。PRECEDING：表示行窗口的起始位置。FOLLOWING：表示行窗口的結(jié)束位置。常用的行窗口指定方式：
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：表示從結(jié)果集的第一個(gè)行到當(dāng)前行，包括當(dāng)前行。ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING：表示從當(dāng)前行到結(jié)果集的最后一個(gè)行，包括當(dāng)前行。ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING：表示包含當(dāng)前行在內(nèi)的前后各一行。說(shuō)明: 行窗口可以用于計(jì)算每組的總和、平均值、計(jì)數(shù)等聚合操作，也可以用于計(jì)算每個(gè)行的排名、累積和等操作。
4.2、普通聚合函數(shù)做開(kāi)窗函數(shù)普通聚合函數(shù)只能對(duì)整個(gè)數(shù)據(jù)表或者數(shù)據(jù)集進(jìn)行操作，計(jì)算結(jié)果為單一值。而開(kāi)窗函數(shù)可以針對(duì)每個(gè)行進(jìn)行操作，計(jì)算結(jié)果會(huì)在每個(gè)行上顯示。
4.2.1、表結(jié)構(gòu)DROP TABLE IF EXISTS `order_for_goods`;CREATE TABLE `order_for_goods` ( `order_id` int(0) NOT NULL AUTO_INCREMENT, `user_id` int(0) NULL DEFAULT NULL, `money` decimal(10, 2) NULL DEFAULT NULL, `quantity` int(0) NULL DEFAULT NULL, `join_time` datetime(0) NULL DEFAULT NULL, PRIMARY KEY (`order_id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 12 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;4.2.2、表數(shù)據(jù)INSERT INTO order_for_goods (user_id, money, quantity, join_time )VALUES( 1001, 1800.90, 1, '2023-06-07'),( 1001, 3600.89, 5, '2023-05-02'),( 1001, 1000.10, 6, '2023-01-08'),( 1002, 1100.90, 9, '2023-04-07'),( 1002, 4500.99, 1, '2023-03-14'),( 1003, 2500.10, 3, '2023-02-14'),( 1002, 2500.90, 1, '2023-03-14'),( 1003, 2500.90, 1, '2022-12-12'),( 1003, 2500.90, 2, '2022-09-08'), ( 1003, 6000.90, 8, '2023-01-10');4.2.3、普通函數(shù)做開(kāi)窗函數(shù)1、語(yǔ)句如下
select *,sum(money) over(partition by user_id order by order_id) as alias_sum,avg(money) over(partition by user_id order by order_id) as alias_avg,max(money) over(partition by user_id order by order_id) as alias_max,min(money) over(partition by user_id order by order_id) as alias_min,count(money) over(partition by user_id order by order_id) as alias_countfrom order_for_goods;從 order_for_goods 表中選擇了所有的列，并計(jì)算了每個(gè)用戶在每個(gè)訂單中的總金額、平均金額、最大金額、最小金額和計(jì)數(shù)。
這個(gè)查詢使用了 sum()、avg()、max()、min() 和 count() 函數(shù)來(lái)計(jì)算每個(gè)訂單的總金額、平均金額、最大金額、最小金額和計(jì)數(shù)。這些函數(shù)后面跟著 over() 子句，用于指定計(jì)算的窗口。在這個(gè)例子中，窗口是按照 user_id 分區(qū)，按照 order_id 排序的。
2、查詢結(jié)果返回了選擇的列和計(jì)算出的別名列如下
1、執(zhí)行語(yǔ)句
select *from (select *,row_number() over(partition by user_id order by money desc) as alias_row_numberfrom order_for_goods) twhere alias_row_number<=3;以上SQL語(yǔ)句使用了窗口函數(shù) row_number() 來(lái)為每個(gè)分區(qū)內(nèi)的行分配序號(hào)。然后，外部查詢從這些序號(hào)中選擇前三個(gè)最高的行。內(nèi)部查詢從 order_for_goods 表中選擇了所有的列，并使用 row_number() 函數(shù)為每個(gè)分區(qū)內(nèi)的行分配序號(hào)。在這個(gè)例子中，子查詢將數(shù)據(jù)是按照 user_id 列進(jìn)行分區(qū) ，按照 money 列的降序排列的。外部查詢從內(nèi)部查詢的結(jié)果中選擇了序號(hào)小于等于 3 的行，這些行對(duì)應(yīng)于分區(qū)內(nèi)前三高的行。2、執(zhí)行結(jié)果
3、執(zhí)行語(yǔ)句
select *from (select *,row_number() over(partition by user_id order by money desc) as alias_row_numberfrom order_for_goods) twhere alias_row_number<=1;以上這個(gè)查詢語(yǔ)句與上一個(gè)查詢語(yǔ)句類(lèi)似，只不過(guò) alias_row_number<=3 改成了 alias_row_number<=1，因此結(jié)果將只返回分區(qū)內(nèi)最高的一行。
4、執(zhí)行結(jié)果
總結(jié): 可以發(fā)散思維想一想，舉個(gè)栗子: 比如統(tǒng)計(jì)各個(gè)商品領(lǐng)域銷(xiāo)量排行前三。使用開(kāi)窗是不是可以解決很多問(wèn)題，也避免了大量難以維護(hù)且看不懂的sql邏輯。
4.3.2、RANK()函數(shù)1、執(zhí)行語(yǔ)句
select *, rank() over(partition by user_id order by money desc) as alias_rank from order_for_goods;以上SQL語(yǔ)句使用了窗口函數(shù) rank() 來(lái)為每個(gè)用戶計(jì)算一個(gè)別名排名（alias_rank）。rank() 函數(shù)會(huì)為每個(gè)分區(qū)內(nèi)的連續(xù)排名計(jì)算一個(gè)排名值，因此這個(gè)語(yǔ)句會(huì)為每個(gè)用戶計(jì)算一個(gè)別名排名。注意語(yǔ)句沒(méi)有指定任何條件，因此它會(huì)返回 order_for_goods 表中的所有行和列。如果需要查詢特定的行或列，可以在 select 子句中指定相應(yīng)的條件或列名。
2、執(zhí)行結(jié)果
1、執(zhí)行語(yǔ)句
select *, dense_rank() over(partition by user_id order by money desc) as alias_dense_rank from order_for_goods; 以上SQL語(yǔ)句使用了窗口函數(shù) dense_rank() 來(lái)為每個(gè)用戶計(jì)算一個(gè)別名密集排名（alias_dense_rank）。dense_rank() 函數(shù)會(huì)為每個(gè)分區(qū)內(nèi)的排名計(jì)算一個(gè)排名值，對(duì)于相鄰排名值相同的行，排名值會(huì)連續(xù)分配。因此，這個(gè)語(yǔ)句會(huì)為每個(gè)用戶計(jì)算一個(gè)別名密集排名。注意語(yǔ)句沒(méi)有指定任何條件，因此它會(huì)返回 order_for_goods 表中的所有行和列。如果需要查詢特定的行或列，可以在 select 子句中指定相應(yīng)的條件或列名。2、執(zhí)行結(jié)果
1、執(zhí)行語(yǔ)句
select *, row_number() over(partition by user_id order by money desc) as alias_row_number, rank() over(partition by user_id order by money desc) as alias_rank, dense_rank() over(partition by user_id order by money desc) as alias_dense_rankfrom order_for_goods; 從 order_for_goods 表中選擇了所有的列，并計(jì)算了每個(gè)用戶在每個(gè)訂單中的總金額，以及計(jì)算了每個(gè)用戶在每個(gè)訂單中的序號(hào)、排名和稠密排名。這個(gè)查詢使用了 row_number()、rank() 和 dense_rank() 函數(shù)來(lái)計(jì)算每個(gè)分區(qū)內(nèi)的行的序號(hào)、排名和稠密排名。這些函數(shù)后面跟著 over() 子句，用于指定計(jì)算的窗口。在這個(gè)例子中，窗口是按照 user_id 分區(qū)，按照 money 列的降序排列的。2、執(zhí)行結(jié)果
1、執(zhí)行語(yǔ)句
select *,percent_rank() over(partition by user_id order by money desc) as alias_percent_rankfrom order_for_goods; 從 order_for_goods 表中選擇了所有的列，并計(jì)算了每個(gè)用戶在每個(gè)訂單中的總金額，以及計(jì)算了每個(gè)用戶在每個(gè)訂單中的百分比排名。這個(gè)查詢使用了 percent_rank() 函數(shù)來(lái)計(jì)算每個(gè)分區(qū)內(nèi)的行的百分比排名。這個(gè)函數(shù)后面跟著 over() 子句，用于指定計(jì)算的窗口。在這個(gè)例子中，窗口是按照 user_id 分區(qū)，按照 money 列的降序排列的。2、執(zhí)行結(jié)果
1、執(zhí)行語(yǔ)句
select *,cume_dist() over(partition by user_id order by money desc) as alias_percent_rankfrom order_for_goods; 從 order_for_goods 表中選擇了所有的列，并計(jì)算了每個(gè)用戶在每個(gè)訂單中的總金額，以及計(jì)算了每個(gè)用戶在每個(gè)訂單中的累積百分比。這個(gè)查詢使用了 cume_dist() 函數(shù)來(lái)計(jì)算每個(gè)分區(qū)內(nèi)的行的累積百分比。這個(gè)函數(shù)后面跟著 over() 子句，用于指定計(jì)算的窗口。在這個(gè)例子中，窗口是按照 user_id 分區(qū)，按照 money 列的降序排列的。2、執(zhí)行結(jié)果
1、語(yǔ)法說(shuō)明
LAG()函數(shù)是用于在時(shí)間序列中向前移動(dòng)指定周期的函數(shù)。
LAG(expression, offset, default_value)expression：要取值的列 offset：向前數(shù)第幾行的值 default_value：如果沒(méi)有值，可設(shè)置默認(rèn)值
2、執(zhí)行語(yǔ)句
select *,lag(join_time, 1, 0) over(partition by user_id order by join_time desc) as alias_lagfrom order_for_goods;3、執(zhí)行結(jié)果
1、語(yǔ)法說(shuō)明
LEAD()函數(shù)是用于在時(shí)間序列中向后移動(dòng)指定周期的函數(shù)。
LAG(expression, offset, default_value)expression：要取值的列 offset：向后數(shù)第幾行的值 default_value：如果沒(méi)有值，可設(shè)置默認(rèn)值
2、執(zhí)行語(yǔ)句
select *,lead(join_time, 1, 0) over(partition by user_id order by join_time desc) as alias_leadfrom order_for_goods;3、執(zhí)行結(jié)果
1、語(yǔ)法說(shuō)明
FIRST_VALUE：取窗口第一行的值
FIRST_VALUE(expression)expression：一個(gè)表達(dá)式，用于指定要獲取第一行值的列或計(jì)算結(jié)果。
2、執(zhí)行語(yǔ)法
select *,first_value(money) over(partition by user_id order by join_time desc) as alias_first_valuefrom order_for_goods;注意，如果某個(gè)用戶在指定時(shí)間范圍內(nèi)沒(méi)有數(shù)據(jù)，則 LAST_VALUE() 函數(shù)將返回默認(rèn)值 NULL。
3、執(zhí)行結(jié)果
1、語(yǔ)法說(shuō)明
LAST_VALUE：取窗口最后一行的值。
LAST_VALUE(expression)expression：一個(gè)表達(dá)式，用于指定要獲取最后一行值的列或計(jì)算結(jié)果。
2、執(zhí)行語(yǔ)法
select *,first_value(money) over(partition by user_id order by join_time desc) as alias_first_valuefrom order_for_goods;注意，如果某個(gè)用戶在指定時(shí)間范圍內(nèi)沒(méi)有數(shù)據(jù)，則 LAST_VALUE() 函數(shù)將返回默認(rèn)值 NULL。
2、執(zhí)行結(jié)果
3、解釋
你可能會(huì)發(fā)現(xiàn)LAST_VALUE() 不是取窗口的最后一個(gè)值，窗口按照 user_id 分區(qū)，按照 join_time 列排序，按道理是返回1001分區(qū)中money為1800.90才對(duì)啊? 為什么? 為什么?原因是LAST_VALUE()默認(rèn)統(tǒng)計(jì)范圍是 rows between unbounded preceding and current row
3、驗(yàn)證
select *,last_value(money) over(partition by user_id order by join_time) as alias_last_value1,last_value(money) over(partition by user_id order by join_time rows between unbounded preceding and current row) as alias_last_value2,last_value(money) over(partition by user_id order by join_time rows between unbounded preceding and unbounded following) as alias_last_value3from order_for_goods; 可以看到別名 alias_last_value2 驗(yàn)證了LAST_VALUE()默認(rèn)統(tǒng)計(jì)范圍就是 rows between unbounded preceding and current row(表示從當(dāng)前行開(kāi)始向前沒(méi)有邊界地進(jìn)行計(jì)算，即計(jì)算當(dāng)前行之前的所有行的結(jié)果。) 可以看到別名 alias_last_value3 在指定 rows between unbounded preceding and unbounded following(表示從當(dāng)前行開(kāi)始向前和向后都沒(méi)有邊界地進(jìn)行計(jì)算，即計(jì)算整個(gè)分區(qū)的結(jié)果。)全部統(tǒng)計(jì)情況下可以得到，user_id 分區(qū)，join_time 列排序，返回1001分區(qū)中字段money最后一筆交易金額為1800.90。+----------+---------+---------+----------+---------------------+------------------+------------------+------------------+| order_id | user_id | money | quantity | join_time | alias_last_value | alias_last_value | alias_last_value |+----------+---------+---------+----------+---------------------+------------------+------------------+------------------+| 34 | 1001 | 1000.10 |6 | 2023-01-08 00:00:00 | 1000.10 | 1000.10 | 1800.90 || 33 | 1001 | 3600.89 |5 | 2023-05-02 00:00:00 | 3600.89 | 3600.89 | 1800.90 || 32 | 1001 | 1800.90 |1 | 2023-06-07 00:00:00 | 1800.90 | 1800.90 | 1800.90 || 36 | 1002 | 4500.99 |1 | 2023-03-14 00:00:00 | 2500.90 | 4500.99 | 1100.90 || 38 | 1002 | 2500.90 |1 | 2023-03-14 00:00:00 | 2500.90 | 2500.90 | 1100.90 || 35 | 1002 | 1100.90 |9 | 2023-04-07 00:00:00 | 1100.90 | 1100.90 | 1100.90 || 40 | 1003 | 2500.90 |2 | 2022-09-08 00:00:00 | 2500.90 | 2500.90 | 2500.10 || 39 | 1003 | 2500.90 |1 | 2022-12-12 00:00:00 | 2500.90 | 2500.90 | 2500.10 || 41 | 1003 | 6000.90 |8 | 2023-01-10 00:00:00 | 6000.90 | 6000.90 | 2500.10 || 37 | 1003 | 2500.10 |3 | 2023-02-14 00:00:00 | 2500.10 | 2500.10 | 2500.10 |+----------+---------+---------+----------+---------------------+------------------+------------------+------------------+10 rows in set (0.00 sec)4.7、其它函數(shù)4.7.1、NTILE()函數(shù)1、語(yǔ)法說(shuō)明
NTILE() 用于將一個(gè)查詢結(jié)果集劃分成指定數(shù)量的桶，并根據(jù)桶的大小將數(shù)據(jù)分配到各個(gè)桶中。
NTILE(bucket_size)bucket_size：一個(gè)整數(shù)參數(shù)，表示要將結(jié)果集劃分成的桶的數(shù)量。
2、執(zhí)行語(yǔ)句
select *, ntile(1) over(partition by user_id order by join_time desc) as alias_ntile1,ntile(2) over(partition by user_id order by join_time desc) as alias_ntile2,ntile(3) over(partition by user_id order by join_time desc) as alias_ntile3from order_for_goods; 查詢使用窗口函數(shù) NTILE()，它可以將數(shù)據(jù)集合平均分配到指定的數(shù)量的桶中，并返回每個(gè)行所屬的桶號(hào)。 以別名 "alias_ntile3" 舉例，該查詢中ntile(3) 表示將每個(gè)用戶分為三個(gè)組，partition by user_id 表示按照 user_id 分組，order by join_time desc 表示按照 join_time 降序排序。如果是ntile(2)就表示分兩個(gè)組ntile(1)就表示分一個(gè)組。3、執(zhí)行結(jié)果
說(shuō)明: NTILE()函數(shù)，可以將有序的數(shù)據(jù)集合平均分配到指定的數(shù)量的桶中，將桶號(hào)分配給每一行。如果不能平均分配，則較小桶號(hào)的桶分配額外的行，并且各個(gè)桶中能放的行數(shù)最多相差1。
4.7.2、NTH_VALUE()函數(shù)1、語(yǔ)法說(shuō)明
NTH_VALUE() 函數(shù)是 SQL 中用于計(jì)算一個(gè)有序數(shù)據(jù)集合中指定位置的值的窗口函數(shù)。
NTH_VALUE(expression, nth_parameter) expression：要計(jì)算其值的表達(dá)式，其求值為單個(gè)值。 nth_parameter：是一個(gè)整數(shù)參數(shù)，表示要計(jì)算的值的序號(hào)。2、執(zhí)行語(yǔ)句
select *, nth_value(money, 2) over(partition by user_id order by join_time ) as alias_nth_valuefrom order_for_goods;注意，如果某個(gè)用戶在指定時(shí)間范圍內(nèi)沒(méi)有數(shù)據(jù)，則 NTH_VALUE()函數(shù)將返回默認(rèn)值 NULL。
3、執(zhí)行結(jié)果
到此這篇關(guān)于MySQL8.0數(shù)據(jù)庫(kù)開(kāi)窗函數(shù)的文章就介紹到這了,更多相關(guān)MySQL8.0開(kāi)窗函數(shù)內(nèi)容請(qǐng)搜索好吧啦網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持好吧啦網(wǎng)！
網(wǎng)公網(wǎng)安備