Skip to content

cdf.md

Latest commit

 

History

History
38 lines (32 loc) · 1.12 KB

cdf.md

File metadata and controls

38 lines (32 loc) · 1.12 KB

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
  (row_number() over (order by <quantity> asc)) / (select count(*) from <table>) cdf,
  <quantity>,
from <table>

where

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

Example

Using total Spotify streams as an example data source, let's identify:

  • table - this is called raw
  • quantity - this is the column streams

then the query becomes:

select
  (row_number() over (order by streams asc)) / (select count(*) from raw) frac,
  streams,
from raw
frac streams
0 374359
0 375308
... ...
1 7778950