OVER() 語法和它的關鍵特色

OVER() 就是告訴 SQL 要對哪一組資料用 window function。 你可以把它想像成一種方式，來決定「window」的範圍。想像一下我們有一間房間，裡面站滿人，我們想算每一平方公尺站了幾個人。OVER() 就是指定我們要專注在哪一塊區域。換句話說，它就是定義 function 要處理哪一組資料。

OVER() 這個 operator 只能跟 window functions 一起用，讓你可以對一個或多個 table 的資料列做運算，而且不用 group by。

語法：

window_function() OVER (
    [PARTITION BY ...]
    [ORDER BY ...]
    [ROWS/RANGE ...]
)

說明：

• PARTITION BY — 把資料分成邏輯上的 group
• ORDER BY — 決定每個 group 裡面資料列的順序
• ROWS/RANGE — 更細緻地定義「window」的大小（例如：目前這列 + 下一列）

範例：OVER() 沒有參數

當 OVER() 沒有加任何參數時，前面的 function 會對整個資料集運作。

SELECT
    employee_id,
    salary,
    ROW_NUMBER() OVER () AS row_num -- ROW_NUMBER() 會套用到所有結果列
FROM employees;

發生什麼事？

1. ROW_NUMBER() 會給每一列一個獨立的編號。
2. 因為 OVER() 沒有參數，所有 employees table 的資料都當成一組。

結果：

用 PARTITION BY 分組

好，現在假設我們想要在每個部門內編號員工，而不是整間公司。這時就要用 PARTITION BY。

PARTITION BY 在 OVER() 裡面會把資料分成 group（或叫「分區」）。每個 group 都會獨立計算 function。就像 ROW_NUMBER() 是服務生，他會對每一桌（分區）重新開始編號。

範例：用 PARTITION BY

SELECT
    department_id,
    employee_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id) AS row_num
FROM employees;

發生什麼事？

1. 從 employees table 依 department_id 分組。
2. 每個 group 裡的資料列都會用 ROW_NUMBER() 編號。

結果：

用 ORDER BY 排序

現在我們想要更有結構一點。假設我們不只要編號，還要照薪水高低來排。這時就要用 ORDER BY。

ORDER BY 會決定 window function 處理資料列的順序。

範例：在 OVER() 裡用 ORDER BY

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

發生什麼事？

1. 資料先依 department_id 分組（PARTITION BY department_id）。
2. 每個 group 裡的資料列再依薪水高低排序（ORDER BY salary DESC）。
3. 每一列根據排序結果給一個 rank。

結果：

多個 window function 一起用

SQL 允許你在同一個查詢裡用多個 window function，而且每個 function 可以有自己的規則。就像一間房間裡同時放音樂又在數人數——各做各的！

範例：多個 window function

SELECT
    department_id,
    employee_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num,
    AVG(salary) OVER (PARTITION BY department_id) AS avg_salary
FROM employees;

發生什麼事？

1. ROW_NUMBER() 會在每個 group 依薪水高低編號。
2. AVG() 會算每個 group 的平均薪水。

結果：

真實世界的例子

window function 加 OVER() 在很多實際情境都超好用。舉幾個例子：

• 銷售分析：在每個分類裡，商品依銷售量排名。
• 排行榜：算每個學生在各自 group 的平均分數排名。
• 時間序列：依時間累積銷售總額。

銷售分析範例：

SELECT
    category_id,
    product_id,
    product_name,
    SUM(sales) OVER (PARTITION BY category_id ORDER BY sales DESC) AS cumulative_sales
FROM products;

window function 常見錯誤

1. 沒用 PARTITION BY

如果你沒用 PARTITION BY，window function 會對整個 table 運作。這可能會讓結果跟你預期的分組不一樣。

---

2. ORDER BY 用錯資料型態

window function 裡的 ORDER BY 很在意資料型態。如果你用 date 欄位但它是文字（VARCHAR），排序會變成字母順序，不是時間順序。

3. ROWS BETWEEN 用錯

window function 預設會用 ROWS BETWEEN 來決定範圍。如果你沒明確指定，可能會用 RANGE，這樣結果可能比你想的多。

4. 處理 NULL 不正確

window function 處理 NULL 的方式不一樣。像 RANK() 跟 DENSE_RANK() 會把 NULL 當成一個值，給它自己的 rank。

5. 用 window aggregate function 取代一般 aggregate function

有時候大家會用 window aggregate function（像 SUM() OVER(...)）來做本來用 GROUP BY 就能解決的事，這樣只會讓查詢更複雜、速度更慢。