Skip to main content
Version: 2.4.x

Two-step aggregation

First, create an intermediate aggregation by using aggregate functions instead of calculating the final result in one step.Then, calculate the final result using analytic functions.

gauge_agg

Analyze Gauge data.Unlike Counter, Gauge can decrease as well as increase.

gauge_agg(time, numeric_expression)
OptionsDescription
numeric_expressionExpression to operate on.Can be a constant, column, or function, and any combination of arithmetic operators.
View gauge_agg Example
SELECT gauge_agg(time, pressure) FROM air GROUP BY date_trunc('month', time);
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| gauge_agg(air.time,air.pressure) |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {first: {ts: 2023-03-01T00:00:00, val: 54.0}, second: {ts: 2023-03-01T00:00:00, val: 59.0}, penultimate: {ts: 2023-03-14T16:00:00, val: 55.0}, last: {ts: 2023-03-14T16:00:00, val: 80.0}, num_elements: 13122} |
| {first: {ts: 2023-02-01T00:00:00, val: 60.0}, second: {ts: 2023-02-01T00:00:00, val: 54.0}, penultimate: {ts: 2023-02-28T23:57:00, val: 74.0}, last: {ts: 2023-02-28T23:57:00, val: 59.0}, num_elements: 26880} |
| {first: {ts: 2023-01-14T16:00:00, val: 63.0}, second: {ts: 2023-01-14T16:00:00, val: 68.0}, penultimate: {ts: 2023-01-31T23:57:00, val: 54.0}, last: {ts: 2023-01-31T23:57:00, val: 77.0}, num_elements: 16640} |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

gauge_agg supports the following functions for two-step aggregation

FunctionDescription
deltaObtain the changes of Gauge within a period of time.This is a simple increment, calculated by subtracting the last seen value from the first one.
time_deltaGet the duration, subtract the time of the last Gauge from the time of the first Gauge.
rateCalculate the ratio of Gauge changes to time changes.
idelta_leftCalculate the earliest instantaneous change of Gauge.This is equal to the second value minus the first value.
idelta_rightCalculate the latest instantaneous change of Gauge.This is equal to the last value minus the penultimate value.

compact_state_agg

Given a system or value that switches between discrete states, summarize the time spent in each state.For example, you can use the compact_state_agg function to track the time the system spends in error, running, or starting states.

compact_state_agg(time_expression, state)
OptionsDescription
time_expressionThe time expression to operate.Can be a constant, column, or function, and any combination of arithmetic operators.
View compact_state_agg Example

The example dataset is as follows:

CREATE TABLE states(state STRING);
INSERT INTO states VALUES ('2020-01-01 10:00:00', 'starting'),('2020-01-01 10:30:00', 'running'),('2020-01-03 16:00:00', 'error'),('2020-01-03 18:30:00', 'starting'),('2020-01-03 19:30:00', 'running'),('2020-01-05 12:00:00', 'stopping');
SELECT * FROM states;
+---------------------+----------+
| time | state |
+---------------------+----------+
| 2020-01-01T10:00:00 | starting |
| 2020-01-01T10:30:00 | running |
| 2020-01-03T16:00:00 | error |
| 2020-01-03T18:30:00 | starting |
| 2020-01-03T19:30:00 | running |
| 2020-01-05T12:00:00 | stopping |
+---------------------+----------+

Aggregate using the compact_state_agg function:

SELECT compact_state_agg(time, state) FROM states;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| compact_state_agg(states.time,states.state) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {state_duration: [{state: error, duration: 0 years 0 mons 0 days 2 hours 30 mins 0.000000000 secs}, {state: starting, duration: 0 years 0 mons 0 days 1 hours 30 mins 0.000000000 secs}, {state: stopping, duration: 0 years 0 mons 0 days 0 hours 0 mins 0.000000000 secs}, {state: running, duration: 0 years 0 mons 3 days 22 hours 0 mins 0.000000000 secs}], state_periods: []} |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

The above example aggregates the state data together for further analysis, compact_state_agg supports the following two-step aggregation functions:

FunctionDescription
duration_inCalculate the duration of a certain state, or calculate the duration of a certain state within a specific time period.

duration_in

duration_in(state_agg_data, state [,begin_time, interval_time]) 
OptionsDescription
state_agg_dataResult set returned by the state_agg_data function.
stateThe state type of any is the same as compact_state_agg.
begin_timeOptional, specifies the start time within the specified time period.
interval_timeOptional, specifies the duration of the time period, when not specified, the time period is infinite.
View duration_in Example
SELECT duration_in(compact_state_agg(time, state), 'running') FROM states;
+--------------------------------------------------------------------------+
| duration_in(compact_state_agg(states.time,states.state),Utf8("running")) |
+--------------------------------------------------------------------------+
| 0 years 0 mons 3 days 22 hours 0 mins 0.000000000 secs |
+--------------------------------------------------------------------------+

state_agg

Given a system or value that switches between discrete states, track transitions between states.

state_agg(time_expression, state)

Calculate the time spent in each state.

View state_agg Example

The example dataset is as follows:

CREATE TABLE states(state STRING);
INSERT INTO states VALUES('2020-01-01 10:00:00', 'starting'),('2020-01-01 10:30:00', 'running'),('2020-01-03 16:00:00', 'error'),('2020-01-03 18:30:00', 'starting'),('2020-01-03 19:30:00', 'running'),('2020-01-05 12:00:00', 'stopping');
SELECT * FROM states;
+---------------------+----------+
| time | state |
+---------------------+----------+
| 2020-01-01T10:00:00 | starting |
| 2020-01-01T10:30:00 | running |
| 2020-01-03T16:00:00 | error |
| 2020-01-03T18:30:00 | starting |
| 2020-01-03T19:30:00 | running |
| 2020-01-05T12:00:00 | stopping |
+---------------------+----------+

Use state_agg function to aggregate:

SELECT state_agg(time, state) FROM states;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| state_agg(states.time,states.state) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {state_duration: [{state: running, duration: 0 years 0 mons 3 days 22 hours 0 mins 0.000000000 secs}, {state: error, duration: 0 years 0 mons 0 days 2 hours 30 mins 0.000000000 secs}, {state: stopping, duration: 0 years 0 mons 0 days 0 hours 0 mins 0.000000000 secs}, {state: starting, duration: 0 years 0 mons 0 days 1 hours 30 mins 0.000000000 secs}], state_periods: [{state: running, periods: [{start_time: 2020-01-01T10:30:00, end_time: 2020-01-03T16:00:00}, {start_time: 2020-01-03T19:30:00, end_time: 2020-01-05T12:00:00}]}, {state: starting, periods: [{start_time: 2020-01-01T10:00:00, end_time: 2020-01-01T10:30:00}, {start_time: 2020-01-03T18:30:00, end_time: 2020-01-03T19:30:00}]}, {state: error, periods: [{start_time: 2020-01-03T16:00:00, end_time: 2020-01-03T18:30:00}]}]} |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

The above example aggregates the state data together for further analysis, state_agg supports the following two-step aggregation functions:

FunctionDescription
duration_inCalculate the duration of a certain state, or calculate the duration of a certain state within a specific time period.
state_atStatistics are in a state of affairs at a time.

duration_in

duration_in(state_agg_data, state [,begin_time, interval_time]) 
OptionsDescription
state_agg_dataResult set returned by the state_agg function.
stateThe state type of any is the same as compact_state_agg.
begin_timeOptional, specifies the start time within the specified time period.
interval_timeOptional, specifies the duration of the time period, when not specified, the time period is infinite.
View duration_in Example

**Calculate the duration of the 'running' status.

SELECT duration_in(state_agg(time, state), 'running') FROM states;
+------------------------------------------------------------------+
| duration_in(state_agg(states.time,states.state),Utf8("running")) |
+------------------------------------------------------------------+
| 0 years 0 mons 3 days 22 hours 0 mins 0.000000000 secs |
+------------------------------------------------------------------+

Calculate the duration of the 'running' state starting from '2020-01-01 11:00:00'.

SELECT duration_in(state_agg(time, state), 'running', Timestamp '2020-01-01 11:00:00') FROM states;
+----------------------------------------------------------------------------------------------+
| duration_in(state_agg(states.time,states.state),Utf8("running"),Utf8("2020-01-01 11:00:00")) |
+----------------------------------------------------------------------------------------------+
| 0 years 0 mons 3 days 21 hours 30 mins 0.000000000 secs |
+----------------------------------------------------------------------------------------------+

Calculate the duration of the 'running' state starting from 2020-01-01 11:00:00 within four days.

SELECT duration_in(state_agg(time, state), 'running', Timestamp '2020-01-01 11:00:00', interval '4 day') FROM states;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| duration_in(state_agg(states.time,states.state),Utf8("running"),Utf8("2020-01-01 11:00:00"),IntervalMonthDayNano("73786976294838206464")) |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| 0 years 0 mons 3 days 20 hours 30 mins 0.000000000 secs |
+-------------------------------------------------------------------------------------------------------------------------------------------+

state_at

state_at(state_agg_data, time_expression)
OptionsDescription
state_agg_dataResult set returned by the state_agg function.
time_expressionThe time expression to operate.Can be a constant, column, or function, and any combination of arithmetic operators.
View state_at Example
SELECT state_at(state_agg(time, state), Timestamp '2020-01-01 10:30:00') FROM states;
+---------------------------------------------------------------------------+
| state_at(state_agg(states.time,states.state),Utf8("2020-01-01 10:30:00")) |
+---------------------------------------------------------------------------+
| running |
+---------------------------------------------------------------------------+