Hi,
I have a table with 5 columns:
- One with a timestamp incrementing in 15 minutes per row (time_15m)
- One counting entries (in_count)
- One counting exits (out_count)
- One summing both ins and outs (count_sum)
- One with a cumulative sum of count_sum (cumulative)
| time_15m |
in_count |
out_count |
count_sum |
cumulative |
| 2022-01-01 00:00:00+00 |
0 |
0 |
0 |
0 |
| 2022-01-01 00:15:00+00 |
2 |
0 |
2 |
2 |
| 2022-01-01 00:30:00+00 |
0 |
1 |
-1 |
1 |
| 2022-01-01 00:45:00+00 |
3 |
1 |
2 |
3 |
| 2022-01-01 01:00:00+00 |
1 |
1 |
0 |
3 |
| 2022-01-01 01:15:00+00 |
0 |
2 |
-2 |
1 |
The query for cumulative is as follows:
rangesum( above( sum(count_sum),0,rowno(TOTAL)))
I need cumulative to reset to 0 every time a new day begins, like so:
| time_15m |
in_count |
out_count |
count_sum |
cumulative |
| 2022-01-01 23:30:00+00 |
0 |
0 |
0 |
3 |
| 2022-01-01 23:45:00+00 |
0 |
1 |
-1 |
2 |
| 2022-01-02 00:00:00+00 |
0 |
0 |
0 |
0 |
| 2022-01-02 00:15:00+00 |
1 |
0 |
1 |
1 |
| 2022-01-02 00:30:00+00 |
2 |
1 |
1 |
2
|
The query currently has the cumulative column constantly accumulating regardless of the time_15m value.
How can this query be modified to achieve this?
Thank you.
