CODEWITHSUNDEEP
postgresql
Avi
Avi

Reputation: 567

Unable to get Percentile_Cont() to work in Postgresql

I am trying to calculate a percentile using the percentile_cont() function in PostgreSQL using common table expressions. The goal is find the top 1% of accounts regards to their balances (called amount here). My logic is to find the 99th percentile which will return those whose account balances are greater than 99% of their peers (and thus finding the 1 percenters)

Here is my query

--ranking subquery works fine
with ranking as(
       select a.lname,sum(c.amount) as networth  from customer a
       inner join 
       account b on a.customerid=b.customerid
       inner join 
       transaction c on b.accountid=c.accountid 
       group by a.lname order by sum(c.amount)
 )
select lname, networth, percentile_cont(0.99) within group 
order by networth over (partition by lname) from ranking ;

I keeping getting the following error. 

ERROR:  syntax error at or near "order"
LINE 2: ...ame, networth, percentile_cont(0.99) within group order by n..

I am thinking that perhaps I forgot a closing brace etc. but I can't seem to figure out where. I know it could be something with the order keyword but I am not sure what to do. Can you please help me to fix this error?

Upvotes: 1

Views: 7108

Answers (3)

Ben Paul
Ben Paul

Reputation: 118

This tripped me up, too.

It turns out percentile_cont is not supported in postgres 9.3, only in 9.4+.

https://www.postgresql.org/docs/9.4/static/release-9-4.html

So you have to use something like this:

with ordered_purchases as (
  select
      price,
      row_number() over (order by price) as row_id,
      (select count(1) from purchases) as ct
  from purchases
)

select avg(price) as median
from ordered_purchases
where row_id between ct/2.0 and ct/2.0 + 1

That query care of https://www.periscopedata.com/blog/medians-in-sql (section: "Median on Postgres")

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1269533

I want to point out that you don't need a subquery for this:

select c.lname, sum(t.amount) as networth,
       percentile_cont(0.99) within group (order by sum(t.amount)) over (partition by lname)
from customer c inner join
     account a
     on c.customerid = a.customerid inner join
     transaction t
     on a.accountid = t.accountid
group by c.lname
order by networth;

Also, when using table aliases (which should be always), table abbreviations are much easier to follow than arbitrary letters.

Upvotes: 2

Gurwinder Singh
Gurwinder Singh

Reputation: 39457

You are missing the brackets in the within group (order by x) part.

Try this:

with ranking
as (
    select a.lname,
        sum(c.amount) as networth
    from customer a
    inner join account b on a.customerid = b.customerid
    inner join transaction c on b.accountid = c.accountid
    group by a.lname
    order by networth
    )
select lname,
    networth,
    percentile_cont(0.99) within group (
        order by networth
        ) over (partition by lname)
from ranking;

Upvotes: 2

Related Questions