How to filter using the count function?

,
1

I want to make a Flux query that returns the series only if the number of items is greater than a given value. For example: if it returned two series like

table _time _value
0 2023-12-20T14:00:00Z 5
0 2023-12-20T15:00:00Z 6
1 2023-12-20T14:00:00Z 5
1 2023-12-20T15:00:00Z 6
1 2023-12-20T16:00:00Z 7

if I wanted to keep only the one with more than two items, then the final result would be this:

table _time _value
1 2023-12-20T14:00:00Z 5
1 2023-12-20T15:00:00Z 6
1 2023-12-20T16:00:00Z 7

The query I’m trying to ask is this

from(bucket: “my_bucket”)
|> range(start: -30d)
|> filter(fn: (r) => r[“_measurement”] == “Lora_Temperatura”)
|> filter(fn: (r) => r[“idDispositivo”] == “1980” )
|> filter(fn: (r) => r[“_field”] == “temperatura”)
|> aggregateWindow(every: 1h, fn: mean, createEmpty: false)
|> yield(name: “main”)
|> count()
|> yield(name: “count”)
|> map(fn: (r) => ({
_time: r._time,
_value: r._value,
idReferencia: r.idReferencia,
nueva_columna: if r._value > 10 then 2.0 else 3.0 // Ajusta la lógica para la nueva columna según tus necesidades
}))
|> filter(fn: (r) => r[“new_colum”] > 2.0)
|> yield(name: “result”)

thanks in advance

2

Hello @raidel,
You could do something like:

import "array"

data = array.from(rows: [
{_time: 2020-01-01T00:00:00Z, _value: 1.0, table_num:0},
{_time: 2020-01-02T00:00:00Z, _value: 2.0, table_num:0},
{_time: 2020-01-03T00:00:00Z, _value: 2.0, table_num:1},
{_time: 2020-01-04T00:00:00Z, _value: 3.0, table_num:1},
{_time: 2020-01-05T00:00:00Z, _value: 4.0, table_num:1},
{_time: 2020-01-06T00:00:00Z, _value: 5.0, table_num:1}])
  |> range(start: 2020-01-01T00:00:00Z, stop: 2020-01-07T00:00:00Z)
  |> group(columns: ["table_num"], mode:"by")
//   |> yield(name: "data")


countVal = data |> count() 

join(tables: {data: data, count: countVal}, on: ["table_num"], method: "inner")
|> filter(fn: (r) => r._value_count > 3.0)
3

Thank you for you try to help me. I have made some modifications to your example to adapt it to my real case. This is the result

 data = from(bucket: "my_bucket")
  |> range(start: -30d)
  |> filter(fn: (r) => r["_measurement"] == "my_measure")
  |> filter(fn: (r) => r["idDispositivo"] == "my_idDispositivo")
  |> filter(fn: (r) => r["_field"] == "my_fiel")
  |> aggregateWindow(every: 1h, fn: mean, createEmpty: false)
  //|> group(columns: ["table"], mode:"by")
  countVal = data |> count() 
  |> join(tables: {data: data, count: countVal}, on: ["table"], method: "inner")
  |> filter(fn: (r) => r["_value_count"] > 700)

I have commented the group function because it counted the total result and not by “table”.
The result I get is an error:

type error 15:39-15:45: undefined identifier “countVal”

I have tried to solve the error and have not been able to. Can you continue helping me please?

4

I kept trying and came up with something like this.

data = from(bucket: "myBucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "myMeasurement")
  |> filter(fn: (r) => r["idDispositivo"] == "myIdDispositivo")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> yield(name: "mean")
  data_count = data |> count()
  |> yield(name: "count")
  data |> map(fn: (r) => ({
    r with
    newColumn: data_count |> filter(fn: (row) => row["idReferencia"] == r["idReferencia"])
    			 row["_value"]
  }))
  |> filter(fn: (r) => r["newColumn"] > 100 )
  |> yield(name: "result")

but the result is this:

loc 11:16-12:22:expected an operator between two expressions

I don’t know how to get the query to do what I want. Maybe what I want to do is not possible