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 BY sort_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 BY expression)

Oracle also allows some use of windowing syntax:

PERCENTILE_CONT(percentile) WITHIN GROUP
(ORDER BY sort_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          1

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