SQL 筆記(1),視窗函數的應用 — 排名與累計加總
這是 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 的情境正好是我所希望的。