InfluxDB: selecting last() values from different tags

influx-dbqueryquery-performancetime-series-database

I have a bucket with the raw data of several sensors, things like temperature, air pressure and etc. The sensors don't send their data at the same time, which means that a given timestamp might have several data points from various sensors or just from just one.

Each reading is tagged with the ID of the sensor it comes from. I need to get the Last() reading from a given number of IDs. When I try this code:

select last(Temperature) from raw_measure where ID =~ /4372502|4399699|4406512|4407840/

instead of returning the last Temperature observation of each ID, it returns the most recent temperature timestamp of the whole group.

How can I get the last reading of each ID in a single query?

Best Answer

I found the solution. All you need to do is add an ORDER BY with the TAG field. So the end query becomes:

SELECT last(Temperature) 
FROM raw_measure 
where ID =~ /4372502|4399699|4406512|4407840/ 
ORDER BY ID

Related Solutions

Mysql – Several values from different tables

Ok, first off I think you are almost there. From looking at the schema and data one thing I have noticed is that you seem to have overlooked the fact that a friendship is bi-directional. So when you create a friend entry from a request you also need to create one in the other direction as well:

INSERT INTO `default_friend` (`friend_id`, `user_id`, `is_suscriber`, `privacy`, `created_at`, `friend_list_id`, `approved`) 
VALUES (1, 2, 1, 0, '2012-08-13 18:16:11', 0, 1);

After you have done that your query should be more like the result you are after. Running this query:

select distinct u.id as `user_id`, u.username, f.id as `friend_id`, f.username as friend, s.*
from default_users as u
left join default_friend as df on df.user_id = u.id
left join default_users as f on f.id = df.friend_id
left join default_status as s on s.user_id = u.id
left join default_comment as c on c.status_id = s.status_id
order by s.status_id;

returns the following result set:

user_id username    friend_id   friend  status_id   message created_at  privacy user_id is_reply    device  
1   admin   2   demo    1   dasdasdasdasdasd    2012-08-13 19:45:37 NULL    1   0   
2   demo    1   admin   2   dasdasdasdasdasd    2012-08-13 19:46:03 NULL    2   0   
1   admin   2   demo    3   dasdsad344hbvnbnhjhgjhjghjhj    2012-08-13 21:54:53 NULL    1   0

Is this anywhere near what you are looking for?

MySQL Query fix and speed up

Start by changing IN ( SELECT max... ) to = ( SELECT max... ).

Change f.Food_name to

( SELECT Food_name FROM Food WHERE Food_therm_id = t.Therm_id ) AS Food_name

and get rid of the LEFT JOIN Food ... (I am unsure whether the LEFT JOIN before JOIN is causing trouble.)

If those suggestions do not speed it up enough, then...

Please provide EXPLAIN SELECT ... so we can see how the query is executed.

What table is Temp_records_id in? Perhaps e? (Please qualify all column names.)

Best Answer

Related Solutions

Mysql – Several values from different tables

MySQL Query fix and speed up

Related Question