Aggregate Flux Query in Influxdb

user_11590192 0
Viewed 4123

I am new to Influxdb. I am using 1.8+ Influxdb and com.influxdb:influxdb-client-java:1.11.0. I have a below measurement

stocks {
  (tag) symbol: String
  (field) price: Double
  (field) volume: Long
  (time) ts: Long
}

I am trying to query the measurement with a 15 min window. I have the below query

"from(bucket: \"test/autogen\")" +
  " |> range(start: -12h)" +
  " |> filter(fn: (r) => (r[\"_measurement\"] == \"$measurementName\" and r[\"_field\"] == \"volume\"))" +
  " |> cumulativeSum(columns: [\"_value\"])" +
  " |> window(every: 15m, period: 15m)"

I believe that the above query calculates the cumulative sum over the data and returns just the volume field. However, I want the entire measurement including price, symbol, and ts along with the cumulative sum of the volume in a single flux query. I am not sure how to do this. Any help is appreciated. Thanks.

influxdb
1 Answers

Thanks to Ethan Zhang. Flux output tables use a vertical (column-wise) data layout for fields. Note that the price and the volume fields are stored as two separate rows. To achieve the result you can use a function called v1.fieldsAsCols() to convert the table from a vertical layout back to the horizontal layout. Here is a link to its documentation: https://docs.influxdata.com/influxdb/v2.0/reference/flux/stdlib/influxdb-v1/fieldsascols/ Hence query can be rewritten as follows: sample query 1

from(bucket: \"test/autogen\")
|> range(start: -1h)
|> filter(fn: (r) => r["_measurement"] == "stocks"))
|> v1.fieldsAsCols()
|> group()
|> cumulativeSum(columns: ["volume"])
|> window(every: 15m, period: 15m)

Another approach is using pivot: sample query 2

from(bucket: \"test/autogen\")
|> range(start: -1h)
|> filter(fn: (r) => r["_measurement"] == "stocks")
|> pivot(rowKey:[\"_time\"], columnKey: [\"_field\"], valueColumn: \"_value\")
|> group()
|> cumulativeSum(columns: ["volume"])
|> window(every: 15m, period: 15m)
user_115901920
Related