In a BigQuery table, I need to refer to the previous value from the same column to calculate the new value for that column like this:
Timestamp
Liters per Minute
Liters remaining
2022-08-15 08:50 UTC
5
1000
2022-08-15 08:51 UTC
4
1000 - (avg(5, 4) = 4.5) = 995.5
2022-08-15 08:51 UTC
6
995.5 - (avg(4, 6) = 5) = 990.5
2022-08-15 08:51 UTC
6
990.5 - (avg(6, 6) = 6) = 984.5
Sometimes the 'Liters remaining' will come from outside, so I actually have used coalesce to choose either this external actual value if it exists, or the previous value in time for this column if it doesn't:
liters remaining estimated = coalesce(Liters remaining actual, lag(liters remaining estimated) - liters per minute)
but I can't refer to 'self' (liters remaining estimated) in the calculation (I think!)
Timestamp
Liters per Minute
Liters remaining actual
Liters remaining estimated
2022-08-15 08:50 UTC
5
1000
1000
2022-08-15 08:51 UTC
4
1000 - (avg(5, 4) = 4.5) = 995.5
2022-08-15 08:51 UTC
6
995.5 - (avg(4, 6) = 5) = 990.5
2022-08-15 08:51 UTC
6
990.5 - (avg(6, 6) = 6) = 984.5
2022-08-15 08:51 UTC
6
986
986
2022-08-15 08:51 UTC
3
986 - (avg(6, 3) = 4.5) 981.5
I'm not sure how to split this up or if there is some magic bq sql I'm about to learn :)
