SQL Anomaly Detection with Rolling Baselines and Z-Scores

Short answer

A rolling baseline compares each reading with the mean and standard deviation of the readings just before it. Exclude the current reading from its own baseline, require a minimum number of samples, and put a floor under the standard deviation.
Every threshold, window and floor on this page (z above 4, a 0.5 kW floor, 10 of 12 samples, a one-hour window, four weeks of hour-of-week history, modified z above 3.5) is an illustrative assumption for the synthetic data. Choose your own from the sensor's resolution and noise, how many false alarms the people receiving them can tolerate, and a review against labelled history.
On the sample data, a one-hour rolling z-score with a threshold of 4 caught a 420 kW spike but also flagged every weekday start-up at 08:00 and shut-down at 18:00: ten false alarms in one week. On this sample, the rolling baseline treated scheduled changes of state as anomalies.
On the same sample, a seasonal baseline (mean and spread for the same hour of the same weekday over the previous four weeks) flagged only the spike and a chiller running at 02:00 on a Saturday, which the rolling method had reported as two separate one-sample events.
Neither z-score method sees a stuck sensor holding a normal value; that needs a variance or run-length check, and its aftermath is exactly where a missing variance floor causes false alarms.
The same rolling query runs in MySQL 8.0 with STDDEV_SAMP over a RANGE INTERVAL frame and in SQLite with sums of squares; all three engines returned identical flags on the sample.

How we know: Research-based from the PostgreSQL 18 documentation (window functions, value expressions and window frames, aggregate functions), the MySQL 8.0 Reference Manual (window function frames, aggregate functions), the SQLite documentation (window functions, aggregate functions) and the NIST/SEMATECH e-Handbook of Statistical Methods (detection of outliers), checked 27 October 2026. Every query on this page was run on generated data: all PostgreSQL queries on PostgreSQL 18.6 (PGlite 0.5.11) with the session time zone set to UTC, the MySQL variant on MySQL 8.0.46 and the SQLite variant on SQLite 3.39.2 through PHP PDO_SQLITE. Outputs are copied from those runs. The data, the faults, the thresholds, the windows and the floors are illustrative assumptions for the synthetic sample, not tuned or recommended values for real plant; the results show how each method behaves on that sample only. The methods are presented as common approaches, not as a ranking or a universal best method. The statement that SQLite has no standard deviation aggregate is based on the built-in aggregate function list in the SQLite documentation as checked on 27 October 2026.

Scope: what this page builds

This is the implementation companion to time-series anomaly detection: methods and architecture, which explains the families of methods, where detection should run and how to avoid alert fatigue. This page builds the simplest statistical family in SQL and shows its behaviour on data with known faults. It is for engineers who already store readings in a SQL database and want a first detector that runs as a scheduled query, before reaching for a stream processor or a machine learning service.

Implementation decisions made here, each discussed below: score each point separately; compare readings with a baseline built only from earlier data; use 5-minute values (aggregate one-second data first, for example with date_bin); flag with a z-score threshold plus guards; group consecutive flags into episodes before alerting. Data quality checks such as range limits and stuck-value run lengths belong before this step; see IoT data quality checks.

About the numbers on this page. The thresholds (a z-score above 4, a modified z-score above 3.5), the variance floor (0.5 kW), the minimum sample rule (10 of 12), the window lengths (one hour, 12 readings, four weeks of history) and the hour-of-week buckets (computed in UTC because the sample is generated in UTC) are illustrative assumptions chosen so the planted faults are easy to see in this synthetic data. They are not recommendations for any sensor. To choose your own, consider: the sensor's resolution and normal noise (which sets a sensible floor); how many false alarms the people who receive them can tolerate per week (which pushes the threshold up or down); how quickly a fault must be noticed (which bounds the window and persistence rules); and, above all, a review of candidate values against a labelled history of real faults and known normal events for each point class.

The three methods shown (rolling z-score, hour-of-week baseline, median and MAD) are common statistical approaches, compared here on one synthetic sample. They are not a ranking, and no single method is best for every signal; the results below show how each behaved on this data only.

The sample: five weeks of chiller power with three planted faults

One point, CH-01 power in kW, every 5 minutes from Monday 28 September to Sunday 1 November 2026 (10,080 readings). The chiller runs at about 180 kW on weekdays from 08:00 to 17:55 and idles at about 25 kW otherwise, with a small repeating disturbance of up to 4 kW. The values are generated with integer arithmetic, so every engine produces exactly the same data. In the last week, three faults are planted:

Spike: a 420 kW reading on Tuesday 27 October at 10:00 (a meter glitch).
Stuck value: exactly 181 kW from 14:00 to 15:55 on Wednesday 28 October (a frozen sensor that happens to read a normal value).
Out-of-schedule running: the chiller at full load from 02:00 to 03:55 on Saturday 31 October (normal for a weekday afternoon, abnormal at that hour).

Table and generated data (PostgreSQL, run on 18.6)

SET TimeZone = 'UTC';

CREATE TABLE sensor_reading (
    point_id  integer NOT NULL,
    ts        timestamptz NOT NULL,
    value     double precision NOT NULL,
    PRIMARY KEY (point_id, ts)
);

-- n = 5-minute step; n / 288 = day number (day 0 is a Monday); (n % 288) / 12 = hour
INSERT INTO sensor_reading (point_id, ts, value)
SELECT 1,
       timestamptz '2026-09-28 00:00+00' + n * interval '5 minutes',
       CASE
         WHEN n = 8472 THEN 420                                    -- spike, Tue 27 Oct 10:00
         WHEN n BETWEEN 8808 AND 8831 THEN 181                     -- stuck, Wed 28 Oct 14:00-15:55
         WHEN n BETWEEN 9528 AND 9551
              THEN 180 + ((n * 37) % 11 - 5) * 0.8                 -- running, Sat 31 Oct 02:00-03:55
         WHEN (n / 288) % 7 < 5 AND (n % 288) / 12 BETWEEN 8 AND 17
              THEN 180 + ((n * 37) % 11 - 5) * 0.8                 -- weekday occupied hours
         ELSE 25 + ((n * 37) % 11 - 5) * 0.8                       -- idle
       END
FROM generate_series(0, 10079) AS n;

SELECT count(*) AS rows, min(ts)::text AS first_ts, max(ts)::text AS last_ts,
       round(min(value)::numeric, 1) AS min_kw, round(max(value)::numeric, 1) AS max_kw
FROM sensor_reading;

Output

 rows  |        first_ts        |        last_ts         | min_kw | max_kw
-------+------------------------+------------------------+--------+--------
 10080 | 2026-09-28 00:00:00+00 | 2026-11-01 23:55:00+00 |   21.0 |  420.0

Method 1: rolling mean, standard deviation and z-score

For each reading, compute the mean and sample standard deviation of the readings in the previous hour, then the z-score: how many standard deviations the reading is from that mean. In PostgreSQL any ordinary aggregate can be used as a window function, and a RANGE frame over a timestamp takes an interval offset, so "the previous hour" is written directly and stays correct when readings are missing.

In this example, three guards matter as much as the formula (the values used for them here are illustrative assumptions for the sample):

Exclude the current row (the frame ends one microsecond before it). Otherwise a spike inflates its own baseline and hides itself.
Minimum samples (base_n >= 10 of a possible 12 in this example). After a gap, a baseline of two or three readings is a weak basis for scoring; how many samples you require depends on the interval and how often gaps occur.
Variance floor (greatest(base_sd, 0.5): 0.5 kW is an assumed figure for this synthetic meter, chosen to be of the order of its resolution). When the baseline is perfectly flat the standard deviation is zero and any change becomes infinitely unusual. Base your floor on the real sensor's resolution and normal noise.

The query flags a reading when it is more than 4 standard deviations from the baseline mean. The value 4 is an illustrative assumption picked for this data, not a standard: a lower value catches more faults and raises more false alarms, a higher one the reverse. Tune it per point class against labelled history, as discussed under running it in production below.

Rolling one-hour z-score, flags in the final week summarised per day (PostgreSQL, run on 18.6)

WITH scored AS (
    SELECT ts, value,
           avg(value)         OVER w AS base_mean,
           stddev_samp(value) OVER w AS base_sd,
           count(*)           OVER w AS base_n
    FROM sensor_reading
    WINDOW w AS (PARTITION BY point_id ORDER BY ts
                 RANGE BETWEEN interval '1 hour' PRECEDING AND interval '1 microsecond' PRECEDING)
)
SELECT to_char(ts, 'YYYY-MM-DD') AS day, to_char(ts, 'Dy') AS dow, count(*) AS flags,
       string_agg(to_char(ts, 'HH24:MI'), ', ' ORDER BY ts) AS flagged_at
FROM scored
WHERE ts >= '2026-10-26' AND base_n >= 10
  AND abs(value - base_mean) / greatest(base_sd, 0.5) > 4
GROUP BY 1, 2 ORDER BY 1;

Output

    day     | dow | flags |     flagged_at
------------+-----+-------+---------------------
 2026-10-26 | Mon |     2 | 08:00, 18:00
 2026-10-27 | Tue |     3 | 08:00, 10:00, 18:00
 2026-10-28 | Wed |     3 | 08:00, 16:05, 18:00
 2026-10-29 | Thu |     2 | 08:00, 18:00
 2026-10-30 | Fri |     2 | 08:00, 18:00
 2026-10-31 | Sat |     2 | 02:00, 04:00

What the rolling method caught and missed

Caught: the 10:00 spike on Tuesday.
False alarms: every 08:00 start-up and 18:00 shut-down, ten in the week. A rolling baseline knows only the last hour, so a scheduled change of state looks exactly like a fault.
Partly caught: the Saturday night running was flagged at its start (02:00) and its end (04:00), as two one-sample events. Once the abnormal values fill the baseline window they become the new normal.
Missed: the stuck value on Wednesday, because 181 kW is normal for that hour. The 16:05 flag is a side effect of it: the hour before 16:05 contained eleven identical readings, so the baseline spread collapsed to almost nothing.

On this sample, rolling baselines look better suited to signals without a strong schedule (a plant-room temperature, a pressure that should be steady) and to short-window checks such as rate of change. For signals driven by occupancy or a time schedule, a seasonal baseline is worth comparing, as the next section does on the same sample.

Method 2: a seasonal baseline by hour of week

Instead of the last hour, compare each reading with the same hour on the same weekday in previous weeks. Building loads follow a weekly schedule, so 168 hour-of-week buckets can capture weekday start-ups, weekend set-backs and night idling. In this example the baseline is built from four earlier weeks only (an illustrative history length: longer histories give steadier statistics but follow genuine changes more slowly), stored in a table and refreshed on a schedule, so scoring is a cheap join rather than a recomputation. How often to refresh is an operational choice; tie it to how often schedules and plant change. The buckets are computed in UTC here only because the sample is generated in UTC.

percentile_cont is an ordered-set aggregate. PostgreSQL does not allow ordered-set aggregates as window functions, so percentiles belong in a grouped baseline table like this one rather than in a rolling window.

Baseline table from weeks 1 to 4 (PostgreSQL, run on 18.6)

CREATE TABLE seasonal_baseline AS
SELECT point_id,
       extract(isodow FROM ts)::int AS iso_dow,      -- 1 = Monday ... 7 = Sunday
       extract(hour FROM ts)::int   AS hour_of_day,
       count(*)                      AS samples,
       avg(value)                    AS mean_value,
       stddev_samp(value)            AS sd_value,
       percentile_cont(0.5)  WITHIN GROUP (ORDER BY value) AS p50,
       percentile_cont(0.99) WITHIN GROUP (ORDER BY value) AS p99
FROM sensor_reading
WHERE ts >= '2026-09-28' AND ts < '2026-10-26'
GROUP BY 1, 2, 3;

SELECT count(*) AS buckets, min(samples) AS min_samples, max(samples) AS max_samples
FROM seasonal_baseline;

SELECT iso_dow, hour_of_day, samples, round(mean_value::numeric, 1) AS mean_kw,
       round(sd_value::numeric, 2) AS sd_kw, round(p50::numeric, 1) AS p50_kw, round(p99::numeric, 1) AS p99_kw
FROM seasonal_baseline
WHERE (iso_dow, hour_of_day) IN ((2, 2), (2, 10), (6, 2)) ORDER BY 1, 2;

Output

 buckets | min_samples | max_samples
---------+-------------+-------------
     168 |          48 |          48

 iso_dow | hour_of_day | samples | mean_kw | sd_kw | p50_kw | p99_kw
---------+-------------+---------+---------+-------+--------+--------
       2 |           2 |      48 |    25.1 |  2.48 |   25.0 |   29.0
       2 |          10 |      48 |   180.0 |  2.46 |  180.0 |  184.0
       6 |           2 |      48 |    25.0 |  2.46 |   25.0 |   29.0

Score the final week against the baseline and group consecutive flags into episodes (run on 18.6)

WITH scored AS (
    SELECT r.ts, r.value, b.mean_value, b.sd_value,
           (r.value - b.mean_value) / greatest(b.sd_value, 0.5) AS z
    FROM sensor_reading r
    JOIN seasonal_baseline b
      ON b.point_id = r.point_id
     AND b.iso_dow = extract(isodow FROM r.ts)
     AND b.hour_of_day = extract(hour FROM r.ts)
    WHERE r.ts >= '2026-10-26' AND r.ts < '2026-11-02'
), flagged AS (
    SELECT ts, value, z,
           ts - row_number() OVER (ORDER BY ts) * interval '5 minutes' AS grp   -- gaps-and-islands
    FROM scored
    WHERE abs(z) > 4
)
SELECT min(ts)::text AS episode_start, max(ts)::text AS episode_end, count(*) AS samples,
       round(max(value)::numeric, 1) AS max_kw, round(max(abs(z))::numeric, 1) AS max_abs_z
FROM flagged GROUP BY grp ORDER BY 1;

Output

     episode_start      |      episode_end       | samples | max_kw | max_abs_z
------------------------+------------------------+---------+--------+-----------
 2026-10-27 10:00:00+00 | 2026-10-27 10:00:00+00 |       1 |  420.0 |      97.4
 2026-10-31 02:00:00+00 | 2026-10-31 03:55:00+00 |      24 |  184.0 |      64.5

Why the episodes matter

With the same illustrative threshold of 4 and floor of 0.5 kW, the seasonal method flagged the spike and all 24 readings of the Saturday night running, and none of the scheduled start-ups and shut-downs. Grouping consecutive flags (the "gaps-and-islands" step: a timestamp minus its row number times the interval is constant within a run) turns 25 flagged readings into two events with a start, an end and a peak, which is what an alarm list or a CMMS work-order rule should receive. The overview article covers how to route such events without flooding operators.

Limitations of hour-of-week baselines: they need several weeks of history per point; public holidays, daylight-saving changes and seasonal plant changes look abnormal until the baseline catches up (exclude known holidays, compute buckets in the building's local time, and rebuild after schedule changes); and four samples per bucket per week is little data for points that report every 15 minutes.

Method 3: a robust baseline with median and MAD

Means and standard deviations are pulled by the anomalies you are trying to find: one bad week in the training window widens the baseline for a month. The NIST/SEMATECH e-Handbook notes that z-scores can mislead, particularly for small samples, and cites Iglewicz and Hoaglin's modified z-score, 0.6745 times the distance from the median divided by the median absolute deviation (MAD), with values above 3.5 in absolute terms labelled potential outliers. That 3.5 is the cited authors' recommendation for labelling potential outliers; applying it to the sample below is an illustration, and the same review against labelled history applies. The query also puts an assumed floor of 0.5 kW under the MAD, because the MAD is zero whenever more than half of a bucket's values are identical. The same hour-of-week buckets work with medians:

Median and MAD per hour of week, then modified z-scores for the final week (PostgreSQL, run on 18.6)

CREATE TABLE robust_baseline AS
WITH med AS (
    SELECT point_id, extract(isodow FROM ts)::int AS iso_dow, extract(hour FROM ts)::int AS hour_of_day,
           percentile_cont(0.5) WITHIN GROUP (ORDER BY value) AS median_value
    FROM sensor_reading
    WHERE ts >= '2026-09-28' AND ts < '2026-10-26'
    GROUP BY 1, 2, 3
)
SELECT m.point_id, m.iso_dow, m.hour_of_day, m.median_value,
       percentile_cont(0.5) WITHIN GROUP (ORDER BY abs(r.value - m.median_value)) AS mad
FROM med m
JOIN sensor_reading r
  ON r.point_id = m.point_id
 AND extract(isodow FROM r.ts) = m.iso_dow
 AND extract(hour FROM r.ts) = m.hour_of_day
WHERE r.ts >= '2026-09-28' AND r.ts < '2026-10-26'
GROUP BY m.point_id, m.iso_dow, m.hour_of_day, m.median_value;

SELECT iso_dow, hour_of_day, median_value, mad
FROM robust_baseline WHERE (iso_dow, hour_of_day) IN ((2, 10), (6, 2)) ORDER BY 1, 2;

SELECT to_char(r.ts, 'YYYY-MM-DD HH24:MI') AS ts, r.value,
       round((0.6745 * (r.value - b.median_value) / greatest(b.mad, 0.5))::numeric, 1) AS modified_z
FROM sensor_reading r
JOIN robust_baseline b
  ON b.point_id = r.point_id
 AND b.iso_dow = extract(isodow FROM r.ts)
 AND b.hour_of_day = extract(hour FROM r.ts)
WHERE r.ts >= '2026-10-26' AND r.ts < '2026-11-02'
  AND abs(0.6745 * (r.value - b.median_value) / greatest(b.mad, 0.5)) > 3.5
ORDER BY r.ts
LIMIT 4;
-- the same filter with count(*) returns 25 flagged samples

Output

 iso_dow | hour_of_day | median_value | mad
---------+-------------+--------------+-----
       2 |          10 |          180 |   2
       6 |           2 |           25 |   2

        ts        | value | modified_z
------------------+-------+------------
 2026-10-27 10:00 |   420 |       80.9
 2026-10-31 02:00 | 182.4 |       53.1
 2026-10-31 02:05 | 176.8 |       51.2
 2026-10-31 02:10 |   180 |       52.3

 flagged_samples
-----------------
              25

Stuck values and the variance floor

Neither z-score method can see the Wednesday fault: 181 kW is a normal value for that hour. What gives it away is the absence of variation. A rolling population standard deviation of exactly zero over the last 12 readings (one hour at this interval; an illustrative run length, since a real sensor's legitimate flat periods decide how long is suspicious) finds it; a run-length check, as in the data quality article, is the more general tool and belongs in ingestion validation.

Zero variation over the last 12 readings, grouped into episodes (PostgreSQL, run on 18.6)

WITH w AS (
    SELECT ts, value,
           stddev_pop(value) OVER last12 AS sd_12,
           count(*)          OVER last12 AS n_12
    FROM sensor_reading
    WINDOW last12 AS (PARTITION BY point_id ORDER BY ts ROWS BETWEEN 11 PRECEDING AND CURRENT ROW)
), stuck AS (
    SELECT ts, value, ts - row_number() OVER (ORDER BY ts) * interval '5 minutes' AS grp
    FROM w WHERE n_12 = 12 AND sd_12 = 0
)
SELECT (min(ts) - interval '55 minutes')::text AS flat_from, max(ts)::text AS flat_to,
       min(value) AS stuck_value, count(*) + 11 AS samples
FROM stuck GROUP BY grp ORDER BY 1;

Output

       flat_from        |        flat_to         | stuck_value | samples
------------------------+------------------------+-------------+---------
 2026-10-28 14:00:00+00 | 2026-10-28 15:55:00+00 |         181 |      24

Why the floor matters

Testing stddev = 0 on floating-point results is safe here only because the stuck values are bit-for-bit identical. Anything computed by subtraction can come out as a tiny positive or even negative number instead of zero. The SQLite variant below shows this: the SQLite documentation's list of built-in aggregate functions (checked 27 October 2026) contains no standard deviation or variance function, so the query computes variance from running sums of values and squares. Without a floor, that query flagged 13 extra readings on Wednesday, every reading from 15:00 to 16:00, because the variance of eleven or twelve identical values came out as a rounding residue rather than zero. With the floor, all three engines agree.

The rolling z-score in MySQL 8.0 and SQLite

MySQL 8.0 supports STDDEV_SAMP as a window function and a RANGE frame with a temporal INTERVAL offset. SQLite supports RANGE frames with numeric offsets, so store or derive Unix seconds and use 3600 for an hour. Its documented built-in aggregates are avg, count, group_concat, max, median, min, percentile, percentile_cont, percentile_disc, string_agg, sum and total, with no standard deviation or variance function (median and the percentile functions are available from SQLite 3.51.0 only when the library is compiled with -DSQLITE_ENABLE_PERCENTILE, or as a loadable extension, and were not used here). So compare squared distances with variance (z > 4 becomes distance squared > 16 times variance), which also avoids needing a square root function.

MySQL 8.0 (run on 8.0.46; same generated data via a recursive CTE)

SET SESSION cte_max_recursion_depth = 20000;
CREATE TABLE sensor_reading (
    point_id  INT NOT NULL,
    ts        DATETIME NOT NULL,
    value     DOUBLE NOT NULL,
    PRIMARY KEY (point_id, ts)
);
INSERT INTO sensor_reading (point_id, ts, value)
WITH RECURSIVE seq (n) AS (SELECT 0 UNION ALL SELECT n + 1 FROM seq WHERE n < 10079)
SELECT 1,
       '2026-09-28 00:00:00' + INTERVAL (n * 5) MINUTE,
       CASE
         WHEN n = 8472 THEN 420
         WHEN n BETWEEN 8808 AND 8831 THEN 181
         WHEN n BETWEEN 9528 AND 9551 THEN 180 + (MOD(n * 37, 11) - 5) * 0.8
         WHEN MOD(n DIV 288, 7) < 5 AND (n MOD 288) DIV 12 BETWEEN 8 AND 17 THEN 180 + (MOD(n * 37, 11) - 5) * 0.8
         ELSE 25 + (MOD(n * 37, 11) - 5) * 0.8
       END
FROM seq;

WITH scored AS (
    SELECT ts, value,
           AVG(value)         OVER w AS base_mean,
           STDDEV_SAMP(value) OVER w AS base_sd,
           COUNT(*)           OVER w AS base_n
    FROM sensor_reading
    WINDOW w AS (PARTITION BY point_id ORDER BY ts
                 RANGE BETWEEN INTERVAL 1 HOUR PRECEDING AND INTERVAL 1 SECOND PRECEDING)
)
SELECT DATE(ts) AS day, DATE_FORMAT(ts, '%a') AS dow, COUNT(*) AS flags,
       GROUP_CONCAT(DATE_FORMAT(ts, '%H:%i') ORDER BY ts SEPARATOR ', ') AS flagged_at
FROM scored
WHERE ts >= '2026-10-26' AND base_n >= 10
  AND ABS(value - base_mean) / GREATEST(base_sd, 0.5) > 4
GROUP BY DATE(ts), DATE_FORMAT(ts, '%a') ORDER BY day;

MySQL output

+------------+-----+-------+---------------------+
| day        | dow | flags | flagged_at          |
+------------+-----+-------+---------------------+
| 2026-10-26 | Mon |     2 | 08:00, 18:00        |
| 2026-10-27 | Tue |     3 | 08:00, 10:00, 18:00 |
| 2026-10-28 | Wed |     3 | 08:00, 16:05, 18:00 |
| 2026-10-29 | Thu |     2 | 08:00, 18:00        |
| 2026-10-30 | Fri |     2 | 08:00, 18:00        |
| 2026-10-31 | Sat |     2 | 02:00, 04:00        |
+------------+-----+-------+---------------------+

SQLite (run on 3.39.2; ts stored as Unix seconds in UTC)

CREATE TABLE sensor_reading (
    point_id  INTEGER NOT NULL,
    ts        INTEGER NOT NULL,  -- Unix seconds, UTC
    value     REAL NOT NULL,
    PRIMARY KEY (point_id, ts)
);
INSERT INTO sensor_reading (point_id, ts, value)
WITH RECURSIVE seq (n) AS (SELECT 0 UNION ALL SELECT n + 1 FROM seq WHERE n < 10079)
SELECT 1,
       unixepoch('2026-09-28 00:00:00') + n * 300,
       CASE
         WHEN n = 8472 THEN 420
         WHEN n BETWEEN 8808 AND 8831 THEN 181
         WHEN n BETWEEN 9528 AND 9551 THEN 180 + ((n * 37) % 11 - 5) * 0.8
         WHEN (n / 288) % 7 < 5 AND (n % 288) / 12 BETWEEN 8 AND 17 THEN 180 + ((n * 37) % 11 - 5) * 0.8
         ELSE 25 + ((n * 37) % 11 - 5) * 0.8
       END
FROM seq;

WITH w AS (
    SELECT ts, value,
           count(*)           OVER win AS n,
           sum(value)         OVER win AS s,
           sum(value * value) OVER win AS ss
    FROM sensor_reading
    WINDOW win AS (PARTITION BY point_id ORDER BY ts
                   RANGE BETWEEN 3600 PRECEDING AND 1 PRECEDING)
), scored AS (
    SELECT ts, value, n,
           s / n                      AS base_mean,
           (ss - s * s / n) / (n - 1) AS base_var
    FROM w WHERE n >= 10
)
SELECT date(ts, 'unixepoch') AS day, strftime('%w', ts, 'unixepoch') AS dow_0_sun, count(*) AS flags,
       group_concat(strftime('%H:%M', ts, 'unixepoch'), ', ') AS flagged_at
FROM scored
WHERE ts >= unixepoch('2026-10-26')
  AND (value - base_mean) * (value - base_mean) > 16 * max(base_var, 0.25)   -- floor: sd 0.5
GROUP BY 1, 2 ORDER BY 1;

SQLite output

day         dow_0_sun  flags  flagged_at
----------  ---------  -----  -------------------
2026-10-26  1          2      08:00, 18:00
2026-10-27  2          3      08:00, 10:00, 18:00
2026-10-28  3          3      08:00, 16:05, 18:00
2026-10-29  4          2      08:00, 18:00
2026-10-30  5          2      08:00, 18:00
2026-10-31  6          2      02:00, 04:00

Dialect notes

The SQLite documentation says the order of group_concat output is arbitrary unless an ORDER BY argument is included. The query above, run on SQLite 3.39.2, has none; the times came out in order on this run, but do not rely on it.
Sums of squares lose precision when values are large relative to their spread (for example a meter reading of 1,234,567.8 kWh changing by 0.1). Subtract a per-point offset, or score differences between consecutive readings instead.
MySQL's frame end INTERVAL 1 SECOND PRECEDING and PostgreSQL's interval '1 microsecond' PRECEDING both exclude the current reading here because readings are at least 5 minutes apart; choose an offset smaller than your shortest interval.

Running it in production

The intervals, history lengths and persistence counts below are illustrative assumptions; set them from how quickly someone must act on a fault and what your database can afford to scan.

Schedule. Run the scoring query on a short schedule (for example every 5 or 15 minutes) over the last few hours only, with a time predicate on the partition column so that it reads one or two partitions. Write flagged episodes to an events table with point, start, end, peak score and method.
Persistence. Alert on episodes that last at least N readings, or that recur, rather than single flags; the overview article discusses persistence and alert fatigue.
Baselines. Rebuild seasonal baselines on a schedule (for example weekly, from the previous four to eight weeks), excluding readings already marked as anomalies or bad quality, so faults do not become the new normal.
Many points. All queries partition by point_id; for thousands of points, index (point_id, ts), score in batches of points, and run heavy baseline rebuilds on a replica or in the warehouse.
Thresholds. The 4 for the z-score and the 3.5 for the modified z-score used on this page are illustrations for the synthetic data, not values to copy. Score a labelled history of real faults and known normal events for each point class, count the faults caught and the false alarms raised at several candidate thresholds, and pick the value whose false-alarm rate the receiving team can sustain; repeat after plant or schedule changes.
CMMS link. An episode on a chiller outside its schedule can raise a work order against the asset ID held in the point metadata, with the episode attached as evidence.

When not to use this. In our view, safety limits belong in the BMS or controller, not in a query that runs every few minutes. Signals with no stable pattern, very short histories or complex multi-sensor faults (a valve that leaks only when a pump runs) need engineering rules or the model-based methods described in the overview. To try the queries, paste the PostgreSQL blocks into the PostgreSQL playground.

Frequently asked questions

How do I detect anomalies in SQL?

One common approach, not the only one: compute a baseline per point with window functions or a grouped baseline table (mean and standard deviation, or median and MAD), score each new reading against it, flag scores above a threshold that you choose and review against labelled history, and group consecutive flags into episodes. Exclude the current reading from its baseline and add a minimum-sample rule and a variance floor.

What is a rolling z-score?

The distance between a reading and the mean of a moving window of earlier readings, divided by that window's standard deviation. In PostgreSQL it is avg() and stddev_samp() over a RANGE BETWEEN interval '1 hour' PRECEDING AND interval '1 microsecond' PRECEDING frame.

Why does my rolling baseline flag every morning start-up?

Because it only knows the last window, so a scheduled change of state looks like a fault. A seasonal baseline, for example the same hour of the same weekday over previous weeks, avoided those false alarms on the sample data on this page.

Can I use percentile_cont as a window function?

Not in PostgreSQL: it is an ordered-set aggregate, and only ordinary aggregates can be used as window functions. Compute percentiles in a grouped baseline table and join to it.

How do I calculate a standard deviation in SQLite?

The SQLite documentation's list of built-in aggregate functions includes no standard deviation or variance function (checked 27 October 2026). Use window sums of the value and of its square to get the variance, compare squared distances with it, and apply a variance floor because the subtraction can leave rounding residue.

Sources

Checked 27 October 2026.

How we research these articles: our editorial method. Research-based from official documentation; SQL examples are run on a local database where the page says so. CodeWithSQL earns nothing from the vendors mentioned.

SQL Anomaly Detection Using Rolling Baselines

Scope: what this page builds

The sample: five weeks of chiller power with three planted faults

Method 1: rolling mean, standard deviation and z-score

What the rolling method caught and missed

Method 2: a seasonal baseline by hour of week

Why the episodes matter

Method 3: a robust baseline with median and MAD

Stuck values and the variance floor

Why the floor matters

The rolling z-score in MySQL 8.0 and SQLite

Dialect notes

Running it in production

Frequently asked questions

Sources

Building a sensor or telemetry database?

Scope: what this page builds

The sample: five weeks of chiller power with three planted faults

Method 1: rolling mean, standard deviation and z-score

What the rolling method caught and missed

Method 2: a seasonal baseline by hour of week

Why the episodes matter

Method 3: a robust baseline with median and MAD

Stuck values and the variance floor

Why the floor matters

The rolling z-score in MySQL 8.0 and SQLite

Dialect notes

Running it in production

Frequently asked questions

Sources

Related

Building a sensor or telemetry database?