Categories:

Aggregate Functions (General) , Window Functions

# PERCENTILE_DISC¶

Returns a percentile value based on a discrete distribution of the input column (specified in `order_by_expr`). The returned value is that whose row has the smallest CUME_DIST value that is greater than or equal to the given percentile. NULL values are ignored in the calculation.

PERCENTILE_CONT

## Syntax¶

Aggregate function

```PERCENTILE_DISC( <percentile> ) WITHIN GROUP (ORDER BY <order_by_expr> )
```

Window function

```PERCENTILE_DISC( <percentile> ) WITHIN GROUP (ORDER BY <order_by_expr> ) OVER ( [ PARTITION BY <expr3> ] )
```

## Arguments¶

`percentile`

The percentile of the value that you want to find. The percentile must be a constant between 0.0 and 1.0. For example, if you want to find the value at the 90th percentile, specify 0.9.

`order_by_expr`

The expression (typically a column name) by which to order the values. For example, if you want to want to find the student whose math SAT score is at the 90th percentile, you’ll specify the column containing the math SAT score.

Note that this is also implicitly the column from which the returned value is chosen; e.g. if you order by math SAT scores, then the result you’ll get is one of the math SAT scores. You can’t order by one column and get a percentile value for a different column.

`expr3`

This is the optional expression used to group rows into partitions.

## Returns¶

Returns the value that is at the specified percentile.

## Usage Notes¶

• The `percentile` argument to the function must be a constant.

• DISTINCT is not supported for this function.

• The function `PERCENTILE_CONT` interpolates between the two closest values, while the function `PERCENTILE_DISC` chooses the closest value rather than interpolating.

• When used as a window function:

• This function does not support:

• ORDER BY sub-clause in the OVER() clause.

• Window frames.

## Examples¶

The following example shows the values at the 25th percentile (0.25) within various groups:

Create and populate a table with values:

```create or replace table aggr(k int, v decimal(10,2));
insert into aggr (k, v) values
(0,  0),
(0, 10),
(0, 20),
(0, 30),
(0, 40),
(1, 10),
(1, 20),
(2, 10),
(2, 20),
(2, 25),
(2, 30),
(3, 60),
(4, NULL);
```

Run a query and show the output:

```select k, percentile_disc(0.25) within group (order by v)
from aggr
group by k
order by k;
+---+-------------------------------------------------+
| K | PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY V) |
|---+-------------------------------------------------|
| 0 |                                           10.00 |
| 1 |                                           10.00 |
| 2 |                                           10.00 |
| 3 |                                           60.00 |
| 4 |                                            NULL |
+---+-------------------------------------------------+
``` 