Calculate common statistical measures

Use two-step aggregation for continuous aggregates and window functions

Statistical aggregation provides efficient ways to calculate common statistical measures like averages, standard deviations, and kurtosis. These aggregates work seamlessly with continuous aggregates and window functions, making it easy to analyze time-series data at different time scales and perform rolling calculations.

The statistical aggregation functions in use a two-step aggregation process. First, you create an aggregate with stats_agg(), which produces an intermediate form that can be efficiently stored and re-aggregated. Second, you apply accessor functions like average(), stddev(), or kurtosis() to extract the final values. This design makes it straightforward to combine aggregates, work with continuous aggregates, and perform complex rolling window calculations.

Statistical aggregates are available in both one-dimensional and two-dimensional forms. The two-dimensional form enables linear regression analysis by tracking the relationship between dependent and independent variables.

Prerequisites

To follow the steps on this page:

• Create a target with Real-time analytics enabled.

  You need your connection details. This procedure also works for .

Two-step aggregation

This group of functions uses the two-step aggregation pattern.

Rather than calculating the final result in one step, you first create an intermediate aggregate by using the aggregate function.

Then, use any of the accessors on the intermediate aggregate to calculate a final result. You can also roll up multiple intermediate aggregates with the rollup functions.

The two-step aggregation pattern has several advantages:

1. More efficient because multiple accessors can reuse the same aggregate
2. Easier to reason about performance, because aggregation is separate from final computation
3. Easier to understand when calculations can be rolled up into larger intervals, especially in window functions and continuous aggregates
4. Perform retrospective analysis even when underlying data is dropped, because the intermediate aggregate stores extra information not available in the final result

To learn more, see the blog post on two-step aggregates.

Calculate basic statistics

This example calculates the average, standard deviation, and kurtosis over time buckets.

1. Create the measurements hypertable

   CREATE TABLE measurements (
     ts TIMESTAMPTZ NOT NULL,
     sensor_id INTEGER,
     val DOUBLE PRECISION
   ) WITH (tsdb.hypertable);
   

2. Insert sample data

   Generate measurement data with varying distributions:

   -- Insert normal measurements
   INSERT INTO measurements (ts, sensor_id, val)
   SELECT
     time,
     sensor_id,
     10 + random() * 5 as val
   FROM generate_series(
     now() - interval '7 days',
     now(),
     interval '30 seconds'
   ) AS g1(time),
   generate_series(1, 3) AS g2(sensor_id);
   
   -- Insert some outliers
   INSERT INTO measurements (ts, sensor_id, val)
   SELECT
     time,
     1 as sensor_id,
     50 + random() * 20 as val
   FROM generate_series(
     now() - interval '7 days',
     now(),
     interval '1 hour'
   ) AS g1(time)
   WHERE random() < 0.1;
   

3. Calculate statistics over 10-minute buckets

   SELECT
       time_bucket('10 min'::interval, ts) as bucket,
       average(stats_agg(val)) as avg,
       stddev(stats_agg(val), 'pop') as std_dev,
       kurtosis(stats_agg(val), 'pop') as kurt
   FROM measurements
   GROUP BY bucket
   ORDER BY bucket DESC
   LIMIT 10;
   

Calculate rolling window statistics

Use window functions to calculate statistics over rolling time windows.

1. Calculate 15-minute rolling statistics

   This query first aggregates data into 1-minute buckets, then uses a window function to calculate rolling 15-minute statistics:

   SELECT
       bucket,
       average(rolling(stats_agg) OVER fifteen_min) as avg,
       stddev(rolling(stats_agg) OVER fifteen_min, 'pop') as std_dev,
       kurtosis(rolling(stats_agg) OVER fifteen_min, 'pop') as kurt
   FROM (
       SELECT
           time_bucket('1 min'::interval, ts) AS bucket,
           stats_agg(val)
       FROM measurements
       WHERE ts > now() - interval '1 day'
       GROUP BY bucket
   ) AS stats
   WINDOW fifteen_min as (ORDER BY bucket ASC RANGE '15 minutes' PRECEDING)
   ORDER BY bucket DESC
   LIMIT 10;
   

Perform linear regression

The two-dimensional stats_agg performs linear regression on two variables.

1. Create the measurements_multival hypertable

   CREATE TABLE measurements_multival (
     ts TIMESTAMPTZ NOT NULL,
     sensor_id INTEGER,
     val1 DOUBLE PRECISION,
     val2 DOUBLE PRECISION
   ) WITH (tsdb.hypertable);
   

2. Insert correlated sample data

   Generate data where val2 has a linear relationship with val1:

   INSERT INTO measurements_multival (ts, sensor_id, val1, val2)
   SELECT
     time,
     sensor_id,
     random() * 100 as val1,
     (random() * 100) * 2 + 50 as val2  -- val2 ≈ 2*val1 + 50 + noise
   FROM generate_series(
     now() - interval '7 days',
     now(),
     interval '1 minute'
   ) AS g1(time),
   generate_series(1, 3) AS g2(sensor_id);
   

3. Calculate regression statistics

   The two-dimensional aggregate calculates:

   • Individual statistics for each variable (using _y and _x suffixes)
   • Linear regression parameters (slope, intercept)
   • Correlation coefficient between the variables
   SELECT
       time_bucket('1 hour'::interval, ts) as bucket,
       average_y(stats_agg(val2, val1)) as avg_y,
       average_x(stats_agg(val2, val1)) as avg_x,
       stddev_y(stats_agg(val2, val1)) as std_dev_y,
       stddev_x(stats_agg(val2, val1)) as std_dev_x,
       slope(stats_agg(val2, val1)) as regression_slope,
       intercept(stats_agg(val2, val1)) as regression_intercept,
       corr(stats_agg(val2, val1)) as correlation
   FROM measurements_multival
   WHERE ts > now() - interval '1 day'
   GROUP BY bucket
   ORDER BY bucket DESC
   LIMIT 10;
   

• For more information about how aggregation works and how it inspired the two-step aggregation design, read our aggregation blog post.
• For technical details about two-step aggregation, see the developer documentation.