How to calculate 90th percentile in Teradata

Analytics
Highlighted
Fan

How to calculate 90th percentile in Teradata

Seems no build in available in tera data to calculate 90th or nth percentile for the given dataset
unlike ORACLE
2 REPLIES
Enthusiast

Re: How to calculate 90th percentile in Teradata

Teradata is slightly more generic - it uses QUANTILE which allows you to split into groups based on any fraction.
So 90th percentile is:

Select
blah,blah,blah
From somewhere
Qualify Quantile(100,col1) = 90
;
c_
Enthusiast

Re: How to calculate 90th percentile in Teradata

this is what i use. ansi, so you can use it in oracle or td. can easily be expanded out to give you the entire distibution should you want.

SELECT
col
, MAX(CASE WHEN percentile <=10 THEN pctl_var END) AS percentile_10
, MAX(CASE WHEN percentile <=20 THEN pctl_var END) AS percentile_20
, MAX(CASE WHEN percentile <=30 THEN pctl_var END) AS percentile_30
, MAX(CASE WHEN percentile <=40 THEN pctl_var END) AS percentile_40
, MAX(CASE WHEN percentile <=50 THEN pctl_var END) AS percentile_50
, MAX(CASE WHEN percentile <=60 THEN pctl_var END) AS percentile_60
, MAX(CASE WHEN percentile <=70 THEN pctl_var END) AS percentile_70
, MAX(CASE WHEN percentile <=80 THEN pctl_var END) AS percentile_80
, MAX(CASE WHEN percentile <=90 THEN pctl_var END) AS percentile_90
, MAX(CASE WHEN percentile <=100 THEN pctl_var END) AS percentile_100
from
(
select
,col
,pctl_Var

from
table1 as a
) as b
group by 1