raw Software
RAW Software Databases MySQL

Statistical Analysis with MySQL 8.4

Robert Eisele

A database is often the best place to calculate descriptive statistics: it already holds the filtered rows, understands their groups, and can reduce millions of observations before transferring a small result to the application. MySQL 8.4 provides the essential aggregates and a capable set of window functions. The less obvious statistics can be assembled from the same building blocks without installing a server-side extension.

The examples use a table in which one row represents one observation. That detail matters. If a join duplicates an observation before aggregation, every count, average, and variance after the join describes the duplicated data rather than the original population. 

CREATE TABLE observation (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  segment     VARCHAR(40) NOT NULL,
  observed_at DATETIME(6) NOT NULL,
  value       DECIMAL(18, 6) NULL,
  weight      DECIMAL(18, 6) NULL,
  x           DOUBLE NULL,
  y           DOUBLE NULL,
  PRIMARY KEY (id),
  INDEX observation_segment_time (segment, observed_at, id)
);

Start with the Statistical Question

Before choosing a function, define the unit of observation, the population being filtered, and the meaning of a missing value. SQL makes it easy to calculate a precise answer to the wrong question. A customer-level analysis, for example, should normally aggregate orders to one row per customer before comparing customers.

Most MySQL aggregate functions ignore NULL. Consequently, COUNT(*) counts selected rows while COUNT(value) counts non-NULL measurements. Showing both is a useful audit: 

SELECT
  segment,
  COUNT(*) AS rows_seen,
  COUNT(value) AS measured_values,
  COUNT(DISTINCT value) AS distinct_values,
  MIN(value) AS minimum,
  MAX(value) AS maximum,
  AVG(value) AS mean,
  STDDEV_POP(value) AS population_stddev,
  STDDEV_SAMP(value) AS sample_stddev
FROM observation
WHERE observed_at >= '2026-01-01'
  AND observed_at <  '2027-01-01'
GROUP BY segment;

Use the population functions when the selected rows are the complete population of interest. Use the sample functions when those rows are a sample from a larger process. VAR_POP() and STDDEV_POP() divide by n; VAR_SAMP() and STDDEV_SAMP() divide by n - 1. The shorter aliases VARIANCE(), STD(), and STDDEV() all mean the population form, so the explicit names make an analysis easier to review.

Choosing an Average

AVG(value) is the arithmetic mean and gives every non-missing row equal influence. A weighted mean is more appropriate when a row represents a different quantity, exposure, duration, or confidence: 

SELECT
  segment,
  SUM(value * weight) / NULLIF(SUM(weight), 0) AS weighted_mean
FROM observation
WHERE value IS NOT NULL
  AND weight > 0
GROUP BY segment;

Rejecting zero and negative weights is part of the definition here, not merely protection from division by zero. If a domain permits signed weights, it needs a separate interpretation and validation rule.

Harmonic mean for rates

Rates such as requests per second or distance per unit time should be averaged through the underlying quantity. For positive rates value and workloads weight, the weighted harmonic mean is: 

SELECT
  segment,
  SUM(weight) / NULLIF(SUM(weight / value), 0) AS harmonic_mean
FROM observation
WHERE value > 0
  AND weight > 0
GROUP BY segment;

Equal weights describe equal workloads. If the observations instead cover equal time intervals, total work divided by total time leads back to an arithmetic mean of the rates. Stating what is held equal prevents the common mistake of applying the harmonic mean to every quantity called a rate.

Geometric mean for multiplicative change

Growth factors and other multiplicative quantities are naturally averaged in logarithmic space. Averaging the logs before exponentiation also avoids constructing a potentially enormous intermediate product: 

SELECT
  segment,
  EXP(SUM(weight * LN(value)) / NULLIF(SUM(weight), 0)) AS geometric_mean
FROM observation
WHERE value > 0
  AND weight > 0
GROUP BY segment;

The geometric and harmonic means require strictly positive values under these definitions. Silently dropping invalid rows can bias a result, so production reports should count or reject them explicitly.

Mode, Median, and Percentiles

Return every mode

A mode is a value with the highest frequency. LIMIT 1 hides ties and may choose a different winner from one execution to another. Ranking the frequencies returns every valid mode: 

WITH frequencies AS (
  SELECT segment, value, COUNT(*) AS frequency
  FROM observation
  WHERE value IS NOT NULL
  GROUP BY segment, value
), ranked AS (
  SELECT
    segment,
    value,
    frequency,
    DENSE_RANK() OVER (
      PARTITION BY segment
      ORDER BY frequency DESC
    ) AS frequency_rank
  FROM frequencies
)
SELECT segment, value, frequency
FROM ranked
WHERE frequency_rank = 1
ORDER BY segment, value;

Exact median without a UDF

MySQL 8.4 does not provide a native median aggregate. ROW_NUMBER() and COUNT() can select the middle row for an odd sample and the two middle rows for an even sample. The primary key breaks ties deterministically; it does not change the median because tied rows have the same value. 

WITH ordered AS (
  SELECT
    segment,
    value,
    ROW_NUMBER() OVER (
      PARTITION BY segment
      ORDER BY value, id
    ) AS rn,
    COUNT(*) OVER (PARTITION BY segment) AS sample_size
  FROM observation
  WHERE value IS NOT NULL
)
SELECT segment, AVG(value) AS median
FROM ordered
WHERE rn IN (
  (sample_size + 1) DIV 2,
  (sample_size + 2) DIV 2
)
GROUP BY segment;

This computes the conventional midpoint median. It sorts every partition, so it is substantially more expensive than a one-pass average. An index may help produce the filtered rows, but a large grouped median can still need a temporary table and an explicit sort.

Nearest-rank percentiles

CUME_DIST() gives the fraction of rows less than or equal to each value. Taking the first value at or above a requested fraction produces an empirical nearest-rank percentile: 

WITH distribution AS (
  SELECT
    segment,
    value,
    CUME_DIST() OVER (
      PARTITION BY segment
      ORDER BY value
    ) AS cumulative_fraction
  FROM observation
  WHERE value IS NOT NULL
)
SELECT
  segment,
  MIN(CASE WHEN cumulative_fraction >= 0.50 THEN value END) AS p50_nearest_rank,
  MIN(CASE WHEN cumulative_fraction >= 0.90 THEN value END) AS p90_nearest_rank,
  MIN(CASE WHEN cumulative_fraction >= 0.99 THEN value END) AS p99_nearest_rank
FROM distribution
GROUP BY segment;

Percentile definitions are not interchangeable. The query returns observed values and does not interpolate between neighboring rows. If a report requires a particular interpolated convention, specify that convention and implement its lower and upper ranks explicitly rather than labeling every quantile simply as "the percentile."

Distributions, Buckets, and Long Tails

A histogram needs buckets of equal width when bar area is meant to represent frequency. With a width of 10 and an origin of zero, FLOOR() also assigns negative values consistently: 

WITH buckets AS (
  SELECT
    segment,
    FLOOR(value / 10) AS bucket_id
  FROM observation
  WHERE value IS NOT NULL
)
SELECT
  segment,
  bucket_id * 10 AS lower_bound,
  (bucket_id + 1) * 10 AS upper_bound,
  COUNT(*) AS frequency
FROM buckets
GROUP BY segment, bucket_id
ORDER BY segment, bucket_id;

NTILE(10) answers a different question: it creates ten groups with roughly equal row counts, so their value ranges can have very different widths. That is useful for deciles but not a replacement for an equal-width histogram.

Long-tail reports often ask how many of the most frequent entities account for a chosen share of all events. A running sum replaces the user-variable tricks required by old MySQL versions: 

WITH entity_frequency AS (
  SELECT phrase_id, COUNT(*) AS frequency
  FROM search_event
  GROUP BY phrase_id
), cumulative AS (
  SELECT
    phrase_id,
    frequency,
    ROW_NUMBER() OVER (
      ORDER BY frequency DESC, phrase_id
    ) AS frequency_rank,
    SUM(frequency) OVER (
      ORDER BY frequency DESC, phrase_id
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_events,
    SUM(frequency) OVER () AS total_events
  FROM entity_frequency
)
SELECT MIN(frequency_rank) AS entities_for_half_of_events
FROM cumulative
WHERE running_events >= total_events * 0.50;

Variance and Standard Deviation

MySQL already implements the stable, native aggregate forms. Prefer them to algebraic rewrites such as AVG(value * value) - AVG(value) * AVG(value); subtracting two nearly equal large numbers can erase most of the useful precision. 

SELECT
  segment,
  VAR_POP(value) AS population_variance,
  STDDEV_POP(value) AS population_stddev,
  VAR_SAMP(value) AS sample_variance,
  STDDEV_SAMP(value) AS sample_stddev
FROM observation
GROUP BY segment;

When raw rows cannot be retained and a statistic must be updated after every insert, a persisted running standard deviation can use Welford's algorithm instead of rescanning the group.

Skewness and Kurtosis

MySQL has no native skewness or kurtosis aggregate. They can be expressed through centered moments after calculating the mean. The following query returns population moment coefficients; it does not apply a small-sample bias correction. 

WITH means AS (
  SELECT segment, AVG(value) AS mean_value
  FROM observation
  WHERE value IS NOT NULL
  GROUP BY segment
), centered AS (
  SELECT o.segment, o.value - m.mean_value AS deviation
  FROM observation AS o
  JOIN means AS m ON m.segment = o.segment
  WHERE o.value IS NOT NULL
), moments AS (
  SELECT
    segment,
    COUNT(*) AS sample_size,
    AVG(POWER(deviation, 2)) AS m2,
    AVG(POWER(deviation, 3)) AS m3,
    AVG(POWER(deviation, 4)) AS m4
  FROM centered
  GROUP BY segment
)
SELECT
  segment,
  CASE
    WHEN sample_size >= 3 AND m2 > 0
    THEN m3 / POWER(m2, 1.5)
  END AS population_skewness,
  CASE
    WHEN sample_size >= 4 AND m2 > 0
    THEN m4 / (m2 * m2) - 3
  END AS population_excess_kurtosis
FROM moments;

Excess kurtosis subtracts 3, making a normal distribution's theoretical value zero. Sample-adjusted estimators use different finite-sample factors; choose and document one when matching another statistics package.

Covariance, Correlation, and Linear Regression

Two-variable statistics must use the same paired rows throughout. Filtering x and y separately gives inconsistent counts and means when either column is NULL. Centering first also avoids the unstable shortcut AVG(x * y) - AVG(x) * AVG(y). 

WITH paired AS (
  SELECT segment, x, y
  FROM observation
  WHERE x IS NOT NULL
    AND y IS NOT NULL
), means AS (
  SELECT
    segment,
    AVG(x) AS mean_x,
    AVG(y) AS mean_y
  FROM paired
  GROUP BY segment
), centered_sums AS (
  SELECT
    p.segment,
    COUNT(*) AS sample_size,
    MAX(m.mean_x) AS mean_x,
    MAX(m.mean_y) AS mean_y,
    SUM((p.x - m.mean_x) * (p.y - m.mean_y)) AS sum_xy,
    SUM(POWER(p.x - m.mean_x, 2)) AS sum_xx,
    SUM(POWER(p.y - m.mean_y, 2)) AS sum_yy
  FROM paired AS p
  JOIN means AS m ON m.segment = p.segment
  GROUP BY p.segment
)
SELECT
  segment,
  sum_xy / sample_size AS covariance_population,
  sum_xy / NULLIF(sample_size - 1, 0) AS covariance_sample,
  sum_xy / NULLIF(SQRT(sum_xx * sum_yy), 0) AS pearson_r,
  sum_xy / NULLIF(sum_xx, 0) AS regression_slope,
  mean_y - (sum_xy / NULLIF(sum_xx, 0)) * mean_x AS regression_intercept
FROM centered_sums;

Pearson's coefficient measures linear association, not causation. It is undefined when either variable has zero spread. The regression coefficients fit y = intercept + slope * x by ordinary least squares with an intercept. A production model still needs residual diagnostics, uncertainty estimates, and validation on data not used for fitting.

Ranks and Rolling Statistics

Window functions keep every observation while adding a statistic calculated over related rows. This makes rankings, cumulative distributions, and rolling summaries much clearer than the session-variable techniques used before MySQL 8. 

SELECT
  id,
  segment,
  observed_at,
  value,
  ROW_NUMBER() OVER window_order AS row_number,
  RANK() OVER window_value AS rank_with_gaps,
  DENSE_RANK() OVER window_value AS dense_rank,
  PERCENT_RANK() OVER window_value AS percent_rank,
  CUME_DIST() OVER window_value AS cumulative_distribution,
  NTILE(4) OVER window_value AS quartile_bucket
FROM observation
WHERE value IS NOT NULL
WINDOW
  window_order AS (
    PARTITION BY segment
    ORDER BY observed_at, id
  ),
  window_value AS (
    PARTITION BY segment
    ORDER BY value
  );

A seven-observation rolling window uses an explicit ROWS frame. Including id makes the order deterministic when timestamps tie: 

SELECT
  id,
  segment,
  observed_at,
  value,
  COUNT(value) OVER rolling AS observations_in_window,
  AVG(value) OVER rolling AS rolling_mean,
  STDDEV_SAMP(value) OVER rolling AS rolling_sample_stddev
FROM observation
WINDOW rolling AS (
  PARTITION BY segment
  ORDER BY observed_at, id
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
)
ORDER BY segment, observed_at, id;

This means seven rows, not seven days. For irregular observations, aggregate into the intended time grain first or use a temporal RANGE frame whose boundary expresses the actual interval. Writing the frame explicitly also avoids MySQL's ordered-window default, which is a peer-aware RANGE frame rather than a fixed row count.

Outlier Scores within Groups

A z-score expresses distance from the group mean in standard deviations. A CTE avoids repeating the window expressions and keeps zero-variance groups well-defined as NULL: 

WITH scored AS (
  SELECT
    id,
    segment,
    value,
    AVG(value) OVER (PARTITION BY segment) AS group_mean,
    STDDEV_SAMP(value) OVER (PARTITION BY segment) AS group_stddev
  FROM observation
  WHERE value IS NOT NULL
)
SELECT
  id,
  segment,
  value,
  (value - group_mean) / NULLIF(group_stddev, 0) AS z_score
FROM scored
ORDER BY ABS((value - group_mean) / NULLIF(group_stddev, 0)) DESC;

A large absolute z-score is a review signal, not proof that a row is erroneous. Heavy-tailed or multimodal data can make mean-and-standard-deviation thresholds misleading; median-based robust methods may be more appropriate there.

Correctness and Performance Checklist

SQL is excellent for filtering, grouping, descriptive summaries, and preparing compact datasets. Inferential procedures, model selection, confidence intervals, and plots often belong in a statistics environment after MySQL has performed the expensive relational reduction. Keeping that boundary explicit is usually safer than expanding the database server with a custom UDF solely to imitate a full statistics package.

References

Related Stuff

raw / Databases
19 Aug
·
Robert Eisele

Running Standard Deviation in MySQL

This article derives two ways to keep a running standard deviation per group in MySQL without keeping the raw rows: an additive sum-and-sum-of-squares summary, and the numerically stable Welford online algorithm with its mean and M2 update rules derived from the definition of variance. It also derives the pairwise formula for combining two already-aggregated groups, and covers common pitfalls such as population vs. sample variance and the deprecated VALUES() syntax.
raw / Databases
04 Jun
·
Robert Eisele

Analyze User Activity Patterns with MySQL and PHP

A practical method for analyzing when login events occur without confusing event frequency with online duration. MySQL builds complete timezone-aware hourly histograms and weekday heatmaps, while PHP finds continuous activity windows on a circular 24-hour clock. Sample-size checks, index use, daylight-saving behavior, missing buckets, interpretation, and privacy constraints make the result suitable for operational decisions.
raw / Databases
24 Oct
·
Robert Eisele

Efficient Pagination in MySQL

A modern guide to pagination in MySQL using deterministic composite ordering, matching indexes, and keyset cursors. It derives forward and backward boundary queries, explains cursor design and concurrent changes, separates counting from page retrieval, defines the remaining uses for offsets, and verifies performance with EXPLAIN ANALYZE.
All Software