I would like to compute weighted sum between 1 previous row and current row.
value = "value from current row" * weight + "value from previous row" * (1 - weight)
The below gets cyclic aliases error but conceptually this is what I want to achieve.
CREATE TABLE tbl (
neighbor(weighted_value, -1) AS prev_value,
value * 0.7 + prev_value * 0.3 AS weighted_value
WHERE timestamp >= now() - toIntervalMinute(5)
This query is stateful similar to moving average. Maybe window functions can help?
The number of rows are up to 300. Maybe array can help but I can't figure it out so far.