15.00 - PERCENTILE_CONT / PERCENTILE_DISC

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

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

PERCENTILE_CONT and PERCENTILE_DISC are aggregate function.

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.

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

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

Nulls are ignored in the calculation.

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.

Using this table:

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;