BigQuery statistical aggregate functions: STDDEV, VARIANCE, CORR and median

The table used in every example

Paste this before any query below and it runs as is:

WITH daily AS (
  SELECT DATE '2026-10-01' AS day, 'ads' AS channel,
         100.0 AS spend, 10 AS orders
  UNION ALL SELECT DATE '2026-10-02', 'ads', 120.0, 13
  UNION ALL SELECT DATE '2026-10-03', 'ads', 90.0, 9
  UNION ALL SELECT DATE '2026-10-04', 'ads', 150.0, 16
  UNION ALL SELECT DATE '2026-10-05', 'ads', 130.0, 12
  UNION ALL SELECT DATE '2026-10-06', 'ads', NULL, 11
  UNION ALL SELECT DATE '2026-10-01', 'social', 40.0, 3
  UNION ALL SELECT DATE '2026-10-02', 'social', 60.0, 5
  UNION ALL SELECT DATE '2026-10-03', 'social', 50.0, 6
  UNION ALL SELECT DATE '2026-10-04', 'social', 80.0, 4
  UNION ALL SELECT DATE '2026-10-05', 'social', 70.0, 7
  UNION ALL SELECT DATE '2026-10-01', 'email', 20.0, 4
)

Daily ad spend and orders for three channels: six days of ads (the last day has no spend recorded), five days of social, and a single day of email.

STDDEV and VARIANCE: sample or population

SELECT
  channel,
  COUNT(*) AS days,
  ROUND(AVG(orders), 2) AS avg_orders,
  ROUND(STDDEV(orders), 3) AS stddev,
  ROUND(STDDEV_SAMP(orders), 3) AS stddev_samp,
  ROUND(STDDEV_POP(orders), 3) AS stddev_pop,
  ROUND(VARIANCE(orders), 3) AS variance,
  ROUND(VAR_POP(orders), 3) AS var_pop
FROM daily
GROUP BY channel
ORDER BY days DESC;

Three things in that table.

STDDEV and STDDEV_SAMP are the same column. Google’s reference calls STDDEV “an alias of the STDDEV_SAMP function”, and VARIANCE an alias of VAR_SAMP (read in October 2026). Neither guesses whether your data is a sample.

The gap between sample and population is the n - 1. Sample variance divides by n - 1, population variance by n. For ads, 5.139 × 6 / 5 = 6.167. With six rows the difference is 20%; with six thousand it disappears. Use the population versions when the rows are everything you are describing (all orders last month), and the sample versions when they stand for something larger (a test group, last month as a guide to next month).

One row gives NULL for the sample functions and 0 for the population ones. Email has one day. A sample standard deviation of one value is undefined, so BigQuery returns NULL; a population of one has no spread, so it returns 0. Not NaN, and not an error. If a dashboard averages these per channel, a NULL drops the channel out, while a 0 pulls the average down.

CORR, COVAR_SAMP and COVAR_POP

SELECT
  channel,
  COUNTIF(spend IS NOT NULL AND orders IS NOT NULL) AS pairs,
  ROUND(CORR(spend, orders), 3) AS corr,
  ROUND(COVAR_SAMP(spend, orders), 2) AS covar_samp,
  ROUND(COVAR_POP(spend, orders), 2) AS covar_pop
FROM daily
GROUP BY channel
ORDER BY pairs DESC;

Ads has six days but five pairs. CORR and the covariance functions drop any row where either value is NULL, so the day with no spend is not used at all. Count the pairs, as above, before you trust the number.

CORR is the one to read. It is Pearson’s coefficient: 0.956 for ads means orders rose almost in step with spend over these five days; 0.3 for social means a weak link. Correlation has no units, so it is comparable across channels.

Covariance carries the units of both columns. The same ads data with spend in thousands:

SELECT
  ROUND(CORR(spend / 1000, orders), 3) AS corr_in_thousands,
  ROUND(COVAR_SAMP(spend / 1000, orders), 4) AS covar_in_thousands
FROM daily
WHERE channel = 'ads';

The correlation did not move; the covariance fell from 62.5 to 0.0625. Covariance tells you the direction (positive or negative) but its size depends on the scale, so do not compare it across metrics. It is mostly an input to other calculations, such as a regression slope: COVAR_POP(x, y) / VAR_POP(x).

NULL, NaN and constant columns

SELECT
  COUNT(spend) AS spend_values,
  ROUND(STDDEV(spend), 2) AS stddev_skip_null,
  ROUND(STDDEV(COALESCE(spend, 0)), 2) AS stddev_null_as_zero,
  STDDEV(IFNULL(spend, IEEE_DIVIDE(0, 0))) AS stddev_with_nan,
  CORR(spend, 1) AS corr_constant
FROM daily
WHERE channel = 'ads';

• NULLs are skipped. The standard deviation of ads spend uses five values.
• Turning NULL into 0 more than doubles it, from 23.87 to 52.69, because a zero-spend day is far from the others. Decide what the missing day means before you fill it. The COALESCE guide covers the options.
• One NaN makes the whole result NaN. IEEE_DIVIDE(0, 0) produces a NaN; once it is in the column, the standard deviation is NaN. The same happens with infinity. Filter with WHERE NOT IS_NAN(x) first.
• CORR against a column that never changes returns NaN. Google’s reference lists this case: NaN when the variance of either input is 0 (read in October 2026). A campaign with the same budget every day will do this.

The standard deviation and variance functions also accept DISTINCT in the aggregate form, which you rarely want: STDDEV(DISTINCT orders) measures the spread of the distinct values, not of the days.

CORR and STDDEV as window functions

Every statistical aggregate takes an OVER clause, with PARTITION BY, ORDER BY and a frame. That gives a rolling correlation:

SELECT
  day,
  spend,
  orders,
  ROUND(CORR(spend, orders) OVER (
    PARTITION BY channel ORDER BY day
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 3) AS corr_3day,
  ROUND(CORR(spend, orders) OVER (
    PARTITION BY channel ORDER BY day), 3) AS corr_to_date
FROM daily
WHERE channel = 'ads'
ORDER BY day;

Small windows lie. Two points always lie on a straight line, so any window with exactly two valid pairs returns 1 or -1 (or NaN, if the two values of one column are equal). That is what happened on 2 October, and again on 6 October, where the 3-day window had three rows but only two pairs because spend is NULL. A rolling correlation needs a window much wider than three rows, and a pair count next to it.

With ORDER BY and no frame, the window runs from the first row to the current one, plus any rows tied with it on the ORDER BY value (Google’s default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW); here every day is unique, so corr_to_date on the last day equals the full-period 0.956 from the aggregate query.

The same pattern turns STDDEV into an outlier check. A z-score is how many standard deviations a day is from its channel’s average:

SELECT
  channel,
  day,
  orders,
  ROUND(SAFE_DIVIDE(
    orders - AVG(orders) OVER w,
    STDDEV(orders) OVER w), 2) AS z_score
FROM daily
WHERE channel IN ('ads', 'email')
WINDOW w AS (PARTITION BY channel)
ORDER BY channel, day;

4 October, with 16 orders, is the furthest from normal for ads. Email’s z-score is NULL because its standard deviation is NULL, and dividing by NULL gives NULL with plain / too. SAFE_DIVIDE is there for the other case: a channel whose values are all equal has a standard deviation of 0, and / would fail with “division by zero”. Two limits of the window form: DISTINCT is not allowed (STDDEV(DISTINCT x) OVER () fails with “Analytic function stddev does not support DISTINCT”), and to filter on the result you need QUALIFY, see WHERE, HAVING and QUALIFY. More on frames in the window functions guide.

Median: APPROX_QUANTILES or PERCENTILE_CONT

BigQuery has no MEDIAN. There are two ways to get one, and they answer slightly differently.

As an aggregate, with GROUP BY: APPROX_QUANTILES(x, n) returns an array of n + 1 boundaries, from the minimum to the maximum. With n = 2 the middle element is the median.

SELECT
  channel,
  APPROX_QUANTILES(orders, 2) AS quantiles,
  APPROX_QUANTILES(orders, 2)[OFFSET(1)] AS approx_median,
  APPROX_QUANTILES(spend, 4) AS spend_quartiles
FROM daily
GROUP BY channel
ORDER BY channel;

The ads spend quartiles are built from five values: the NULL day is ignored. APPROX_QUANTILES is, as the name says, approximate: on a table this small it is exact, on millions of rows it is close but can differ from the true value. It always returns a value that is in the data, never one in between.

As a window function: PERCENTILE_CONT(x, 0.5) is the exact median, interpolated. It works only with OVER; called as a plain aggregate it fails with “percentile_cont aggregate function is not supported”. To get one row per channel, add DISTINCT:

SELECT DISTINCT
  channel,
  PERCENTILE_CONT(orders, 0.5) OVER (PARTITION BY channel) AS median_cont,
  PERCENTILE_DISC(orders, 0.5) OVER (PARTITION BY channel) AS median_disc
FROM daily
ORDER BY channel;

Ads has an even number of days (9, 10, 11, 12, 13, 16), so the true median sits between 11 and 12. PERCENTILE_CONT returns 11.5; PERCENTILE_DISC and APPROX_QUANTILES return 11, a value that exists in the column. Pick PERCENTILE_CONT when you report “the median” and need it to match a spreadsheet’s MEDIAN; pick APPROX_QUANTILES for large tables, where an exact window over every row costs more. The OVER clause of PERCENTILE_CONT takes only PARTITION BY: no ORDER BY and no frame (read in October 2026), so there is no rolling median this way. Both functions sit with the other navigation functions.

Which one to use

• Spread of a metric in a report: STDDEV_POP if the rows are the whole period you describe, STDDEV if they are a sample. Write down which, because two analysts will otherwise pick differently and their numbers will not match.
• Do two metrics move together: CORR, with the pair count next to it. Skip covariance unless a formula asks for it.
• Typical value with outliers: the median, not the mean. PERCENTILE_CONT for an exact figure, APPROX_QUANTILES for large tables.
• Spotting unusual days: a z-score from AVG and STDDEV over a window.

For COUNT, SUM, AVG and the other basic aggregates, see BigQuery aggregate functions. All function families are mapped in the BigQuery functions overview.

Keeping the definition in one place

A standard deviation in one report and a different one in another is rarely a bug in the function. It is one query using STDDEV, another using STDDEV_POP, and a third filling NULL spend with zero. In OWOX Data Marts the query that makes those choices is published once as a Data Mart, with the choice written in the column’s description, and every report reads that one data model, whether in Google Sheets or Data Studio. OWOX Data Marts runs your SQL as written, in your own BigQuery project, and does not transform the data.

Google BigQueryBigQuery Visualization Tools: Data Studio, Looker, Sheets, Power BI, Tableau and Excel ComparedOctober 16, 2026→Google BigQueryHow to Load Data into BigQuery: Every Method and When to Use ItOctober 6, 2026→Google BigQueryHow to Connect BigQuery to Google Sheets: Three WaysOctober 6, 2026→

See all articles →