Name
PERCENTILE_CONT
Generates an interpolated value corresponding to a percentile that you specify.
ANSI SQL Standard Syntax
In the following syntax, percentile
is a number between 0 and 1:
PERCENTILE_CONT(percentile
) WITHIN GROUP (ORDER BYsort_list
)sort_list
::=sort_item
[,sort_item
...]sort_item
::=expression
[ASC | DESC] [NULLS FIRST | NULLS LAST]
Oracle
Oracle allows only one expression in the ORDER BY
clause:
PERCENTILE_CONT(percentile
) WITHIN GROUP (ORDER BYexpression
)
Oracle also allows some use of windowing syntax:
PERCENTILE_CONT(percentile
) WITHIN GROUP (ORDER BYsort_list
) OVER (partitioning
)
See “ANSI SQL Window Functions,” later in this chapter, for a description of partitioning.
MySQL, PostgreSQL, and SQL Server
These platforms do not implement PERCENTILE_CONT.
Example
The following example groups the data in test4 by the column named odd and invokes PERCENTILE_CONT to return a 50th-percentile value for each group:
SELECT * FROM test4;
NUM ODD ---------- ---------- 0 0 1 1 2 0 3 1 3 1 4 0 5 1SELECT odd, PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY NUM)
FROM test4 GROUP BY odd;
ODD PERCENTILE_CONT(0.50)WITHINGROUP(ORDERBYNUM) ---------- -------------------------------------------- 0 2 1 3
Get SQL in a Nutshell, 3rd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.