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.
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 |