# PERCENTILE_DISC

In SingleStore Helios, percentile functions are available as window functions and aggregate functions.

There are two percentile functions: PERCENTILE_CONT and MEDIAN and `PERCENTILE_DISC`.

`PERCENTILE_DISC()` is an inverse distribution function that assumes a discrete distribution between values of the expression in the sort specification. It returns the first element whose sort position among all non-null rows is greater than or equal to the percentile. For an overview of window functions in SingleStore Helios, see Window Functions Guide.

## Syntax

`PERCENTILE_DISC(percentile)WITHIN GROUP (ORDER BY (col | expr))[OVER (    [PARTITION BY (col | expr), ...]    [frame_clause])]`

or equivalently

`PERCENTILE_DISC(percentile)OVER (        ORDER BY (col | expr)        [PARTITION BY {col | expr}, ...]        [frame_clause])`

## Arguments

percentile

A numeric value between 0 and 1.

col

A column of any column data type.

expr

An expression that evaluates to any column data type.

## Remarks

`PERCENTILE_DISC()` can be used as a window function as well as an aggregate function.

The `OVER()` clause defines how to partition the input set. If no `PARTITION BY` clause is specified, the entire input set is treated as a single partition.

Only one column or expression is allowed in the `ORDER BY` clause, and this column is the one for which the percentile is calculated.

## Examples

Example 1

The following example demonstrates the use of `PERCENTILE_DISC` as an aggregate function.

```CREATE TABLE perdisc_sets (a INT, b INT, c INT);
INSERT INTO perdisc_sets VALUES (1,1,2),(2,2,3),(3,1,2),(4,2,4),(5,1,1),(2,1,1),(null,null,3);```
`SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY a) FROM perdisc_sets;`
``````+------------------------------------------------+
| PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY a) |
+------------------------------------------------+
|                                              2 |
+------------------------------------------------+``````
`SELECT c, PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY a) AS "PERCENTILE" FROM perdisc_sets GROUP BY c ORDER BY c;`
``````+------+------------+
| c    | PERCENTILE |
+------+------------+
|    1 |          2 |
|    2 |          1 |
|    3 |          2 |
|    4 |          4 |
+------+------------+``````
`SELECT PERCENTILE_DISC(0.25)WITHIN GROUP (ORDER BY a) AS "PERCENTILE"FROM perdisc_sets;`
``````+------------+
| PERCENTILE |
+------------+
|          2 |
+------------+``````

The percentile of a data set may change depending on how it is ordered.

`SELECT PERCENTILE_DISC(0.25)WITHIN GROUP (ORDER BY a DESC) AS "PERCENTILE"FROM perdisc_sets;`
``````+------------+
| PERCENTILE |
+------------+
|          4 |
+------------+``````

Multiple `PERCENTILE_DISC` functions can be used in a single query, and each function must have its own `WITHIN GROUP` clause.

`SELECT PERCENTILE_DISC(0.25)       WITHIN GROUP (ORDER BY a) AS "PERCENTILE a",       PERCENTILE_DISC(0.25)       WITHIN GROUP (ORDER BY b) AS "PERCENTILE b"FROM perdisc_sets;`
``````+--------------+--------------+
| PERCENTILE a | PERCENTILE b |
+--------------+--------------+
|            2 |            1 |
+--------------+--------------+``````

Example 2

The following example creates a table containing accounts, the account type, and the current balance of each. The resulting window frame shows the discrete median of the balance for each account type.

`CREATE TABLE perdisc_accounts(account_id INT, category VARCHAR(30), balance DECIMAL(18,2));`
`INSERT INTO perdisc_accounts VALUES    (1, "Checking", 100.01), (2, "Savings", 1000.00),    (3, "Checking", 12345.67), (4, "Savings", 654321.00), (null, "Checking", 2.00),    (5, "Savings", 250.00), (6, "CD", 1500.00), (7, "CD", 100.00), (8, "Checking", 80.80);`
`SELECT account_id, category, balance,PERCENTILE_DISC(.5)WITHIN GROUP (ORDER BY balance)OVER (PARTITION BY category)AS discrete_medianFROM perdisc_accounts;`
``````+------------+----------+-----------+-----------------+
| account_id | category | balance   | discrete_median |
+------------+----------+-----------+-----------------+
|          7 | CD       |    100.00 |          100.00 |
|          6 | CD       |   1500.00 |          100.00 |
|          5 | Savings  |    250.00 |         1000.00 |
|          2 | Savings  |   1000.00 |         1000.00 |
|          4 | Savings  | 654321.00 |         1000.00 |
|       NULL | Checking |      2.00 |           80.80 |
|          8 | Checking |     80.80 |           80.80 |
|          1 | Checking |    100.01 |           80.80 |
|          3 | Checking |  12345.67 |           80.80 |
+------------+----------+-----------+-----------------+``````