窗口函數是在sql中用于在查詢結果中執行計算的函數，其基本語法為function_name() over (partition by column1, column2 order by column3)，其中function_name如rank(), row_number(), sum(), avg()等，over()定義窗口范圍，partition by用于分區，order by用于排序。1. 窗口函數與group by的區別在于group by聚合數據減少行數，而窗口函數保留原始行并添加計算值；2. 排名函數包括rank()（跳過后續排名），dense_rank()（不跳過排名），row_number()（唯一編號）和ntile(n)（分組編號）；3. 窗口函數處理NULL值時通常忽略它們，但排序中的null值位置可通過nulls first/last控制；4. 性能優化技巧包括索引優化、避免不必要的排序、限制窗口大小、物化中間結果及使用數據庫特定優化；5. 實際業務應用涵蓋移動平均、累計總和、top n查找以及百分比排名等場景。
窗口函數，簡單來說，就是在SQL查詢中，讓你能針對特定的“窗口”進行計算，而這個“窗口”通常是與當前行相關的行集合。它能讓你在同一行中看到聚合結果和原始數據，這在很多報表和數據分析場景下非常有用。
窗口函數的基本語法是：function_name() OVER (PARTITION BY column1, column2 ORDER BY column3)
- function_name()：你要使用的窗口函數，例如 RANK(), ROW_NUMBER(), SUM(), AVG() 等。
- OVER()： 關鍵詞，表示這是一個窗口函數。
- PARTITION BY： 定義窗口的分區，類似于 GROUP BY，但不會將結果聚合為一行。
- ORDER BY： 定義窗口內數據的排序方式。
窗口函數與GROUP BY的區別是什么？
很多人容易把窗口函數和 GROUP BY 混淆。GROUP BY 會將數據聚合為更少的行，而窗口函數不會減少行數，它只是為每一行計算一個值。想象一下，你想知道每個部門的平均工資，同時還想看到每個員工的工資。GROUP BY 只能告訴你每個部門的平均工資，而窗口函數可以同時顯示每個員工的工資和他們所在部門的平均工資。
舉個例子，假設我們有一個 employees 表，包含 employee_id, department_id, 和 salary 字段。如果我們想知道每個員工的工資和他們所在部門的平均工資，可以使用如下SQL：
SELECT employee_id, department_id, salary, AVG(salary) OVER (PARTITION BY department_id) AS department_avg_salary FROM employees;
這個查詢會返回每個員工的ID、部門ID、工資，以及他們所在部門的平均工資。
如何使用窗口函數進行排名？
窗口函數在排名方面非常強大。SQL提供了幾個用于排名的窗口函數：
- RANK()： 為每個分區中的行分配一個排名，如果存在并列，則會跳過后續排名。
- DENSE_RANK()： 類似于 RANK()，但不會跳過排名。如果存在并列，則后續排名會緊隨其后。
- ROW_NUMBER()： 為每個分區中的行分配一個唯一的序列號，從1開始。
- NTILE(n)： 將每個分區中的行分成 n 組，并為每行分配一個組號。
例如，如果我們想對 employees 表中的員工按工資進行排名，可以使用如下SQL：
SELECT employee_id, salary, RANK() OVER (ORDER BY salary DESC) AS salary_rank, DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_dense_rank, ROW_NUMBER() OVER (ORDER BY salary DESC) AS salary_row_number FROM employees;
這個查詢會返回每個員工的ID、工資，以及他們的工資排名（使用 RANK(), DENSE_RANK(), 和 ROW_NUMBER() 三種方式）。
窗口函數如何處理NULL值？
窗口函數在處理 NULL 值時，通常遵循SQL的標準規則。例如，在計算 SUM() 或 AVG() 時，NULL 值會被忽略。在排序時，NULL 值的處理方式取決于具體的數據庫系統。有些數據庫會將 NULL 值排在最前面，有些則會排在最后面。你可以使用 NULLS FIRST 或 NULLS LAST 來顯式指定 NULL 值的排序方式，但這并非所有數據庫都支持。
例如，如果我們想對 employees 表中的員工按工資進行排名，并將 NULL 值排在最后面，可以使用如下SQL（假設你的數據庫支持 NULLS LAST）：
SELECT employee_id, salary, RANK() OVER (ORDER BY salary DESC NULLS LAST) AS salary_rank FROM employees;
窗口函數的性能優化有哪些技巧？
窗口函數在處理大數據集時可能會比較慢。以下是一些性能優化技巧：
- 索引優化： 確保 PARTITION BY 和 ORDER BY 中使用的列都有索引。
- 避免不必要的排序： 如果你的窗口函數不需要排序，就不要使用 ORDER BY 子句。
- 限制窗口大小： 使用 ROWS BETWEEN 子句來限制窗口的大小。例如，ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING 表示只考慮當前行之前和之后的各一行。
- 物化中間結果： 如果你需要在多個窗口函數中使用相同的結果，可以考慮將中間結果物化為一個臨時表。
- 使用數據庫特定的優化： 不同的數據庫系統可能有針對窗口函數的特定優化。查閱你使用的數據庫的文檔，了解如何優化窗口函數的性能。
如何在實際業務場景中使用窗口函數？
窗口函數在很多實際業務場景中都有應用，例如：
- 計算移動平均： 可以使用 AVG() 窗口函數來計算一段時間內的移動平均值。例如，計算過去7天的平均銷售額。
- 計算累計總和： 可以使用 SUM() 窗口函數來計算累計總和。例如，計算每個月的累計銷售額。
- 查找Top N： 可以使用排名窗口函數來查找每個類別中的Top N。例如，查找每個部門中工資最高的3名員工。
- 計算百分比排名： 可以使用 PERCENT_RANK() 或 CUME_DIST() 窗口函數來計算百分比排名。例如，計算每個學生的成績在班級中的百分比排名。
總而言之，窗口函數是SQL中一個非常強大的工具，可以讓你更方便地進行數據分析和報表生成。理解窗口函數的基本語法和常用函數，并結合實際業務場景進行應用，可以大大提高你的SQL技能。
