InfluxDB: Is flux the only way to add simple calculations as a column in a query?

Question

I'm trying a query like so:

SELECT COUNT("value"), F("value"),G("value") FROM "someTable" WHERE time >= t1 AND time < t2 GROUP BY (aggregateWindow),*

F = sum of squares, and this wouldn't be too hard if I could do something like the following SUM("value"*"value"), but apparently that doesn't work in Influx (or maybe I'm using the syntax wrong).

G = time stamp of aggregate in unix epoch + aggregateWindow. So for example, if aggregateWindow == 1s, then I would want the following output (assuming there's only one point in that aggregateWindow whose value is value):

time                value F  G
----                ----- -- -----------------
1600272300000000000 1     1  1600272301000000000
1600272301000000000 2     4  1600272302000000000
1600272302000000000 3     9  1600272303000000000
1600272303000000000 4     16 1600272304000000000
1600272304000000000 5     25 1600272305000000000

I know you can implement sum of squares via flux as described here, but I'm worried about the performance of Flux vs regular Influx queries as mentioned here. So basically I'm asking, is flux the only and most efficient way of making a query like this?

Answer 1

Simple:

SELECT 
 COUNT("value"), 
 "value"*"value" AS F,
 POW("value", 2) AS FwithPOWfunction
FROM "someTable" 
WHERE 
  time >= t1 AND time < t2 
GROUP BY (aggregateWindow)

You can't create new time column, but you can apply offset to time grouping GROUP BY time(time_interval,[<offset_interval])

Doc is your good friend to get more details and learn correct syntax:

• https://docs.influxdata.com/influxdb/v1.8/query_language/explore-data/#group-by-time-intervals-and-fill
• https://docs.influxdata.com/influxdb/v1.8/query_language/functions/#pow