Postgres percentile_cont with power

Question

I am using Postgres new percentile_cont to calculate percentiles for a table since it started. However, we are now changing the table to include power for each row, and I'm not sure how to implement percentile_cont to take this into account.

Let's say the table looked like this:

+--------+--------------+
| name   | age          |
+--------+--------------+
|  Joe   | 10           |
+--------+--------------+
|  Bob   | 11           |
+--------+--------------+
|  Lisa  | 12           |
+--------+--------------+

Calculating the 85th percentile by age in the set will simply be done with: percentile_cont(0.85) WITHIN group (ORDER BY age asc) 85

Now we have the power for each name (the number of people with that particular name). It looks something like this:

+--------------+--------+
| name   | age | count  |
+--------+-----+--------+
|  Joe   | 10  |   2    |
+--------+-----+--------+
|  Bob   | 11  |   1    |
+--------+-----+--------+
|  Lisa  | 12  |   1    |
+--------+-----+--------+

Is there a way to use percentile_cont or any other built-in function in Postgres to calculate percentile taking account / power into account?

Answer 1

count.

:

create table a_table (name text, age int, count int);
insert into a_table values
    ('Joe', 10, 3),
    ('Bob', 11, 2),
    ('Lisa', 12, 1);

Query:

with recursive data (name, age, count) as (
    select * 
    from a_table
union all
    select name, age, count- 1
    from data
    where count > 1
    )
select name, age 
from data
order by 1, 2;

 name | age 
------+-----
 Bob  |  11
 Bob  |  11
 Joe  |  10
 Joe  |  10
 Joe  |  10
 Lisa |  12
(6 rows)