MariaDB · 新特性 · 窗口函數 · 數據庫內核月報

## 簡介窗口函數（Window Function）是 SQL:2013 標準中提出的，在后續標準版本的更新中也多次擴展，最新的版本是 SQL:2011 中的標準。從某些方面來說，窗口函數與聚合函數（Aggregate Function）有些類似，他們都是對一系列的行進行聚合計算，然而不同于聚合函數的是，窗口函數的輸出并不是被聚集到單獨的一行。像 SUM, AVG, COUNT 這些聚合函數最終輸出都是一行記錄。窗口函數可以通過計算每行周圍窗口上的集合值來分析數據，例如：計算一定記錄范圍內、一定值域內、或者一段時間內的累計和以及移動平均值等等。之所以使用窗口這個術語，是因為對結果的處理使用了一個滑動的查詢結果集范圍。大部分數據庫，尤其是商業數據庫都支持SQL標準中定義的部分窗口函數，但是MySQL一直沒有支持這個特性。窗口函數在MySQL社區一直呼聲很高，卻一直沒有被實現，直到 MairaDB 10.2 響應了客戶的需求，實現了部分窗口函數，并且在持續完善中，我們就來簡單介紹一下 MairaDB 的窗口函數。 ## 語法 MariaDB的窗口函數查詢指定使用 OVER 關鍵字，默認情況下，用于計算的行集合（Window，窗口）是整個數據集，并且可以用 ORDER BY 子句排序。PARTITION BY 子句可以用于將窗口縮小到特定的集合內。使用語法： ~~~ function (expression) OVER ( [ PARTITION BY expression_list ] [ ORDER BY order_list [ frame_clause ] ] ) function: A valid window function expression_list: expression | column_name [, expr_list ] order_list: expression | column_name [ ASC | DESC ] [, ... ] frame_clause: ~~~ 例如，給出下面的原始數據： ~~~ CREATE TABLE student (name CHAR(10), test CHAR(10), score TINYINT); INSERT INTO student VALUES ('Chun', 'SQL', 75), ('Chun', 'Tuning', 73), ('Esben', 'SQL', 43), ('Esben', 'Tuning', 31), ('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88), ('Tatiana', 'SQL', 87), ('Tatiana', 'Tuning', 83); ~~~ 下面兩個查詢可以分別返回按 test 和 name 分區處理的平均數： ~~~ SELECT name, test, score, AVG(score) OVER (PARTITION BY test) AS average_by_test FROM student; +---------+--------+-------+-----------------+ | name | test | score | average_by_test | +---------+--------+-------+-----------------+ | Chun | SQL | 75 | 65.2500 | | Chun | Tuning | 73 | 68.7500 | | Esben | SQL | 43 | 65.2500 | | Esben | Tuning | 31 | 68.7500 | | Kaolin | SQL | 56 | 65.2500 | | Kaolin | Tuning | 88 | 68.7500 | | Tatiana | SQL | 87 | 65.2500 | | Tatiana | Tuning | 83 | 68.7500 | +---------+--------+-------+-----------------+ SELECT name, test, score, AVG(score) OVER (PARTITION BY name) AS average_by_name FROM student; +---------+--------+-------+-----------------+ | name | test | score | average_by_name | +---------+--------+-------+-----------------+ | Chun | SQL | 75 | 74.0000 | | Chun | Tuning | 73 | 74.0000 | | Esben | SQL | 43 | 37.0000 | | Esben | Tuning | 31 | 37.0000 | | Kaolin | SQL | 56 | 72.0000 | | Kaolin | Tuning | 88 | 72.0000 | | Tatiana | SQL | 87 | 85.0000 | | Tatiana | Tuning | 83 | 85.0000 | +---------+--------+-------+-----------------+ ~~~ ## 用例 ### RANK 描述： RANK() 可以顯示給定行的序號，從1開始，順序以 ORDER BY 字段排序后的序列為準。語法： ~~~ RANK() OVER ( [ PARTITION BY partition_expression ] [ ORDER BY order_list ] ) ~~~ 例子 ~~~ CREATE TABLE student(course VARCHAR(10), mark int, name varchar(10)); INSERT INTO student VALUES ('Maths', 60, 'Thulile'), ('Maths', 60, 'Pritha'), ('Maths', 70, 'Voitto'), ('Biology', 60, 'Bilal'), ('Biology', 70, 'Roger'); SELECT RANK() OVER (PARTITION BY course ORDER BY mark) AS rank, DENSE_RANK() OVER (PARTITION BY course ORDER BY mark) AS dense_rank, ROW_NUMBER() OVER (PARTITION BY course ORDER BY mark) AS row_num, course, mark, name from student; +------+------------+---------+---------+------+---------+ | rank | dense_rank | row_num | course | mark | name | +------+------------+---------+---------+------+---------+ | 1 | 1 | 1 | Maths | 60 | Thulile | | 1 | 1 | 2 | Maths | 60 | Pritha | | 3 | 2 | 3 | Maths | 70 | Voitto | | 1 | 1 | 1 | Biology | 60 | Bilal | | 2 | 2 | 2 | Biology | 70 | Roger | +------+------------+---------+---------+------+---------+ ~~~ ### CUME_DIST 描述： CUME_DIST() 可以返回一行數據的累積分布（cumulative distribution）。計算公式如下 `(number of rows <= current row) / (total rows)` 語法： ~~~ CUME_DIST() OVER ( [ PARTITION BY partition_expression ] [ ORDER BY order_list ] ) ~~~ 例子： ~~~ create table t1 ( pk int primary key, a int, b int ); insert into t1 values ( 1 , 0, 10), ( 2 , 0, 10), ( 3 , 1, 10), ( 4 , 1, 10), ( 8 , 2, 10), ( 5 , 2, 20), ( 6 , 2, 20), ( 7 , 2, 20), ( 9 , 4, 20), (10 , 4, 20); select pk, a, b, rank() over (order by a), percent_rank() over (order by a), cume_dist() over (order by a) from t1; +----+------+------+--------------------------+----------------------------------+-------------------------------+ | pk | a | b | rank() over (order by a) | percent_rank() over (order by a) | cume_dist() over (order by a) | +----+------+------+--------------------------+----------------------------------+-------------------------------+ | 1 | 0 | 10 | 1 | 0.0000000000 | 0.2000000000 | | 2 | 0 | 10 | 1 | 0.0000000000 | 0.2000000000 | | 3 | 1 | 10 | 3 | 0.2222222222 | 0.4000000000 | | 4 | 1 | 10 | 3 | 0.2222222222 | 0.4000000000 | | 5 | 2 | 20 | 5 | 0.4444444444 | 0.8000000000 | | 6 | 2 | 20 | 5 | 0.4444444444 | 0.8000000000 | | 7 | 2 | 20 | 5 | 0.4444444444 | 0.8000000000 | | 8 | 2 | 10 | 5 | 0.4444444444 | 0.8000000000 | | 9 | 4 | 20 | 9 | 0.8888888889 | 1.0000000000 | | 10 | 4 | 20 | 9 | 0.8888888889 | 1.0000000000 | +----+------+------+--------------------------+----------------------------------+-------------------------------+ select pk, a, b, percent_rank() over (order by pk), cume_dist() over (order by pk) from t1 order by pk; +----+------+------+-----------------------------------+--------------------------------+ | pk | a | b | percent_rank() over (order by pk) | cume_dist() over (order by pk) | +----+------+------+-----------------------------------+--------------------------------+ | 1 | 0 | 10 | 0.0000000000 | 0.1000000000 | | 2 | 0 | 10 | 0.1111111111 | 0.2000000000 | | 3 | 1 | 10 | 0.2222222222 | 0.3000000000 | | 4 | 1 | 10 | 0.3333333333 | 0.4000000000 | | 5 | 2 | 20 | 0.4444444444 | 0.5000000000 | | 6 | 2 | 20 | 0.5555555556 | 0.6000000000 | | 7 | 2 | 20 | 0.6666666667 | 0.7000000000 | | 8 | 2 | 10 | 0.7777777778 | 0.8000000000 | | 9 | 4 | 20 | 0.8888888889 | 0.9000000000 | | 10 | 4 | 20 | 1.0000000000 | 1.0000000000 | +----+------+------+-----------------------------------+--------------------------------+ select pk, a, b, percent_rank() over (partition by a order by a), cume_dist() over (partition by a order by a) from t1; +----+------+------+-------------------------------------------------+----------------------------------------------+ | pk | a | b | percent_rank() over (partition by a order by a) | cume_dist() over (partition by a order by a) | +----+------+------+-------------------------------------------------+----------------------------------------------+ | 1 | 0 | 10 | 0.0000000000 | 1.0000000000 | | 2 | 0 | 10 | 0.0000000000 | 1.0000000000 | | 3 | 1 | 10 | 0.0000000000 | 1.0000000000 | | 4 | 1 | 10 | 0.0000000000 | 1.0000000000 | | 5 | 2 | 20 | 0.0000000000 | 1.0000000000 | | 6 | 2 | 20 | 0.0000000000 | 1.0000000000 | | 7 | 2 | 20 | 0.0000000000 | 1.0000000000 | | 8 | 2 | 10 | 0.0000000000 | 1.0000000000 | | 9 | 4 | 20 | 0.0000000000 | 1.0000000000 | | 10 | 4 | 20 | 0.0000000000 | 1.0000000000 | +----+------+------+-------------------------------------------------+----------------------------------------------+ ~~~ ## 總結 * 支持 ROWS 和 RANGE類型的Frame * 各種類型的Frame界定都支持，包括 RANGE PRECEDING | FOLLOWING n個Frame范圍（不同于PostgreSQL 和 SQL Server） * RANGE類型的Frame 還不支持 DATE[TIME] 數據類型和四則運算，但MDEV-9727正在進行開發 * 還不支持 GROUPS類型的Frame（但好像還沒有流行的數據庫有支持這玩意的） * 不支持禁用Frame（好像也沒其他數據庫支持） * 不支持顯式的 NULLS FIRST 和 NULLS LAST * 不支持窗口函數嵌套處理（就是VALUE_OF(expr AT row_marker [, default_value) 這種語法） * 下面這些窗口函數都支持： * “Streamable（流式）” 窗口函數：ROW_NUMBER, RANK, DENSE_RANK * 一旦分區中的行數知道后，就可以流式處理的窗口函數：PERCENT_RANK, CUME_DIST, NTILE * 目前支持窗口函數的聚合函數有： COUNT, SUM, AVG, BIT_OR, BIT_AND, BIT_XOR * 包含 DISTINCT 子句的聚合函數不支持作為窗口函數大家可以在[這里](https://mariadb.com/kb/en/mariadb/window-functions/)查看具體支持哪些函數以及未來新增了哪些函數