Closed williamhbaker closed 3 years ago
Closes https://github.com/influxdata/influxdb-comparisons/issues/206 Related to https://github.com/influxdata/influxdb/issues/22467
Updates the InfluxQL version of the query to output equivalent results as the flux queries for bare aggregates.
Example of current flux query:
from(bucket:"benchmark_db") |> range(start:2018-01-01T01:00:00Z, stop:2018-01-01T03:00:00Z) |> filter(fn:(r) => r._measurement == "air_condition_room" and r._field == "temperature") |> last() |> yield()
Output:
,result,table,_start,_stop,_time,_value,_field,_measurement,home_id,room_id,sensor_id ,_result,0,2018-01-01T01:00:00Z,2018-01-01T03:00:00Z,2018-01-01T02:59:00Z,15,temperature,air_condition_room,0000000000000,1,0000000000005 ,_result,1,2018-01-01T01:00:00Z,2018-01-01T03:00:00Z,2018-01-01T02:59:00Z,18.46016517715859,temperature,air_condition_room,0000000000000,2,0000000000010 ,_result,2,2018-01-01T01:00:00Z,2018-01-01T03:00:00Z,2018-01-01T02:59:00Z,15,temperature,air_condition_room,0000000000000,3,0000000000019 ,_result,3,2018-01-01T01:00:00Z,2018-01-01T03:00:00Z,2018-01-01T02:59:00Z,18.325209815665175,temperature,air_condition_room,0000000000000,4,0000000000028 ,_result,4,2018-01-01T01:00:00Z,2018-01-01T03:00:00Z,2018-01-01T02:59:00Z,27.576516838979604,temperature,air_condition_room,0000000000000,5,0000000000037 ...many more tables... ,_result,6432,2018-01-01T01:00:00Z,2018-01-01T03:00:00Z,2018-01-01T02:59:00Z,25.54107009213492,temperature,air_condition_room,0000000000999,2,0000000054983 ,_result,6433,2018-01-01T01:00:00Z,2018-01-01T03:00:00Z,2018-01-01T02:59:00Z,26.695283195444624,temperature,air_condition_room,0000000000999,3,0000000054990 ,_result,6434,2018-01-01T01:00:00Z,2018-01-01T03:00:00Z,2018-01-01T02:59:00Z,19.96569762569192,temperature,air_condition_room,0000000000999,4,0000000054999 ,_result,6435,2018-01-01T01:00:00Z,2018-01-01T03:00:00Z,2018-01-01T02:59:00Z,25.099527197332623,temperature,air_condition_room,0000000000999,5,0000000055004 ,_result,6436,2018-01-01T01:00:00Z,2018-01-01T03:00:00Z,2018-01-01T02:59:00Z,21.744428074966727,temperature,air_condition_room,0000000000999,6,0000000055009
Example of current InfluxQL query:
SELECT last(temperature) FROM air_condition_room WHERE time >= '2018-01-01T01:00:00Z' AND time < '2018-01-01T03:00:00Z'
{ "results": [ { "statement_id": 0, "series": [ { "name": "air_condition_room", "columns": [ "time", "last" ], "values": [ [ "2018-01-01T02:59:00Z", 21.744428074966727 ] ] } ] } ] }
Example of updated InfluxQL query:
SELECT last(temperature) FROM air_condition_room WHERE time >= '2018-01-01T01:00:00Z' AND time < '2018-01-01T03:00:00Z' GROUP BY home_id,room_id,sensor_id
{ "results": [ { "statement_id": 0, "series": [ { "name": "air_condition_room", "tags": { "home_id": "0000000000000", "room_id": "1", "sensor_id": "0000000000005" }, "columns": [ "time", "last" ], "values": [ [ "2018-01-01T02:59:00Z", 15 ] ] }, { "name": "air_condition_room", "tags": { "home_id": "0000000000000", "room_id": "2", "sensor_id": "0000000000010" }, "columns": [ "time", "last" ], "values": [ [ "2018-01-01T02:59:00Z", 18.46016517715859 ] ] }, { "name": "air_condition_room", "tags": { "home_id": "0000000000000", "room_id": "3", "sensor_id": "0000000000019" }, "columns": [ "time", "last" ], "values": [ [ "2018-01-01T02:59:00Z", 15 ] ] }, { "name": "air_condition_room", "tags": { "home_id": "0000000000000", "room_id": "4", "sensor_id": "0000000000028" }, "columns": [ "time", "last" ], "values": [ [ "2018-01-01T02:59:00Z", 18.325209815665175 ] ] }, { "name": "air_condition_room", "tags": { "home_id": "0000000000000", "room_id": "5", "sensor_id": "0000000000037" }, "columns": [ "time", "last" ], "values": [ [ "2018-01-01T02:59:00Z", 27.576516838979604 ] ] }, // ...many more series... { "name": "air_condition_room", "tags": { "home_id": "0000000000999", "room_id": "2", "sensor_id": "0000000054983" }, "columns": [ "time", "last" ], "values": [ [ "2018-01-01T02:59:00Z", 25.54107009213492 ] ] }, { "name": "air_condition_room", "tags": { "home_id": "0000000000999", "room_id": "3", "sensor_id": "0000000054990" }, "columns": [ "time", "last" ], "values": [ [ "2018-01-01T02:59:00Z", 26.695283195444624 ] ] }, { "name": "air_condition_room", "tags": { "home_id": "0000000000999", "room_id": "4", "sensor_id": "0000000054999" }, "columns": [ "time", "last" ], "values": [ [ "2018-01-01T02:59:00Z", 19.96569762569192 ] ] }, { "name": "air_condition_room", "tags": { "home_id": "0000000000999", "room_id": "5", "sensor_id": "0000000055004" }, "columns": [ "time", "last" ], "values": [ [ "2018-01-01T02:59:00Z", 25.099527197332623 ] ] }, { "name": "air_condition_room", "tags": { "home_id": "0000000000999", "room_id": "6", "sensor_id": "0000000055009" }, "columns": [ "time", "last" ], "values": [ [ "2018-01-01T02:59:00Z", 21.744428074966727 ] ] } ] } ] }
Closes https://github.com/influxdata/influxdb-comparisons/issues/206 Related to https://github.com/influxdata/influxdb/issues/22467
Updates the InfluxQL version of the query to output equivalent results as the flux queries for bare aggregates.
Example of current flux query:
Output:
Example of current InfluxQL query:
Output:
Example of updated InfluxQL query:
Output: