interpolated_rate() - Tiger Data

Early access Since 1.8.0 Calculate the rate of change in a gauge over a time period. Data points at the exact boundaries of the time period aren’t needed. The function interpolates the gauge values at the boundaries from adjacent gauge aggregates if needed.

Samples

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,
        gauge_agg(ts, val) AS summary
    FROM foo
    GROUP BY id, time_bucket('15 min'::interval, ts)
) t

Arguments

The syntax is:

interpolated_rate(
    summary GaugeSummary,
    start TIMESTAMPTZ,
    interval INTERVAL
    [, prev GaugeSummary]
    [, next GaugeSummary]
) RETURNS DOUBLE PRECISION

Name	Type	Default	Required	Description
`summary`	`GaugeSummary`	-	✔	A gauge aggregate created using `gauge_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
`prev`	`GaugeSummary`	-		The gauge 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`	`GaugeSummary`	-		The gauge 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

Column	Type	Description
interpolated_rate	DOUBLE PRECISION	The per-second rate of change of the gauge 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 gauge aggregates.