簡介

數(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ù)一個或多個字段對結果集進行分組，并在每個分組內(nèi)生成一個行號，以便用戶可以輕松地跟蹤數(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ù)一個或多個字段對結果集進行分組，并在每個分組內(nèi)生成一個行號，以便用戶可以輕松地跟蹤數(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：表示包含當前行在內(nèi)的前后各一行。

說明: 行窗口可以用于計算每組的總和、平均值、計數(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_count
from order_for_goods;

從 order_for_goods 表中選擇了所有的列，并計算了每個用戶在每個訂單中的總金額、平均金額、最大金額、最小金額和計數(shù)。

這個查詢使用了 sum()、avg()、max()、min() 和 count() 函數(shù)來計算每個訂單的總金額、平均金額、最大金額、最小金額和計數(shù)。這些函數(shù)后面跟著 over() 子句，用于指定計算的窗口。在這個例子中，窗口是按照 user_id 分區(qū)，按照 order_id 排序的。

2、查詢結果返回了選擇的列和計算出的別名列如下

4.3、序號函數(shù)

4.3.1、ROW_NUMBER()函數(shù)

1、執(zhí)行語句

select *
from (
	select *,row_number() over(partition by user_id order by money desc) as alias_row_number
	from order_for_goods) t
where alias_row_number<=3;

• 以上SQL語句使用了窗口函數(shù) row_number() 來為每個分區(qū)內(nèi)的行分配序號。然后，外部查詢從這些序號中選擇前三個最高的行。
• 內(nèi)部查詢從 order_for_goods 表中選擇了所有的列，并使用 row_number() 函數(shù)為每個分區(qū)內(nèi)的行分配序號。在這個例子中，子查詢將數(shù)據(jù)是按照 user_id 列進行分區(qū) ，按照 money 列的降序排列的。
• 外部查詢從內(nèi)部查詢的結果中選擇了序號小于等于 3 的行，這些行對應于分區(qū)內(nèi)前三高的行。

2、執(zhí)行結果

3、執(zhí)行語句

select *
from (
	select *,row_number() over(partition by user_id order by money desc) as alias_row_number
	from order_for_goods) t
where alias_row_number<=1;

 以上這個查詢語句與上一個查詢語句類似，只不過 alias_row_number<=3 改成了 alias_row_number<=1，因此結果將只返回分區(qū)內(nèi)最高的一行。

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ū)內(nèi)的連續(xù)排名計算一個排名值，因此這個語句會為每個用戶計算一個別名排名。注意語句沒有指定任何條件，因此它會返回 order_for_goods 表中的所有行和列。如果需要查詢特定的行或列，可以在 select 子句中指定相應的條件或列名。

 2、執(zhí)行結果

4.3.3、DENSE_RANK()函數(shù)

 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ū)內(nèi)的排名計算一個排名值，對于相鄰排名值相同的行，排名值會連續(xù)分配。因此，這個語句會為每個用戶計算一個別名密集排名。
• 注意語句沒有指定任何條件，因此它會返回 order_for_goods 表中的所有行和列。如果需要查詢特定的行或列，可以在 select 子句中指定相應的條件或列名。

 2、執(zhí)行結果

4.3.4、上述三種序號函數(shù)對比

 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_rank
from order_for_goods;

•  從 order_for_goods 表中選擇了所有的列，并計算了每個用戶在每個訂單中的總金額，以及計算了每個用戶在每個訂單中的序號、排名和稠密排名。
• 這個查詢使用了 row_number()、rank() 和 dense_rank() 函數(shù)來計算每個分區(qū)內(nèi)的行的序號、排名和稠密排名。這些函數(shù)后面跟著 over() 子句，用于指定計算的窗口。在這個例子中，窗口是按照 user_id 分區(qū)，按照 money 列的降序排列的。

  2、執(zhí)行結果

4.4、分布函數(shù)

4.4.1、PERCENT RANK()函數(shù)

  1、執(zhí)行語句

select 
	*,
	percent_rank() over(partition by user_id order by money desc) as alias_percent_rank
from order_for_goods;

•  從 order_for_goods 表中選擇了所有的列，并計算了每個用戶在每個訂單中的總金額，以及計算了每個用戶在每個訂單中的百分比排名。
• 這個查詢使用了 percent_rank() 函數(shù)來計算每個分區(qū)內(nèi)的行的百分比排名。這個函數(shù)后面跟著 over() 子句，用于指定計算的窗口。在這個例子中，窗口是按照 user_id 分區(qū)，按照 money 列的降序排列的。

  2、執(zhí)行結果

4.4.2、CUME_DIST()函數(shù)

  1、執(zhí)行語句

select 
	*,
	cume_dist() over(partition by user_id order by money desc) as alias_percent_rank
from order_for_goods;

•  從 order_for_goods 表中選擇了所有的列，并計算了每個用戶在每個訂單中的總金額，以及計算了每個用戶在每個訂單中的累積百分比。
• 這個查詢使用了 cume_dist() 函數(shù)來計算每個分區(qū)內(nèi)的行的累積百分比。這個函數(shù)后面跟著 over() 子句，用于指定計算的窗口。在這個例子中，窗口是按照 user_id 分區(qū)，按照 money 列的降序排列的。

  2、執(zhí)行結果

4.5、前后函數(shù)

4.5.1、LAG()函數(shù)

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_lag
from order_for_goods;

 3、執(zhí)行結果

4.5.2、LEAD()函數(shù)

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_lead
from order_for_goods;

3、執(zhí)行結果

4.6、收尾函數(shù)

4.6.1、FIRST_VALUE()函數(shù)

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_value
from order_for_goods;

注意，如果某個用戶在指定時間范圍內(nèi)沒有數(shù)據(jù)，則 LAST_VALUE() 函數(shù)將返回默認值 NULL。 

 3、執(zhí)行結果

4.6.2、LAST_VALUE()函數(shù)

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_value
from order_for_goods;

注意，如果某個用戶在指定時間范圍內(nèi)沒有數(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_value3
from 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_ntile3
from 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_value
from order_for_goods;

注意，如果某個用戶在指定時間范圍內(nèi)沒有數(shù)據(jù)，則 NTH_VALUE()函數(shù)將返回默認值 NULL。 

3、執(zhí)行結果

總結

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