Create column based on previous row value

Fluxlang
, ,
1

Hello together, I’m trying to create a query that operates on a given table. The goal is to add a new column val that calculates the difference between the current and the previous value of column _value if the current value is bigger than the previous and _field of the current row is equal to _field of the previous row. The following is a minimal example:

Input:
+---------------------+--------+--------+
|        _time        | _field | _value |
+---------------------+--------+--------+
| 2021-10-24 02:00:00 | x      |      0 |  
| 2021-10-24 02:10:00 | x      |      2 |  
| 2021-10-24 02:20:00 | x      |      1 |  
| 2021-10-24 02:00:00 | y      |      3 |  
| 2021-10-24 02:10:00 | y      |      3 |  
| 2021-10-24 02:20:00 | y      |      4 |  
+---------------------+--------+--------+

Output:
+---------------------+--------+--------+-----+
|        _time        | _field | _value | val |
+---------------------+--------+--------+-----+
| 2021-10-24 02:00:00 | x      |      0 |   0 |
| 2021-10-24 02:10:00 | x      |      2 |   2 |
| 2021-10-24 02:20:00 | x      |      1 |   0 |
| 2021-10-24 02:00:00 | y      |      3 |   0 |
| 2021-10-24 02:10:00 | y      |      3 |   0 |
| 2021-10-24 02:20:00 | y      |      4 |   1 |
+---------------------+--------+--------+-----+

How would I achieve this within the Flux language?

2

I would use something like

data	
	|> duplicate(column: "_value", as: "val")
	|> difference(columns: ["val"], nonNegative: true, keepFirst: true)
	|> fill(column: "val", value: 0)
3

Thanks for your answer. Your code works. But how does it know that the subtraction between row 3 and row 4 should not be calculated? The code doesn’t mention that if the value of _field is not equal to the previous value, then the difference should be set to zero. However, it still works. Why?

4

I think the subtraction is always calculated, if you try it without nonNegative: true and fill(), you will see.