Query sum max until zero

Grafana InfluxDB
1

Hi everybody,

I’m trying to make a table where i can find the total consumption of a machine in relation to an ID number. If the machine never shuts down i can see what is the maximum value of the component it and show the correct value. The problem is when the value goes back to zero and restart a new count

grafana3
grafana31822×1125 171 KB

As you can see here, the machine stop three times and restart the count. In the transformation, if i just take the “max” it will only show 31,1 (as you can see in the image below) but the correct value i want is “40.65”

GRAFANA223
GRAFANA2231363×781 39.5 KB

I use this transformation to represent the table grouped by “ID NUMBER”

Can you guys help me with this? It’s even possible watch the max to zero and sum all this values regarding with “ID Number”?

3

how can one tell that this machine stopped and restarted 3 times?

image

4

It’s because the next value is smaller.

5

31.1 is not smaller than 31.1 ? if you highlighted 31.1 why not 2.71?
also how do you get 40,65?

6

It’s not smaller but it’s the last value for that ID.
You can ignore the 40,65. it’s all the values add up.
I have one query in SQL that works:

select id,parta,partb,partc from 
 (
  select 
   id
   ,parta
   ,partb
   ,partc
   ,lead(parta) over (partition by id order by id) nextval
  from csv
)A
where parta > coalesce(nextval,-1)

This help you?

7

not really as I do not have access to your sql server :stuck_out_tongue_winking_eye:
please provide sample data for csv, ddl and dml

8 
Time,"id","parta","partb","partc"
2022-08-16 15:05:00,1,0.181,0.0909,0.0148
2022-08-16 15:10:00,7,1.23,0.638,0.0936
2022-08-16 15:15:00,7,1.46,0.747,0.112
2022-08-16 15:25:00,7,0.269,0.133,0.0210
2022-08-16 15:30:00,7,1.79,0.848,0.141
2022-08-16 15:35:00,7,1.79,0.848,0.141
2022-08-16 15:40:00,7,1.79,0.848,0.141
2022-08-16 15:45:00,7,1.79,0.848,0.141
2022-08-16 15:50:00,7,3.50,1.66,0.274
2022-08-16 16:00:00,7,4.18,1.99,0.329
2022-08-16 16:05:00,7,4.18,1.99,0.329
2022-08-16 16:10:00,7,4.18,1.99,0.329
2022-08-16 16:15:00,7,4.18,1.99,0.329
2022-08-16 16:20:00,7,4.18,1.99,0.329
2022-08-16 16:25:00,7,4.18,1.99,0.329
2022-08-16 16:30:00,7,4.18,1.99,0.329
2022-08-16 16:35:00,7,4.53,2.15,0.355
2022-08-16 16:40:00,7,4.53,2.15,0.356
2022-08-16 16:45:00,7,4.92,2.34,0.386
2022-08-16 16:50:00,7,5.45,2.58,0.424
2022-08-16 16:55:00,7,5.45,2.58,0.424
2022-08-16 17:00:00,7,5.45,2.58,0.424
2022-08-16 17:05:00,7,7.18,3.40,0.561
2022-08-16 17:10:00,7,7.18,3.40,0.561
2022-08-16 17:15:00,7,7.18,3.40,0.561
2022-08-16 17:20:00,7,7.18,3.40,0.561
2022-08-16 17:25:00,7,7.18,3.40,0.561
2022-08-16 17:30:00,7,7.18,3.40,0.561
2022-08-16 17:35:00,7,7.18,3.40,0.561
2022-08-16 17:40:00,7,7.18,3.40,0.561
2022-08-16 17:45:00,7,7.91,3.76,0.620
2022-08-16 17:50:00,7,8.57,4.07,0.670
2022-08-16 17:55:00,7,8.57,4.07,0.670
2022-08-16 18:00:00,7,8.61,4.09,0.673
2022-08-16 18:05:00,10,0.298,0.137,0.0231
2022-08-16 18:10:00,10,0.628,0.294,0.0497
2022-08-16 18:15:00,17,1.27,0.612,0.100
2022-08-16 18:20:00,17,3.39,1.64,0.268
2022-08-16 18:25:00,17,5.28,2.57,0.417
2022-08-16 18:30:00,17,7.42,3.58,0.585
2022-08-16 18:35:00,17,8.86,4.24,0.693
2022-08-16 18:40:00,17,10.9,5.18,0.846
2022-08-16 18:45:00,17,13.0,6.15,1.00
2022-08-16 18:50:00,17,14.2,6.67,1.09
2022-08-16 18:55:00,17,14.2,6.67,1.09
2022-08-16 19:00:00,17,16.2,7.69,1.17
2022-08-16 19:05:00,17,16.7,7.93,1.22
2022-08-16 19:10:00,17,16.9,8.03,1.24
2022-08-16 19:15:00,17,16.9,8.03,1.24

and i get this as result
sql

1 Like
9

So your post mentions InfluxDB. but your query looks like sql server?

select id,parta,partb,partc from 
 (
  select 
   id
   ,parta
   ,partb
   ,partc
   ,lead(parta) over (partition by id order by id) nextval
  from csv
)A
where parta > coalesce(nextval,-1)
1 Like
10

I already have it working in SQL but I need to change it to InfluxDB and then see it in grafana.

1 Like
11

So i’m still around this trouble and i don’t know if i found a easier solution to solve this, can you please take a look?
So if i use the “SELECT difference” i can see if the next value of the “parta” is bigger or smaller than the previous (if the value is bigger it will gave me positive value and if it’s smaller the value will be negative). Using this i will have the following table:

coonsum
coonsum1412×497 35.9 KB

My problem, as you can see is when the value is negative (in this case “-3.2”) the “id” and “parta” in the line must be the previous values… It’s possible to get this? It will be something like:

grafanaerro
grafanaerro1140×392 80.5 KB

After that i just need to do a transformation to ignore all the “non-negative” values and it should work!!
This are the queries that i’m using:

SELECT difference(distinct("parta")) FROM "testsrv" WHERE $timeFilter GROUP BY time($__interval) fill(null)

SELECT distinct("id") FROM "testsrv" WHERE $timeFilter GROUP BY time($__interval) fill(null)

SELECT distinct("parta") FROM "testsrv" WHERE $timeFilter GROUP BY time($__interval) fill(null)```
12

Hello ,

fill(previous)

you can try this,
and i strongly suggest you to upgrade influxdb to 2.0,
so much feature with telegraf integration, try it !

1 Like
13

in addition to what @alexandrearmand said