influxdb - 优化查询（最小值、最大值、第一个和最后一个）

Question

第一篇在这里发布，是 InfluxDB 的新手。

我收到了以下查询，但我不知道如何优化它。

目前，24 小时和 1m 个窗口大约需要 2.4 秒（这是预期的时间量）。

我怀疑其中一个原因是有 4 个表（查询同一组数据）和 3 个连接。

我已经查看了 map 函数以尝试将其减少到一张表，但我似乎无法让它与窗口一起使用。

  bucketName = "${bucket}"
  startTime = -${period}
  interval = ${interval}
  token = "${token}"
  
  minPrice = from (bucket: bucketName)
    |> range(start: startTime, stop: now())
    |> filter(fn: (r) => r["_field"] == token)
    |> window(every: interval)
    |> min()
    |> duplicate(column: "_value", as: "low")
    |> keep(columns: ["low", "_start", "_stop"] )
  
  maxPrice = from (bucket: bucketName)
    |> range(start: startTime, stop: now())
    |> filter(fn: (r) => r["_field"] == token)
    |> window(every: interval)
    |> max()
    |> duplicate(column: "_value", as: "high")
    |> keep(columns: ["high", "_start", "_stop"] )
  
  openPrice = from (bucket: bucketName)
    |> range(start: startTime, stop: now())
    |> filter(fn: (r) => r["_field"] == token)
    |> window(every: interval)
    |> first()
    |> duplicate(column: "_value", as: "open")
    |> keep(columns: ["open", "_stop", "_start"] )
  
  closePrice = from (bucket: bucketName)
    |> range(start: startTime, stop: now())
    |> filter(fn: (r) => r["_field"] == token)
    |> window(every: interval)
    |> last()
    |> duplicate(column: "_value", as: "close")
    |> keep(columns: ["close", "_stop", "_start"] )
  
  highLowData = join(tables: {min: minPrice, max: maxPrice}, on: ["_start", "_stop"])
  openCloseData = join(tables: {open: openPrice, close: closePrice}, on:  ["_start", "_stop"])
  join(tables: {highLow: highLowData, openClose: openCloseData}, on:  ["_start", "_stop"])

我已经设法通过使用联合而不是连接将其优化到 0.7 秒。但是现在我面临着具有空字段的数据。

像这样：

下方查询

startTime = -24h
breakDown = 1m
token = "tokenName"

all = from (bucket: "prices")
  |> range(start: startTime, stop: now())
  |> filter(fn: (r) => r["_field"] == token)
  |> window(every: breakDown)

lowPrice = all
  |> min()
  |> duplicate(column: "_value", as: "low")
  |> keep(columns: ["low", "_stop", "_start"] )

highPrice = all
  |> max()
  |> duplicate(column: "_value", as: "high")
  |> keep(columns: ["high", "_stop", "_start"] )

openPrice = all
  |> first()
  |> duplicate(column: "_value", as: "open")
  |> keep(columns: ["open", "_stop", "_start"] )

closePrice = all
  |> last()
  |> duplicate(column: "_value", as: "close")
  |> keep(columns: ["close", "_stop", "_start"] )

highLowData = union(tables: [lowPrice, highPrice])
openCloseData = union(tables: [openPrice, closePrice])
result = union(tables: [highLowData, openCloseData])
|> yield (name: "Result")

标签： influxdbinfluxdb-2