Outdated Version

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