MySQL 分布函數、前後函數
- 2020 年 1 月 2 日
- 筆記
本文標識 : MQ0017
本文編輯 : 長安月下賞美人兒
編程工具 : MySQL、DBeaver
閱讀時長 : 6分鐘
1、分布函數
（1）應用場景：快速查看某個記錄所歸屬的組內的比例
（2）分布函數分類及基礎語法
percent_rank()over(partition by 分區欄位 order by 排序欄位 desc/asc); cume_dist()over (partition by 分區欄位 order by 排序欄位 desc/asc);
（3）兩種分布函數的介紹
percent_rank() 函數
- 顯示的結果，每行按照如下公式進行計算: (rank-1)/(rows-1)
- rank 為 RANK() 函數產生的序號，rows 為當前窗口的記錄總行數
- 對於重複值，取重複值的第一行記錄的位置
cume_dist() 函數
- 顯示的結果，每行按照如下公式進行計算：rank/rows
- rank 為 RANK() 函數產生的序號，rows 為當前窗口的記錄總行數
- 對於重複值，取重複值的最後一行記錄的位置
（4）實例比較兩種分布函數
SELECT t3.* ,PERCENT_RANK()OVER(ORDER BY tal_amt)AS p1 ,CUME_DIST()OVER(ORDER BY tal_amt)AS p2 FROM( SELECT t2.goodsID ,t2.dimShopID ,SUM(t2.AMT) AS tal_amt ,RANK()OVER(PARTITION BY t2.dimShopID ORDER BY SUM(t2.AMT) DESC)AS rn2 FROM fct_sales AS t1 JOIN fct_sales_item AS t2 ON t1.salesID=t2.salesID WHERE t1.dimDateID BETWEEN 20170801 AND 20170807 AND t2.dimShopID=34 GROUP BY t2.goodsID ,t2.dimShopID ORDER BY SUM(t2.AMT) DESC) AS t3 WHERE t3.rn2<101;
數據結果：
差異點對比：
- 由 percent_rank() 函數，產生的 p1 數列，計算方式為：（25-1）/（100-1）
- 由 cume_dist() 函數，產生的 p2 數列，計算方式為：26/100
2、前後函數
（1）應用場景
- 常用於計算時間序列數據，計算時間間隔，如會員每次購買商品的時間間隔等
- 當前行與前 n 行或者後 n 行的值
- 若有分區，分區值則所有分區的前後 n 行的值
（2）前後函數分類及基礎語法
lag(欄位，N)over(partition by 分區欄位 order by 排序欄位 desc/asc); lead(欄位,N)over (partition by 分區欄位 order by 排序欄位 desc/asc);
（3）兩種前後函數的介紹
- lag(欄位,N) 函數，指當前行的前 N 行的對應欄位值
- lead(欄位,N) 函數，指當前行的後 N 行的對應欄位值
（4）實例比較前後函數
SELECT t1.dimMemberID ,t1.salesno ,t1.dimDateID ,lag(t1.dimDateID,1)OVER(PARTITION BY t1.dimMemberID ORDER BY t1.dimDateID) AS t1 ,lag(t1.dimDateID,2)OVER(PARTITION BY t1.dimMemberID ORDER BY t1.dimDateID) AS t2 ,lead(t1.dimDateID,1)OVER(PARTITION BY t1.dimMemberID ORDER BY t1.dimDateID) AS t3 FROM fct_sales AS t1 WHERE t1.dimMemberID>0 LIMIT 100;
數據結果：
結果解析：
- 由 lag(t1.dimDateID,1) 產生的數列 t1，觀察紅線連接的兩個數字，顯示 8 號客戶，最新購買商品的時間為 20170607，以此時間點向前查詢，上 1 次購買商品的時間為 20170603
- 由 lag(t1.dimDateID,2)產生的數列 t2，觀察紅線連接的兩個數字，顯示 9 號客戶，最新購買商品的時間為 20170615，以此時間點向前查詢，上 2 次購買商品的時間為 20170602
- 由 lead(t1.dimDateID,1) 產生的數列 t3，觀察紅線連接的兩個數字，顯示 9 號客戶，以 20170613 向後查詢，下 1 次購買商品的時間為 20170615
