SQL Reference ยท Window Functions
SQL PERCENT_RANK() and CUME_DIST()
Reference for SQL PERCENT_RANK() and CUME_DIST(): relative rank and cumulative distribution as fractions between 0 and 1, formulas, ties and percentile use.
PERCENT_RANK() and CUME_DIST() express a row's position in its window as a fraction. They answer questions such as "what share of employees earn less than this person" without you having to count rows yourself. Both are ordered by the window's ORDER BY and restart in every partition.
They differ in the question they answer. PERCENT_RANK() measures how far a row is along the ranking, from 0 for the first rank up to 1 for the last. CUME_DIST() measures the share of rows that sort at or before the current row, so it is never 0 and the last row is always 1.
SELECT first_name, salary, PERCENT_RANK() OVER (ORDER BY salary) AS pct_rank, CUME_DIST() OVER (ORDER BY salary) AS cume_dist FROM employees;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
PERCENT_RANK() OVER ([PARTITION BY ...] ORDER BY expression [ASC | DESC] [, ...]) CUME_DIST() OVER ([PARTITION BY ...] ORDER BY expression [ASC | DESC] [, ...])
Let N be the number of rows in the partition, rank the value RANK() would give, and peers the number of rows that sort at or before the current row (including all ties):
| Function | Formula | Range |
|---|---|---|
PERCENT_RANK() |
(rank - 1) / (N - 1) |
0 to 1 (0 for a single-row partition) |
CUME_DIST() |
peers / N |
greater than 0, up to 1 |
Returns: a floating-point number (double precision, or NUMBER in Oracle). Neither function takes arguments.
Dialect differences
| Feature | SQLite | PostgreSQL | MySQL | SQL Server | Oracle |
|---|---|---|---|---|---|
PERCENT_RANK(), CUME_DIST() windowed |
3.25+ | 8.4+ | 8.0+ | 2012+ | Yes |
Hypothetical-set form (WITHIN GROUP) |
No | Yes | No | No | Yes |
PERCENTILE_CONT / PERCENTILE_DISC |
No | Yes (ordered-set) | No | Yes (as window function) | Yes |
-- PostgreSQL, Oracle: fraction of salaries at or below 90000 without storing a row SELECT CUME_DIST(90000) WITHIN GROUP (ORDER BY salary) FROM employees; -- PostgreSQL: the median salary SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY salary) FROM employees;
Usage notes
PERCENT_RANK()is based onRANK(), so tied rows share a value and the next distinct value jumps ahead. It starts at exactly 0 and ends at exactly 1 (for two or more rows).CUME_DIST()counts ties together. All peers share the same value, which is the share of rows up to and including the last peer.- For "top X percent" filters,
CUME_DIST()is simple: withORDER BY salary,cume_dist > 0.8keeps the highest paid fifth or a bit more, depending on ties. - These are not percentages. Multiply by 100 or format them for display.
- The sort direction decides everything. Order ascending and a high value means "high in the list"; order descending and it means "near the top".
- Single-row partitions:
PERCENT_RANK()returns 0 to avoid dividing by zero;CUME_DIST()returns 1. - Floating-point output: round it for display, and do not compare to a decimal literal with
=. - When you need buckets, use
NTILE()instead; for a real median use the percentile functions where they exist.