Nested InfluxDB query as HA sensor

Configuration
1

I’d like to translate this query into an influxdb sensor:

SELECT MEDIAN("derivatives") FROM (SELECT derivative(MEAN("value"),1h) AS "derivatives" FROM "home_assistant"."autogen"."sensor.temp_esszimmer_pm" WHERE time > now() - 150m GROUP BY time(5m) )

But I don’t know how to handle the 2 SELECT statements. Can anyone give me a hint?
Thank you!

2

As a workaround I’m using an SQL query at the moment:

SELECT * FROM ( SELECT ( SELECT ( (SELECT state FROM `states` WHERE entity_id = 'sensor.temp_esszimmer_pm' ORDER BY last_updated DESC LIMIT 1) - (SELECT state FROM `states` WHERE entity_id = 'sensor.temp_esszimmer_pm' and last_updated < UTC_TIMESTAMP() - INTERVAL 30 MINUTE ORDER BY last_updated DESC LIMIT 1) ) / ( SELECT TIMESTAMPDIFF(SECOND, (SELECT last_updated FROM `states` WHERE entity_id = 'sensor.temp_esszimmer_pm' and last_updated < UTC_TIMESTAMP() - INTERVAL 30 MINUTE ORDER BY last_updated DESC LIMIT 1), (SELECT last_updated FROM `states` WHERE entity_id = 'sensor.temp_esszimmer_pm' ORDER BY last_updated DESC LIMIT 1)) / 3600 )) AS rate UNION SELECT ( SELECT ( (SELECT state FROM `states` WHERE entity_id = 'sensor.temp_esszimmer_pm' ORDER BY last_updated DESC LIMIT 1) - (SELECT state FROM `states` WHERE entity_id = 'sensor.temp_esszimmer_pm' and last_updated < UTC_TIMESTAMP() - INTERVAL 60 MINUTE ORDER BY last_updated DESC LIMIT 1) ) / ( SELECT TIMESTAMPDIFF(SECOND, (SELECT last_updated FROM `states` WHERE entity_id = 'sensor.temp_esszimmer_pm' and last_updated < UTC_TIMESTAMP() - INTERVAL 60 MINUTE ORDER BY last_updated DESC LIMIT 1), (SELECT last_updated FROM `states` WHERE entity_id = 'sensor.temp_esszimmer_pm' ORDER BY last_updated DESC LIMIT 1)) / 3600 )) UNION SELECT ( SELECT ( (SELECT state FROM `states` WHERE entity_id = 'sensor.temp_esszimmer_pm' ORDER BY last_updated DESC LIMIT 1) - (SELECT state FROM `states` WHERE entity_id = 'sensor.temp_esszimmer_pm' and last_updated < UTC_TIMESTAMP() - INTERVAL 90 MINUTE ORDER BY last_updated DESC LIMIT 1) ) / ( SELECT TIMESTAMPDIFF(SECOND, (SELECT last_updated FROM `states` WHERE entity_id = 'sensor.temp_esszimmer_pm' and last_updated < UTC_TIMESTAMP() - INTERVAL 90 MINUTE ORDER BY last_updated DESC LIMIT 1), (SELECT last_updated FROM `states` WHERE entity_id = 'sensor.temp_esszimmer_pm' ORDER BY last_updated DESC LIMIT 1)) / 3600 )) UNION SELECT ( SELECT ( (SELECT state FROM `states` WHERE entity_id = 'sensor.temp_esszimmer_pm' ORDER BY last_updated DESC LIMIT 1) - (SELECT state FROM `states` WHERE entity_id = 'sensor.temp_esszimmer_pm' and last_updated < UTC_TIMESTAMP() - INTERVAL 120 MINUTE ORDER BY last_updated DESC LIMIT 1) ) / ( SELECT TIMESTAMPDIFF(SECOND, (SELECT last_updated FROM `states` WHERE entity_id = 'sensor.temp_esszimmer_pm' and last_updated < UTC_TIMESTAMP() - INTERVAL 120 MINUTE ORDER BY last_updated DESC LIMIT 1), (SELECT last_updated FROM `states` WHERE entity_id = 'sensor.temp_esszimmer_pm' ORDER BY last_updated DESC LIMIT 1)) / 3600 )) UNION SELECT ( SELECT ( (SELECT state FROM `states` WHERE entity_id = 'sensor.temp_esszimmer_pm' ORDER BY last_updated DESC LIMIT 1) - (SELECT state FROM `states` WHERE entity_id = 'sensor.temp_esszimmer_pm' and last_updated < UTC_TIMESTAMP() - INTERVAL 150 MINUTE ORDER BY last_updated DESC LIMIT 1) ) / ( SELECT TIMESTAMPDIFF(SECOND, (SELECT last_updated FROM `states` WHERE entity_id = 'sensor.temp_esszimmer_pm' and last_updated < UTC_TIMESTAMP() - INTERVAL 150 MINUTE ORDER BY last_updated DESC LIMIT 1), (SELECT last_updated FROM `states` WHERE entity_id = 'sensor.temp_esszimmer_pm' ORDER BY last_updated DESC LIMIT 1)) / 3600 )) ORDER BY rate DESC LIMIT 3) two ORDER BY rate ASC LIMIT 1

Yes, that’s kind of a monster… :slight_smile: