All Forums Analytics
Navnit 2 posts Joined 10/09
07 Oct 2009
How to calculate 90th percentile in Teradata

Seems no build in available in tera data to calculate 90th or nth percentile for the given datasetunlike ORACLE

Jimm 298 posts Joined 09/07
07 Oct 2009

Teradata is slightly more generic - it uses QUANTILE which allows you to split into groups based on any fraction.So 90th percentile is:Selectblah,blah,blahFrom somewhereQualify Quantile(100,col1) = 90;

c@lib3ar 7 posts Joined 10/09
24 Nov 2009

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_100from( select ,col ,pctl_Var from table1 as a) as bgroup by 1

You must sign in to leave a comment.