MySQL 8.0 新增SQL語法對視窗函數和CTE的支持

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

嘗試了一下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決定。