昨天發了篇趣味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,來做到同樣的功能.
or
By clicking below, you agree to our terms of service.
New to HackMD? Sign up