Open in app

Sign in

Write

Sign in

十分鐘內快速上手與使用 Window function｜SQL 教學

本文帶你快速釐清 Window function 的查詢基本概念。

學．誌｜Chris Kang
不止數據｜Not Only Data

--

Photo by Markus Spiske on Unsplash

一開始學習 SQL 的時候完全不知道有 Window Function 可以使用，因此只要遇到需要連續計算的資料，通常都會以很多個 Subquery 來執行；但缺點就是不僅 Code 很長很難看懂，自己也很容易寫到糊塗。

因此本篇的教學，是希望以簡單的概念來介紹 Window Function 以及如何應用裡面的功能來查詢。

Window Function Structure

整個 Window Function 主要由兩個部分組成，分別是：

# STANDARD FORMULA
Aggregation_function # like ROW_NUMBER()
OVER(PARTITION BY col1
     ORDER BY col2)
  1. OVER()
  2. Aggregation function

OVER() 的功能是詳細定義對於該分區（Partition）的條件，例如以哪一行進行分區、排序如何等規則。Aggregation Function 則主要負責對特定的分區（Partition）進行計算，除了常見的 AVG 等函數，還多了 ROW_NUMBER 和 NTILE 等新的函數能夠使用。

在 OVER() 的部分，我們會著重介紹：

  • PARTITION BY
  • FRAME: RANGE & ROWS BETWEEN
  • GROUP: ROLLUP & CUBE

再來 Aggregation function，會在本篇介紹的有：

  • ROW_NUMBER()
  • RANK() & DENSE_RANK()
  • LAG(column, n) & LEAD(column, n)
  • NTILE()
  • MAX()& MIN()&AVG()
  • FIRST_VALUE() & LAST_VALUE()

接著，筆者會在文末補充用於 Subquery 的 CTE 子查詢表格該如何使用，就讓我們開始吧！

PART_1: OVER()

1. PARTITION BY:

這個參數在一開始習慣使用 GROUP BY 後，確實比較難想像 PARTITION BY 和 GROUP BY 的差異在哪裡。這裡筆者就以一個簡單易懂的方式，來說明 PARTITION BY 的差異是什麼：

在使用 PARTITION BY 的時候，你可以直接想成對特定欄位中，把個別的值拉到同一個區塊來呈現。

這裡舉個簡單的資料來說明，下列表格是簡單的成績表：

# SQL query
SELECT NAME, OBJECT, GRADE
FROM CLASS_A;# OUTPUT
NAME, OBJECT, GRADE
John, Eng, 60  
John, History, 70
Alice, Eng, 60
Bob, History, 60
Alice, History, 70

如果我想要知道每個學生個別的科目排名，那要怎麼辦呢？過去可以使用 GROUP BY 來使用，但 GROUP BY 的問題在於並沒有辦法直接個別列出排名，因此使用 PARTITION BY 就有奇效啦！

經過簡單的 PARTITION BY 之後，則會變成下列的結果：

# SQL query
SELECT NAME, OBJECT, GRADE,
ROW_NUMBER(GRADE) OVER(PARTITION BY NAME
ORDER BY GRADE DESC) AS GRADE_RANK
FROM CLASS_A
ORDER BY NAME ASC;# OUTPUT
NAME, OBJECT, GRADE, GRADE_RANK
Alice, History, 70, 1
Alice, Eng, 60, 2
Bob, History, 50, 1
John, History, 70, 1
John, Eng, 60, 2

這裡補充說明一下，使用 ROW_NUMBER() 的目的就是為了取得在「以每個名字為分組的群組內，成績的排名」，並以各個群組內成績的排名，以高到低（DESC，從大到小）呈現。最後在整個程式碼的外面，以 NAME 作為整張表來排序。

2. FRAME: RANGE BETWEEN / ROWS BETWEEN

接著筆者要來講一個很細微的差異 — — RANGE 和 ROWS 的差異。基本上這兩個函數在做的事情沒有什麼差異；但一遇到重複的值，ROWS 一樣會排序重複的值，並逐一計算 AVG 或 MAX 等數值。然而 RANGE 則會直接將所有的重複值進行運算，並讓所有的重複值都有相同的結果。

舉個實際的例子來說：

| Year | Medals | Rows_RT | Range_RT | |------|--------|---------|----------|
| 1992 | 10     |      10 |       10 | 
| 1996 | 50     |      60 |      110 | 
| 2000 | 50     |     110 |      110 | 
| 2004 | 60     |     170 |      230 | 
| 2008 | 60     |     230 |      230 | 
| 2012 | 70     |     300 |      300 |

如果我以 ROWS 來執行，就會跑出跟預期很接近的結果。

# SQL Query
  SELECT Year,
         SUM(Medals) OVER(ORDER BY Year ASC
                          ROWS BETWEEN 1 PRECEDING
                          AND CURRENT ROW) AS Medal_Total
    FROM TABLE
ORDER BY Year ASC;# OUTPUT
Year, Medal_Total
1992, 10
1996, 60
2000, 100
2004, 110
2008, 120
2012, 130

但如果以 RANGE 來執行，就會跑出和預期不同的結果：

# SQL Query
  SELECT Year,
         SUM(Medals) OVER(ORDER BY Year ASC
                          RANGE BETWEEN 1 PRECEDING
                          AND CURRENT ROW) AS Medal_Total
    FROM TABLE
ORDER BY Year ASC;# OUTPUT
Year, Medal_Total
1992, 10
1996, 110
2000, 100
2004, 170
2008, 120
2012, 190

RANGE 會把重複的值直接拉在一起共同計算，這時的結果通常就不會是我們想要的。因此在實務中我們使用 ROWS 通常遠多於使用 RANGE。

3. ROLLUP & CUBE(column_1, column_2, …)

一開始在看 ROLLUP 和 CUBE 的差異時，會有點分不出來實際上的差異。但如果真的實際操作一輪後，就可以很容易分辨出這兩者的差異。

你可以把 CUBE 當作加強版的 ROLLUP，前者會比後者多計算第二層的 GROUP 值。我們直接以實際的 Code 來看：

# ROLLUP OUTPUT

# ROLLUP
SELECT Gender, medal
       COUNT(*) AS Gold_Awards
  FROM Summer_Medals
GROUP BY Country, ROLLUP(Gender)
ORDER BY Country ASC, Gender ASC;# OUTPUT
gender, medal, awards
Men, Bronze, 34
Men, Gold, 23 
Men, Silver, 7
Men, null, 64
Women, Bronze, 17
Women, Gold, 24
Women, Silver, 25
Women null, 66, 
null, null, 130

可以看到他會有一些 null 的地方，最後一列則是用來顯示總數。

# CUBE OUTPUT

# ROLLUP
SELECT Gender, medal
       COUNT(*) AS Gold_Awards
  FROM Summer_Medals
GROUP BY Country, CUBE(Gender, medal)
ORDER BY Country ASC, Gender ASC;# OUTPUT
gender, medal, awards
Men, Bronze, 34
Men, Gold, 23 
Men, Silver, 7
Men, null, 64
Women, Bronze, 17
Women, Gold, 24
Women, Silver, 25
Women null, 66
null, Bronze, 51 # 個別計算 Bronze 的 COUNT(*)
null, Gold, 47 # 個別計算 Gold 的 COUNT(*)
null, Silver, 32 # 個別計算 Silver 的 COUNT(*)
null, null, 130

可以看到 CUBE 多了個別子類別的計數，因此如果希望獲得更多的統計數量，可以考慮使用 CUBE 函數。

3. COALESCE() 替代函數

這個函數通常都會和 ROLLUP 跟 CUBE 一起使用。我們可以看到上面會有一些 null 在結果當中；但如果我們不想要只是看到 null，COALESCE() 就可以起到替換 null 的功能。

PART_2: Aggregation Function

在瞭解整個 Window Function 的架構後，我們再來瞭解 Aggregation function 在整個 Window function 中扮演的角色。我們可以把 OVER() 當作是劃分區域的工具，而前面的 Aggregation function 則是針對這個範圍進行運算的計算機。

1. ROW_NUMBER / RANK / DENSE_RANK 排序

這個幾個參數很常拿來作為排序使用，我們可以先把上面提到的函數粗略分成 ROW_NUMBER 跟 RANK 兩類。而這兩種最關鍵的差異，就在於 RANK 類別的函數考慮到了「重複值」。

如果使用 ROW_NUMBER()，就算遇到重複的值，他也可能會因為內部 ORDER BY 的順序，或是系統自己排序出來的順序依序編號；但 RANK 類型的就會考慮到重複值，並選擇給予重複值相同的排名。

SELECT Year, Event, Country,
       ROW_NUMBER() OVER(ORDER BY Medals DESC,
       Event ASC) AS Row_N,
       Medals
  FROM Summer_Medals
 WHERE Medal = 'Gold';# OUTPUT
| Year | Event | Country | Row_N | Medals 
| 2012 | + 100KG | FRA | 1 | 32
| 2012 | + 67 KG | SRB | 2 | 32
| 2012 | + 78KG  | CUB | 3 | 30

可以看到第一名和第二名其實擁有一樣多的獎牌數，但卻分別被計算成第一名和第二名。因此如果單純只是想針對特定的資料來編號，ROW_NUMBER 函數就很適合；但如果要使用排名，還是盡可能地使用 RANK 的參數。

那 RANK 和 DENSE_RANK 差在哪裡呢？

唯一的差異就在於 DENSE_RANK 如果遇到重複的值，他在排序完相同的排名 N之後，會給予下一個值 N+1 的排名。而 RANK 則會在遇到重複值（假設有兩個重複值）時，給予重複值相同的排名 N 後，直接在下一個值給予 N+2 的排序。

2. LAG / LEAD (column, n)

這個函數在使用上非常的直覺，LAG 故名思義就是會延遲幾行的結果顯現；而 LEAD 則是優先幾行呈現。非常直覺而且簡單的計算方式，就是拿來進行環比的分析，亦即比起上一個月，這一個月的變化如何。只要簡單的幾句，就可以快速計算出跟上個月的比例差異。

SELECT Year,
         ROUND(LAG(Revenue, 1)/ Revenue) OVER(
         ROWS BETWEEN 1 PRECEDING
         AND CURRENT ROW) AS Compare_to_last_month_ratio
    FROM Product_revenue
ORDER BY Year;

這裡的 LAG 是指相較於當下處理的這行，「要往前幾行顯示」；這裡的 LEAD 則是指相較於當下處理的這行，「要往後幾行顯示」。如果遇到沒有數值的時候，則會顯示 null。

3. PAGING: NTILE( Page_number ) 分頁

這個 Function 其實和其他介紹的 Function 有些不同，他主要用於實際撈資料的過程中，有時我們會遇到 Data Set 太大或者是我們只想要看特定比例段的資料，這時就會使用 NTILE() 來提前切分資料。NTILE() 的用法為：

NTILE(n) OVER()

之中的 n 是用於設定「總共要切幾頁相等的資料」，如果設定 3 就代表切成 3 頁資料數相等的表格；但如果遇到資料數無法平分，就會在最後的一頁有溢出得現象，亦即資料可能會比前幾頁多或少。

返回的數值，則是被 NTILE 所分配後的頁數，例如第 X 筆位在 NTILE(5) 的第三頁，那就會獲得 3 在 NTILE 這一行的返回數值。

4. Aggregation: MAX, MIN, AVG( Col_name ) 計算函數

在 Window Function 當中，Aggregation Function 的用法完美展現了「累積」的概念。什麼意思呢？一般來說使用 GROUP BY 都是直接針對整張表來單次性計算；但在 Window function 的應用下，他會變成「累加」、「累積最大」的功能。舉個實際的 Code 來說：

# SQL query
SELECT NAME, OBJECT, GRADE,
MAX(GRADE) OVER(PARTITION BY NAME
ORDER BY GRADE DESC) AS GRADE_MIN
FROM CLASS_A
ORDER BY NAME ASC;# OUTPUT
NAME, OBJECT, GRADE, GRADE_MIN
Alice, History, 70, 70
Alice, Eng, 60, 60
Bob, History, 50, 50
John, History, 70, 70
John, Eng, 60, 60

可以看到以 Alice 來說，從上面數下來第一個在分區內的資料，因為僅有一筆 70 分，所以就會呈現自身數據。接著到第二名後，因為最小的分數在 Alice 這個分區內，已經變成了 60 分，所以數據就會因此更新成 60。其他的兩筆數據也可以依此類推。

如果沒有使用 PARTITION BY，則分數就不會僅限定在 Alice 或 John，而是會跟著整張表更新，結果會變成：

# SQL query
SELECT NAME, OBJECT, GRADE,
MAX(GRADE) OVER(ORDER BY GRADE DESC) AS GRADE_MIN
FROM CLASS_A
ORDER BY NAME ASC;# OUTPUT
NAME, OBJECT, GRADE, GRADE_MIN
Alice, History, 70, 70
Alice, Eng, 60, 60
Bob, History, 50, 50
John, History, 70, 50
John, Eng, 60, 50

因為在 Bob 的 50 分時，就已經是整張表最低的數值了，所以之後所有最低的數值就都會維持在 50 分。

其中最常被使用的功能，莫過於 Moving AVG() 滾動平均

Moving 的功能，其實是來自於 Frame 的 ROWS / RANGE BETWEEN 之使用。目的就是為了讓我們在計算平均 AVG() 時，可以只限定在特定的範圍或資料內。這個功能是 Window Function 最常被使用的時機，他的使用觀念其實很簡單。我們先來看滾動平均的定義

在過去的 N 天／時段，各個區段內的平均值。

如果我想要求得前三屆（含本屆）的平均獎牌數，以 SQL 的程式來寫的話，就會變成：

  WITH US_Medals AS (...)SELECT Year, Medals, 
       AVG(Medals) OVER(ORDER BY Year ASC
                        ROWS BETWEEN 2 PRECEDING 
                        AND CURRENT ROW) AS Medals_MA
  FROM US_Medals
 ORDER BY Year ASC;

在應用上有一個小細節可以注意：例如我想要知道這個月內，七天的用戶活躍（WAU, Week Active User）是多少。這時我可以在表格內加上 OFFSET 6 來跳過顯示前面 6 個資料，因為前面六個統計平均其實並不足 7 天，這會導致前面 6 筆資料並沒有參考價值。

     WITH US_Medals AS (...)   SELECT Year, Medals, 
          AVG(Medals) OVER(ORDER BY Year ASC
                           ROWS BETWEEN 2 PRECEDING 
                           AND CURRENT ROW) AS Medals_MA
     FROM US_Medals
 ORDER BY Year ASC
   OFFSET 6; # 會略過前 6 筆資料

因此如果想要獲得都是足 7 天的數據，就能以下列的寫法來取消掉前六筆。而且盡可能地將ORDER BY 寫上，不然有可能會出現不可預期的排序資料。

5. FIRST_VALUE & LAST_VALUE

這個函數在名稱上很容易理解，但在使用上其實會有一個小陷阱。如果我們使用 FIRST_VALUE，都會正常顯示該分區的第一個值；但如果使用 LAST_VALUE 時，其實顯示的會是：

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

注意到了嗎？因為 FIRST_VALUE 預設就會從第一行開始讀，所以不會出現什麼問題；但 LAST_VALUE 就會導致在顯示時只會出現到該行的最後一個值。因此如果想要呈現整個區域的最後一個值，就要特別加上 RANGE 或 ROWS 的範圍，讓 SQL 在計算時能夠從特定區域來撈資料。

LAST_VALUE(Value) OVER (PARTITION BY Id 
                         ORDER BY Value 
                         RANGE BETWEEN UNBOUNDED PRECEDING
                         AND UNBOUNDED FOLLOWING) as FirstValue2

就會在每一行顯示出每一個 Id 的最後一個值。瞭解之後，FIRST_VALUE 和 LAST_VALUE 就變得直覺而容易使用了！

PART_3: Functool

Subquery Factoring 子查詢部分

這個方法又被稱為公用表表達式（CTE）。當初在知道有這個寫法後，簡直驚為天人。因為過去在寫 SQL 都習慣把所有的 Code 都寫在一張表上。導致整個表格的易讀性非常的低，隔一陣子再回來看就什麼都看不懂了。

WITH Country_Gold AS (SELECT DISTINCT Year, Country, Event 
  FROM Summer_Medals
 WHERE Year IN (2008, 2012) 
   AND Country IN ('CHN', 'JPN') 
   AND Gender = 'Women'
   AND Medal = 'Gold'
),
Table_2 AS (...)

但有了 CTE，簡直就像 Python 的 function 一樣，把原本要查詢的表格先包起來成為一張獨立的表格，再利用這張表格來寫許多的關連，能把很多需要重寫的地方都整理地非常乾淨！

另外補充一下：

  1. 在寫完這張表格後，是不可以放分號；的喔！
  2. 務必注意 CTE 必須直接接上 CTE 的 SQL 語句（如 select、insert、update等），否則 CTE 將失效。
  3. CTE 後面也可以跟其他的 CTE 但只能使用一個 with，多個 CTE 中間用逗號（,）分隔。
  4. CTE 可以引用自身，也可以引用在同一 with 子句中預先定義的 CTE。

Pivot_Table 樞紐分析表

在一開始使用 CROSS TAB 時，需要先使用 EXTENSION 來建立 Table。

-- Create the correct extention to enable CROSSTAB
CREATE EXTENSION IF NOT EXISTS tablefunc;

接著就可以使用 CROSSTAB() 加上 $$ 包在表格裡，並以 ct 來呈現想要觀察的列表。

  SELECT * 
    FROM CROSSTAB($$
           SELECT Gender, Year, Country
             FROM Summer_Medals
            WHERE Year IN (2008, 2012)
              AND Medal = 'Gold'
              AND Event = 'Pole Vault'
         ORDER BY Gender ASC, Year ASC;
                  $$) AS ct (Gender VARCHAR,
                             "2008" VARCHAR,
                             "2012" VARCHAR)
ORDER BY Gender ASC;

3. STRING_AGG()

有時候資料在排序過後，就已經根據排名來呈現了，因此排名本身就變成一個冗餘的資料，這時候怎麼辦呢？我們就可以使用 STRING_AGG() 來將一個欄位壓縮成為一列。這個函數具體來說怎麼用呢？

通常結合上面的 CTE 之後，我們最後就可以以 STRING_AGG() 來彙整整個排行到一列上：

  WITH Country_Medals AS (...),
       Country_Ranks AS (...)SELECT STRING_AGG(Country, ', ') 
  FROM Country_Medals;# OUTPUT
CHN, RUS, USA

可以看到輸出就變得非常簡潔，有時想要快速整理數據到一行，就可以參考使用 STRING_AGG()，當然還有更多的參數可以使用，可直接參考 PostgreSQL 繁體中文官方手冊。

Summary

現在回來看 Window Function 的設計，發現 SQL在語法的發展也越來越人性化了。之前在學 SQL 的時候每次使用總是像在練一些奇技淫巧，每次都用一些既不直覺也不好理解的方式來寫 Query，這次的 Window Function 真的把原本很複雜的排序和滾動平均都以非常簡單直覺得方式設計出來。希望這篇文章能夠幫到有需要的讀者。

**【希望用你的掌聲來投票與支持】**
拍 5~10 下：簽個到，表示支持（感謝你的鼓勵啊啊啊）
拍 10~30 下：希望我可以多寫一些文章！有你這位讀者，寫這篇也心滿意足了！
拍 30~50 下：內容對你感覺很有共鳴，希望能多分享給周圍的朋友！

你可能也有興趣：

｜參考資料｜

Sql
Postgresql
Window Functions
Data Manipulation

--

--

學．誌｜Chris Kang
不止數據｜Not Only Data

Written by 學．誌｜Chris Kang

879 Followers
Editor for

嗨！我是 Chris，一位擁有技術背景的獵頭，熱愛解決生活與職涯上的挑戰。專注於產品管理／資料科學／前端開發 / 人生成長，在這條路上，歡迎你找我一起聊聊。歡迎來信合作和交流： chriskang0917@gmail.com

Help

Status

About

Careers

Press

Blog

Privacy

Terms

Text to speech

Teams