window-functions - 分区之间的 clickhouse 移动平均线

Question

如何计算clickhouse中分区之间的移动平均值。

对于 window_functions

设置allow_experimental_window_functions = 1；

1、建表

CREATE TABLE IF NOT EXISTS tb (t DateTime, v Float32) ENGINE=MergeTree()
PARTITION BY toYYYYMM(t) ORDER BY t; 

（需要按月分区）

2、插入数值

INSERT INTO tb (t, v) VALUES ('2021-07-31 23:00:00', 5.0),
('2021-07-31 22:00:00', 4.0), ('2021-07-31 21:00:00', 3.0),
('2021-07-31 20:00:00', 2.0), ('2021-07-31 19:00:00', 1.0);

3、物化视图计算移动平均线

CREATE MATERIALIZED VIEW IF NOT EXISTS mv_tb ENGINE=MergeTree()
PARTITION BY toYYYYMM(t)
ORDER BY t
POPULATE
AS SELECT t, v, avg(v) OVER w AS ma
FROM tb
WINDOW w AS (ORDER BY t ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW);

4、查询物化视图（一切正常） select * from mv_tb;

5、插入新数据

INSERT INTO tb (t, v) VALUES ('2021-08-01 01:00:00', 7.0),
('2021-08-01 00:00:00', 6.0);

6、查询物化视图（！！！错误！！！）

select * from mv_tb;

标签： window-functionsclickhousemoving-average