# stats_agg() (one variable) functions

ToolkitTimescaleDB Toolkit functions are available under Timescale Community Edition. They are automatically included with Timescale, but must be installed separately for self-hosted TimescaleDB. Click to learn more.## Introduction

Perform common statistical analyses, such as calculating averages and standard deviations, using this group of functions. These functions are similar to the PostgreSQL statistical aggregates, but they include more features and are easier to use in continuous aggregates and window functions.

These functions work on one-dimensional data. To work with two-dimensional data,
for example to perform linear regression, see the two-dimensional `stats_agg`

functions.

###### Related hyperfunction groups

### Aggregate

- stats_agg (one variable)
- Aggregate data into an intermediate statistical aggregate form for further calculation

### Accessor

- average
- Calculate the average from a one-dimensional statistical aggregate
- kurtosis
- Calculate the kurtosis from a one-dimensional statistical aggregate
- num_vals
- Calculate the number of values in a one-dimensional statistical aggregate
- skewness
- Calculate the skewness from a one-dimensional statistical aggregate
- stddev
- Calculate the standard deviation from a one-dimensional statistical aggregate
- sum
- Calculate the sum from a one-dimensional statistical aggregate
- variance
- Calculate the variance from a one-dimensional statistical aggregate

### Rollup

stats_agg(value DOUBLE PRECISION) RETURNS StatsSummary1D

This is the first step for performing any statistical aggregate calculations
on one-dimensional data. Use `stats_agg`

to create an intermediate aggregate
(`StatsSummary1D`

) from your data. This intermediate form can then be used
by one or more accessors in this group to compute final results. Optionally,
multiple such intermediate aggregate objects can be combined using
`rollup()`

or `rolling()`

before an accessor is
applied.`stats_agg`

is well suited for creating a continuous aggregate that can
serve multiple purposes later. For example, you can create a continuous
aggregate using `stats_agg`

to calculate average and sum. Later, you can
reuse the same `StatsSummary1D`

objects to calculate standard deviation from
the same continuous aggregate.

## Required arguments

Name | Type | Description |
---|---|---|

`value` | `DOUBLE PRECISION` | The variable to use for the statistical aggregate. |

## Returns

Column | Type | Description |
---|---|---|

`stats_agg` | `StatsSummary1D` | The statistical aggregate, containing data about the variables in an intermediate form. Pass the aggregate to accessor functions in the statistical aggregates API to perform final calculations. Or, pass the aggregate to rollup functions to combine multiple statistical aggregates into larger aggregates. |

average(summary StatsSummary1D) RETURNS DOUBLE PRECISION

Calculate a simple average (or mean) from the values in a statistical aggregate.

## Required arguments

Name | Type | Description |
---|---|---|

`summary` | `StatsSummary1D` | The statistical aggregate produced by a `stats_agg` call |

## Returns

Column | Type | Description |
---|---|---|

`average` | `DOUBLE PRECISION` | The average of the values in the statistical aggregate |

## Examples

Calculate the average of the integers from 0 to 100:

SELECT average(stats_agg(data))FROM generate_series(0, 100) data;

average-----------50

kurtosis(summary StatsSummary1D,[ method TEXT ]) DOUBLE PRECISION

Calculate the kurtosis from the values in a statistical aggregate. The kurtosis is the fourth statistical moment. It is a measure of “tailedness” of a data distribution compared to a normal distribution.

## Required arguments

Name | Type | Description |
---|---|---|

`summary` | `StatsSummary1D` | The statistical aggregate produced by a `stats_agg` call |

## Optional arguments

Name | Type | Description |
---|---|---|

`method` | `TEXT` | The method used for calculating the kurtosis. The two options are `population` and `sample` , which can be abbreviated to `pop` or `samp` . Defaults to `sample` . |

## Returns

Column | Type | Description |
---|---|---|

`kurtosis` | `DOUBLE PRECISION` | The kurtosis of the values in the statistical aggregate |

## Examples

Calculate the kurtosis of a sample containing the integers from 0 to 100:

SELECT kurtosis(stats_agg(data))FROM generate_series(0, 100) data;

kurtosis----------1.78195

num_vals(summary StatsSummary1D) RETURNS BIGINT

Calculate the number of values contained in a statistical aggregate.

## Required arguments

Name | Type | Description |
---|---|---|

`summary` | `StatsSummary1D` | The statistical aggregate produced by a `stats_agg` call |

## Returns

Column | Type | Description |
---|---|---|

`num_vals` | `DOUBLE PRECISION` | The number of values in the statistical aggregate |

## Examples

Calculate the number of values from 0 to 100, inclusive:

SELECT num_vals(stats_agg(data))FROM generate_series(0, 100) data;

num_vals--------101

skewness(summary StatsSummary1D,[ method TEXT ]) RETURNS DOUBLE PRECISION

Calculate the skewness from the values in a statistical aggregate. The skewness is the third statistical moment. It is a measure of asymmetry in a data distribution.

## Required arguments

Name | Type | Description |
---|---|---|

`summary` | `StatsSummary1D` | The statistical aggregate produced by a `stats_agg` call |

## Optional arguments

Name | Type | Description |
---|---|---|

`method` | `TEXT` | The method used for calculating the skewness. The two options are `population` and `sample` , which can be abbreviated to `pop` or `samp` . Defaults to `sample` . |

## Returns

Column | Type | Description |
---|---|---|

`skewness` | `DOUBLE PRECISION` | The skewness of the values in the statistical aggregate |

## Examples

Calculate the skewness of a sample containing the integers from 0 to 100:

SELECT skewness(stats_agg(data))FROM generate_series(0, 100) data;

skewness_x----------0

stddev(summary StatsSummary1D,[ method TEXT ]) RETURNS DOUBLE PRECISION

Calculate the standard deviation from the values in a statistical aggregate.

## Required arguments

Name | Type | Description |
---|---|---|

`summary` | `StatsSummary1D` | The statistical aggregate produced by a `stats_agg` call |

## Optional arguments

Name | Type | Description |
---|---|---|

`method` | `TEXT` | The method used for calculating the standard deviation. The two options are `population` and `sample` , which can be abbreviated to `pop` or `samp` . Defaults to `sample` . |

## Returns

Column | Type | Description |
---|---|---|

`stddev` | `DOUBLE PRECISION` | The standard deviation of the values in the statistical aggregate |

## Examples

Calculate the standard deviation of a sample containing the integers from 0 to 100:

SELECT stddev(stats_agg(data))FROM generate_series(0, 100) data;

stddev_y--------29.3002

sum(summary StatsSummary1D) RETURNS DOUBLE PRECISION

Calculate the sum of the values contained in a statistical aggregate.

## Required arguments

Name | Type | Description |
---|---|---|

`summary` | `StatsSummary1D` | The statistical aggregate produced by a `stats_agg` call |

## Returns

Column | Type | Description |
---|---|---|

`sum` | `DOUBLE PRECISION` | The sum of the values in the statistical aggregate |

## Examples

Calculate the sum of the integers from 0 to 100:

SELECT sum(stats_agg(data))FROM generate_series(0, 100) data;

sum-----5050

variance(summary StatsSummary1D,[ method TEXT ]) RETURNS DOUBLE PRECISION

Calculate the variance from the values in a statistical aggregate.

## Required arguments

Name | Type | Description |
---|---|---|

`summary` | `StatsSummary1D` | The statistical aggregate produced by a `stats_agg` call |

## Optional arguments

Name | Type | Description |
---|---|---|

`method` | `TEXT` | The method used for calculating the standard deviation. The two options are `population` and `sample` , which can be abbreviated to `pop` or `samp` . Defaults to `sample` . |

## Returns

Column | Type | Description |
---|---|---|

`variance` | `DOUBLE PRECISION` | The variance of the values in the statistical aggregate |

## Examples

Calculate the variance of a sample containing the integers from 0 to 100:

SELECT variance(stats_agg(data))FROM generate_series(0, 100) data;

variance----------858.5

rolling(ss StatsSummary1D) RETURNS StatsSummary1D

Combine multiple intermediate statistical aggregate (`StatsSummary1D`

) objects into a single `StatsSummary1D`

object. It is optimized for use in a window function context for computing tumbling window statistical aggregates.

###### Note

This is especially useful for computing tumbling window aggregates from a continuous aggregate. It can be orders of magnitude faster because it uses inverse transition and combine functions, with the possibility that bigger floating point errors can occur in unusual scenarios.
For re-aggregation in a non-window function context, such as combining hourly buckets into daily buckets, see `rollup()`

.

## Required arguments

Name | Type | Description |
---|---|---|

`summary` | `StatsSummary1D` | The statistical aggregate produced by a `stats_agg` call |

## Returns

Column | Type | Description |
---|---|---|

`rolling` | `StatsSummary1D` | A new statistical aggregate produced by combining the input statistical aggregates |

## Examples

Combine hourly continuous aggregates to create a tumbling window daily aggregate. Calculate the average and standard deviation using the appropriate accessors:

CREATE MATERIALIZED VIEW foo_hourlyWITH (timescaledb.continuous)AS SELECTtime_bucket('1h'::interval, ts) AS bucket,stats_agg(value) as statsFROM fooGROUP BY 1;SELECTbucket,average(rolling(stats) OVER (ORDER BY bucket RANGE '1 day' PRECEDING)),stddev(rolling(stats) OVER (ORDER BY bucket RANGE '1 day' PRECEDING)),FROM foo_hourly;

rollup(ss StatsSummary1D) RETURNS StatsSummary1D

Combine multiple intermediate statistical aggregate (`StatsSummary1D`

) objects produced by `stats_agg`

(one variable) into a single intermediate `StatsSummary1D`

object. For example, you can use `rollup`

to combine statistical aggregates from 15-minute buckets into daily buckets.
For use in window functions, see `rolling()`

.

## Required arguments

Name | Type | Description |
---|---|---|

`summary` | `StatsSummary1D` | The statistical aggregate produced by a `stats_agg` call |

## Returns

Column | Type | Description |
---|---|---|

`rollup` | `StatsSummary1D` | A new statistical aggregate produced by combining the input statistical aggregates |

Create a statistical aggregate to summarize daily statistical data about the
variable `val1`

. Use the statistical aggregate to calculate average, standard
deviation, and skewness of the variable:

WITH t as (SELECTtime_bucket('1 day'::interval, ts) as dt,stats_agg(val1) AS stats1DFROM fooWHERE id = 'bar'GROUP BY time_bucket('1 day'::interval, ts))SELECTaverage(stats1D),stddev(stats1D),skewness(stats1D)FROM t;

Keywords

Found an issue on this page?

Report an issue!