From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | jonlachlan(at)gmail(dot)com |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #12228: Primary use-case of PERCENT_RANK not supported |
Date: | 2014-12-14 23:30:57 |
Message-ID: | 8865.1418599857@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
jonlachlan(at)gmail(dot)com writes:
> I'm using the 9.4 RC1 and trying out the WITHIN GROUP functions. I used a
> percentile_disc pattern successfully, however I think I came across a
> problem with using percent_rank(). Here is my SQL:
> SELECT percent_rank(datavalue) WITHIN GROUP (ORDER BY datavalue) as
> pct_rank, measureid
> FROM measuredata
> WHERE surveyyear=2013
> GROUP BY measureid;
That query isn't very sensible: the direct argument of percent_rank() has
to be a constant over any one aggregation group, else the percentile
calculation is meaningless.
> I want to be able to run a query that programmatically displays the
> 'pct_rank' of datavalue.
I'm not 100% sure what you mean by that, but I suspect you are looking for
something closer to the basic percent_rank() window function, not the
hypothetical-set function. Hypothetical-set functions are for computing
the measure that would be attributed to a row that's not actually present
in the data. Moreover, since they're aggregates, they produce only one
output per GROUP BY group, and I don't understand what you mean by
"pct_rank of datavalue" at a group level. Perhaps what you want is
SELECT percent_rank() OVER (PARTITION BY measureid ORDER BY datavalue) as
pct_rank, measureid
FROM measuredata
WHERE surveyyear=2013;
It could also be that what you're after will require doing a window
function like that in a sub-SELECT, and then grouping and/or aggregating
in the outer query.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | aleks.dark | 2014-12-15 15:15:55 | BUG #12241: uninitialized value $lib_path |
Previous Message | Mark Kirkwood | 2014-12-14 02:24:47 | Re: regression, deadlock in high frequency single-row UPDATE |