postgresql - postgres : Combining filter() with a percentile_cont()

Question

尝试使用 filter()调用 percentile_const 中的条款我不确定我能做到这一点。有办法吗？这是示例查询:

select
    count(*) as n1,
    count(*) filter(where ha >= 0) as n2,
    percentile_cont(.9) within group (order by es asc) as p1,
    percentile_cont(.9) filter (where ha >= 0) within group (order by es asc) as p2
from mytable where mypid = 123;

在没有 p2 的情况下，查询工作正常当然打电话，但你可以看到我想做什么。

Answer 1

filter需要去追 within group部分:

select
    count(*) as n1,
    count(*) filter(where ha >= 0) as n2,
    percentile_cont(.9) within group (order by es asc) as p1,
    percentile_cont(.9) within group (order by es asc) filter (where ha >= 0) as p2
from mytable 
where mypid = 123;