Cloud

cume_dist

The cume_dist() function is a window function used to calculate the cumulative distribution of a value within a set of values. This function returns a value between 0 and 1, representing a relative position of a row within a partition or result set.

Syntax

The syntax for this function is:

CUME_DIST() OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

Parameters

  • (): This function does not take any arguments, but the parentheses are required.

Examples

The following example uses a simplified version of the film table from the Pagila database, containing only the title, length and rating columns.

DROP TABLE IF EXISTS film;
CREATE TABLE film (
  title text NOT NULL,
  length int,
  rating text
);

INSERT INTO film(title, length, rating) VALUES
  ('ATTRACTION NEWTON', 83, 'PG-13'),
  ('CHRISTMAS MOONSHINE', 150, 'NC-17'),
  ('DANGEROUS UPTOWN', 121, 'PG'),
  ('KILL BROTHERHOOD', 54, 'G'),
  ('HALLOWEEN NUTS', 47, 'PG-13'),
  ('HOURS RAGE', 122, 'NC-17'),
  ('PIANIST OUTFIELD', 136, 'NC-17'),
  ('PICKUP DRIVING', 77, 'G'),
  ('INDEPENDENCE HOTEL', 157, 'NC-17'),
  ('PRIVATE DROP', 106, 'PG'),
  ('SAINTS BRIDE', 125, 'G'),
  ('FOREVER CANDIDATE', 131, 'NC-17'),
  ('MILLION ACE', 142, 'PG-13'),
  ('SLEEPY JAPANESE', 137, 'PG'),
  ('WRATH MILE', 176, 'NC-17'),
  ('YOUTH KICK', 179, 'NC-17'),
  ('CLOCKWORK PARADISE', 143, 'PG-13');

The following query uses the cume_dist() function to calculate the cumulative distribution of film lengths:

SELECT
    title,
    length,
    CUME_DIST() OVER (ORDER BY length) AS cume_dist
FROM film;

The query returns:

        title        | length |      cume_dist
---------------------+--------+----------------------
 HALLOWEEN NUTS      |     47 | 0.058823529411764705
 KILL BROTHERHOOD    |     54 |  0.11764705882352941
 PICKUP DRIVING      |     77 |  0.17647058823529413
 ATTRACTION NEWTON   |     83 |  0.23529411764705882
 PRIVATE DROP        |    106 |  0.29411764705882354
 DANGEROUS UPTOWN    |    121 |  0.35294117647058826
 HOURS RAGE          |    122 |   0.4117647058823529
 SAINTS BRIDE        |    125 |  0.47058823529411764
 FOREVER CANDIDATE   |    131 |   0.5294117647058824
 PIANIST OUTFIELD    |    136 |   0.5882352941176471
 SLEEPY JAPANESE     |    137 |   0.6470588235294118
 MILLION ACE         |    142 |   0.7058823529411765
 CLOCKWORK PARADISE  |    143 |   0.7647058823529411
 CHRISTMAS MOONSHINE |    150 |   0.8235294117647058
 INDEPENDENCE HOTEL  |    157 |   0.8823529411764706
 WRATH MILE          |    176 |   0.9411764705882353
 YOUTH KICK          |    179 |                    1
(17 rows)