sql窗口函數是在不減少結果集行數的前提下，基于當前行相關行集合進行計算的函數。1. 它通過over()子句定義窗口范圍，支持partition by分區和order by排序；2. 常用類型包括聚合函數（如sum、avg）、排序函數（如rank、row_number）和偏移函數（如lag、lead）；3. 可用于計算移動平均、累計總和、top n排名等復雜分析；4. 優化技巧包括索引創建、合理定義窗口范圍、物化中間結果及查詢重寫；5. 與group by不同在于其保留原始行數且支持細粒度行間計算。
SQL窗口函數，簡單來說，就是能在查詢結果的“窗口”內進行計算的函數。它不像聚合函數那樣會改變結果集的行數，而是為每一行返回一個值，這個值是基于與當前行相關的行集合計算出來的。
SQL窗口函數，能讓你在不改變結果集行數的前提下，進行復雜的統計分析。掌握它，能極大地提升你的SQL功力。
窗口函數的基本語法：函數名(參數) OVER (PARTITION BY 列名 ORDER BY 列名 ASC|DESC)
解決方案
-
理解基本概念：窗口和分區
- 窗口：窗口函數計算的行集合。你可以把它想象成一個在結果集上滑動的“窗口”，窗口函數就在這個窗口內進行計算。
- 分區：PARTITION BY子句將結果集分成多個分區。窗口函數會分別在每個分區內進行計算。如果沒有PARTITION BY子句，則整個結果集被視為一個分區。
-
常用窗口函數
-
聚合函數作為窗口函數：SUM(), AVG(), MIN(), MAX(), count()等。
-
示例：計算每個部門的工資總額和每個員工的工資占部門工資總額的比例。
SELECT department, employee_name, salary, SUM(salary) OVER (PARTITION BY department) AS department_total_salary, salary * 1.0 / SUM(salary) OVER (PARTITION BY department) AS salary_ratio FROM employees;
-
-
排序函數：RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE()。
-
RANK()：為每個分區中的每行分配一個排名，排名可能是不連續的。
-
DENSE_RANK()：與RANK()類似，但排名是連續的。
-
ROW_NUMBER()：為每個分區中的每行分配一個唯一的序號。
-
NTILE(n)：將每個分區中的行分成n組，并為每行分配一個組號。
-
示例：按銷售額對客戶進行排名。
SELECT customer_name, sales_amount, RANK() OVER (ORDER BY sales_amount DESC) AS sales_rank FROM sales;
-
-
偏移函數：LAG(), LEAD()。
-
LAG(column, offset, default)：訪問結果集中當前行之前offset行的column的值。如果offset超出范圍，則返回default。
-
LEAD(column, offset, default)：訪問結果集中當前行之后offset行的column的值。如果offset超出范圍，則返回default。
-
示例：計算每個月的銷售額與上個月的銷售額的差額。
SELECT month, sales_amount, LAG(sales_amount, 1, 0) OVER (ORDER BY month) AS previous_month_sales, sales_amount - LAG(sales_amount, 1, 0) OVER (ORDER BY month) AS sales_difference FROM monthly_sales;
-
-
-
OVER()子句詳解
- PARTITION BY：定義分區。
- ORDER BY：定義排序方式。
- ROWS或RANGE：定義窗口的范圍。這部分比較高級，用于更精細地控制窗口的大小。例如，ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW表示窗口包括當前行和之前的所有行。
-
實際應用場景
- 計算移動平均值。
- 計算累計總和。
- 查找每個類別中的前N個記錄。
- 計算同比、環比增長率。
窗口函數性能優化技巧有哪些？
窗口函數本身在某些情況下可能會影響查詢性能，尤其是在處理大數據集時。
- 索引優化： 確保PARTITION BY和ORDER BY子句中使用的列都有合適的索引。索引可以顯著提高排序和分區操作的效率。
- 避免不必要的分區： 仔細評估是否真的需要分區。如果整個結果集都可以作為一個窗口處理，則省略PARTITION BY子句可以減少計算開銷。
- 合理選擇窗口范圍： 如果使用了ROWS或RANGE子句，確保窗口范圍定義合理。過大的窗口范圍會增加計算量。
- 物化中間結果： 對于復雜的窗口函數查詢，可以考慮將中間結果物化到臨時表中，然后再進行后續計算。這可以避免重復計算。
- 數據庫版本和配置： 不同數據庫版本對窗口函數的支持和優化程度可能不同。確保使用最新版本的數據庫，并根據實際情況調整數據庫配置。
- 查詢重寫： 有時候，可以通過重寫查詢來避免使用窗口函數，或者使用更高效的窗口函數替代方案。例如，某些情況下可以使用自連接來替代窗口函數。
- 并行處理： 如果數據庫支持并行處理，可以嘗試啟用并行執行計劃，以提高窗口函數的計算速度。
- 監控和分析： 使用數據庫提供的監控工具來分析查詢執行計劃，找出性能瓶頸，并進行針對性優化。
窗口函數和GROUP BY的區別是什么？
- 結果集行數：GROUP BY會將結果集分組，并對每個組返回一行結果。窗口函數則不會改變結果集的行數，而是為每一行返回一個值。
- 計算范圍：GROUP BY對整個組進行聚合計算。窗口函數可以在與當前行相關的行集合（窗口）內進行計算。
- 應用場景：GROUP BY主要用于分組統計。窗口函數主要用于在不改變結果集行數的前提下進行復雜的統計分析，例如排名、移動平均值等。
如何用窗口函數實現Top N問題？
Top N問題是窗口函數的一個經典應用場景。
- 使用RANK()或DENSE_RANK()函數：首先，使用RANK()或DENSE_RANK()函數為每個分區中的行分配排名。
- 過濾排名結果：然后，使用WHERE子句過濾排名結果，只保留排名在前N名的行。
示例：查詢每個部門工資最高的前3名員工。
WITH RankedEmployees AS ( SELECT department, employee_name, salary, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank FROM employees ) SELECT department, employee_name, salary FROM RankedEmployees WHERE salary_rank <= 3;
這個例子首先使用WITH子句創建一個名為RankedEmployees的公共表表達式（CTE）。在CTE中，使用DENSE_RANK()函數為每個部門的員工分配排名，排名依據是工資從高到低。然后，在主查詢中，從RankedEmployees CTE中選擇排名在前3名的員工。
