> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify-poc.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Calculate common statistical measures

> Use two-step aggregation for continuous aggregates and window functions

export const TOOLKIT_LONG = 'TimescaleDB Toolkit';

export const PG = 'Postgres';

export const TIMESCALE_DB = 'TimescaleDB';

export const SERVICE_LONG = 'Tiger Cloud service';

export const SELF_LONG = 'self-hosted TimescaleDB';

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 {TIMESCALE_DB} use a two-step aggregation process. First,
you create an aggregate with [`stats_agg()`][stats-aggs], 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 [{SERVICE_LONG}][create-service] with Real-time analytics enabled.<p />

  You need [your connection details][connection-info]. This procedure also
  works for [{SELF_LONG}][enable-timescaledb].

[create-service]: /deploy-and-operate/tiger-cloud/get-started/create-services

[enable-timescaledb]: /deploy-and-operate/self-hosted/install-and-update/install-self-hosted

[connection-info]: /integrations/find-connection-details

## 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][blog-two-step-aggregates].

[blog-two-step-aggregates]: https://www.timescale.com/blog/how-postgresql-aggregation-works-and-how-it-inspired-our-hyperfunctions-design

## Calculate basic statistics

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

1. **Create the `measurements` hypertable**

   ```sql theme={"dark"}
   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:

   ```sql theme={"dark"}
   -- 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**

   ```sql theme={"dark"}
   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:

   ```sql theme={"dark"}
   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**

   ```sql theme={"dark"}
   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:

   ```sql theme={"dark"}
   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

   ```sql theme={"dark"}
   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 {PG} aggregation works and how it inspired the two-step
  aggregation design, read our [aggregation blog post][blog-aggregates].
* For technical details about two-step aggregation, see the
  [developer documentation][gh-two-step-agg].

[blog-aggregates]: https://www.tigerdata.com/blog/how-postgresql-aggregation-works-and-how-it-inspired-our-hyperfunctions-design

[gh-two-step-agg]: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/two-step_aggregation.md

[stats-aggs]: /api-reference/timescaledb-toolkit/statistical-and-regression-analysis/stats_agg