state_agg() and timeline_agg()

The state_agg aggregate measures the amount of time spent in each distinct value of a state field. It is designed to work with a relatively small number of states and might not perform well on queries where states are mostly distinct across rows.

The timeline_agg aggregate works the same as state_agg, but also tracks when states are entered and exited. It has increased memory usage since it needs to track more data.

warning

Experimental features could have bugs. They might not be backwards compatible, and could be removed in future releases. Use these features at your own risk, and do not use any experimental features in production.

Required arguments

Returns

Sample usage

This example creates an aggregate over a status column in a devices table, with a timestamp column time.

-- create a state aggregate:
SELECT toolkit_experimental.state_agg(time, status) FROM devices;
-- create a timeline aggregate:
SELECT toolkit_experimental.timeline_agg(time, status) FROM devices;