15.00 - PERCENTILE_CONT / PERCENTILE_DISC - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)
Last Update
2018-09-24

PERCENTILE_CONT / PERCENTILE_DISC

Purpose  

Returns an interpolated value that falls within its value_expression with respect to its sort specification.

Type

PERCENTILE_CONT and PERCENTILE_DISC are aggregate function.

Syntax  

where:

 

Syntax element …

Specifies …

value_expression_1

a numeric value between 0 and 1 inclusive.

WITHIN GROUP

the group over which the function operates.

ORDER BY

the order in which the values in a group or partition are sorted.

value_expression_2

a single expression that must be a numeric value.

ASC

that the results are to be ordered in ascending sort order.

If the sort field is a character string, the system orders it in ascending order according to the definition of the collation sequence for the current session.

The default order is ASC.

DESC

that the results are to be ordered in descending sort order.

If the sort field is a character string, the system orders it in descending order according to the definition of the collation sequence for the current session.

NULLS FIRST

that NULL results are to be listed first.

NULLS LAST

that NULL results are to be listed last.

ANSI Compliance

PERCENTILE_CONT and PERCENTILE_DISC are Teradata extensions to the ANSI SQL:2011 standard.

Result

The function returns the same data type as the data type of the argument.

Nulls are ignored in the calculation.

Usage Notes

Both functions are inverse distribution functions that assume a continuous distribution.

  • PERCENTILE_CONT returns a computed result after doing linear interpolation.
  • PERCENTILE_DISC simply returns a value from the set of values.
  • Example  

    Using this table:

     

    Area

    Address

    Price

    Downtown

    72 Easy Street

    509000

    Downtown

    29 Right Way

    402000

    Downtown

    45 Diamond Lane

    203000

    Downtown

    76 Blind Alley

    201000

    Downtown

    15 Tern Pike

    199000

    Downtown

    444 Kanga Road

    102000

    Uptown

    15 Peak Street

    456000

    Uptown

    27 Primrose Path

    349000

    Uptown

    44 Shady Lane

    341000

    Uptown

    34 Design Road

    244000

    Uptown

    2331 Highway 64

    244000

    Uptown

    77 Sunset Strip

    102000

    the following SQL statement returns a computed result after doing linear interpolation, as shown in the table immediately below.

    SELECT area,
           AVG(price),
           PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY price),
           PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price)
    FROM market
    GROUP BY area;
     

    Area

    Average Price

    PERC_DISC

    PERC_CONT

    Downtown

    269333

    201000

    202000

    Uptown

    289333

    244000

    292500