Redshift MEDIAN in Snowflake, Databricks and BigQuery
Snowflake and Databricks both have MEDIAN as an aggregate. BigQuery does not: use APPROX_QUANTILES for an approximate median in GROUP BY queries, or PERCENTILE_CONT as a window function for an exact one.
Redshift function: MEDIAN(median_expression) · Redshift docs
Equivalents
| Target | Support | Syntax | Notes | Source |
|---|---|---|---|---|
| Snowflake | Same name | MEDIAN(expr) | Aggregate or window function; with an even count it returns the average of the two middle values. | Snowflake docs |
| Databricks | Same name | median([ALL | DISTINCT] expr) | Databricks SQL and Databricks Runtime 11.3 LTS and above; also usable as a window function. | Databricks docs |
| BigQuery | Rewrite | APPROX_QUANTILES(expr, 2)[OFFSET(1)] | Approximate. For an exact median use PERCENTILE_CONT(expr, 0.5) OVER (PARTITION BY ...), which is a window function only. | BigQuery docs |
Behavior differences to check
BigQuery
- PERCENTILE_CONT cannot be used in GROUP BY; compute it in a window over the group and then take one row per group, for example with QUALIFY or SELECT DISTINCT.
Examples
Median order value per store
SELECT store_id, MEDIAN(amount) AS median_amount FROM orders GROUP BY store_id;SELECT store_id, MEDIAN(amount) AS median_amount FROM orders GROUP BY store_id;SELECT store_id, median(amount) AS median_amount FROM orders GROUP BY store_id;SELECT DISTINCT store_id, PERCENTILE_CONT(amount, 0.5) OVER (PARTITION BY store_id) AS median_amount FROM orders;