深入理解MySQL 8的視窗函式及其應用場景

引言：

MySQL 8的引入為資料庫查詢帶來了一項強大的功能：視窗函式（Window Functions）。這些函式擴充套件了SQL的能力，允許開發者和資料分析師在保持資料行詳情的同時執行高階統計和分析操作。

視窗函式概述：

視窗函式是一類特殊的SQL函式，它們在執行聚合計算時不會像GROUP BY子句那樣摺疊行，而是允許在定義的資料視窗上執行計算。這個“視窗”指的是一組排序後的行集合，通常是基於某些列的值進行分割槽。視窗函式可以訪問視窗中的其他行而不僅僅是當前行，從而實現對資料的複雜分析。

使用示例

MySQL 8 引入了對視窗函式（Window Functions）的支援，這是一種強大的功能，用於執行資料的分割槽計算，而不需要將資料分組為單獨的行。視窗函式可以對資料集的子集（稱為視窗）執行聚合操作， 同時保持行的獨立性。以下是一些常用的視窗函式及其使用示例：

1. ROW_NUMBER()： 為每個分割槽內的行提供一個唯一的序號。

   SELECT 
       name,
       department,
       salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
   FROM employees;

   
   

這個查詢會為每個部門內的員工根據薪水降序排列提供一個唯一的序號。

2. RANK()： 在分割槽內對行進行排名，相同值的行會有相同的排名，排名之間會有間隔。

   SELECT 
       name,
       department,
       salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
   FROM employees;

   
   

3. DENSE_RANK()： 類似於RANK()，但排名之間不會有間隔。

   SELECT 
       name,
       department,
       salary,
       DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
   FROM employees;

   
   

4. SUM()： 計算分割槽內值的總和。

   SELECT 
       name,
       department,
       salary,
       SUM(salary) OVER (PARTITION BY department) AS department_total_salary
   FROM employees;

   
   

5. AVG()： 計算分割槽內值的平均值。

   SELECT 
       name,
       department,
       salary,
       AVG(salary) OVER (PARTITION BY department) AS department_avg_salary
   FROM employees;

   
   

6. LEAD() 和 LAG()： 獲取分割槽內當前行的下一行或上一行的值。

   SELECT 
       name,
       department,
       salary,
       LEAD(salary) OVER (PARTITION BY department ORDER BY salary) AS next_highest_salary,
       LAG(salary) OVER (PARTITION BY department ORDER BY salary) AS previous_salary
   FROM employees;

   
   

7. FIRST_VALUE() 和 LAST_VALUE()： 獲取分割槽內的第一個值和最後一個值。

   SELECT 
       name,
       department,
       salary,
       FIRST_VALUE(name) OVER (PARTITION BY department ORDER BY salary DESC) AS highest_earner,
       LAST_VALUE(name) OVER (PARTITION BY department ORDER BY salary DESC 
          RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lowest_earner
   FROM employees;

   
   

8. NTILE()： 將分割槽內的行分成指定數量的近似相等的排名組。

   SELECT 
       name,
       department,
       salary,
       NTILE(4) OVER (PARTITION BY department ORDER BY salary DESC) AS quartile
   FROM employees;

   
   

在使用LAST_VALUE()函式時，通常需要指定視窗的範圍，如示例中的RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING，以確保能夠獲取到整個分割槽內的最後一個值。

9. CUME_DIST()： 計算當前行在其分割槽中的累積分佈位置，其值為當前行之前的所有行數加上當前行（包含重複行）除以分割槽中的總行數。

   SELECT 
       name,
       department,
       salary,
       CUME_DIST() OVER (PARTITION BY department ORDER BY salary DESC) AS cume_dist
   FROM employees;

   
   

10. PERCENT_RANK()： 計算當前行的百分比排名，類似於RANK()，但表示為介於0和1之間的比例。

   SELECT 
       name,
       department,
       salary,
       PERCENT_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS percent_rank
   FROM employees;

這些函式可以在各種資料分析任務中非常有用，例如計算累計總和、移動平均值或者進行排名。視窗函式的一個關鍵特徵是它們允許在不改變原始資料集行數的情況下進行聚合計算。

業務場景應用：

視窗函式在多種業務場景中都有實際的應用價值：

- 業績評估： 透過RANK()或DENSE_RANK()，可以快速識別出業績最好或最差的員工。
- 財務分析： SUM()和AVG()視窗函式可以用來計算部門的總薪水或平均薪水，而不會丟失個別員工的薪水資訊。
- 資料探勘： LEAD()和LAG()函式可以用來比較序列資料，例如股票價格的前後變化。
- 報告和分段： NTILE()可以用於分段使用者或產品，以便進行更細緻的市場分析。

結論：

MySQL 8的視窗函式為資料庫查詢提供了前所未有的靈活性和強大的分析能力。無論是資料分析師還是開發者，都可以利用這些工具來提升數據處理的效率和深度。掌握視窗函式的使用將是任何希望在資料驅動決策領域取得成功的專業人士的重要技能。