关于clickhouse:用-ClickHouse-的-SummingMergeTree-实现准实时看板qbit

本文测试环境 ClickHouse 版本为 22.3 LTS
SummingMergeTree 官网文档：https://clickhouse.com/docs/e…

创立库
```
CREATE DATABASE qbit;
```

创立表

CREATE TABLE download (
  userid UInt32,
  when DateTime,
  size Float32
) ENGINE = MergeTree 
PARTITION BY toYYYYMM(when)
ORDER BY(userid, when);

插入数据

INSERT INTO qbit.download
SELECT
  rand() % 5,
  now() + number * 60 as when,
  rand() % 100000000
FROM system.numbers
LIMIT 5000;

查看数据
```
SELECT * FROM qbit.download LIMIT 10;
```

查看每天的下载量（ 扫描 10000 条数据 ）

SELECT
  userid,
  toYYYYMMDD(when) AS day,
  count() AS cnt,
  sum(size) AS size
FROM qbit.download
GROUP BY
  userid,
  day
ORDER BY cnt DESC

创立视图 view1

CREATE MATERIALIZED VIEW qbit.view1
ENGINE = SummingMergeTree((cnt, size))
ORDER BY (userid, day) POPULATE AS
SELECT
  userid,
  toYYYYMMDD(when) AS day,
  toInt64(1) AS cnt,
  toFloat64(size) AS size
FROM qbit.download

查问视图 view1（能够看到跟后面的查问后果是统一的）
```
SELECT *
FROM qbit.view1
ORDER BY cnt DESC
LIMIT 10
```

创立视图 view2

CREATE MATERIALIZED VIEW qbit.view2
ENGINE = SummingMergeTree
PARTITION BY day
ORDER BY (userid, day) POPULATE AS
SELECT
  userid,
  toYYYYMMDD(when) AS day,
  count() AS cnt,
  sum(size) AS size
FROM qbit.download
GROUP BY
  userid,
  day

查问视图 view2（能够看到跟后面的查问后果是统一的）
```
SELECT *
FROM qbit.view2
ORDER BY cnt DESC
LIMIT 10
```

创立视图（view3）

CREATE MATERIALIZED VIEW qbit.view3
ENGINE = AggregatingMergeTree
PARTITION BY day
ORDER BY (userid, day) POPULATE AS
SELECT
  userid,
  toYYYYMMDD(when) AS day,
  countState() AS cnt,
  sumState(size) AS size
FROM qbit.download
GROUP BY
  userid,
  day

查问视图 view3（能够看到跟后面的查问后果是统一的）

SELECT
  userid,
  day,
  countMerge(cnt) AS cnt,
  sumMerge(size) AS size
FROM qbit.view3
GROUP BY
  userid,
  day
ORDER BY cnt DESC
LIMIT 10

本文出自 qbit snap

关于clickhouse:用-ClickHouse-的-SummingMergeTree-实现准实时看板qbit

前言

初始化

测试数据生成

SummingMergeTree 准实时看板

形式一（view1）

形式二（view2）

AggregatingMergeTree 准实时看板

插入更新数据