I have a column in a table with multiple integer records.
I need to write a PostgreSql query which will return the average of all the values which are greater than 90, 95 and 98 percentile.
e.g.
I have series 1-150 in my column.
Now if I take 90th percentile of this column it is around 135.
I need to calculate average of all the values greater than 135.
Similarly for 95 and 98 percentile also.
And if possible all the three values in single query.
as sample I use numbers from 1 to 300:
t=# select generate_series(1,300,1) g
g
---
1
2
3
4
...
here's an example:
t=# with p as (
with s as (
select generate_series(1,300,1) g
)
select g,ntile(100) over (order by g)
, case when ntile(100) over (order by g) between 90 and 94 then 90
when ntile(100) over (order by g) between 95 and 97 then 95
when ntile(100) over (order by g) >=98 then 98 end "percentile"
from s
)
select distinct "percentile",avg(g) over (partition by "percentile")
from p
where ntile >=90;
percentile | avg
------------+----------------------
90 | 275.0000000000000000
98 | 296.0000000000000000
95 | 287.0000000000000000
(3 rows)
If I correctly understood, may be you need this.
try:
with t(col) as(
select * from generate_series(1, 150)
)
select
(select avg(col) from t where col > (select count(*) * 90 / 100.00 from t)),
(select avg(col) from t where col > (select count(*) * 95 / 100.00 from t)),
(select avg(col) from t where col > (select count(*) * 98 / 100.00 from t))