Cumulative distribution functions.md

Cumulative distribution functions

Explore this snippet with some demo data here.

Description

Cumulative distribution functions (CDF) are a method for analysing the distribution of a quantity, similar to histograms. They show, for each value of a quantity, what fraction of rows are smaller or greater. One method for calculating a CDF is as follows:

select 
--  Use a row_number window function to get the position of this row and CAST to convert row_number and count from integers to decimal
CAST(row_number() over (order by <quantity> asc) AS DECIMAL)/CAST((select count(*) from <table>) AS DECIMAL) AS cdf
from <table>

where

• quantity - the column containing the metric of interest
• table - the table name

Note: CAST is used to convert the numerator and denominator of the fraction into decimals before they are divided.

Example

Using some student test scores as an example data source, let’s identify:

• table - this is called Example_data
• quantity - this is the column score

then the query becomes:

select 
student_id, 
score, 
CAST(row_number() over (order by score asc) AS DECIMAL)/CAST((select count(*) from Example_data) AS DECIMAL) AS frac
from Example_data

student_id	score	frac
3	76	0.2
5	77	0.2
4	82	0.6
...	...	...
1	97	1