Retrieve all last points/records per tag in InfluxDB, without WHERE clause

0
votes

I have roughly 1500 devices which, at random frequencies, push data to my InfluxDB v1.8+ instance. I am looking for the most efficient way, at least in terms of performance, to collect the last point for each device_id (tag).

For the sake of brevity the data in this measurement it quite simple:

time, device_id (tag), usage (in bytes)
1593514228321002500, 'A12345', 32212254720

I am guaranteed that each and every point has the 'usage' field populated. That is not a concern. So having the latest point, for each tag (device_id), with a timestamp is actually what I want.

My current solution, which is far from scalable nor efficient, is listing all device_id's first:

$ show tag values on "my_database" with key = "device_id"

... and then iterate through each to get the last point:

$ select time, device_id, usage
    from @measurement
    where device_id = '@device_id'
    order by time desc
    limit 1

My question: how can I most efficiently fetch the last points/records for all device_ids (tag), without providing a WHERE clause on device_id? Preferably in one command, so I can remove the foreach iteration.

The same question has been asked for other flavours, for example here in MySQL. This question is specific to InfluxDB.

1
sqlinfluxdbinfluxql

1 Answers

0
votes

You can try this. Use last() for any of the one field.

select last(usage), * from @measurement group by device_id;