Explore this snippet with some demo data here .
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>
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.
Using some student test scores as an example data source, let’s identify:
table- this is called
quantity- this is the
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