用 SQL 窗口函數做資料分析的範例

現在你已經準備好要跳進實戰範例的世界，來看看這些東西在真實任務裡怎麼用啦！

範例：依地區計算銷售排名

假設我們有一張 sales 表，裡面有不同地區的銷售資料。我們要幫每個地區算出銷售排名。

任務：找出每個地區的銷售排名 (RANK)

SELECT
    region,
    sales_amount,
    RANK() OVER (PARTITION BY region ORDER BY sales_amount DESC) AS sales_rank
FROM 
    sales;

結果：

注意我們用了 PARTITION BY region，這樣排名才會分地區算。如果沒用 PARTITION BY，排名就會是全表一起比。

範例：計算每個客戶的累積收入

現在來處理 transactions 表，幫每個客戶算出累積（累加）收入。

任務：幫每個客戶算累積總和

SELECT
    customer_id,
    purchase_date,
    amount,
    SUM(amount) OVER (PARTITION BY customer_id ORDER BY purchase_date) AS cumulative_sum
FROM 
    transactions;

結果：

這裡重點是 OVER() 裡面有 ORDER BY purchase_date，這樣累積總和才會照時間順序算。

範例：把資料分成分位數

假設我們有一張 students 表，裡面有名字跟測驗分數。我們想要把學生依成績分成 4 組。

任務：用 NTILE() 把學生分成 4 組

SELECT
    name,
    test_score,
    NTILE(4) OVER (ORDER BY test_score DESC) AS quartile
FROM 
    students;

查詢結果會長這樣：

NTILE(4) 會把資料分成 4 組。分數最高的學生在第一組，最低的在最後一組。

範例：時序資料分析

site_visits 表裡有每天網站的訪問數。我們要算出每個網站每天訪問數的變化。

我們的任務 — 算出每天訪問數的變化

SELECT
    site_id,
    visit_date,
    visits,
    visits - LAG(visits) OVER (PARTITION BY site_id ORDER BY visit_date) AS visit_diff
FROM 
    site_visits;

結果：

LAG() 這個函數可以拿到前一列的值。如果沒有前一列，結果就會是 NULL。更多細節我們之後的課會再聊 :P