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
參考資料: