如何解决Postgres/Citus中不支持的percentile_cont？

Question

我有一个类似如下的查询：

select
      coalesce(s.import_date, r.import_date) as import_date,
      coalesce(s.bedrooms, r.bedrooms) as bedrooms,
      coalesce(s.ptype, r.ptype) as property_type,
      s.s_price,
      s.s_transactions,
      ....
      r.r_rent,
      ....
from
(
    select

       sc.import_date,
       sc.bedrooms,
       sc.ptype,

       percentile_cont(array[0.25,0.5,0.75,0.9]) within group (order by sc.asking_price) filter(where sc.price > 0) as s_price,

       sum(1) filter(where sc.sold_price > 0) as s_transactions,

       ......

       from prices sc
       where sc.ptype = 'F' and  sc.bedrooms = 2 and st_Intersects('010300002.....'::geometry,sc.geom)
       and sc.import_date between '2012-01-01' and '2019-01-01'
       group by sc.import_date, sc.bedrooms, sc.property_type
) s
full join
(
    select
       rc.import_date,
       rc.bedrooms,
       rc.ptype,

       percentile_cont(array[0.25,0.5,0.75,0.9]) within group (order by rc.rent) filter(where rc.rent > 0) as r_rent,

      .....

       from rents rc
       where rc.ptype = 'F' and  rc.bedrooms = 2 and st_Intersects('010300002....'::geometry,rc.geom)
       and rc.import_date between '2012-01-01' and '2019-01-01'
       group by rc.import_date, rc.bedrooms, rc.property_type
) r
on r.import_date = s.import_date;

当我在Citus/Postgres-11上对我的分布式表运行它时，我得到：

ERROR: unsupported aggregate function percentile_cont

有什么方法可以绕过这个限制吗？

Answer 1

AFAIK对于这个问题没有简单的解决方法。

您可以随时将所有数据拉到协调器，并在那里计算百分位数。不过，在同一查询中这样做并不可取。

SELECT percentile_cont(array[0.25,0.5,0.75,0.9]) within group (order by r.order_col)
FROM
(
    SELECT order_col, ...
    FROM rents
    WHERE ...
) r
GROUP BY ...

此查询将内部子查询返回的所有数据拉到协调器，并计算协调器中的百分位数。