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,來做到同樣的功能.