窗口函數(shù)是針對查詢的每一行，使用對應(yīng)改行相關(guān)的行進(jìn)行計算。大多數(shù)聚合函數(shù)也可以用作窗口函數(shù)。
窗口函數(shù)
窗口函數(shù)的語法
函數(shù)() over()
over()字句是關(guān)鍵字，用來指定函數(shù)執(zhí)行的窗口范圍，如果后面括號中什么都不寫，則意味著窗口包含滿足where條件的所有行，窗口函數(shù)基于所有行進(jìn)行計算。
函數(shù)() over(partition by 列1,列2 order by 列3) as 列別名
partition by子句也可以被稱為查詢分區(qū)字句，非常類似于group by，都是將數(shù)據(jù)按照邊界值分組，而over()之前的函數(shù)在每一個分組之內(nèi)進(jìn)行，如果超出了分組，則函數(shù)會重新計算
窗口函數(shù)的執(zhí)行時機(jī)
1. from子句
2. on子句
3. where子句
4. group by子句
5. having子句
6. select子句（注意：此處得到的是結(jié)果集）
7. 窗口函數(shù)（注意：窗口函數(shù)時處理的select結(jié)果集）
8. distinct去重子句
9. order by子句
10 limit子句
窗口函數(shù)分區(qū)：partition by函數(shù)
partition by字句會將查詢的結(jié)果集分成幾組。窗口函數(shù)將作用于各個分區(qū)。
mysql> select row_number() over(partition by user_no order by amount) as row_num,user_no,amount from order_tab;
+---------+---------+--------+
| row_num | user_no | amount |
+---------+---------+--------+
| 1 | 001 | 100 |
| 2 | 001 | 300 |
| 3 | 001 | 500 |
| 4 | 001 | 800 |
| 5 | 001 | 900 |
| 1 | 002 | 300 |
| 2 | 002 | 500 |
| 3 | 002 | 600 |
| 4 | 002 | 800 |
| 5 | 002 | 800 |
+---------+---------+--------+
10 rows in set (0.01 sec)
上述是在結(jié)果集中對user_no進(jìn)行分區(qū)，窗口函數(shù)會單獨的作用到每個分區(qū)數(shù)據(jù)，partition by的執(zhí)行時機(jī)是select子句后，distinct去重關(guān)鍵字前。
窗口函數(shù)排序：order by函數(shù)
order by子句暗示如何對每個分區(qū)中的行進(jìn)行排序。根據(jù)order by子句相等的分區(qū)行被視為對等行。如果order by省略，則分區(qū)是無序的，沒有暗含的處理順序，并且所有分區(qū)行都是對等的（注意：窗口函數(shù)中有無排序的影響很大）。
按照功能劃分
按照功能劃分，可以將MySQL支持的窗口函數(shù)分為如下幾類：
| 功能 | 函數(shù) |
|---|---|
| 序號函數(shù) | row_number()/rank()/dense_rank() |
| 分布函數(shù) | percent_rank() / cume_dist() |
| 前后函數(shù) | lag() / lead() |
| 頭尾函數(shù) | first_val() / last_val() |
| 其他函數(shù) | nth_value() / nfile() |
數(shù)據(jù)準(zhǔn)備
CREATE TABLE `order_tab` (
`order_id` int(11) NOT NULL AUTO_INCREMENT,
`user_no` varchar(100) DEFAULT NULL,
`amount` double DEFAULT NULL,
`create_data` timestamp DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=30001;
INSERT INTO `order_tab`(`order_id`, `user_no`, `amount`, `create_data`) VALUES (1, '001', 100, '2020-05-19 21:53:12');
INSERT INTO `order_tab`(`order_id`, `user_no`, `amount`, `create_data`) VALUES (2, '001', 300, '2020-05-19 21:54:14');
INSERT INTO `order_tab`(`order_id`, `user_no`, `amount`, `create_data`) VALUES (3, '001', 500, '2020-05-19 21:54:14');
INSERT INTO `order_tab`(`order_id`, `user_no`, `amount`, `create_data`) VALUES (4, '001', 800, '2020-05-19 21:54:14');
INSERT INTO `order_tab`(`order_id`, `user_no`, `amount`, `create_data`) VALUES (5, '001', 900, '2020-05-19 21:54:14');
INSERT INTO `order_tab`(`order_id`, `user_no`, `amount`, `create_data`) VALUES (6, '002', 500, '2020-05-19 21:54:14');
INSERT INTO `order_tab`(`order_id`, `user_no`, `amount`, `create_data`) VALUES (7, '002', 600, '2020-05-19 21:54:14');
INSERT INTO `order_tab`(`order_id`, `user_no`, `amount`, `create_data`) VALUES (8, '002', 300, '2020-05-19 21:54:14');
INSERT INTO `order_tab`(`order_id`, `user_no`, `amount`, `create_data`) VALUES (9, '002', 800, '2020-05-19 21:54:14');
INSERT INTO `order_tab`(`order_id`, `user_no`, `amount`, `create_data`) VALUES (10, '002', 800, '2020-05-19 21:54:14');
2.1 序號函數(shù)
序號函數(shù)有三個：
| 名稱 | 描述 |
|---|---|
| row_number() | 分區(qū)中當(dāng)前行的數(shù)量 |
| rank() | 當(dāng)前行在其分區(qū)內(nèi)的排名，有間隔 |
| dense()_rank() | 當(dāng)前行在其分區(qū)內(nèi)的排名，無間隔 |
如下圖所示：
mysql> select rank() over w rank_num,
row_number() over w row_num,
dense_rank() over w dense_rank_num,
user_no,
amount
from order_tab
window w as (order by amount);
+----------+---------+----------------+---------+--------+
| rank_num | row_num | dense_rank_num | user_no | amount |
+----------+---------+----------------+---------+--------+
| 1 | 1 | 1 | 001 | 100 |
| 2 | 2 | 2 | 001 | 300 |
| 2 | 3 | 2 | 002 | 300 |
| 4 | 4 | 3 | 001 | 500 |
| 4 | 5 | 3 | 002 | 500 |
| 6 | 6 | 4 | 002 | 600 |
| 7 | 7 | 5 | 001 | 800 |
| 7 | 8 | 5 | 002 | 800 |
| 7 | 9 | 5 | 002 | 800 |
| 10 | 10 | 6 | 001 | 900 |
+----------+---------+----------------+---------+--------+
10 rows in set (0.01 sec)
- rank()/dense_rank()函數(shù)應(yīng)用于order by將分區(qū)行按所需順序排序，如果沒有order by，所有行都是對等體。
mysql> select rank() over() rank_num,dense_rank() over() dense_rank_num,amount from order_tab;
+----------+----------------+--------+
| rank_num | dense_rank_num | amount |
+----------+----------------+--------+
| 1 | 1 | 100 |
| 1 | 1 | 300 |
| 1 | 1 | 500 |
| 1 | 1 | 800 |
| 1 | 1 | 900 |
| 1 | 1 | 500 |
| 1 | 1 | 600 |
| 1 | 1 | 300 |
| 1 | 1 | 800 |
| 1 | 1 | 800 |
+----------+----------------+--------+
10 rows in set (0.01 sec)
RANK()函數(shù)返回當(dāng)前行在其分區(qū)內(nèi)的間隔（帶間隔）。若排序得到字段值相等，那么
RANK()將獲取相同的等級。并結(jié)果是不連續(xù)的等級編號。（eg：成績排名，2個學(xué)生分?jǐn)?shù)相同的是并列第2名，那么下一個是第4名。）DENSE_RANK()返回當(dāng)前行在其分區(qū)內(nèi)的位置，沒有空格。若排序得到字段值相等，那么
DENSE_RANK()將獲取相同的等級。但結(jié)果是連續(xù)的等級編號。ROW_NUMBER()返回當(dāng)前分區(qū)內(nèi)當(dāng)前行的編號。行數(shù)范圍從1到分區(qū)行數(shù)。但是ROW_NUMBER()為同級分配不同的行號。要為同級分配相同的值，請使用
RANK()或DENSE_RANK()。
2.2 分布函數(shù)
cume_dist()函數(shù)
此函數(shù)應(yīng)用于order by將分區(qū)按所需順序排序。如果沒有order by，則所有行都是對等端，并且值N/N=1，其中N是分區(qū)大小。
該函數(shù)表示：窗口分區(qū)當(dāng)前行之前或?qū)Φ鹊男袛?shù)/窗口分區(qū)的總行數(shù)。返回的范圍是0到1（即某分區(qū)中小于等于某字段的行數(shù)與總行數(shù)的比例）。
應(yīng)用場景：大于等于當(dāng)前訂單金額的訂單比例。
mysql> select rank() over w rank_num,
row_number() over w row_num,
cume_dist() over w cust_dist_rate,
amount
from order_tab
window w as (order by amount);
+----------+---------+----------------+--------+
| rank_num | row_num | cust_dist_rate | amount |
+----------+---------+----------------+--------+
| 1 | 1 | 0.1 | 100 |
| 2 | 2 | 0.3 | 300 |
| 2 | 3 | 0.3 | 300 |
| 4 | 4 | 0.5 | 500 |
| 4 | 5 | 0.5 | 500 |
| 6 | 6 | 0.6 | 600 |
| 7 | 7 | 0.9 | 800 |
| 7 | 8 | 0.9 | 800 |
| 7 | 9 | 0.9 | 800 |
| 10 | 10 | 1 | 900 |
+----------+---------+----------------+--------+
10 rows in set (0.01 sec)
percent_rank()
公式：(rankl-1)/(rows-1)
其中：rank是RANK()函數(shù)產(chǎn)生的序號，rows為當(dāng)前窗口記錄的總行數(shù)。
2.3 頭尾函數(shù)
得到分區(qū)中的第一個/最后一個指定參數(shù)的值。
mysql> select first_value(amount) over(),amount,order_id,user_no from order_tab;
+----------------------------+--------+----------+---------+
| first_value(amount) over() | amount | order_id | user_no |
+----------------------------+--------+----------+---------+
| 100 | 100 | 1 | 001 |
| 100 | 300 | 2 | 001 |
| 100 | 500 | 3 | 001 |
| 100 | 800 | 4 | 001 |
| 100 | 900 | 5 | 001 |
| 100 | 500 | 6 | 002 |
| 100 | 600 | 7 | 002 |
| 100 | 300 | 8 | 002 |
| 100 | 800 | 9 | 002 |
| 100 | 800 | 10 | 002 |
+----------------------------+--------+----------+---------+
10 rows in set (0.01 sec)
因為沒有使用order by函數(shù)，所以所有行都是對等端。返回該分區(qū)中的第一列的值。
mysql> select last_value(amount) over(partition by user_no order by amount),amount,order_id,user_no from order_tab;
+---------------------------------------------------------------+--------+----------+---------+
| last_value(amount) over(partition by user_no order by amount) | amount | order_id | user_no |
+---------------------------------------------------------------+--------+----------+---------+
| 100 | 100 | 1 | 001 |
| 300 | 300 | 2 | 001 |
| 500 | 500 | 3 | 001 |
| 800 | 800 | 4 | 001 |
| 900 | 900 | 5 | 001 |
| 300 | 300 | 8 | 002 |
| 500 | 500 | 6 | 002 |
| 600 | 600 | 7 | 002 |
| 800 | 800 | 9 | 002 |
| 800 | 800 | 10 | 002 |
+---------------------------------------------------------------+--------+----------+---------+
因為使用了order by分組函數(shù)，此時分區(qū)行記錄不對等。
- 第一條記錄是獲取分區(qū)（此時分區(qū)只有一條記錄）最后一條記錄，即100。
- 第二條記錄是獲取分區(qū)（此時分區(qū)中存在2條記錄）最后一條記錄，即300。
- 第二條記錄是獲取分區(qū)（此時分區(qū)中存在3條記錄）最后一條記錄，即500。
依次類推...
2.4 聚合函數(shù)用于窗口函數(shù)
許多聚合函數(shù)都可以使用over()函數(shù)，因此取決于over()子句是否存在，它們可以用于窗口或者非窗口函數(shù)。
AVG()
BIT_AND()
BIT_OR()
BIT_XOR()
COUNT()
JSON_ARRAYAGG()
JSON_OBJECTAGG()
MAX()
MIN()
STDDEV_POP(), STDDEV(), STD()
STDDEV_SAMP()
SUM()
VAR_POP(), VARIANCE()
VAR_SAMP()
例如：因為over()函數(shù)中沒有使用order by函數(shù)，所以各個行均是對等的。返回的是分區(qū)中的最大值。
mysql> select max(amount) over(),user_no,amount from order_tab;
+--------------------+---------+--------+
| max(amount) over() | user_no | amount |
+--------------------+---------+--------+
| 900 | 001 | 100 |
| 900 | 001 | 300 |
| 900 | 001 | 500 |
| 900 | 001 | 800 |
| 900 | 001 | 900 |
| 900 | 002 | 500 |
| 900 | 002 | 600 |
| 900 | 002 | 300 |
| 900 | 002 | 800 |
| 900 | 002 | 800 |
+--------------------+---------+--------+
10 rows in set (0.01 sec)
3. 實戰(zhàn)使用
1. 獲取每種類型消耗金額的top1000數(shù)據(jù)。
row_number() over(partition by type order by amount desc)解釋：
-
row_number()序號函數(shù) -
partition by窗口函數(shù)（多個窗口）；
注意：窗口函數(shù)處理的數(shù)據(jù)源是select字句的數(shù)據(jù)。不受當(dāng)前sql中where和group by語句的影響。
partition by字句會將查詢的結(jié)果集分成幾組。窗口函數(shù)將作用于各個分區(qū)。
mysql> SELECT
row_num,
user_no,
amount
FROM -- 查詢top1000的數(shù)據(jù)必須要含有子查詢
( SELECT row_number ( ) over ( PARTITION BY user_no ORDER BY amount DESC ) AS row_num, user_no, amount FROM order_tab ) a
WHERE
row_num < 1000;
+---------+---------+--------+
| row_num | user_no | amount |
+---------+---------+--------+
| 1 | 001 | 900 |
| 2 | 001 | 800 |
| 3 | 001 | 500 |
| 4 | 001 | 300 |
| 5 | 001 | 100 |
| 1 | 002 | 800 |
| 2 | 002 | 800 |
| 3 | 002 | 600 |
| 4 | 002 | 500 |
| 5 | 002 | 300 |
+---------+---------+--------+
10 rows in set (0.01 sec)
