MySQL8.0數(shù)據(jù)庫開窗函數(shù)圖文詳解
數(shù)據(jù)庫開窗函數(shù)是一種在SQL中使用的函數(shù)，它可以用來對結果集中的數(shù)據(jù)進行分組和排序，以便更好地分析和處理數(shù)據(jù)。開窗函數(shù)與聚合函數(shù)不同，它不會將多行數(shù)據(jù)聚合成一行，而是保留每一行數(shù)據(jù)，并對其進行分組和排序。
常見的開窗函數(shù)包括ROW_NUMBER()、RANK()、DENSE_RANK()、NTILE()、LAG()、LEAD()等。這些函數(shù)可以幫助用戶在結果集中生成分組和排序的結果，以便更好地理解和分析數(shù)據(jù)。
例如，使用ROW_NUMBER()函數(shù)可以根據(jù)一個或多個字段對結果集進行分組，并在每個分組內生成一個行號，以便用戶可以輕松地跟蹤數(shù)據(jù)。使用LAG()和LEAD()函數(shù)可以在結果集中的每一行之前和之后提取數(shù)據(jù)，以便用戶可以查看當前行之前或之后的數(shù)據(jù)。
開窗函數(shù)是SQL中非常有用的工具，可以幫助用戶對結果集中的數(shù)據(jù)進行分組和排序，以便更好地分析和處理數(shù)據(jù)。
MySQL 官方文檔: https://dev.mysql.com/doc/refman/8.0/en/window-functions.html
注意: 官方解釋 開窗函數(shù)只有MySQL8.0版本之后才有哦。
一、開窗函數(shù) 與 聚合函數(shù) 有什么區(qū)別?數(shù)據(jù)處理范圍：聚合函數(shù)只能對整個數(shù)據(jù)表或者數(shù)據(jù)集進行操作，計算結果為單一值。而開窗函數(shù)則可以對每個行進行操作，計算結果會在每個行上顯示。
計算結果：聚合函數(shù)的計算結果只有一個，通常用于執(zhí)行諸如求和、取平均值、計算最大值/最小值等的操作。而開窗函數(shù)的計算結果可以有多個，它提供給查詢結果集中每一行的附加列。語法：聚合函數(shù)通常用于SELECT語句中的SELECT子句和HAVING子句，而開窗函數(shù)通常在OVER關鍵字后使用。
二、官方解釋的開窗函數(shù)
翻譯
官方說的很官方，稍有點難以理解還是。
三、開窗函數(shù)細分3.1、序號ROW_NUMBER()：該函數(shù)可以根據(jù)一個或多個字段對結果集進行分組，并在每個分組內生成一個行號，以便用戶可以輕松地跟蹤數(shù)據(jù)。RANK()：該函數(shù)可以根據(jù)一個或多個字段對結果集進行排序，并在每個排序中生成一個排名，以便用戶可以了解數(shù)據(jù)的大小和順序。DENSE_RANK()：該函數(shù)可以根據(jù)一個或多個字段對結果集進行排序，并在每個排序中生成一個排名，但跳過的位次比RANK()函數(shù)少一位。3.2、分布PERCENT RANK()：函數(shù)用于計算數(shù)據(jù)集中每個值的百分比排名。CUME_DIST()：函數(shù)用于計算數(shù)據(jù)集中每個值的累積密度排名。3.3、前后LAG()：該函數(shù)可以在結果集中的每一行之前提取數(shù)據(jù)，以便用戶可以查看當前行之前的數(shù)據(jù)。LEAD()：該函數(shù)可以在結果集中的每一行之后提取數(shù)據(jù)，以便用戶可以查看當前行之后的數(shù)據(jù)。3.4、首尾FIRST_VALUE()：函數(shù)返回結果集的有序分區(qū)中的第一個值。LAST_VALUE()：函數(shù)返回結果集的有序分區(qū)中的最后一個值。3.5、其它NTILE()：該函數(shù)可以根據(jù)一個或多個字段對結果集進行分組，并將每個分組分配到指定數(shù)量的桶中，以便用戶可以更好地分析和分組數(shù)據(jù)。NTH_VALUE()：函數(shù)返回結果集的有序分區(qū)中第n行的值。四、語法使用4.1、語法結構<窗口函數(shù)> OVER ([PARTITION BY <分組列>] [ORDER BY <排序列> {ASC|DESC}] [<行窗口>|<范圍窗口>] [<開始位置>|<結束位置>|<長度>])
<窗口函數(shù)>表示要執(zhí)行的聚合函數(shù)，如SUM、AVG、MAX、MIN、COUNT等；<分組列>表示要進行分組的列；<排序列>表示按照哪個列進行排序，可以指定多個排序列，用逗號分隔；<行窗口>和<范圍窗口>分別表示行級窗口和范圍級窗口；<開始位置>、<結束位置>和<長度>表示窗口的起始位置、結束位置和長度。在 MySQL 8.0 中，行窗口是指一組連續(xù)的行，這些行被視為一個整體，并且可以用于窗口函數(shù)的計算。
行窗口由以下關鍵字指定：
ROWS：表示行窗口。BETWEEN：用于指定行窗口的起始位置和結束位置。PRECEDING：表示行窗口的起始位置。FOLLOWING：表示行窗口的結束位置。常用的行窗口指定方式：
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：表示從結果集的第一個行到當前行，包括當前行。ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING：表示從當前行到結果集的最后一個行，包括當前行。ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING：表示包含當前行在內的前后各一行。說明: 行窗口可以用于計算每組的總和、平均值、計數(shù)等聚合操作，也可以用于計算每個行的排名、累積和等操作。
4.2、普通聚合函數(shù)做開窗函數(shù)普通聚合函數(shù)只能對整個數(shù)據(jù)表或者數(shù)據(jù)集進行操作，計算結果為單一值。而開窗函數(shù)可以針對每個行進行操作，計算結果會在每個行上顯示。
4.2.1、表結構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ù)做開窗函數(shù)1、語句如下
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 表中選擇了所有的列，并計算了每個用戶在每個訂單中的總金額、平均金額、最大金額、最小金額和計數(shù)。
這個查詢使用了 sum()、avg()、max()、min() 和 count() 函數(shù)來計算每個訂單的總金額、平均金額、最大金額、最小金額和計數(shù)。這些函數(shù)后面跟著 over() 子句，用于指定計算的窗口。在這個例子中，窗口是按照 user_id 分區(qū)，按照 order_id 排序的。
2、查詢結果返回了選擇的列和計算出的別名列如下
1、執(zhí)行語句
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語句使用了窗口函數(shù) row_number() 來為每個分區(qū)內的行分配序號。然后，外部查詢從這些序號中選擇前三個最高的行。內部查詢從 order_for_goods 表中選擇了所有的列，并使用 row_number() 函數(shù)為每個分區(qū)內的行分配序號。在這個例子中，子查詢將數(shù)據(jù)是按照 user_id 列進行分區(qū) ，按照 money 列的降序排列的。外部查詢從內部查詢的結果中選擇了序號小于等于 3 的行，這些行對應于分區(qū)內前三高的行。2、執(zhí)行結果
3、執(zhí)行語句
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;以上這個查詢語句與上一個查詢語句類似，只不過 alias_row_number<=3 改成了 alias_row_number<=1，因此結果將只返回分區(qū)內最高的一行。
4、執(zhí)行結果
總結: 可以發(fā)散思維想一想，舉個栗子: 比如統(tǒng)計各個商品領域銷量排行前三。使用開窗是不是可以解決很多問題，也避免了大量難以維護且看不懂的sql邏輯。
4.3.2、RANK()函數(shù)1、執(zhí)行語句
select *, rank() over(partition by user_id order by money desc) as alias_rank from order_for_goods;以上SQL語句使用了窗口函數(shù) rank() 來為每個用戶計算一個別名排名（alias_rank）。rank() 函數(shù)會為每個分區(qū)內的連續(xù)排名計算一個排名值，因此這個語句會為每個用戶計算一個別名排名。注意語句沒有指定任何條件，因此它會返回 order_for_goods 表中的所有行和列。如果需要查詢特定的行或列，可以在 select 子句中指定相應的條件或列名。
2、執(zhí)行結果
1、執(zhí)行語句
select *, dense_rank() over(partition by user_id order by money desc) as alias_dense_rank from order_for_goods; 以上SQL語句使用了窗口函數(shù) dense_rank() 來為每個用戶計算一個別名密集排名（alias_dense_rank）。dense_rank() 函數(shù)會為每個分區(qū)內的排名計算一個排名值，對于相鄰排名值相同的行，排名值會連續(xù)分配。因此，這個語句會為每個用戶計算一個別名密集排名。注意語句沒有指定任何條件，因此它會返回 order_for_goods 表中的所有行和列。如果需要查詢特定的行或列，可以在 select 子句中指定相應的條件或列名。2、執(zhí)行結果
1、執(zhí)行語句
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 表中選擇了所有的列，并計算了每個用戶在每個訂單中的總金額，以及計算了每個用戶在每個訂單中的序號、排名和稠密排名。這個查詢使用了 row_number()、rank() 和 dense_rank() 函數(shù)來計算每個分區(qū)內的行的序號、排名和稠密排名。這些函數(shù)后面跟著 over() 子句，用于指定計算的窗口。在這個例子中，窗口是按照 user_id 分區(qū)，按照 money 列的降序排列的。2、執(zhí)行結果
1、執(zhí)行語句
select *,percent_rank() over(partition by user_id order by money desc) as alias_percent_rankfrom order_for_goods; 從 order_for_goods 表中選擇了所有的列，并計算了每個用戶在每個訂單中的總金額，以及計算了每個用戶在每個訂單中的百分比排名。這個查詢使用了 percent_rank() 函數(shù)來計算每個分區(qū)內的行的百分比排名。這個函數(shù)后面跟著 over() 子句，用于指定計算的窗口。在這個例子中，窗口是按照 user_id 分區(qū)，按照 money 列的降序排列的。2、執(zhí)行結果
1、執(zhí)行語句
select *,cume_dist() over(partition by user_id order by money desc) as alias_percent_rankfrom order_for_goods; 從 order_for_goods 表中選擇了所有的列，并計算了每個用戶在每個訂單中的總金額，以及計算了每個用戶在每個訂單中的累積百分比。這個查詢使用了 cume_dist() 函數(shù)來計算每個分區(qū)內的行的累積百分比。這個函數(shù)后面跟著 over() 子句，用于指定計算的窗口。在這個例子中，窗口是按照 user_id 分區(qū)，按照 money 列的降序排列的。2、執(zhí)行結果
1、語法說明
LAG()函數(shù)是用于在時間序列中向前移動指定周期的函數(shù)。
LAG(expression, offset, default_value)expression：要取值的列 offset：向前數(shù)第幾行的值 default_value：如果沒有值，可設置默認值
2、執(zhí)行語句
select *,lag(join_time, 1, 0) over(partition by user_id order by join_time desc) as alias_lagfrom order_for_goods;3、執(zhí)行結果
1、語法說明
LEAD()函數(shù)是用于在時間序列中向后移動指定周期的函數(shù)。
LAG(expression, offset, default_value)expression：要取值的列 offset：向后數(shù)第幾行的值 default_value：如果沒有值，可設置默認值
2、執(zhí)行語句
select *,lead(join_time, 1, 0) over(partition by user_id order by join_time desc) as alias_leadfrom order_for_goods;3、執(zhí)行結果
1、語法說明
FIRST_VALUE：取窗口第一行的值
FIRST_VALUE(expression)expression：一個表達式，用于指定要獲取第一行值的列或計算結果。
2、執(zhí)行語法
select *,first_value(money) over(partition by user_id order by join_time desc) as alias_first_valuefrom order_for_goods;注意，如果某個用戶在指定時間范圍內沒有數(shù)據(jù)，則 LAST_VALUE() 函數(shù)將返回默認值 NULL。
3、執(zhí)行結果
1、語法說明
LAST_VALUE：取窗口最后一行的值。
LAST_VALUE(expression)expression：一個表達式，用于指定要獲取最后一行值的列或計算結果。
2、執(zhí)行語法
select *,first_value(money) over(partition by user_id order by join_time desc) as alias_first_valuefrom order_for_goods;注意，如果某個用戶在指定時間范圍內沒有數(shù)據(jù)，則 LAST_VALUE() 函數(shù)將返回默認值 NULL。
2、執(zhí)行結果
3、解釋
你可能會發(fā)現(xiàn)LAST_VALUE() 不是取窗口的最后一個值，窗口按照 user_id 分區(qū)，按照 join_time 列排序，按道理是返回1001分區(qū)中money為1800.90才對啊? 為什么? 為什么?原因是LAST_VALUE()默認統(tǒng)計范圍是 rows between unbounded preceding and current row
3、驗證
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 驗證了LAST_VALUE()默認統(tǒng)計范圍就是 rows between unbounded preceding and current row(表示從當前行開始向前沒有邊界地進行計算，即計算當前行之前的所有行的結果。) 可以看到別名 alias_last_value3 在指定 rows between unbounded preceding and unbounded following(表示從當前行開始向前和向后都沒有邊界地進行計算，即計算整個分區(qū)的結果。)全部統(tǒng)計情況下可以得到，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、語法說明
NTILE() 用于將一個查詢結果集劃分成指定數(shù)量的桶，并根據(jù)桶的大小將數(shù)據(jù)分配到各個桶中。
NTILE(bucket_size)bucket_size：一個整數(shù)參數(shù)，表示要將結果集劃分成的桶的數(shù)量。
2、執(zhí)行語句
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ù)量的桶中，并返回每個行所屬的桶號。 以別名 "alias_ntile3" 舉例，該查詢中ntile(3) 表示將每個用戶分為三個組，partition by user_id 表示按照 user_id 分組，order by join_time desc 表示按照 join_time 降序排序。如果是ntile(2)就表示分兩個組ntile(1)就表示分一個組。3、執(zhí)行結果
說明: NTILE()函數(shù)，可以將有序的數(shù)據(jù)集合平均分配到指定的數(shù)量的桶中，將桶號分配給每一行。如果不能平均分配，則較小桶號的桶分配額外的行，并且各個桶中能放的行數(shù)最多相差1。
4.7.2、NTH_VALUE()函數(shù)1、語法說明
NTH_VALUE() 函數(shù)是 SQL 中用于計算一個有序數(shù)據(jù)集合中指定位置的值的窗口函數(shù)。
NTH_VALUE(expression, nth_parameter) expression：要計算其值的表達式，其求值為單個值。 nth_parameter：是一個整數(shù)參數(shù)，表示要計算的值的序號。2、執(zhí)行語句
select *, nth_value(money, 2) over(partition by user_id order by join_time ) as alias_nth_valuefrom order_for_goods;注意，如果某個用戶在指定時間范圍內沒有數(shù)據(jù)，則 NTH_VALUE()函數(shù)將返回默認值 NULL。
3、執(zhí)行結果
到此這篇關于MySQL8.0數(shù)據(jù)庫開窗函數(shù)的文章就介紹到這了,更多相關MySQL8.0開窗函數(shù)內容請搜索好吧啦網以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持好吧啦網！
網公網安備