窗口函數通過單次掃描完成分析計算，能大幅簡化子查詢結構并提升性能，尤其在排名、累計計算等場景?15。以下是核心優化技巧：

一、排名場景替代方案

1. ?部門工資排名?
   傳統子查詢需自連接和聚合計數：

   sql

   SELECT e1.name, e1.salary, (SELECT COUNT(*)+1 FROM employees e2 WHERE e2.dept=e1.dept AND e2.salary>e1.salary) AS rank FROM employees e1;

   窗口函數單次掃描完成：

   sql

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

2. ?分組TOP N查詢?
   使用ROW_NUMBER()直接過濾組內前N條：

   sql

   WITH ranked AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY sales DESC) AS rn FROM sales ) SELECT * FROM ranked WHERE rn <= 3;

   避免關聯子查詢的多次索引掃描?。

二、累計計算優化

• 子查詢瓶頸?：逐行執行導致O(n2)復雜度

  sql

  SELECT t1.date, (SELECT SUM(t2.amount) FROM sales t2 WHERE t2.date<=t1.date) AS cumulative FROM sales t1;

• 窗口方案?：線性時間復雜度

  sql

  SELECT date, SUM(amount) OVER (ORDER BY date) AS cumulative FROM sales;

  百萬級數據性能提升顯著?。

三、跨行引用優化

使用LAG()避免自連接，例如環比增長率計算：

sql

SELECT month, revenue, (revenue - LAG(revenue,1) OVER (ORDER BY month)) / LAG(revenue,1) OVER (ORDER BY month) AS growth FROM financials;

比關聯子查詢減少50%以上I/O消耗?。

四、關鍵優化原則

1. ?索引匹配?
   PARTITION BY和ORDER BY字段需建復合索引，否則全表掃描?.
2. ?框架選擇?
   大分區數據用RANGE替代ROWS防內存溢出：

   sql

   SUM(amount) OVER (ORDER BY date RANGE UNBOUNDED PRECEDING)

3. ?避免反模式?
   窗口函數中嵌套子查詢會抵消性能優勢.