Warning
SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.
PERCENTILE_ DISC
On this page
In SingleStore, percentile functions are available as window functions and aggregate functions.
There are two percentile functions: PERCENTILE_PERCENTILE_
.
PERCENTILE_
is an inverse distribution function that assumes a discrete distribution between values of the expression in the sort specification.
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_
can be used as a window function as well as an aggregate function.
The OVER()
clause defines how to partition the input set.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_
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_
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.
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 |
+------------+----------+-----------+-----------------+
Last modified: February 16, 2023