PostgreSQL Statistical Aggregate Functions

`corr()` returns NULL when either variable has zero standard deviation (all values identical) — failing to guard against this produces silent NULLs in dashboards; use `NULLIF(stddev_pop(x), 0)` checks before relying on the result. Treating a high Pearson correlation as evidence of a linear relationship without inspecting the data — a non-linear relationship or outliers can produce misleadingly high or low `corr()` values. Also: Passing arguments in the wrong order: `corr(X, Y)` vs `corr(Y, X)` — the result is the same numerically (Pearson is symmetric) but the convention `corr(Y, X)` matches the regression functions `regr_slope(Y, X)`; mixing conventions causes confusion. Also: Using `corr()` on time-series data without accounting for autocorrelation — two independently trending series will show high correlation even with no real relationship (spurious correlation).

✓ Instead: See description and related functions for the correct approach.

Interpreting covariance magnitude directly to compare relationships across different variable scales — a covariance of 1000 between height(cm) and weight(kg) says nothing about 'strength' compared to covariance between other pairs; always normalize to correlation. Using `covar_pop` on sampled data instead of `covar_samp`, producing a downward-biased estimate (divides by N instead of N-1). Also: Not protecting the normalization formula `covar_pop(y,x) / (stddev_pop(y) * stddev_pop(x))` against division by zero when either variable is constant — wrap with `NULLIF`. Also: Running separate queries for `covar_pop` and `stddev_pop` when a single SELECT computes all three aggregates in one table scan.

✓ Instead: See description and related functions for the correct approach.

Confusing `cume_dist(x)` with `percentile_cont(f)` — `cume_dist` takes a value and returns its fractional position; `percentile_cont` takes a fraction and returns the value at that position; they are inverses of each other. Expecting `cume_dist` to return 0.0 for the minimum value in the group — unlike `percent_rank`, `cume_dist` uses rank/N so the minimum gets 1/N, not 0; code that checks `cume_dist(...) = 0` will never be true. Also: Using `cume_dist` when the hypothetical value type does not match the `ORDER BY` expression type — implicit casts can produce unexpected sort order and an incorrect cumulative fraction. Also: Not accounting for tied values in `cume_dist` results — when the hypothetical value equals several existing rows, `cume_dist` counts all tied rows as <= the value, which can make the fraction jump discontinuously at tie points.

✓ Instead: See description and related functions for the correct approach.

Using `dense_rank()` when downstream logic relies on rank gaps to count tied entries — because `dense_rank` compresses gaps, you cannot infer how many rows share a rank without an additional count. Confusing hypothetical `dense_rank(val) WITHIN GROUP (ORDER BY col)` with the window function `DENSE_RANK() OVER (ORDER BY col)` — the hypothetical aggregate asks about a single external value's position, not each row's rank within a partition. Also: Choosing between `rank` and `dense_rank` without considering the business requirement: use `rank` when you need 'position in a competition with gaps' and `dense_rank` when you need 'number of distinct scores above this value'. Also: Not testing the tie boundary: if the hypothetical value itself ties with existing rows, `dense_rank` includes those ties at the same rank level, which may or may not be the intended behavior for threshold checks.

✓ Instead: See description and related functions for the correct approach.

Relying on `mode()` when the distribution is nearly uniform — in uniform distributions the 'mode' is arbitrary (the smallest among equally frequent values) and carries no analytical meaning. Using `mode()` and expecting it to handle ties in a specific business-defined way — it silently returns the smallest tied value per the `ORDER BY` sort order, which may not match the desired tie-breaking rule. Also: Building a manual 'most frequent value' query with `GROUP BY val ORDER BY COUNT(*) DESC LIMIT 1` inside a subquery per outer group, which is O(N log N) per group and far less efficient than `mode() WITHIN GROUP`. Also: Confusing `mode()` (most frequent value) with `avg()` or `median` — for multimodal distributions, none of these alone describes the distribution well; always pair with a count or histogram.

✓ Instead: See description and related functions for the correct approach.

Confusing `percent_rank` with `cume_dist` — `percent_rank` uses (rank-1)/(N-1) so the minimum value returns 0.0, while `cume_dist` uses rank/N so the minimum returns 1/N; they answer subtly different questions. Using `percent_rank` on a group with N=1 — it returns 0 by definition but the result is meaningless as a percentile with a single data point. Also: Interpreting `percent_rank(x)` as 'the x-th percentile value' — it returns the percentile position OF x, not the value AT a given percentile position; use `percentile_cont` for the inverse operation. Also: Applying `percent_rank` to the wrong sort direction — `ORDER BY col DESC` gives the fraction of values that x ranks ABOVE, while `ORDER BY col ASC` gives the fraction x ranks above from the bottom; document the direction explicitly.

✓ Instead: See description and related functions for the correct approach.

Using `AVG` instead of `percentile_cont(0.5)` for skewed distributions such as income, latency, or response times — the mean is pulled by outliers and does not represent the 'typical' value; the median is almost always more appropriate for skewed data. Calling `percentile_cont` once per percentile in separate queries (e.g., p50, p95, p99 in three separate statements) instead of using the array form `percentile_cont(ARRAY[0.5, 0.95, 0.99])` which sorts once. Also: Using `percentile_cont` on non-numeric types such as text or enums — `percentile_cont` interpolates between values and requires a numeric sort expression; use `percentile_disc` for non-numeric types. Also: Assuming `percentile_cont(0.5)` always returns a value that exists in the table — it interpolates between adjacent values, so the result may not be a real observation in the dataset.

✓ Instead: See description and related functions for the correct approach.

Using `percentile_disc` when a smoothed (interpolated) estimate is needed — `percentile_disc` always returns an actual row value, which can produce a different result from `percentile_cont` on even-sized datasets; choose based on whether a real observation or an interpolated estimate is more appropriate. Expecting `percentile_disc` to support the array-of-fractions shorthand like `percentile_cont` — it does not; each discrete percentile requires its own function call or lateral subquery. Also: Forgetting that `percentile_disc` NULLs are handled by the `ORDER BY` sort expression — `ORDER BY col NULLS LAST` is needed if NULLs should not be the 'smallest' values in the distribution. Also: Using `MIN` or `MAX` where `percentile_disc(0.0)` or `percentile_disc(1.0)` would be clearer in the context of a percentile analysis, making the intent less obvious to readers.

✓ Instead: See description and related functions for the correct approach.

Confusing hypothetical-set `rank(val) WITHIN GROUP (ORDER BY col)` with the window function `RANK() OVER (ORDER BY col)` — the hypothetical form asks 'where would this value rank?' against the aggregate group, not a window partition. Not accounting for gap semantics: `rank()` leaves gaps after ties (ranks 1,1,3), so converting rank to a percentile by dividing by COUNT(*) gives a misleading result when ties exist; use `percent_rank` or `cume_dist` instead. Also: Using `rank()` to determine whether a hypothetical value is 'in the top N%' when `cume_dist()` directly returns the cumulative fraction without requiring division. Also: Passing the hypothetical value as the wrong type (e.g., integer vs double precision) — implicit casting differences can shift the rank result when the sort expression type and the argument type differ subtly.

✓ Instead: See description and related functions for the correct approach.

Using `avg(X)` instead of `regr_avgx(Y, X)` when computing residuals or centering variables for regression — `avg(X)` includes rows where Y is NULL, producing a different mean than the one used internally by `regr_slope`. Confusing `regr_avgx(Y, X)` and `regr_avgy(Y, X)` — the argument order is always (Y, X) for both, but the first returns the mean of X and the second the mean of Y; the naming is counterintuitive. Also: Assuming `regr_avgx` and `avg(x)` are interchangeable in non-regression contexts — use plain `avg` for general-purpose averages and `regr_avg*` only when you need the paired-row subset. Also: Not using `regr_avgx` when manually verifying that the regression line passes through the point (mean_x, mean_y), leading to off-by-NULL-subset verification errors.

✓ Instead: See description and related functions for the correct approach.

Using `COUNT(*)` instead of `regr_count(Y, X)` to verify regression N — `COUNT(*)` counts all rows including those with NULL in either variable, overstating the effective sample size. Not gating regression results with a `HAVING regr_count(...) >= threshold` clause — a slope computed from 2 rows is always a perfect fit (R²=1) yet entirely unreliable. Also: Assuming `regr_count` equals `COUNT(x)` — it only counts rows where BOTH X and Y are non-null; one nullable column can silently halve your effective N. Also: Skipping `regr_count` validation in production pipelines, allowing near-zero-N regression outputs to propagate into forecasting models as if they were reliable.

✓ Instead: See description and related functions for the correct approach.

Interpreting the intercept as a physically meaningful 'base value' when X=0 is far outside the observed data range — the intercept is often nonsensical in context (e.g., a house with 0 square feet). Neglecting to pair `regr_intercept` with `regr_slope` when predicting Y — using only the intercept or only the slope produces wrong predictions. Also: Calling `regr_intercept` and `regr_slope` in separate subqueries, scanning the table twice, when a single SELECT can compute both aggregates in one pass. Also: Ignoring that `regr_intercept` returns NULL when `regr_count` < 2, leading to NULL forecasts without any error or warning.

✓ Instead: See description and related functions for the correct approach.

Assuming a high R² (e.g., 0.95) means the linear model is appropriate — a curved relationship can still produce high R² while the linear assumption is badly violated. Using R² as the sole criterion for model quality without inspecting residuals — outliers or heteroscedasticity can give a misleadingly high or low R². Also: Confusing `regr_r2(Y, X)` with `corr(Y, X)^2` — they are mathematically equivalent for simple linear regression, but mixing them up in code reviews causes confusion; use the most expressive form for your intent. Also: Reporting R² without also reporting sample size (`regr_count`) — an R² of 0.99 from 4 data points is not evidence of a good model.

✓ Instead: See description and related functions for the correct approach.

Swapping Y and X arguments — `regr_slope(X, Y)` gives the inverse slope and is a common argument-order mistake that produces wrong forecasts silently. Using `regr_slope` without checking `regr_r2` or `regr_count` first — a slope computed from 3 points or on data with R²=0.02 is statistically meaningless but looks like a real result. Also: Extrapolating far outside the observed X range — the linear model is only valid within (or near) the range of training data; extreme extrapolation amplifies any model misfit. Also: Assuming a significant slope implies causality — a non-zero slope only means a linear association exists, not that X causes Y.

✓ Instead: See description and related functions for the correct approach.

Dividing `regr_sxy` by `regr_sxx` without guarding against `regr_sxx = 0` (all X values identical) — use `NULLIF(regr_sxx(...), 0)` to avoid division-by-zero. Recomputing these building blocks in multiple separate subqueries instead of computing all three (`regr_sxx`, `regr_syy`, `regr_sxy`) in a single aggregate scan. Also: Manually computing `SUM((x - avg(x)) * (y - avg(y)))` via a self-join or subquery when `regr_sxy` does the same thing in one pass with better numerical stability. Also: Forgetting that all three functions apply the same paired-NULL filter as `regr_count` — rows with NULL in either variable are excluded, so manual `SUM` formulas using `avg(x)` over the full table will produce different results.

✓ Instead: See description and related functions for the correct approach.

Using `stddev` (sample) on full-population data such as a complete census table — this inflates the spread slightly due to the N-1 Bessel correction. Expecting `stddev` to return 0 for a single-row group instead of NULL, leading to silent NULLs propagating into downstream calculations. Also: Comparing standard deviations across groups with very different sample sizes without also checking group counts — a tiny-N group can produce a wildly unstable stddev. Also: Using multiple query passes (one for avg, one for stddev) when both can be computed in a single aggregate scan.

✓ Instead: See description and related functions for the correct approach.

Using `stddev_pop` on a sample dataset instead of `stddev_samp`, producing a biased (too-small) estimate of spread. Confusing `stddev_pop` with `stddev` — `stddev` is an alias for `stddev_samp`, not `stddev_pop`; always check which denominator (N vs N-1) is needed. Also: Interpreting a zero result as 'no data' — `stddev_pop` returns 0 when all values are identical, and NULL only when there are no non-null rows. Also: Running separate queries for `stddev_pop` and `avg` when a single grouped query can compute both in one pass over the data.

✓ Instead: See description and related functions for the correct approach.

Treating `var_pop` output as if it were a standard deviation — variance is in squared units, making it unintuitive for threshold comparisons (e.g., 'variance > 100 ms' is meaningless without taking the square root). Using `var_pop` on sampled data instead of `var_samp`, producing a downward-biased variance estimate that understates true population variance. Also: Summing variances from different groups when the groups are not independent — variance addition only holds under statistical independence. Also: Computing variance with a manual `AVG((x - mean)^2)` subquery instead of the built-in `var_pop`, which is numerically stabler and computed in a single pass.

✓ Instead: See description and related functions for the correct approach.

Using `variance` (sample) on a complete population table, introducing a small upward bias via the N-1 Bessel correction when N should be used instead. Displaying raw variance values to end users — variance is in squared units and is difficult to interpret; always convert to standard deviation via `sqrt(variance(...))` for presentation. Also: Ignoring NULL rows: `var_samp` silently skips NULLs, so the effective N can be much smaller than the table row count; always cross-check with `COUNT(expression)` vs `COUNT(*)`. Also: Running a subquery to compute the mean first and then computing deviations manually — `var_samp` does this in a single numerically stable pass without a self-join.

✓ Instead: See description and related functions for the correct approach.