Materialized View 物化视图

注意：

1. 物化视图建表语句，必须base on本地表，不能base on分布式表。否则没有数据进来。
2. 建表语句不能嵌套，即不能 select * from (select * from ...) tmp。这样试的时候报错了。

AggregatingMergeTree引擎示例

-- 建本地视图
CREATE MATERIALIZED VIEW coco_db.query_aggregate_table ENGINE = AggregatingMergeTree PARTITION BY dt
ORDER BY
    (dt, query) AS
select
    dt,
    query,
    uniqState(e) as imp_count
from
    detail_table
where
    type = 'IMP'
    and position in (1, -1)
GROUP BY
    dt,
    r_query;
-- 建分布式视图
CREATE table coco_db.dist_query_aggregate_table as coco_db.query_aggregate_table ENGINE = Distributed(
    coco_cluster,
    coco_db,
    query_aggregate_table,
    rand()
);
-- 查询
select
    dt,
    r_query,
    uniqMerge(imp_count) as count
from
    coco_db.dist_query_aggregate_table
group by
    dt,
    r_query
order by
    count desc
limit
    10;