2. SQL foundations for industrial time-series

Industrial time-series data has a shape that differs from typical transactional tables. A historian or an OPC UA collector writes one row per sensor reading, so a single production line can generate millions of rows per day. Each row carries a timestamp, a tag identifier, a numeric value, and usually a quality code. Before any Python analysis or machine-learning step, you need to slice, filter, and summarize this data efficiently in SQL. This section builds that foundation using a realistic schema and progressively harder queries.

A representative schema

Assume a table named sensor_readings that stores raw historian data. A second table, sensor_metadata, describes each tag. The DDL below is a reasonable starting point for PostgreSQL or TimescaleDB; other engines differ mainly in type names.

CREATE TABLE sensor_metadata (
    tag_id        TEXT PRIMARY KEY,
    asset_id      TEXT NOT NULL,
    unit          TEXT,
    sample_ms     INTEGER,
    min_valid     NUMERIC,
    max_valid     NUMERIC
);

CREATE TABLE sensor_readings (
    tag_id        TEXT        NOT NULL,
    ts            TIMESTAMPTZ NOT NULL,
    value         DOUBLE PRECISION,
    quality       SMALLINT    NOT NULL DEFAULT 0,
    PRIMARY KEY (tag_id, ts)
);

The composite primary key (tag_id, ts) is deliberate: it enforces one reading per tag per timestamp and gives the query planner a natural index for range scans. The quality column follows the common convention where 0 means good, 1 means uncertain, and 2 means bad. Keeping bad readings in the table rather than deleting them preserves the audit trail, and filtering happens at query time.

SELECT and projection

The simplest useful query returns a bounded time window for one tag. Always constrain ts with a half-open interval [start, end) so adjacent windows do not double-count boundary rows.

SELECT ts, value, quality
FROM   sensor_readings
WHERE  tag_id = 'PUMP-101.DISCHARGE_PRESS'
  AND  ts >= '2024-03-01 06:00:00+00'
  AND  ts <  '2024-03-01 07:00:00+00'
ORDER  BY ts;

Two habits matter here. First, put the equality predicate on tag_id before the range predicate on ts; most engines use the leading column of the composite index to narrow the scan. Second, avoid SELECT * on wide historian tables, because pulling unused columns increases I/O and network cost without benefit.

Filtering on value and quality

Raw values often include sentinel numbers such as -9999 or NaN that indicate a failed sensor. Combine metadata-driven bounds with quality checks so downstream aggregates are not poisoned.

SELECT r.ts, r.value
FROM   sensor_readings r
JOIN   sensor_metadata m USING (tag_id)
WHERE  r.tag_id = 'PUMP-101.DISCHARGE_PRESS'
  AND  r.ts >= '2024-03-01 06:00:00+00'
  AND  r.ts <  '2024-03-01 07:00:00+00'
  AND  r.quality = 0
  AND  r.value BETWEEN m.min_valid AND m.max_valid;

The join to sensor_metadata lets you express validity limits once per tag instead of hard-coding them in every query. If the metadata table is small, this join is cheap and keeps the logic centralized.

Aggregation over time buckets

Dashboards rarely need every raw sample. They need one value per minute or per hour. The standard technique is time bucketing: truncate each timestamp to a fixed grid and group by the truncated value.

SELECT date_trunc('minute', ts) AS bucket,
       avg(value)               AS avg_press,
       min(value)               AS min_press,
       max(value)               AS max_press,
       count(*)                 AS n_samples
FROM   sensor_readings
WHERE  tag_id = 'PUMP-101.DISCHARGE_PRESS'
  AND  ts >= '2024-03-01 06:00:00+00'
  AND  ts <  '2024-03-01 07:00:00+00'
  AND  quality = 0
GROUP  BY bucket
ORDER  BY bucket;

In PostgreSQL and TimescaleDB, date_trunc works directly on timestamptz. In MySQL, use DATE_FORMAT(ts, '%Y-%m-%d %H:%i:00'); in SQL Server, use DATEADD(minute, DATEDIFF(minute, 0, ts), 0). The n_samples column is not decoration: it tells you whether a bucket is trustworthy. A bucket with three samples instead of sixty usually means a collector outage, and you should treat that average as suspect.

Window functions for rolling and comparative metrics

Aggregation collapses rows; window functions keep every row while adding context. This is exactly what you need for rolling averages, deltas, and rate-of-change calculations on sensor streams.

SELECT ts,
       value,
       avg(value) OVER w  AS rolling_avg_15m,
       value - lag(value) OVER w AS delta,
       extract(epoch FROM ts - lag(ts) OVER w) AS dt_seconds
FROM   sensor_readings
WHERE  tag_id = 'PUMP-101.DISCHARGE_PRESS'
  AND  ts >= '2024-03-01 06:00:00+00'
  AND  ts <  '2024-03-01 07:00:00+00'
  AND  quality = 0
WINDOW w AS (ORDER BY ts
             ROWS BETWEEN 14 PRECEDING AND CURRENT ROW)
ORDER  BY ts;

The WINDOW clause names the frame once and reuses it, which keeps the query readable. ROWS BETWEEN 14 PRECEDING AND CURRENT ROW defines a 15-sample window; if the sampling interval is one minute, that is a 15-minute rolling average. Note the distinction between ROWS and RANGE: ROWS counts physical rows, while RANGE works on timestamp values and tolerates gaps. For irregular sampling, prefer RANGE BETWEEN INTERVAL '15 minutes' PRECEDING AND CURRENT ROW.

To compute a rate of change in engineering units per second, divide the delta by the elapsed time. Guard against zero or negative intervals, which can appear when timestamps are duplicated or clocks drift.

SELECT ts,
       value,
       CASE WHEN dt_seconds > 0
            THEN delta / dt_seconds
            ELSE NULL
       END AS rate_per_s
FROM (
    SELECT ts, value,
           value - lag(value) OVER (ORDER BY ts) AS delta,
           extract(epoch FROM ts - lag(ts) OVER (ORDER BY ts)) AS dt_seconds
    FROM   sensor_readings
    WHERE  tag_id = 'PUMP-101.DISCHARGE_PRESS'
      AND  ts >= '2024-03-01 06:00:00+00'
      AND  ts <  '2024-03-01 07:00:00+00'
      AND  quality = 0
) t
ORDER BY ts;

The subquery computes the raw delta and time gap; the outer query applies the safety check. This two-layer pattern is common because window functions cannot be referenced in the same SELECT list where they are defined.

Visualizing the query pipeline

The diagram below shows how a single tag's raw rows move through filtering, bucketing, and windowing to produce the columns a dashboard or Python model consumes.

Practical checks before trusting a result

Three quick validations catch most problems. First, compare count(*) in the filtered set against the expected sample count for the window; a large shortfall points to collector downtime or an overly strict quality filter. Second, look for duplicate (tag_id, ts) pairs if the primary key is not enforced, because duplicates distort both averages and window frames. Third, inspect the distribution of dt_seconds; if it is not close to the configured sample_ms, the data is irregular and ROWS-based windows will misrepresent elapsed time.

With these patterns in place, you can express most industrial time-series questions directly in SQL: bounded scans for raw inspection, bucketed aggregates for trends, and window functions for rolling statistics and rates. The next step is to move these result sets into Python, where the same logic can be parameterized, tested, and composed into larger analytics pipelines.