Important

Self-managed SingleStore will soon transition from version 9.1 RC to version 10. This new semantic versioning scheme will provide SingleStore with finer control over engine and feature releases that were not possible with the current versioning scheme.

In the interim, SingleStore 9.1 RC can be used to preview, evaluate, and provide feedback on the new and upcoming features in SingleStore 10 prior to its general availability. Ahead of this transition, SingleStore 9.0 is recommended for production workloads, which can later be upgraded to SingleStore 10.

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, refer to Working with Window Functions.

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

SingleStore supports two percentile functions: PERCENTILE_CONT and MEDIAN and PERCENTILE_DISC.

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.

SQL

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);

SQL

SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY a) FROM perdisc_sets;

+------------------------------------------------+
| PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY a) |
+------------------------------------------------+
|                                              2 |
+------------------------------------------------+

SQL

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 |
+------+------------+

SQL

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.

SQL

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.

SQL

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.

SQL

CREATE TABLE perdisc_accounts(account_id INT, category VARCHAR(30), balance DECIMAL(18,2));

SQL

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);

SQL

SELECT account_id, category, balance,
PERCENTILE_DISC(.5)
WITHIN GROUP (ORDER BY balance)
OVER (PARTITION BY category)
AS discrete_median
FROM 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 |
+------------+----------+-----------+-----------------+