有没有办法使用ClickHouse MV来计算特定时间范围的差异？

Question

例如，这是我的源表，

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;

我想做的是创建一个MV，该MV异步计算出30分钟的价格差异。然后，我每次都可以使用id获得此MV的区别。这是一个SQL示例。直接执行它，它可以正常工作，但是在创建MV（为select ...）并从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;

这是我创建MV的SQL

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 1

物质视图只是在插入触发器之后，在您插入的新数据块中起作用
因此，您的插入市场在大多数情况下不包含所有必需的数据

填充重新计算完整表格以进行物质化的视图，只需

尝试在SELECT中使用窗口功能
https：//clickhouse.com/docs/docs/en/sql-reference/sql-reference/window - 功能/，没有实现的视图