compute using previous rows

27 views
Skip to first unread message

Kenji

unread,
May 27, 2021, 2:41:22 PMMay 27
to ClickHouse
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 (
  timestamp DateTime,
  value     Float64
)

SELECT
    timestamp,
    neighbor(weighted_value, -1) AS prev_value,
    value * 0.7 + prev_value * 0.3 AS weighted_value
FROM tbl
WHERE timestamp >= now() - toIntervalMinute(5)
ORDER BY timestamp

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.




Reply all
Reply to author
Forward
0 new messages