窗口函數是在sql中實現跨行計算的關鍵工具，它允許基于當前行與其他相關行進行計算，避免了復雜嵌套查詢。語法為function_name() over (partition by column1 order by column2)，其中partition by用于分組，order by用于排序；若無partition by，則全表視為一個分區。其核心應用場景包括：1. 排名，如rank()、dense_rank()、row_number()實現部門內薪資排名；2. 分組計算，如通過avg(salary) over (partition by department)計算部門平均薪資并比較個體差異；3. 累計計算，如sum(sales) over (order by date)實現銷售額逐日累加。與group by不同的是，窗口函數不減少結果集行數，僅新增計算列。性能優化方面需注意索引創建、避免過度使用、正確設置數據類型、合理定義窗口大小及必要時重寫查詢。支持的聚合函數包括avg、sum、min、max、count、排名函數及取值函數如lag、lead等。窗口函數廣泛應用于用戶行為分析、金融數據處理、電商分析和日志統計等領域，顯著提升數據分析效率和靈活性。
窗口函數，說白了，就是在SQL查詢中，給你一種能夠跨行計算的能力。不再局限于當前行，而是可以訪問與當前行相關的其他行。排名、分組、累計計算，這些以前得繞彎子才能搞定的事情，現在一條SQL就能優雅地解決。
解決方案
窗口函數的基本語法是 function_name() OVER (PARTITION BY column1 ORDER BY column2)。PARTITION BY 用于分組，ORDER BY 用于排序。如果沒有 PARTITION BY，則整個結果集被視為一個分區。
實戰案例：排名
假設我們有一張 employees 表，包含 id, name, department, salary 四個字段。我們要按照部門對員工的薪水進行排名。
SELECT id, name, department, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) as salary_rank FROM employees;
這個sql語句會返回一個結果集，其中 salary_rank 列就是每個員工在其部門內的薪水排名。RANK() 函數會跳過重復的排名。如果想不跳過重復排名，可以使用 DENSE_RANK() 函數。ROW_NUMBER() 函數則會為每一行分配一個唯一的序號，即使薪水相同。
實戰案例：分組
窗口函數的分組功能體現在 PARTITION BY 子句上。上面的排名案例實際上已經展示了分組的用法。我們可以利用分組進行更復雜的計算。比如，計算每個部門的平均薪水，并與每個員工的薪水進行比較。
SELECT id, name, department, salary, AVG(salary) OVER (PARTITION BY department) as avg_salary_department, salary - AVG(salary) OVER (PARTITION BY department) as salary_difference FROM employees;
這里，AVG(salary) OVER (PARTITION BY department) 計算的是每個部門的平均薪水，然后我們用每個員工的薪水減去這個平均薪水，得到 salary_difference，可以用來衡量員工薪水在部門內的相對水平。
實戰案例：累計計算
累計計算，也稱為 running total，經常用于統計一段時間內的總銷售額、總用戶數等。
SELECT date, sales, SUM(sales) OVER (ORDER BY date ASC) as cumulative_sales FROM sales_data;
這個SQL語句會計算每天的累計銷售額。SUM(sales) OVER (ORDER BY date ASC) 會從第一天開始，逐天累加銷售額。ORDER BY date ASC 指定了累加的順序。如果想按月累加，可以把 ORDER BY date ASC 改為 ORDER BY YEAR(date), MONTH(date) ASC。
窗口函數與GROUP BY的區別？
GROUP BY 會將結果集分組，并對每個組進行聚合計算，最終每個組只返回一行結果。而窗口函數不會改變結果集的行數，它只是為每一行添加額外的列，這些列的值是基于窗口的計算結果。換句話說，GROUP BY 是分組聚合，而窗口函數是分組計算。
性能優化：如何避免窗口函數導致的性能問題？
窗口函數雖然強大，但如果使用不當，可能會導致性能問題。特別是在處理大數據集時。
- 索引優化： 確保 PARTITION BY 和 ORDER BY 子句中使用的列都有索引。這可以顯著提高查詢速度。
- 避免過度使用： 盡量避免在一個查詢中使用過多的窗口函數。過多的窗口函數會增加計算復雜度，降低查詢效率。
- 數據類型： 確保窗口函數中使用的列的數據類型是正確的。例如，如果對日期進行排序，應該使用 DATE 或 DATETIME 類型。
- 合理使用窗口大小： 某些窗口函數支持指定窗口大小，例如 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。合理設置窗口大小可以提高性能。
- 查詢重寫： 在某些情況下，可以使用子查詢或臨時表來替代窗口函數，以提高性能。
窗口函數支持哪些聚合函數？
窗口函數支持的聚合函數包括：
- AVG()：平均值
- SUM()：總和
- MIN()：最小值
- MAX()：最大值
- COUNT()：計數
- RANK()：排名
- DENSE_RANK()：密集排名
- ROW_NUMBER()：行號
- FIRST_VALUE()：第一個值
- LAST_VALUE()：最后一個值
- LAG()：前一行
- LEAD()：后一行
- NTH_VALUE()：第N個值
- NTILE()：將結果集分成N組
不同的數據庫系統可能支持不同的窗口函數，具體請參考數據庫的官方文檔。例如，postgresql 提供了更多高級的窗口函數，例如 PERCENT_RANK() 和 CUME_DIST()，可以進行百分比排名和累計分布計算。
窗口函數在實際業務場景中的應用？
除了上面提到的排名、分組、累計計算，窗口函數還可以應用于很多其他的業務場景。
- 用戶行為分析： 例如，計算用戶的留存率、轉化率、活躍度等。
- 金融數據分析： 例如，計算股票的移動平均線、波動率等。
- 電商數據分析： 例如，計算商品的銷售排名、用戶購買偏好等。
- 日志分析： 例如，統計一段時間內的錯誤日志數量、請求響應時間等。
總之，窗口函數是一種非常強大的SQL工具，可以幫助我們更方便地進行數據分析和處理。掌握窗口函數，可以顯著提高SQL查詢的效率和靈活性。
