How to calculate the total volume over a time period, where the field 'vol' records the cumulative meter reading, and the meter might reset to zero during this period.
If there were no resets, I could simply use:
select max(vol) - min(vol) from t
However, this doesn't work if the meter resets to zero during the query period, as it would exclude the volume before the reset.
The logic is:
For a sequence of meter readings vol = [10, 20, 30, 0, 11, 22, 33] within a time range:
This sequence represents cumulative meter readings that increase over time until being reset to zero at certain points (like after the 30 reading), then start accumulating again.
The total volume should be (30-10) + (33-0) = 20 + 33 = 53
The first segment ([10, 20, 30]) shows a total increase of 20 (from 10 to 30). After a reset to 0, the second segment ([0, 11, 22, 33]) accumulates an increase of 33.
How can I implement this in DolphinDB using SQL statements?
The logic can be transformed into:
Calculate (sum of all numbers before 0) - (first number) + (last number).
In DolphinDB SQL, it can be written as:
id = 1..7
vol = [10, 20, 30, 0, 11, 22, 33]
t = table(id, vol)
select vol[next(vol) == 0] - first(vol) + last(vol) as cumVol from t
// output：
cumVol
------
53