PostgreSQL Statistical Aggregate Functions

Use `stddev_pop` when your data IS the entire population. Use `stddev_samp` (or `stddev`) when your data is a sample. For small samples, the difference is significant.

SELECT stddev_pop(score) AS pop_sd, stddev_samp(score) AS sample_sd FROM scores;

Sample standard deviation requires at least 2 values. With only 1 row in a group, `stddev` returns NULL. Use `COALESCE(stddev(x), 0)` if you need 0 instead of NULL for single-row groups.

SELECT group_id, COALESCE(stddev(value), 0) AS spread FROM measurements GROUP BY group_id;

`var_pop(x) = stddev_pop(x)^2`. Use variance when composing statistics (variances add under independence) and convert to std dev for human-readable output.

SELECT sqrt(var_pop(latency)) AS stddev_check, stddev_pop(latency) AS direct FROM perf_logs;

The coefficient of variation (CV = stddev/mean) tells you relative variability. Compute it with `stddev(x) / nullif(avg(x), 0)` — useful for comparing variability across different scales.

SELECT stddev(revenue) / nullif(avg(revenue), 0) AS cv FROM monthly_sales;

Use `corr()` to quickly scan for relationships between columns. A correlation near 0 means no linear relationship. Values above 0.7 or below -0.7 usually warrant further investigation.

SELECT corr(load_avg, response_time) AS load_vs_latency, corr(connection_count, response_time) AS conn_vs_latency FROM server_metrics;

Use `regr_slope(y, x) * future_x + regr_intercept(y, x)` to forecast values in-database. This is a lightweight alternative to exporting data to a separate analytics tool.

SELECT regr_slope(revenue, week_num) * 52 + regr_intercept(revenue, week_num) AS projected_annual FROM weekly_sales;

Retrieve slope, intercept, and R² together: `SELECT regr_slope(y,x), regr_intercept(y,x), regr_r2(y,x)`. The R² value tells you how well the line fits (0 = no fit, 1 = perfect fit).

SELECT regr_slope(price, sqft) AS slope, regr_intercept(price, sqft) AS base, regr_r2(price, sqft) AS r_squared FROM homes;

An R² of 0.8 means 80% of the variance in Y is explained by X. Note: a high R² does not guarantee the relationship is causal or that the linear model is appropriate.

SELECT feature, regr_r2(churn::int, feature_value) AS predictive_power FROM feature_correlations ORDER BY predictive_power DESC;

Always check `regr_count` alongside regression results. A slope computed from 3 data points is unreliable. Filter groups with too few data points using HAVING.

SELECT segment, regr_slope(ltv, age), regr_r2(ltv, age) FROM customers GROUP BY segment HAVING regr_count(ltv, age) >= 30;

`regr_avgx(Y, X)` is not the same as `avg(X)` — it only includes rows where both Y and X are non-null. Use it when you need means computed over the same paired dataset used for regression.

SELECT avg(ad_spend) AS all_avg, regr_avgx(revenue, ad_spend) AS paired_avg FROM campaigns;

`regr_slope = regr_sxy / regr_sxx` and `corr = regr_sxy / sqrt(regr_sxx * regr_syy)`. Having these building blocks allows you to compute weighted or conditional regression variants in SQL.

SELECT regr_sxy(y, x) / nullif(sqrt(regr_sxx(y, x) * regr_syy(y, x)), 0) AS manual_corr FROM data;

`corr(y, x) = covar_pop(y, x) / (stddev_pop(y) * stddev_pop(x))`. Covariance alone is hard to interpret because its magnitude depends on the scales of Y and X. Prefer `corr()` for interpretable results.

SELECT covar_pop(revenue, spend) / nullif(stddev_pop(revenue) * stddev_pop(spend), 0) AS manual_corr, corr(revenue, spend) AS builtin_corr FROM campaigns;

Pass an array of fractions to get multiple percentiles in one pass: `percentile_cont(ARRAY[0.1,0.25,0.5,0.75,0.9,0.99]) WITHIN GROUP (ORDER BY latency)` gives a complete distribution summary without scanning the table multiple times.

SELECT percentile_cont(ARRAY[0.5, 0.95, 0.99]) WITHIN GROUP (ORDER BY response_ms) AS p50_p95_p99 FROM api_requests;

`percentile_disc` works on any sortable type (text, date, enum). `percentile_cont` requires numeric types (it interpolates). Use `percentile_disc` when you need an actual value from the dataset.

SELECT percentile_disc(0.5) WITHIN GROUP (ORDER BY created_at) AS median_signup_date FROM users;

Instead of `SELECT val FROM t GROUP BY val ORDER BY count(*) DESC LIMIT 1`, use `mode() WITHIN GROUP (ORDER BY val)` — it's one aggregation step and composes cleanly in GROUP BY queries.

SELECT user_id, mode() WITHIN GROUP (ORDER BY category) AS favorite_category FROM purchases GROUP BY user_id;

Use `cume_dist(value) WITHIN GROUP (ORDER BY col)` to answer 'what fraction of values are <= this?'. For '1 in N' framing, use `rank(value) WITHIN GROUP (ORDER BY col)` against the total count.

SELECT cume_dist(75) WITHIN GROUP (ORDER BY score) FROM test_results;

If three rows tie for rank 2, `rank()` assigns 2,2,2 then jumps to 5. `dense_rank()` assigns 2,2,2 then 3. Use `dense_rank` when the count of distinct rank positions matters more than the exact gap.

SELECT rank(90) WITHIN GROUP (ORDER BY score DESC) AS with_gaps, dense_rank(90) WITHIN GROUP (ORDER BY score DESC) AS no_gaps FROM scores;

A result of 0.75 means the hypothetical value ranks higher than 75% of the group. Unlike `cume_dist`, `percent_rank` uses (rank-1)/(N-1) so the lowest value always gets 0.

SELECT percent_rank(100) WITHIN GROUP (ORDER BY test_score) FROM students;

Unlike `percent_rank`, `cume_dist` computes rank/N so the minimum value in the group gets 1/N (not 0). Use it when 'what fraction of data is at or below X' is the right question.

SELECT cume_dist(now() - interval '30 days') WITHIN GROUP (ORDER BY last_login) FROM users;