Hi everyone,
I want to process the delta which is null in the middle of a time series by taking the next delta after the null to divide to the (count of null + 1)
Here is the data:
time | id | value | delta |
01/02/2022 | 123 | 12 | |
02/02/2022 | 123 | 15 | 3 |
03/02/2022 | 123 | 20 | 5 |
04/02/2022 | 123 | ||
05/02/2022 | 123 | ||
06/02/2022 | 123 | ||
07/02/2022 | 123 | 60 | 40 |
08/02/2022 | 123 | 60 | 0 |
09/02/2022 | 123 | ||
10/02/2022 | 123 | ||
01/02/2022 | 145 | 20 | |
02/02/2022 | 145 | 50 | 30 |
03/02/2022 | 145 | 70 | 20 |
04/02/2022 | 145 | 100 | 30 |
05/02/2022 | 145 | ||
06/02/2022 | 145 | ||
07/02/2022 | 145 | 190 | 90 |
08/02/2022 | 145 | ||
09/02/2022 | 145 | ||
10/02/2022 | 145 | ||
01/02/2022 | 987 | 50 | |
02/02/2022 | 987 | 100 | 50 |
03/02/2022 | 987 | 160 | 60 |
04/02/2022 | 987 | 200 | 40 |
05/02/2022 | 987 | 230 | 30 |
06/02/2022 | 987 | 280 | 50 |
07/02/2022 | 987 | 360 | 80 |
08/02/2022 | 987 | 420 | 60 |
09/02/2022 | 987 | 500 | 80 |
10/02/2022 | 987 | 550 | 50 |
Here is when I untable
time | 123 | 145 | 987 |
01/02/2022 | 0 | 0 | 0 |
02/02/2022 | 3 | 30 | 50 |
03/02/2022 | 5 | 20 | 60 |
04/02/2022 | 10 | 30 | 40 |
05/02/2022 | 10 | 30 | 30 |
06/02/2022 | 10 | 30 | 50 |
07/02/2022 | 10 | 30 | 80 |
08/02/2022 | 0 | 0 | 60 |
09/02/2022 | 0 | 80 | |
10/02/2022 | 0 | 50 |
As in the table, there is 1 record in 08/02/2022 when delta is 0. For other data where it doesn't have data, it is null --> ok
But for id=145, delta from 08 to 10/02 should be empty.
So that when I calculate the avg(delta), it will not effect.
So my question is how to distinguish the zero and the null in this case?
Thanks!
How did you get from your first table to your second table?