Is there a way to calculate difference whthin a specific time range using clickhouse MV?

For example, this is my source table

CREATE TABLE IF NOT EXISTS market
(
    id          UInt64,
    price       DECIMAL128(18),
    create_time UInt64
) ENGINE = MergeTree()
      partition by toYYYYMM(FROM_UNIXTIME(create_time))
      order by create_time;

What I want to do is to create a MV which asynchronously calculate the price difference whthin 30 min. And then, I can get the difference from this MV using an id every time. Here is an SQL example. It works correctly while directly execute it, but does not work while creating a MV(AS SELECT ...) and querying from the MV.

select id,
       (t1.price - t2.price)                                  as price_delta,
       t2.price                                               as start_price,
       t1.price                                               as end_price,
       round(toFloat64(price_delta) / toFloat64(t2.price), 2) as price_delta_rate,
       now()                                                  as update_time
from (select id, price
      from market
      where create_time >= date_sub(MINUTE, 30, now())
      order by create_time desc
      limit 1) t1
         left join
     (select id, price
      from market
      where create_time >= date_sub(MINUTE, 30, now())
      order by create_time
      limit 1) t2
     ON t1.id = t2.id;

Here is my SQL to create a MV

CREATE MATERIALIZED VIEW IF NOT EXISTS market_stats_30min
            ENGINE = ReplacingMergeTree()
                order by id
            POPULATE
AS
select id,
       (t1.price - t2.price)                                  as price_delta,
       t2.price                                               as start_price,
       t1.price                                               as end_price,
       round(toFloat64(price_delta) / toFloat64(t2.price), 2) as price_delta_rate,
       now()                                                  as update_time
from (select id, price
      from market
      where create_time >= date_sub(MINUTE, 30, now())
      order by create_time desc
      limit 1) t1
         left join
     (select id, price
      from market
      where create_time >= date_sub(MINUTE, 30, now())
      order by create_time
      limit 1) t2
     ON t1.id = t2.id;

Answer

Materialized view is just after insert trigger which works inside new data block which you inserted So, your INSERT INTO market doesn’t contain all required data in most of the cases

POPULATE recalculate full table for materialized view just once

Try to use window functions in SELECT https://clickhouse.com/docs/en/sql-reference/window-functions/, without materialized view

Advertisement

Answer