Skip to main content
Since 1.3.0 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.

​
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.

​
Samples

​
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
FROM foo
WHERE 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
    FROM foo
    WHERE id = 'bar'
    GROUP BY date_trunc('day')
)
SELECT rollup(counter_summary) AS full_cs
FROM 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
    FROM foo
    WHERE id = 'bar'
    GROUP BY date_trunc('day')
), q AS (
    SELECT rollup(counter_summary) AS full_cs
    FROM t
)
SELECT
    dt,
    delta(counter_summary),
    delta(counter_summary) / (SELECT delta(full_cs) FROM q LIMIT 1) AS normalized
FROM t;

​
Available functions

​
Aggregate

  • counter_agg(): aggregate counter data into an intermediate form for further analysis

​
Accessors

  • corr(): calculate the correlation coefficient from a counter aggregate
  • counter_zero_time(): calculate the time when a counter value was zero
  • delta(): calculate the change in a counter’s value
  • extrapolated_delta(): estimate the total change in a counter over a time period
  • extrapolated_rate(): estimate the average rate of change over a time period
  • first_time(): get the timestamp of the first point in a counter aggregate
  • first_val(): get the value of the first point in a counter aggregate
  • idelta_left(): calculate the instantaneous change at the left boundary
  • idelta_right(): calculate the instantaneous change at the right boundary
  • intercept(): calculate the y-intercept from a counter aggregate
  • interpolated_delta(): calculate the change over a specific time range with interpolation
  • interpolated_rate(): calculate the rate of change over a specific time range with interpolation
  • irate_left(): calculate the instantaneous rate at the left boundary
  • irate_right(): calculate the instantaneous rate at the right boundary
  • last_time(): get the timestamp of the last point in a counter aggregate
  • last_val(): get the value of the last point in a counter aggregate
  • num_changes(): get the number of times the counter changed value
  • num_elements(): get the number of points in a counter aggregate
  • num_resets(): get the number of counter resets
  • rate(): calculate the average rate of change
  • slope(): calculate the slope from a counter aggregate
  • time_delta(): calculate the elapsed time in a counter aggregate

​
Rollup

  • rollup(): combine multiple counter aggregates

​
Mutator

  • with_bounds(): add time bounds to a counter aggregate for extrapolation