2

Here is a sample of my data:

> SELECT time, value from task Limit 5;
name: task
time                value
----                -----
1540149422155456967 0
1540149423155456967 1
1540151481498019507 1
1540151482498019507 0
1540151680870649288 0

I have a measurement that is a boolean - encoded as 1 or 0. I'd like to calculate the total area under a graph - but I'm not sure how to do this?

You can think of this value as indicative as where a water pump is on or off - the rate of water flowing is always equal when turned on. I want to calculate / graph. The 'cumulative' total water that was output.

I've thought of cumulative_sum but that does not really return the result I intend:

> SELECT cumulative_sum("value") from task Limit 5;
name: task
time                cumulative_sum
----                --------------
1540149422155456967 0
1540149423155456967 1
1540151481498019507 2
1540151482498019507 2
1540151680870649288 2

I've also considered the integral function but the values returned seem very odd?

> SELECT integral("value") from task GROUP BY time(1s) Limit 5;
name: task
time                integral
----                --------
1540149422000000000 0.35662646729441955
1540149423000000000 0.9879165657055804
1540151481000000000 2057.874007792324
1540151482000000000 0.12401171467626151
1540151680000000000 0.008365803347453472

If there is a more appropriate way of encoding this data then I can also do that.

Chris Stryczynski
  • 30,145
  • 48
  • 175
  • 286
  • `integral` function is right one. Why the values look odd? They looks OK to me. – Jan Garaj Aug 23 '19 at 19:37
  • Because the integral isn't really the area under the graph - it seems to be the area between each two points defined by the GROUP BY function. So it seems like I can achieve what I intend with `SELECT cumulative_sum(integral("value"))...`. – Chris Stryczynski Aug 23 '19 at 20:40
  • Yes, you are right. If you need area of whole graph, then you need to `sum()` `integral()` – Jan Garaj Aug 23 '19 at 22:09

0 Answers0