How to use ClickHouse Live Views for computing Real-time Moving Average

Shiv Iyer
Posted on February 22, 2023

ClickHouse Live Views can be used to compute real-time moving averages efficiently. Here’s how:

  1. Create a Live View: The first step is to create a ClickHouse Live View that will be used to compute the moving average. A Live View is a continuously updated query that can be used to materialize data and compute aggregates in real-time.
  2. Define the Query: The query for the Live View should compute the moving average based on the data in your table. Here’s an example query that computes the moving average of a value column over a window of the last 10 rows:
  avg(value) OVER (ORDER BY timestamp ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) as moving_average,
FROM my_table
  1. This query computes the moving average of the “value” column over a window of the last 10 rows, ordered by the “timestamp” column.
  2. Define the Live View Settings: You can define various settings for the Live View to control how often it updates, how much data it caches, and how it handles errors. For example, you can set the max_rows_to_group_by and max_bytes_to_group_by parameters to limit the amount of data that is used to compute the moving average.
  3. Subscribe to the Live View: Once you have defined the Live View, you can subscribe to it and receive updates in real-time. You can use the ClickHouse client or any other tool that supports ClickHouse to subscribe to the Live View and receive updates.

By using ClickHouse Live Views and defining the appropriate query, settings, and subscription, you can efficiently compute real-time moving averages in ClickHouse. This can be useful for a variety of use cases, such as monitoring real-time data streams or analyzing time-series data.