分析用的主要 window functions

開始之前，想像一下你在處理一張有上千筆銷售紀錄的表格。你的任務：找出每個分類裡賣最好的是誰，第二名是誰，依此類推。或者你想要給查詢結果的每一行編號，方便追蹤順序。這些用 window functions 都超簡單！

Window functions 就是 SQL 裡可以針對一個資料子集（我們叫它「window」）來運算的函數。跟 aggregate functions 不一樣（像 SUM() 或 AVG() 會把多行合成一行），window functions 不會動到原本的資料列，只是多加一個計算出來的值。

跟 aggregate functions 的差別

Aggregate functions 會「壓縮」資料，把多行 group 起來：

SELECT department, COUNT(*)
FROM employees
GROUP BY department;

結果：只會有幾行，跟部門數一樣。

來看看 window function 的差別——這裡每一行都還在，只是多了一個欄位，比如 ROW_NUMBER()：

SELECT employee_name, department,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_within_department
FROM employees;

這樣你會拿到所有原本的資料列，然後多一個 rank_within_department 欄位，顯示每個員工在自己部門裡的排名。

主要的 window functions

OVER() 的語法

每個 window function 最重要的就是那個神奇的 OVER()。它決定這個 function 要對哪個「window」運算。OVER() 裡面可以指定 分組（PARTITION BY）跟/或 排序（ORDER BY）。

基本語法：

<window_function>() OVER (
   [PARTITION BY <group>] 
   [ORDER BY <order>]
)

組件說明：

• PARTITION BY：把資料分組。像是「依部門分組」。
• ORDER BY：指定排序方式。像是「員工依薪水從高到低排序」。

ROW_NUMBER() 函數

ROW_NUMBER() 會從 1 開始，對指定的「window」裡的每一行編號。有時候你只是想在臨時表裡給每一行一個流水號，或是要知道某筆資料的順序，這就超好用。

範例。sales（銷售）表：

查詢：

SELECT seller_name, product_category, revenue,
       ROW_NUMBER() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS row_number
FROM sales;

結果：

怎麼運作：

1. 資料依 product_category 分組。
2. 每組再依 revenue（收入）從大到小排序。
3. 每組裡的資料列都會拿到一個順序編號。

RANK() 函數

RANK() 用來做 排名。跟 ROW_NUMBER() 不一樣的是，它 會考慮重複值，如果有一樣的值，排名會跳號。

範例：

SELECT seller_name, product_category, revenue,
       RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS rank
FROM sales;

結果：

DENSE_RANK() 函數

DENSE_RANK() 跟 RANK() 很像，不過有個差別：它 不會跳過排名，如果有重複值，下一個排名還是緊接著。

範例。多加一筆一樣收入的銷售：

查詢：

SELECT seller_name, product_category, revenue,
       DENSE_RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS dense_rank
FROM sales;

結果：

應用範例：資料列編號

任務：把 orders 表裡所有訂單依日期排序並編號。

SELECT order_id, customer_name, order_date,
       ROW_NUMBER() OVER (ORDER BY order_date) AS order_number
FROM orders;

結果：你會拿到依照執行順序編號的訂單清單。

應用範例：每個分類的前三名賣家

任務：找出每個商品分類裡前三名的賣家。

WITH ranked_sales AS (
    SELECT seller_name, product_category, revenue,
           RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS rank
    FROM sales
)
SELECT seller_name, product_category, revenue
FROM ranked_sales
WHERE rank <= 3;

應用範例：找出一樣的指標

任務：判斷每個分類裡有沒有賣家收入一樣。

SELECT seller_name, product_category, revenue,
       DENSE_RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS dense_rank
FROM sales;

這樣你就可以看到哪些排名「卡住」在一樣的值。