Try   HackMD

PostgreSQL Hypothetical-Set Aggregate Functions 及模擬

前言

昨天發了篇趣味SQL(傳送門).
用來介紹 Hypothetical-Set Aggregate Functions

若不知道有這類函數時,我們可以怎樣做

雖然會麻煩一點,但是可以設法加工.
先來看第一步

with t1 as ( select array_agg(val) as arry from 雞腿組 ) select 排骨組.val as 排骨組val , (select arry || 排骨組.val as newarry from t1) as 新的雞腿組val_array from 排骨組; 排骨組val | 新的雞腿組val_array -----------+-------------------------------------- 14 | {16,26,36,46,56,66,76,86,96,106,14} 24 | {16,26,36,46,56,66,76,86,96,106,24} 34 | {16,26,36,46,56,66,76,86,96,106,34} 44 | {16,26,36,46,56,66,76,86,96,106,44} 54 | {16,26,36,46,56,66,76,86,96,106,54} 64 | {16,26,36,46,56,66,76,86,96,106,64} 74 | {16,26,36,46,56,66,76,86,96,106,74} 84 | {16,26,36,46,56,66,76,86,96,106,84} 94 | {16,26,36,46,56,66,76,86,96,106,94} 104 | {16,26,36,46,56,66,76,86,96,106,104} (10 rows)

我們透過先將雞腿組的val聚合成array,然後將每一筆排骨組的val加入array,產生對應的新array.因為window funtion 是可以分組的.
所以就可以再次展開array , 根據排骨組的val來分組切割後做排名.

with t1 as ( select array_agg(val) as arry from 雞腿組 ), t2 as ( select 排骨組.val , (select arry || 排骨組.val as newarry from t1) from 排骨組 ), t3 as ( select t2.val , unnest(t2.newarry) as grpval from t2 ), t4 as ( select val, grpval , rank() over(partition by val order by grpval desc) as rank from t3 ) select val as 排骨組val , rank as 在雞腿組排名 from t4 where val = grpval order by val desc; 排骨組val | 在雞腿組排名 -----------+-------------- 104 | 2 94 | 3 84 | 4 74 | 5 64 | 6 54 | 7 44 | 8 34 | 9 24 | 10 14 | 11 (10 rows)

結語

PostgreSQL Hypothetical-Set Aggregate Functions 的rank() within group 提供了類似以前模擬考落點預測的功能,在做資料分析時,十分便利.藉此機會介紹.
這次我們又應用了array,結合window function,來做到同樣的功能.