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