Outdated Version

You are viewing an older version of this section. View current production version.

# PERCENTILE_DISC

In SingleStore DB, percentile functions are available as window functions and aggregate functions. There are two percentile functions: PERCENTILE_CONT 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 DB, 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 sets (a INT, b INT, c INT);

INSERT INTO sets VALUES (1,1,2),(2,2,3),(3,1,2),(4,2,4),(5,1,1),(2,1,1),(null,null,3);

SELECT * FROM sets;
****
+------+------+------+
| a    | b    | c    |
+------+------+------+
| NULL | NULL |    3 |
|    1 |    1 |    2 |
|    2 |    1 |    1 |
|    2 |    2 |    3 |
|    3 |    1 |    2 |
|    4 |    2 |    4 |
|    5 |    1 |    1 |
+------+------+------+
``````
``````SELECT PERCENTILE_DISC(0.5)
WITHIN GROUP (ORDER BY a)
FROM 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 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 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 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 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 accounts(account_id INT, category VARCHAR(30), balance DECIMAL(18,2));
``````
``````INSERT INTO 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_median
FROM accounts;
****
+------------+----------+-----------+-----------------+
| account_id | category | balance   | discrete_median |
+------------+----------+-----------+-----------------+
|          7 | CD       |    100.00 |          100.00 |
|          6 | CD       |   1500.00 |          100.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 |
|          5 | Savings  |    250.00 |         1000.00 |
|          2 | Savings  |   1000.00 |         1000.00 |
|          4 | Savings  | 654321.00 |         1000.00 |
+------------+----------+-----------+-----------------+
``````