InluxDB如何高效聚合

提出问题

InluxDB如何高效聚合

解决问题

使用aggregateWindow ，他支持年y、月mo、周w、天d、小时h、分钟m、秒s 各种精度聚合

示例

t_max=from(bucket:"po")
    |> range(start: 2021-12-31T00:00:00.000Z , stop: 2023-04-02T00:00:00.000Z)//1年+时间范围
    |> filter(fn: (r) =>  r.tag_id =~ /^9\d{1,2}$/ )//正则匹配100个点位
    |> aggregateWindow(every:1mo, fn: max, createEmpty: false)//1mo按月聚合 max
    |> set(key: "flag", value: "max")//增加标志列
t_min=from(bucket:"po")
    |> range(start: 2021-12-31T00:00:00.000Z , stop: 2023-04-02T00:00:00.000Z)
    |> filter(fn: (r) =>  r.tag_id =~ /^9\d{1,2}$/ )
    |> aggregateWindow(every:1mo, fn: min, createEmpty: false)
    |> set(key: "flag", value: "min")
t_mean=from(bucket:"po")
    |> range(start: 2021-12-31T00:00:00.000Z , stop: 2023-04-02T00:00:00.000Z)
    |> filter(fn: (r) =>  r.tag_id =~ /^9\d{1,2}$/ )
    |> aggregateWindow(every:1mo, fn: mean, createEmpty: false)
    |> set(key: "flag", value: "mean")
t_last=from(bucket:"po")
    |> range(start: 2021-12-31T00:00:00.000Z , stop: 2023-04-02T00:00:00.000Z)
    |> filter(fn: (r) =>  r.tag_id =~ /^9\d{1,2}$/ )
    |> aggregateWindow(every:1mo, fn: last, createEmpty: false)    
    |> set(key: "flag", value: "last")
union(tables: [t_max,t_mean,t_min,t_last])//聚合结果
    |>pivot(rowKey: ["tag_id"], columnKey: ["flag"], valueColumn: "_value")//透视

反例

不要使用date函数对时间戳转换这样效率很低
Don`t do this

import "date"
t_mean=from(bucket:"po")
    |> range(start: 2022-01-25T00:00:00.000Z , stop: 2022-08-25T00:00:00.000Z)
    |> filter(fn: (r) =>  r.tag_id =~ /[0-9]{0,2}/ )
    |> map(fn:(r)=>({month: date.month(t:r._time),_value:r._value ,tag_id:r.tag_id}))
    |> group(columns: ["tag_id","month"])
    |> mean()
    |> set(key: "tag", value: "mean")
t_max=from(bucket:"po")
    |> range(start: 2022-01-25T00:00:00.000Z , stop: 2022-08-25T00:00:00.000Z)
    |> filter(fn: (r) =>  r.tag_id =~ /[0-9]{0,2}/ )
    |> map(fn:(r)=>({month: date.month(t:r._time),_value:r._value ,tag_id:r.tag_id}))
    |> group(columns: ["tag_id","month"])
    |> max()
    |> set(key: "tag", value: "max")
t_last=from(bucket:"po")
    |> range(start: 2022-01-25T00:00:00.000Z , stop: 2022-08-25T00:00:00.000Z)
    |> filter(fn: (r) =>  r.tag_id =~ /[0-9]{0,2}/ )
    |> map(fn:(r)=>({month: date.month(t:r._time),_value:r._value ,tag_id:r.tag_id}))
    |> group(columns: ["tag_id","month"])
    |> last()
    |> set(key: "tag", value: "last")

t_min=from(bucket:"po")
    |> range(start: 2022-01-25T00:00:00.000Z , stop: 2022-08-25T00:00:00.000Z)
    |> filter(fn: (r) =>  r.tag_id =~ /[0-9]{0,2}/ )
    |> map(fn:(r)=>({month: date.month(t:r._time),_value:r._value ,tag_id:r.tag_id}))
    |> group(columns: ["tag_id","month"])
    |> min()
    |> set(key: "tag", value: "min")

union(tables: [t_max,t_mean,t_min,t_last])
    |>pivot(rowKey: ["tag_id"], columnKey: ["tag"], valueColumn: "_value")