SQL 筆記(1)，視窗函數的應用 — 排名與累計加總

5 min readNov 16, 2021

這是 STATA 教學系列後，和數據分析有關的第一篇文章，未來如果集合夠多文章的話，也許會編排集合成數據分析系列或是 SQL 系列。

關於 SQL，我覺得沒必要從頭去談，畢竟已經存在那麼多優良的線上課程與教學文章。因此我想從我經歷的分析任務出發，討論特定任務可以用何種 SQL 語法與函數處理。BTW，目前我工作上用的是 Oracle，而我初次接觸 SQL 學的是 MySQL，雖然特定的語法可能有些差異，但基本語法大多相同。

我想先從「組內排名」以及「累計加總」這兩個用法談起，並且會用來計算交易明細中的前 10 名大賣家以及前 10% 大賣家。

組內排名

分組並依某變數排序產生組內排名，這件事經常出現在分析流程的某個步驟中，而其本身的應用也很廣。試想，你有一份資料內含學號、班級、成績這三個欄位，這時計算組內排名即是在計算班排名。範例如下：

SELECT student_id, class_id, score, 
  rank() over(partition by class_id order by score desc) 班排名
FROM final2021fall

在 SQL 內，有 row_number()、rank()、dense_rank() 這三個 window function 可以幫你產生組內排名，他們的差異主要在於處理「排序變數（如：成績）相等的狀況」以及「排名是否連續」，細節與範例 google 一下就能找到，我就不贅述了。

累計加總

對某變數做累計加總是另一個基礎而重要的應用。比如說，我們可能會想看一個國家內，都市人口由大到小的人口累計加總。這樣的資訊可以幫我們了解國家的都市型態，以及提供「前五大都市佔了60％人口、70% GDP」等資訊。假想資料如下：

c_id：城市 ID，Primary Key
pop：人口數

SELECT c_id,
  rank() over(order by pop desc) 排名, 
  sum(pop) over(order by pop desc) 累計人口,
  sum(pop) over() 總人口
FROM city_pop

其中我分別利用了 sum() 的兩個 window function 計算累計人口和總人口，據此可以再衍生出累計比例，或是各位也可以試試看 percent_rank() 。之後，最自然的應用就是拿排名和累計人口（或比例）去畫 line plot。

另外，還有幾個和累計加總相關的變體，比如說以下累計、以上 3 列累計，這些都可以透過 over(... RANGE BETWEEN ... AND ...) 實踐，這就留待有興趣的人去研究囉！

大賣家名單

我想再舉幾個例子來說明「排名」以及「累計加總」的應用場景。這次我們想要從交易明細中抓出大賣家的名單。讓我們先考慮所有賣家、所有時間吧！當然，實際應用上，我們也許還會依賣家類別、時間區間細看，這時再補上適當的 partition by 即可。資料相關欄位如下：

t_id：交易 ID，Primary Key
seller_id：賣家 ID
gmv：Gross Merchandise Volume，成交額

前 10 大賣家

我們可以依 gmv 對賣家做排名，在這個例子中，要在前 10 名遇到同樣 gmv 的情形，應該沒那麼容易，因此我使用的是 row_number()，並在排序時多納入 seller_id，確保 gmv 相同時，優先用 seller_id 排前的。¹

SELECT seller_id
FROM (
SELECT seller_id, gmv, 
  row_number() over(order by gmv desc, seller_id) rk
FROM (
SELECT seller_id, 
  sum(gmv) gmv
FROM trans_table tt
GROUP BY seller_id
) tt2
) tt3
WHERE rk<=10

前 10% 賣家

這次我們來撈撈看，成交額由大到小，佔全部前 10% 的賣家吧！我先計算了賣家的總成交額，然後分別計算累計成交額 cum_gmv 以及全站總成交額 total_gmv，最後兩者相除即可算出累計成交額佔比。

SELECT seller_id 
FROM (
SELECT seller_id, gmv,
  sum(gmv) over() as total_gmv,
  sum(gmv) over(order by gmv desc) as cum_gmv
FROM (
SELECT seller_id, sum(gmv) gmv 
FROM trans_table tt
GROUP BY seller_id
) tt2
) tt3
WHERE cum_gmv/total_gmv <= 0.1

我們有可能會需要撈到恰好超過門檻（10%）的賣家，而上面的 SQL 可能只抓到累計至 9.9% 的賣家們，也就是說，還需要多抓下一位賣家才能符合目標。我想到的 trick 是利用 lag() 去位移累計成交佔比的欄位，藉此納入下一位賣家，不知到大家有沒有其他想法呢？

[1] 我其實對 row_number() 有某種偏好（笑），主要是因為 row_number() 不像 rank() 、 dense_rank() 可能因為排序變數相同（tie）而導致處理資料時要做額外的考慮，除非納入 tie 的情境正好是我所希望的。