You are viewing an older version of this section. View current production version.
PERCENTILE_DISC
In MemSQL, percentile functions are only available as window 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 MemSQL, 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 only be used as a window 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.
Example
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.
memsql> CREATE TABLE accounts(account_id INT, category VARCHAR(30), balance DECIMAL(18,2));
memsql> 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);
memsql> 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 |
+------------+----------+-----------+-----------------+
