SQLite CUME_DIST() Function

Summary: in this tutorial, you will learn how to use the SQLite CUME_DIST() function to calculate the cumulative distribution of a value within a group of values.

Introduction to SQLite CUME_DIST() Function

The CUME_DIST() is a window function that returns the cumulative distribution of a value relative to the values in the group.

Here’s the syntax of the CUME_DIST() function:

CUME_DIST() OVER (
  [PARTITION BY partition_expression]
  [ORDER BY order_list]
)Code language: SQL (Structured Query Language) (sql)

In this syntax:

• The PARTITION BY clause specifies how the rows are grouped into partitions to which the CUME_DIST() function applies. If you skip the PARTITION BY clause, the function treats the whole result set as a single partition.
• The ORDER BY clause specifies the order of rows in each partition to which the CUME_DIST() function applies. If you omit the ORDER BY clause, the function returns 1 for all rows.

Suppose N is the value of the current row of the column specified in the ORDER BY clause and the order of rows is from low to high, the cumulative distribution of a value is calculated using the following formula:

The number of rows with values <= N / The number of rows in the window or partition
Code language: SQL (Structured Query Language) (sql)

The return value of the CUME_DIST() function is greater than 0 and less than or equal to 1:

0 < CUME_DIST() <= 1Code language: SQL (Structured Query Language) (sql)

The rows with the same values receive the same result.

SQLite CUME_DIST() function example

First, create a new table named products for the demonstration:

CREATE TABLE products(
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    color TEXT NOT NULL,
    price REAL NOT NULL 
);Code language: SQL (Structured Query Language) (sql)

Second, insert some rows into the products table:

INSERT INTO products
   (name, color, price)
VALUES
   ('A', 'red', 100),
   ('B', 'red', 200),
   ('C', 'red', 200),
   ('D', 'black', 300),
   ('E', 'black', 400),
   ('F', 'white', 500);Code language: SQL (Structured Query Language) (sql)

Third, query data from the products table:

SELECT * FROM products;Code language: SQL (Structured Query Language) (sql)

Output:

id | name | color | price
---+------+-------+------
1  | A    | red   | 100.0
2  | B    | red   | 200.0
3  | C    | red   | 200.0
4  | D    | black | 300.0
5  | E    | black | 400.0
6  | F    | white | 500.0

(6 rows)

Fourth, calculate the cumulative distribution of the price in the products table:

SELECT
  name,
  CUME_DIST() OVER (
    ORDER BY
      price
  ) PriceCumulativeDistribution
FROM
  products;Code language: SQL (Structured Query Language) (sql)

Here is the output:

name | PriceCumulativeDistribution
-----+----------------------------
A    | 0.16666666666666666
B    | 0.5
C    | 0.5
D    | 0.6666666666666666
E    | 0.8333333333333334
F    | 1.0

(6 rows)

Because we skipped the PARTITION BY clause, the function treated the whole result set as a single partition. Therefore, the number of rows to be evaluated is 6.

The following example uses the CUME_DIST() function to calculate the cumulative distribution of prices partitioned by colors:

SELECT
  name,
  color,
  price,
  CUME_DIST() OVER (
    PARTITION BY color
    ORDER BY price
  ) PriceCumulativeDistribution
FROM
  products
ORDER BY color;Code language: SQL (Structured Query Language) (sql)

Output:

name | color | price | PriceCumulativeDistribution
-----+-------+-------+----------------------------
D    | black | 300.0 | 0.5
E    | black | 400.0 | 1.0
A    | red   | 100.0 | 0.3333333333333333
B    | red   | 200.0 | 1.0
C    | red   | 200.0 | 1.0
F    | white | 500.0 | 1.0

(6 rows)

Summary

• Use the CUME_DIST() function to calculate the cumulative distribution of value in a set of values.