Analytics

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic to the Top
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted
##
##### How to calculate 90th percentile in Teradata

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-07-2009
03:04 AM

10-07-2009
03:04 AM

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

unlike ORACLE

unlike ORACLE

2 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-07-2009
04:17 AM

10-07-2009
04:17 AM

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

;

So 90th percentile is:

Select

blah,blah,blah

From somewhere

Qualify Quantile(100,col1) = 90

;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-24-2009
12:33 PM

11-24-2009
12:33 PM

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

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

Copyright © 2004-2015 Teradata Corporation. Your use of this Teradata website is governed by the Privacy Policy and the Terms of Use, including your rights to materials on this website, the rights you grant to your submissions to this website, and your responsibilities regarding your conduct on this website.

The Privacy Policy and Terms of Use for this Teradata website changed effective September 8, 2016.