## Introduction

Analyze data whose values are designed to monotonically increase, and where any decreases are treated as resets. The `counter_agg` functions simplify this task, which can be difficult to do in pure SQL.

If it's possible for your readings to decrease as well as increase, use `gauge_agg` instead.

## Functions in this group

### Aggregate

counter_agg
Aggregate counter data into an intermediate form for further analysis

### Accessor

corr
Calculate the correlation coefficient from a counter aggregate
counter_zero_time
Calculate the time when the counter value is predicted to have been zero
delta
Calculate the change in a counter from a counter aggregate
extrapolated_delta
Calculate the extrapolated change from a counter aggregate
extrapolated_rate
Calculate the extrapolated rate of change from a counter aggregate
first_time
Get the first timestamp from a counter aggregate
first_val
Get the first value from a counter aggregate
idelta_left
Calculate the instantaneous change at the left, or earliest, edge of a counter aggregate
idelta_right
Calculate the instantaneous change at the right, or latest, edge of a counter aggregate
intercept
Calculate the y-intercept from a counter aggregate
interpolated_delta
Calculate the change in a counter, interpolating values at boundaries as needed
interpolated_rate
Calculate the rate of change in a counter, interpolating values at boundaries as needed
irate_left
Calculate the instantaneous rate of change at the left, or earliest, edge of a counter aggregate
irate_right
Calculate the instantaneous rate of change at the right, or latest, edge of a counter aggregate
last_time
Get the last timestamp from a counter aggregate
last_val
Get the last value from a counter aggregate
num_changes
Get the number of times a counter changed from a counter aggregate
num_elements
Get the number of points with distinct timestamps from a counter aggregate
num_resets
Get the number of counter resets from a counter aggregate
rate
Calculate the rate of change from a counter aggregate
slope
Calculate the slope from a counter aggregate
time_delta
Calculate the difference between the first and last times from a counter aggregate

### Rollup

rollup
Combine multiple counter aggregates

### Mutator

with_bounds
Add bounds to a counter aggregate

## Function details

`counter_agg(  ts TIMESTAMPTZ,  value DOUBLE PRECISION  [, bounds TSTZRANGE]) RETURNS CounterSummary`

This is the first step for performing any aggregate calculations on counter data. Use `counter_agg` to create an intermediate aggregate from your data. This intermediate form can then be used by one or more accessors in this group to compute final results. Optionally, you can combine multiple intermediate aggregate objects using `rollup()` before an accessor is applied.

Required arguments
NameTypeDescription
`ts``TIMESTAMPTZ`The time at each point
`value``DOUBLE PRECISION`The value of the counter at each point
Optional arguments
NameTypeDescription
`bounds``TSTZRANGE`The smallest and largest possible times that can be input to this aggregate. Bounds are required for extrapolation, but not for other accessor functions. If you don't specify bounds at aggregate creation time, you can add them later using the `with_bounds` function.
Returns
ColumnTypeDescription
`counter_agg``CounterSummary`The counter aggregate, containing data about the variables in an intermediate form. Pass the aggregate to accessor functions in the counter aggregates API to perform final calculations. Or, pass the aggregate to rollup functions to combine multiple counter aggregates into larger aggregates.
Examples

Create a counter aggregate to summarize daily counter data:

`SELECT  time_bucket('1 day'::interval, ts) as dt,  counter_agg(ts, val) AS csFROM fooWHERE id = 'bar'GROUP BY time_bucket('1 day'::interval, ts)`
`corr(    summary CounterSummary) RETURNS DOUBLE PRECISION`

Calculate the correlation coefficient from a counter aggregate. The calculation uses a linear least-squares fit, and returns a value between 0.0 and 1.0, from no correlation to the strongest possible correlation.

Required arguments
NameTypeDescription
`summary``CounterSummary`A counter aggregate created using `counter_agg`
Returns
ColumnTypeDescription
`corr``DOUBLE PRECISION`The correlation coefficient calculated with time as the independent variable and counter value as the dependent variable.
Examples

Calculate the correlation coefficient to determine the goodness of a linear fit between counter value and time:

`SELECT    id,    bucket,    corr(summary)FROM (    SELECT        id,        time_bucket('15 min'::interval, ts) AS bucket,        counter_agg(ts, val) AS summary    FROM foo    GROUP BY id, time_bucket('15 min'::interval, ts)) t`
`counter_zero_time(    summary CounterSummary) RETURNS TIMESTAMPTZ`

Calculate the time when the counter value is predicted to have been zero. This is the x-intercept of the linear fit between counter value and time.

Required arguments
NameTypeDescription
`summary``CounterSummary`A counter aggregate created using `counter_agg`
Returns
ColumnTypeDescription
`counter_zero_time``TIMESTAMPTZ`The time when the counter value is predicted to have been zero
Examples

Estimate the time when the counter started:

`SELECT    id,    bucket,    counter_zero_time(summary)FROM (    SELECT        id,        time_bucket('15 min'::interval, ts) AS bucket,        counter_agg(ts, val) AS summary    FROM foo    GROUP BY id, time_bucket('15 min'::interval, ts)) t`
`delta(    summary CounterSummary) RETURNS DOUBLE PRECISION`

Get the change in a counter over a time period. This is the simple delta, computed by subtracting the last seen value from the first, after accounting for resets.

Required arguments
NameTypeDescription
`summary``CounterSummary`A counter aggregated created using `counter_agg`
Returns
ColumnTypeDescription
`delta``DOUBLE PRECISION`The change in the counter over the bucketed interval
Examples

Get the change in each counter over the entire time interval in table `foo`:

`SELECT    id,    delta(summary)FROM (    SELECT        id,        counter_agg(ts, val) AS summary    FROM foo    GROUP BY id) t`
`extrapolated_delta(    summary CounterSummary,    method TEXT) RETURNS DOUBLE PRECISION`

Calculate the change in a counter during the time period specified by the bounds in the counter aggregate. The bounds must be specified for the `extrapolated_delta` function to work. You can provide them as part of the original `counter_agg` call, or by using the `with_bounds` function on an existing counter aggregate.

Required arguments
NameTypeDescription
`summary``CounterSummary`A counter aggregate created using `counter_agg`
`method``TEXT`The extrapolation method to use. Not case-sensitive. The only allowed value is `prometheus`, for the Prometheus extrapolation protocol.
Returns
ColumnTypeDescription
`extrapolated_delta``DOUBLE PRECISION`The extrapolated change in the counter over the time period of the counter aggregate.
Examples

Extrapolate the change in a counter over every 15-minute interval:

`SELECT    id,    bucket,    extrapolated_delta(        with_bounds(            summary,            toolkit_experimental.time_bucket_range('15 min'::interval, bucket)        ),'prometheus'    )FROM (    SELECT        id,        time_bucket('15 min'::interval, ts) AS bucket,        counter_agg(ts, val) AS summary    FROM foo    GROUP BY id, time_bucket('15 min'::interval, ts)) t;`
`extrapolated_rate(    summary CounterSummary,    method TEXT) RETURNS DOUBLE PRECISION`

Calculate the rate of change of a counter during the time period specified by the bounds in the counter aggregate. The bounds must be specified for the `extrapolated_rate` function to work. You can provide them as part of the original `counter_agg` call, or by using the `with_bounds` function on an existing counter aggregate.

Required arguments
NameTypeDescription
`summary``CounterSummary`A counter aggregate created using `counter_agg`
`method``TEXT`The extrapolation method to use. Not case-sensitive. The only allowed value is `prometheus`, for the Prometheus extrapolation protocol.
Returns
ColumnTypeDescription
`extrapolated_rate``DOUBLE PRECISION`The extrapolated rate of change of the counter over the timer period of the counter aggregate.
Examples
`SELECT    id,    bucket,    extrapolated_rate(        with_bounds(            summary,            toolkit_experimental.time_bucket_range('15 min'::interval, bucket)        ),'prometheus'    )FROM (    SELECT        id,        time_bucket('15 min'::interval, ts) AS bucket,        counter_agg(ts, val) AS summary    FROM foo    GROUP BY id, time_bucket('15 min'::interval, ts)) t;`
`first_time(    cs CounterSummary) RETURNS TIMESTAMPTZ`

Get the first timestamp from a counter aggregate.

Required arguments
NameTypeDescription
`cs``CounterSummary`A counter aggregate produced using `counter_agg`
Returns
ColumnTypeDescription
`first_time``TIMESTAMPTZ`The timestamp of the first point in the counter aggregate
Examples

Get the first and last point of each daily counter aggregate:

`WITH t as (  SELECT      time_bucket('1 day'::interval, ts) as dt,      counter_agg(ts, val) AS cs -- get a CounterSummary  FROM table  GROUP BY time_bucket('1 day'::interval, ts))SELECT    dt,    first_time(cs) -- extract the timestamp of the first point in the CounterSummary    last_time(cs) -- extract the timestamp of the last point in the CounterSummaryFROM t;`
`first_val(    cs CounterSummary) RETURNS DOUBLE PRECISION`

Get the value of the first point from a counter aggregate.

Required arguments
NameTypeDescription
`cs``CounterSummary`A counter aggregate produced using `counter_agg`
Returns
ColumnTypeDescription
`first_val``DOUBLE PRECISION`The value of the first point in the counter aggregate
Examples

Get the first and last value of each daily counter aggregate:

`WITH t as (  SELECT      time_bucket('1 day'::interval, ts) as dt,      counter_agg(ts, val) AS cs -- get a CounterSummary  FROM table  GROUP BY time_bucket('1 day'::interval, ts))SELECT    dt,    first_val(cs) -- extract the value of the first point in the CounterSummary    last_val(cs) -- extract the value of the last point in the CounterSummaryFROM t;`
`idelta_left(  summary CounterSummary) RETURNS DOUBLE PRECISION`

Calculate the instantaneous change at the left, or earliest, edge of a counter aggregate. This is equal to the second value minus the first value, after accounting for resets.

Required arguments
NameTypeDescription
`summary``CounterSummary`A counter aggregate created using `counter_agg`
Returns
ColumnTypeDescription
`idelta_left``DOUBLE PRECISION`The instantaneous delta at the left, or earliest, edge of the counter aggregate
Examples

Get the instantaneous change at the start of each 15-minute counter aggregate:

`SELECT    id,    bucket,    idelta_left(summary)FROM (    SELECT        id,        time_bucket('15 min'::interval, ts) AS bucket,        counter_agg(ts, val) AS summary    FROM foo    GROUP BY id, time_bucket('15 min'::interval, ts)) t`
`idelta_right(  summary CounterSummary) RETURNS DOUBLE PRECISION`

Calculate the instantaneous change at the right, or latest, edge of a counter aggregate. This is equal to the last value minus the second-last value, after accounting for resets.

Required arguments
NameTypeDescription
`summary``CounterSummary`A counter aggregate created using `counter_agg`
Returns
ColumnTypeDescription
`idelta_right``DOUBLE PRECISION`The instantaneous delta at the right, or latest, edge of the counter aggregate
Examples

Get the instantaneous change at the end of each 15-minute counter aggregate:

`SELECT    id,    bucket,    idelta_right(summary)FROM (    SELECT        id,        time_bucket('15 min'::interval, ts) AS bucket,        counter_agg(ts, val) AS summary    FROM foo    GROUP BY id, time_bucket('15 min'::interval, ts)) t`
`intercept(    summary CounterSummary) RETURNS DOUBLE PRECISION`

Calculate the y-intercept of a linear least-squares fit between counter value and time. This corresponds to the projected value at the PostgreSQL epoch `(2000-01-01 00:00:00+00)`. You can use the y-intercept with the slope to plot a best-fit line.

Required arguments
NameTypeDescription
`summary``CounterSummary`A counter aggregate created using `counter_agg`
Returns
ColumnTypeDescription
`intercept``DOUBLE PRECISION`The y-intercept of the linear least-squares fit
Examples

Calculate the y-intercept of the linear fit for each 15-minute counter aggregate:

`SELECT    id,    bucket,    intercept(summary)FROM (    SELECT        id,        time_bucket('15 min'::interval, ts) AS bucket,        counter_agg(ts, val) AS summary    FROM foo    GROUP BY id, time_bucket('15 min'::interval, ts)) t`
`interpolated_delta(    summary CounterSummary,    start TIMESTAMPTZ,    interval INTERVAL    [, prev CounterSummary]    [, next CounterSummary]) RETURNS DOUBLE PRECISION`

Calculate the change in a counter over the time period covered by a counter aggregate. Data points at the exact boundaries of the time period aren't needed. The function interpolates the counter values at the boundaries from adjacent counter aggregates if needed.

Required arguments
NameTypeDescription
`summary``CounterSummary`A counter aggregate created using `counter_agg`
`start``TIMESTAMPTZ`The start of the time period to compute the delta over
`interval``INTERVAL`The length of the time period to compute the delta over
Optional arguments
NameTypeDescription
`prev``CounterSummary`The counter aggregate from the previous interval, used to interpolate the value at `start`. If `NULL`, the first timestamp in `summary` is used as the start of the interval.
`next``CounterSummary`The counter aggregate from the next interval, used to interpolate the value at `start + interval`. If `NULL`, the last timestamp in `summary` is used as the end of the interval.
Returns
ColumnTypeDescription
`interpolated_delta``DOUBLE PRECISION`The delta between the first and last points of the time interval. If exact values are missing in the raw data for the first and last points, these values are interpolated linearly from the neighboring counter aggregates.
Examples

Calculate the counter delta for each 15-minute interval, using interpolation to get the values at the interval boundaries if they don't exist in the data:

`SELECT    id,    bucket,    interpolated_delta(        summary,        bucket,        '15 min',        LAG(summary) OVER (PARTITION BY id ORDER by bucket),        LEAD(summary) OVER (PARTITION BY id ORDER by bucket)    )FROM (    SELECT        id,        time_bucket('15 min'::interval, ts) AS bucket,        counter_agg(ts, val) AS summary    FROM foo    GROUP BY id, time_bucket('15 min'::interval, ts)) t`
`interpolated_rate(    summary CounterSummary,    start TIMESTAMPTZ,    interval INTERVAL    [, prev CounterSummary]    [, next CounterSummary]) RETURNS DOUBLE PRECISION`

Calculate the rate of change in a counter over a time period. Data points at the exact boundaries of the time period aren't needed. The function interpolates the counter values at the boundaries from adjacent counter aggregates if needed.

Required arguments
NameTypeDescription
`summary``CounterSummary`A counter aggregate created using `counter_agg`
`start``TIMESTAMPTZ`The start of the time period to compute the rate over
`interval``INTERVAL`The length of the time period to compute the rate over
Optional arguments
NameTypeDescription
`prev``CounterSummary`The counter aggregate from the previous interval, used to interpolate the value at `start`. If `NULL`, the first timestamp in `summary` is used as the start of the interval.
`next``CounterSummary`The counter aggregate from the next interval, used to interpolate the value at `start + interval`. If `NULL`, the last timestamp in `summary` is used as the end of the interval.
Returns
ColumnTypeDescription
`interpolated_rate``DOUBLE PRECISION`The per-second rate of change of the counter between the specified bounds. If exact values are missing in the raw data for the first and last points, these values are interpolated linearly from the neighboring counter aggregates.
Examples

Calculate the per-second rate of change for each 15-minute interval, using interpolation to get the values at the interval boundaries if they don't exist in the data:

`SELECT    id,    bucket,    interpolated_rate(        summary,        bucket,        '15 min',        LAG(summary) OVER (PARTITION BY id ORDER by bucket),        LEAD(summary) OVER (PARTITION BY id ORDER by bucket)    )FROM (    SELECT        id,        time_bucket('15 min'::interval, ts) AS bucket,        counter_agg(ts, val) AS summary    FROM foo    GROUP BY id, time_bucket('15 min'::interval, ts)) t`
`irate_left(  summary CounterSummary) RETURNS DOUBLE PRECISION`

Calculate the instantaneous rate of change at the left, or earliest, edge of a counter aggregate. This is equal to the second value minus the first value, divided by the time lapse between the two points, after accounting for resets. This calculation is useful for fast-moving counters.

Required arguments
NameTypeDescription
`summary``CounterSummary`A counter aggregate created using `counter_agg`
Returns
ColumnTypeDescription
`idelta_left``DOUBLE PRECISION`The instantaneous rate of change at the left, or earliest, edge of the counter aggregate
Examples

Get the instantaneous rate of change at the start of each 15-minute counter aggregate:

`SELECT    id,    bucket,    irate_left(summary)FROM (    SELECT        id,        time_bucket('15 min'::interval, ts) AS bucket,        counter_agg(ts, val) AS summary    FROM foo    GROUP BY id, time_bucket('15 min'::interval, ts)) t`
`irate_right(  summary CounterSummary) RETURNS DOUBLE PRECISION`

Calculate the instantaneous rate of change at the right, or latest, edge of a counter aggregate. This is equal to the last value minus the second-last value, divided by the time lapse between the two points, after accounting for resets. This calculation is useful for fast-moving counters.

Required arguments
NameTypeDescription
`summary``CounterSummary`A counter aggregate created using `counter_agg`
Returns
ColumnTypeDescription
`idelta_right``DOUBLE PRECISION`The instantaneous rate of change at the right, or latest, edge of the counter aggregate
Examples

Get the instantaneous rate of change at the end of each 15-minute counter aggregate:

`SELECT    id,    bucket,    irate_right(summary)FROM (    SELECT        id,        time_bucket('15 min'::interval, ts) AS bucket,        counter_agg(ts, val) AS summary    FROM foo    GROUP BY id, time_bucket('15 min'::interval, ts)) t`
`last_time(    cs CounterSummary) RETURNS TIMESTAMPTZ`

Get the last timestamp from a counter aggregate.

Required arguments
NameTypeDescription
`cs``CounterSummary`A counter aggregate produced using `counter_agg`
Returns
ColumnTypeDescription
`last_time``TIMESTAMPTZ`The timestamp of the last point in the counter aggregate
Examples

Get the first and last point of each daily counter aggregate:

`WITH t as (  SELECT      time_bucket('1 day'::interval, ts) as dt,      counter_agg(ts, val) AS cs -- get a CounterSummary  FROM table  GROUP BY time_bucket('1 day'::interval, ts))SELECT    dt,    first_time(cs) -- extract the timestamp of the first point in the CounterSummary    last_time(cs) -- extract the timestamp of the last point in the CounterSummaryFROM t;`
`last_val(    cs CounterSummary) RETURNS DOUBLE PRECISION`

Get the value of the last point from a counter aggregate.

Required arguments
NameTypeDescription
`cs``CounterSummary`A counter aggregate produced using `counter_agg`
Returns
ColumnTypeDescription
`last_val``DOUBLE PRECISION`The value of the last point in the counter aggregate
Examples

Get the first and last value of each daily counter aggregate:

`WITH t as (  SELECT      time_bucket('1 day'::interval, ts) as dt,      counter_agg(ts, val) AS cs -- get a CounterSummary  FROM table  GROUP BY time_bucket('1 day'::interval, ts))SELECT    dt,    first_val(cs) -- extract the value of the first point in the CounterSummary    last_val(cs) -- extract the value of the last point in the CounterSummaryFROM t;`
`num_changes(    summary CounterSummary) RETURNS BIGINT`

Get the number of times the counter changed during the period summarized by the counter aggregate. Any change is counted, including resets to zero.

Required arguments
NameTypeDescription
`summary``CounterSummary`A counter summary created using `counter_agg`
Returns
ColumnTypeDescription
`num_changes``BIGINT`The number of times the counter changed
Examples

Get the number of times the counter changed over each 15-minute interval:

`SELECT    id,    bucket,    num_changes(summary)FROM (    SELECT        id,        time_bucket('15 min'::interval, ts) AS bucket,        counter_agg(ts, val) AS summary    FROM foo    GROUP BY id, time_bucket('15 min'::interval, ts)) t`
`num_elements(    summary CounterSummary) RETURNS BIGINT`

Get the number of points with distinct timestamps from a counter aggregate. Duplicate timestamps are ignored.

Required arguments
NameTypeDescription
`summary``CounterSummary`A counter aggregate created using `counter_agg`
Returns
ColumnTypeDescription
`num_elements``BIGINT`The number of points with distinct timestamps
Examples

Get the number of points for each 15-minute counter aggregate:

`SELECT    id,    bucket,    num_elements(summary)FROM (    SELECT        id,        time_bucket('15 min'::interval, ts) AS bucket,        counter_agg(ts, val) AS summary    FROM foo    GROUP BY id, time_bucket('15 min'::interval, ts)) t`
`num_resets(    summary CounterSummary) RETURNS BIGINT`

Get the number of times the counter is reset.

Required arguments
NameTypeDescription
`summary``CounterSummary`A counter aggregate created using `counter_agg`
Returns
ColumnTypeDescription
`num_resets``BIGINT`The number of resets within the counter aggregate
Examples

Get the number of counter resets for each 15-minute counter aggregate:

`SELECT    id,    bucket,    num_resets(summary)FROM (    SELECT        id,        time_bucket('15 min'::interval, ts) AS bucket,        counter_agg(ts, val) AS summary    FROM foo    GROUP BY id, time_bucket('15 min'::interval, ts)) t`
`rate(    summary CounterSummary) RETURNS DOUBLE PRECISION`

Calculate the rate of change of the counter. This is the simple rate, equal to the last value minus the first value, divided by the time elapsed, after accounting for resets.

Required arguments
NameTypeDescription
`summary``CounterSummary`A counter aggregate created using `counter_agg`
Returns
ColumnTypeDescription
`rate``DOUBLE PRECISION`The rate of change of the counter
Examples

Get the rate of change per `id` over the entire recorded interval:

`SELECT    id,    rate(summary)FROM (    SELECT        id,        counter_agg(ts, val) AS summary    FROM foo    GROUP BY id) t`
`slope(    summary CounterSummary) RETURNS DOUBLE PRECISION`

Calculate the slope of the linear least-squares fit for a counter aggregate. The dependent variable is the counter value, adjusted for resets, and the independent variable is time. Time is always in seconds, so the slope estimates the per-second rate of change. This gives a result similar to `rate`, but it can more accurately reflect the usual counter behavior in the presence of infrequent, abnormally large changes.

Required arguments
NameTypeDescription
`summary``CounterSummary`A counter aggregate created using `counter_agg`
Returns
ColumnTypeDescription
`slope``DOUBLE PRECISION`The slope of the linear least-squares fit
Examples

Calculate the counter slope per `id` and per 15-minute interval:

`SELECT    id,    bucket,    slope(summary)FROM (    SELECT        id,        time_bucket('15 min'::interval, ts) AS bucket,        counter_agg(ts, val) AS summary    FROM foo    GROUP BY id, time_bucket('15 min'::interval, ts)) t`
`time_delta(    summary CounterSummary) RETURNS DOUBLE PRECISION`

Get the number of seconds between the first and last measurements in a counter aggregate

Required arguments
NameTypeDescription
`summary``CounterSummary`A counter aggregate created using `counter_agg`
Returns
ColumnTypeDescription
`time_delta``DOUBLE PRECISION`The difference, in seconds, between the first and last times
Examples

Get the time difference between the first and last counter readings for each 15-minute interval. Note this difference isn't necessarily equal to `15 minutes * 60 seconds / minute`, because the first and last readings might not fall exactly on the interval boundaries:

`SELECT    id,    bucket,    time_delta(summary)FROM (    SELECT        id,        time_bucket('15 min'::interval, ts) AS bucket,        counter_agg(ts, val) AS summary    FROM foo    GROUP BY id, time_bucket('15 min'::interval, ts)) t`
`rollup(    cs CounterSummary) RETURNS CounterSummary`

This function combines multiple counter aggregates into one. This can be used to combine aggregates from adjacent intervals into one larger interval, such as rolling daily aggregates into a weekly or monthly aggregate.

Required arguments
NameTypeDescription
`cs``CounterSummary`A counter aggregate created using `counter_agg`
Returns
ColumnTypeDescription
`counter_agg``CounterSummary`A new counter aggregate created by combining the input counter aggregates
`with_bounds(    summary CounterSummary,    bounds TSTZRANGE,) RETURNS CounterSummary`

Add time bounds to an already-computed counter aggregate. Bounds are necessary to use extrapolation accessors on the aggregate.

Required arguments
NameTypeDescription
`cs``CounterSummary`A counter aggregate created using `counter_agg`
`bounds``TSTZRANGE`A range of `timestamptz` giving the smallest and largest allowed times in the counter aggregate
Returns
ColumnTypeDescription
`counter_agg``CounterSummary`A new counter aggregate with the bounds applied
Examples

Create a counter aggregate for each `id` and each 15-minute interval. Then add bounds to the counter aggregate, so you can calculate the extrapolated rate:

`SELECT    id,    bucket,    extrapolated_rate(        with_bounds(            summary,            time_bucket_range('15 min'::interval, bucket)        )    )FROM (    SELECT        id,        time_bucket('15 min'::interval, ts) AS bucket,        counter_agg(ts, val) AS summary    FROM foo    GROUP BY id, time_bucket('15 min'::interval, ts)) t`

## Extended examples

### Roll up counter aggregates and calculate deltas

Create daily counter aggregates for a counter with id `bar`:

`SELECT    date_trunc('day', ts) as dt,    counter_agg(ts, val) AS counter_summary -- get a counter aggregateFROM fooWHERE id = 'bar'GROUP BY date_trunc('day')`

Roll up the daily aggregates to get a counter aggregate that covers all recorded timestamps:

`WITH t as (    SELECT        date_trunc('day', ts) as dt,        counter_agg(ts, val) AS counter_summary -- get a counter aggregate    FROM foo    WHERE id = 'bar'    GROUP BY date_trunc('day'))SELECT rollup(counter_summary) AS full_cs -- do a second level of aggregation to get the full counter aggregateFROM t`

Calculate the delta, or the difference between the final and first values, from each daily counter aggregate. Also calculate the fraction of the total delta that happens on each day:

`WITH t as (    SELECT        date_trunc('day', ts) as dt,        counter_agg(ts, val) AS counter_summary -- get a counter aggregate    FROM foo    WHERE id = 'bar'    GROUP BY date_trunc('day')), q as (    SELECT rollup(counter_summary) AS full_cs -- do a second level of aggregation to get the full counter aggregate    FROM t)SELECT    dt,    delta(counter_summary),  -- extract the delta from the daily counter aggregates    delta(counter_summary) / (SELECT delta(full_cs) FROM q LIMIT 1)  as normalized -- get the fraction of the delta that happened each day compared to the full change of the counterFROM t;`

Keywords