How to aggregate percentile_disc() function over date time

Question

I have tables like the following:

recorddate score 
2021-05-01   0
2021-05-01   1 
2021-05-01   2
2021-05-02   3
2021-05-02   4
2021-05-03   5
2021-05-07   6

And want to get the 60th percentile for score per week. I tried:

select distinct 
       recorddate
     , PERCENTILE_disc(0.60) WITHIN GROUP (ORDER BY score)
                             OVER (PARTITION BY recorddate) AS top60
from tbl;

It returned something like this:

recorddate top60
2021-05-01  1
2021-05-02  4
2021-05-03  5
2021-05-07  6

But my desired result is like weekly aggregation (7 days). For example for the week ending on 2021-05-07:

recorddate                    top60
2021-05-01 ~ 2021-05-07        2

Is there a solution for this?

What is "top60" supposed to be? And why does your week span 8 days? — Gordon Linoff, Jun 01 '21 at 00:33
I edited my question about week. top60 is cumulative ranking . so that I'd like to sort score in a week and get top60% score. thanks — Heisenberg, Jun 01 '21 at 00:38
Heisenberg . . . I still don't understand what a "cumulative ranking" is supposed to mean in this context. Perhaps Erwin Brandstetter understands better. — Gordon Linoff, Jun 01 '21 at 00:58
@Heisenberg: I took the liberty to edit and make it clearer. Did I get it right? — Erwin Brandstetter, Jun 01 '21 at 01:02

Erwin Brandstetter · Accepted Answer · 2021-06-01T00:52:58.523

I think you want this:

SELECT date_trunc('week', recorddate) AS week
     , percentile_disc(0.60) WITHIN GROUP(ORDER BY score) AS top60
FROM   tbl
GROUP  BY 1;

That's the discrete value at the 60th percentile for each week (where actual data exists) - where 60 % of the rows in the same group (in the week) are the same or smaller. To be precise, in the words of the manual:

the first value within the ordered set of aggregated argument values whose position in the ordering equals or exceeds the specified fraction.

Adding your format on top of it:

SELECT to_char(week_start, 'YYYY-MM-DD" ~ "')
    || to_char(week_start + interval '6 days', 'YYYY-MM-DD') AS week
     , top60
FROM  (
   SELECT date_trunc('week', recorddate) AS week_start
        , percentile_disc(0.60) WITHIN GROUP(ORDER BY score) AS top60
   FROM   tbl
   GROUP  BY 1
   ) sub;

I would rather call it something like "percentile_60".

How to aggregate percentile_disc() function over date time

1 Answers1

Linked