iT邦幫忙

2025 iThome 鐵人賽

DAY 12
1
Software Development

我所不知道的PostgreSQL 30天系列 第 12

Day 12: 簡介 Window Functions 和比較與 Group By 的差異

  • 分享至 

  • xImage
  •  

SQL 資料庫提供 window function 運算資料的功能,跟aggregate function(ex: GROUP BY )相同的地方在於,他們都會先把資料分群,在針對每一群的資料做統計,但不同的是,window function 統計完之後,不會把資料合併成一筆,每一群中的每一筆資料,仍然保持獨立,aggregate function 則會把每一群的資料合併成一筆。

舉例來說,如果我們需要統計DVD租借店中,每個客戶平均花多少錢租借一部DVD, window function 和 aggregate function 的 GROUP BY 的結果會有些不一樣。window function 會先把付款紀錄依照客戶分群,然後針對客戶花的錢除以該客戶所有的訂單數量(一筆付款記錄只會有一部 DVD ,不考慮一筆筆付款記錄有多部 DVD 的情形),就可以看到客戶平均花多少錢租借一部 DVD 。

並且從 SQL 吐回的結果可以看到,資料確實跟上面圖示的一樣,每筆訂單都列出來,這樣做的好處是,可以清楚比較每筆訂單付的錢和平均的差距,如果想要主動推銷 DVD 給客戶,也許比較能知道,推銷什麼類型的 DVD 比較能賺錢XDDD。不過如果單純地只想知道,每個客戶平均每筆訂單付多少錢, window function 的結果就會顯得多餘。

下面是這個範例的 SQL ,要告訴 window function 怎麼分群,需要加上 OVER ,並在 OVER 裡面用 PARTITION ,告訴 window function 要根據哪個欄位做分群。

SELECT payment.customer_id, film.film_id, film.title, amount, AVG(amount) OVER (PARTITION BY payment.customer_id) from payment 
 JOIN rental on rental.rental_id = payment.rental_id
 JOIN inventory ON inventory.inventory_id = rental.inventory_id
 JOIN film on film.film_id = inventory.film_id

而 aggregate function 的 GROUP BY,會把同一個客戶的所有訂單合成一筆,並計算客戶平均每筆訂單付的錢,所以不會看到每一筆的詳細資訊。

GROUP BY 指定欄位,就會依據這個欄位去做集合和統計的動作,最後的結果,每個客戶只會有一筆資料。

SELECT payment.customer_id, AVG(amount), max(amount) from payment 
 GROUP BY payment.customer_id
 ORDER BY payment.customer_id

參考資料:


上一篇
Day 11: json 和 jsonb 如何處理 Unicode escape 字元?
下一篇
Day 13: 如何使用繼承?
系列文
我所不知道的PostgreSQL 30天30
圖片
  熱門推薦
圖片
{{ item.channelVendor }} | {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言