Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Example: Calculate the MEDIAN in BigQuery
# Calculate the MEDIAN in BigQuery; standard-sql; 2020-06-20
# @see http://www.pascallandau.com/bigquery-snippets/calculate-median/
WITH data as (
SELECT
1 as id,
"2020-06-20" as day,
10 as quantity
UNION ALL SELECT 2,"2020-06-20", 15
UNION ALL SELECT 1,"2020-06-21",5
UNION ALL SELECT 2,"2020-06-21",10
UNION ALL SELECT 1,"2020-06-22",9
UNION ALL SELECT 2,"2020-06-22",4
),
median_per_row as (
SELECT
*,
PERCENTILE_CONT(quantity, 0.5) OVER(PARTITION BY id) AS median,
FROM data
)
SELECT
id,
ARRAY_AGG((SELECT as STRUCT day, quantity) ORDER BY quantity) as quantities,
ANY_VALUE(median) as median,
FROM
median_per_row
GROUP BY
id
@paslandau
Copy link
Author

paslandau commented Jun 20, 2020

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment