first aggregate allows you to get the value of one column
as ordered by another. For example,
first(temperature, time) returns the
earliest temperature value based on time within an aggregate group.
first commands do not use indexes, they perform a sequential scan through the group. They are primarily used for ordered selection within a
GROUP BY aggregate, and not as an alternative to an
ORDER BY time DESC LIMIT 1 clause to find the latest value, which uses indexes.
|TEXT||The value to return|
|TIMESTAMP or INTEGER||The timestamp to use for comparison|
Get the earliest temperature by device_id:
SELECT device_id, first(temp, time)FROM metricsGROUP BY device_id;
This example uses first and last with an aggregate filter, and avoids null values in the output:
SELECTTIME_BUCKET('5 MIN', time_column) AS interv,AVG(temperature) as avg_temp,first(temperature,time_column) FILTER(WHERE time_column IS NOT NULL) AS beg_temp,last(temperature,time_column) FILTER(WHERE time_column IS NOT NULL) AS end_tempFROM sensorsGROUP BY interv
Found an issue on this page?Report an issue!