MySQL 8.0 新增SQL文法對視窗函數和CTE的支援

MySQL 8.0 新增SQL文法對視窗函數和CTE的支援

　　嘗試了一下MySQL 8.0的部分新特性。

　　如果用過MSSQL或者是Oracle中的視窗函數（Oracle中叫分析函數），然後再使用MySQL 8.0之前的時候，就知道需要在使用視窗函數處理邏輯的痛苦了，雖然純SQL也能實作類別似於視窗函數的功能，但是這種SQL在可讀性和以及使用方式上大打折扣，看起來寫起了都比較難受。

　　在MSSQL和Oracle以及PostgreSQL都已經完整支援視窗函數的情況下，MySQL 8.0中也加入了視窗函數的功能，這一點實實在在方便了sql的編碼，可以說是MySQL8.0的亮點之一。

　　對於視窗函數，比如row_number()，rank()，dense_rank()，NTILE()，PERCENT_RANK()等等，在MSSQL和Oracle以及PostgreSQL，使用的文法和表達的邏輯，基本上完全一致。

　　這一點，幾個資料庫廠商做的還是比較統一的，如果熟悉任何一種關係資料中的視窗函數（分析函數），在MySQL 8.0之後就放心的用吧。

　　通過一個case來體驗一下視窗函數的方便性，熟悉MSSQL或者Oracle或者PostgreSQL的老司機就不用看了。

　　測試case，簡單類比一個訂單表，欄位分別是訂單號，使用者編號，金額，建立時間

drop table  if exists order_info

create table order_info
(
    order_id int primary key,
    user_no varchar(10),
    amount int,
    create_date datetime
);

insert into order_info values (1,'u0001',100,'2018-1-1');
insert into order_info values (2,'u0001',300,'2018-1-2');
insert into order_info values (3,'u0001',300,'2018-1-2');
insert into order_info values (4,'u0001',800,'2018-1-10');
insert into order_info values (5,'u0001',900,'2018-1-20');

insert into order_info values (6,'u0002',500,'2018-1-5');
insert into order_info values (7,'u0002',600,'2018-1-6');
insert into order_info values (8,'u0002',300,'2018-1-10');
insert into order_info values (9,'u0002',800,'2018-1-16');
insert into order_info values (10,'u0002',800,'2018-1-22');

要求sql查詢求每個使用者的最新的一個訂單。

傳統的方式，盡量格式化的好讀一點的情況下，說實話，這句sql咋一看有點莫名其妙，不知所以。

SELECT * FROM
(
    SELECT
　　 IF(@y=a.user_no, @x:=@x+1, @x:=1) X ,
    IF(@y=a.user_no, @y, @y:=a.user_no) Y,
    a.*
    FROM order_info a, (SELECT @x:=0, @y:=NULL) b
    ORDER BY a.user_no, a.create_date desc
) a
WHERE X <= 1;

如下是執行結果，當然執行結果是可以滿足需求的。

　　如果採用新的視窗函數的方法，
　　就是使用row_number()over(partition by user_no order by create_date desc) as row_num 給原始記錄編一個號，
　　然後取第一個編號的資料，自然就是“使用者的最新的一條訂單”，實現邏輯上清晰了很多，代碼也簡潔，可讀了很多。

select * from
(
    select row_number()over(partition by user_no order by create_date desc) as row_num,
    order_id,user_no,amount,create_date
    from order_info
)t where row_num=1;

　　需要注意的是，MySQL中的使用視窗函數的時候，是不允許使用*的，必須顯式指定每一個欄位。

 

 row_number()

　　(分組)排序編號，正如上面的例子， row_number()over(partition by user_no order by create_date desc) as row_num，按照使用者分組，按照create_date排序，對已有資料產生一個編號。
　　當然也可以不分組，對整體進行排序。任何一個視窗函數，都可以分組統計或者不分組統計（也即可以不要partition by ***都可以，看你的需求了）

　　

rank()

　　類似於 row_number()，也是排序功能，但是rank()有什麼不一樣？新的事物的出現必然是為瞭解決潛在的問題。
　　如果再往測試表中寫入一條資料：insert into order_info values (11,'u0002',800,'2018-1-22');
　　對於測試表中的U002使用者來說，有兩條create_date完全一樣的資料（假設有這樣的資料），那麼在row_number()編號的時候，這兩條資料卻被編了兩個不同的號
　　理論上講，這兩條的資料的排名是並列最新的。因此rank()就是為瞭解決這個問題的，也即：排序條件一樣的情況下，其編號也一樣。

　　

dense_rank()

　　dense_rank()的出現是為瞭解決rank()編號存在的問題的，
　　rank()編號的時候存在跳號的問題，如果有兩個並列第1，那麼下一個名次的編號就是3，結果就是沒有編號為2的資料。
　　如果不想跳號，可以使用dense_rank()替代。

　　

avg，sum等彙總函式在視窗函數中的的增強

　　可以在彙總函式中使用視窗功能，比如sum(amount)over(partition by user_no order by create_date) as sum_amont，達到一個累積計算sum的功能
　　這種需求在沒有視窗函數的情況下，用純sql寫起來，也夠蛋疼的了，就不舉例了。

　　

NTILE(N) 將資料按照某些排序分成N組

　　舉個簡單的例子，按照分數線的倒序排列，將學產生績分成上中下3組，可以得到哪個程式資料上中下三個組中哪一部分，就可以使用NTILE(3) 來實現。這種需求倒是用的不是非常多。
　　如下還是使用上面的表，按照時間將user_no = 'u0002'的訂單按照時間的緯度，劃分為3組，看每一行資料資料哪一組。

　　

first_value(column_name) and last_value(column_name)

　　first_value和last_value基本上見名知意了，就是取某一組資料，按照某種方式排序的，最早的和最新的某一個欄位的值。
　　看結果體會一下。

nth_value(column_name,n)

　　從排序的第n行還是返回nth_value欄位中的值，這個函數用的不多，要表達的這種邏輯，說實話，很難用語言表達出來，看個例子體會一下就行。

　　n = 3

　　n = 4

cume_dist

　　在某種排序條件下，小於等於當前行值的行數/總行數，得到的是資料在某一個緯度的分布百分比情況。
　　比如如下樣本
　　第1行資料的日期（create_date）是2018-01-05 00:00:00，小於等於2018-01-05 00:00:00的資料是1行，計算方式是：1/6 = 0.166666666
　　第2行資料的日期（create_date）是2018-01-06 00:00:00，小於等於2018-01-06 00:00:00的資料是2行，計算方式是：2/6 = 0.333333333
　　依次類推
　　第4行資料的日期（create_date）是2018-01-16 00:00:00，小於等於2018-01-16 00:00:00的資料是4行，計算方式是：4/6 = 0.6666666666
　　第一行資料的0.6666666666 意味著，小於第四行日期（create_date）的資料佔了符合條件資料的66.66666666666%　　

percent_rank()

　　同樣是資料分布的計算方式，只不過演算法變成了：當前RANK值-1/總行數-1 。
　　具體演算法不細說，這個實際中用的也不多。

lag以及lead

　　lag(column,n)擷取當前資料行按照某種定序的上n行資料的某個欄位，lead(column,n)擷取當前資料行按照某種定序的下n行資料的某個欄位，
　　確實很拗口。
　　舉個實際例子，按照時間排序，擷取當前訂單的上一筆訂單發生時間和下一筆訂單發生時間，（可以計算訂單的時間上的間隔度或者說買買買的頻繁程度）

select order_id,
        user_no,
        amount,
        create_date,
      lag(create_date,1) over (partition by user_no order by create_date asc) 'last_transaction_time',
      lead(create_date,1) over (partition by user_no order by create_date asc) 'next_transaction_time'from order_info ;

　　

CTE 通用資料表運算式

　　CTE有兩種用法，非遞迴的CTE和遞迴的CTE。
　　非遞迴的CTE可以用來增加代碼的可讀性，增加邏輯的結構化表達。
　　平時我們比較痛恨一句sql幾十行甚至上上百行，根本不知道其要表達什麼，難以理解，對於這種SQL，可以使用CTE分段解決，
　　比如邏輯塊A做成一個CTE，邏輯塊B做成一個CTE，然後在邏輯塊A和邏輯塊B的基礎上繼續進行查詢，這樣與直接一句代碼實現整個查詢，邏輯上就變得相對清晰直觀。
　　舉個簡單的例子，當然這裡也不足以說明問題，比如還是第一個需求，查詢每個使用者的最新一條訂單
　　第一步是對使用者的訂單按照時間排序編號，做成一個CTE，第二步對上面的CTE查詢，取行號等於1的資料。

　　另外一種是遞迴的CTE，遞迴的話，應用的情境也比較多，比如查詢大部門下的子部門，每一個子部門下面的子部門等等，就需要使用遞迴的方式。
　　這裡不做細節示範，僅示範一種遞迴的用法，用遞迴的方式產生連續日期。

　　

　　當然遞迴不會無限下去，不同的資料庫有不同的遞迴限制，MySQL 8.0中預設限制的最大遞迴次數是1000。
　　超過最大低估次數會報錯：Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.
　　由參數@@cte_max_recursion_depth決定。

　　

　　關於CTE的限制，跟其他資料庫並無太大差異，比如CTE內部的查詢結果都要有欄位名稱，不允許連續對一個CTE多次查詢等等，相信熟悉CTE的老司機都很清楚。

視窗函數和CTE的增加，簡化了SQL代碼的編寫和邏輯的實現，並不是說沒有這些新的特性，這些功能都無法實現，只是新特性的增加，可以用更優雅和可讀性的方式來寫SQL。
不過這都是在MySQL 8.0中實現的新功能，在8.0之前，還是老老實實按照較為複雜的方式實現吧。

本文永久更新連結地址：https://www.bkjia.com/Linux/2018-03/151389.htm