How to join influx db queries

Question

I have a Influx db (V1.8- FLUX disabled by hoster). I want to "add" a column to the measurement which uses the estimated function.

Initial Measurement look like:

time                Current state_string
----                ------- ------------
1577836800000000000 0       off
1577836860000000000 0.125   off
1577836920000000000 0.25    standby
1577836980000000000 0.375   standby
1577837040000000000 0.5     standby
1577837100000000000 0.625   on
1577837160000000000 0.75    on
1577837220000000000 0.875   in use
1577837280000000000 1       in use

Then i would like to add a column which calculates the estimated time:

select elapsed("state", 1s) as "time_in_state" from "device_measurements" where hardware_sensor_id = '31c32c1b-119e-4167-0'

which results in:

time                time_in_state
----                -------------
1577836860000000000 60
1577836920000000000 60
1577836980000000000 60
1577837040000000000 60
1577837100000000000 60
1577837160000000000 60
1577837220000000000 60
1577837280000000000 60

So far so good. Then i wold like to join those two tables and store them in a new one, while preserving the tags. (my real measurements have more tags which are omitted for simplicity here)

I came up with:

select time_in_state into "device_measurements" from 
    (select elapsed("state", 1s) as "time_in_state" from "device_measurements" where hardware_sensor_id = '31c32c1b-119e-4167-0'  group by * ) 
    group by *

I have read that group by * is needed to preserve tags. But i causes one issue:

time                Current state_string time_in_state
----                ------- ------------ -------------
1577836800000000000 0       off          
1577836860000000000 0.125   off          60
1577836920000000000 0.25    standby      
1577836980000000000 0.375   standby      60
1577837040000000000 0.5     standby      60
1577837100000000000 0.625   on           
1577837160000000000 0.75    on           60
1577837220000000000 0.875   in use       
1577837280000000000 1       in use  

As you can see the time_in_state column is not calculated for every row. This is caused by the group by clause...

How can i fromulate the query so that I achieve a table with the tags and the time_in_state for every column. I have read the join() documentation but somehow can't translate the things i read there to code.

Answer 1

Assuming that "state_string" is your tag key, you use "elapsed" function on each series where there are different series for each tag key value: "off", "standby", "on", "in use"... There is no possibility to keep grouping by "state_string" while you want to use elapsed beetwen values from all this different series. What I can suggest is to not group by "state_string" but group by all tag keys excluding this one. So for example you may use grouping only by "hardware_sensor_id":

select time_in_state into "device_measurements" from (select elapsed("state", 1s) as "time_in_state" from "device_measurements" where hardware_sensor_id = '31c32c1b-119e-4167-0' group by "hardware_sensor_id",<put_other_tag_keysIfNeeded> ) group by *

After this operation you will have tables joined but you won't be able to group by "state_string". This is caused because of fact that values of field "time_in_state" won't have tag key "state_string". I've been looking for solution for similar problem a long time but in InfluxQL there is no possibility to make join like that without droping tag keys which separates data on multiple series. Maybe using Flux it would be possible to not drop "state_string" tag key but I have no idea how exacly.