ClickHouse 数据类型 (三)

6、SummingMergeTree

对于不查询明细，只关心以维度进行汇总聚合结果的场景。如果只使用普通的MergeTree的话，无论是存储空间的开销，还是查询时临时聚合的开销都比较大。ClickHouse 为了这种场景，提供了一种能够“预聚合”的引擎SummingMergeTree

建表

create table t_order_smt(
    id UInt32,
    sku_id String,
    total_amount Decimal(16,2) ,
    create_time  Datetime 
 ) engine =SummingMergeTree(total_amount)
   partition by toYYYYMMDD(create_time)
   primary key (id)
   order by (id,sku_id );

插入数据

insert into  t_order_smt values
(101,'sku_001',1000.00,'2022-05-18 12:00:00'),
(102,'sku_002',2000.00,'2022-05-18 11:00:00'),
(102,'sku_004',2500.00,'2022-05-18 12:00:00'),
(102,'sku_002',2000.00,'2022-05-18 13:00:00'),
(102,'sku_002',12000.00,'2022-05-18 13:00:00'),
(102,'sku_002',600.00,'2022-05-19 12:00:00');

第一次查询

SELECT *
FROM t_order_smt

┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │       600.00 │ 2022-05-19 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │      1000.00 │ 2022-05-18 12:00:00 │
│ 102 │ sku_002 │      2000.00 │ 2022-05-18 11:00:00 │
│ 102 │ sku_002 │      2000.00 │ 2022-05-18 13:00:00 │
│ 102 │ sku_002 │     12000.00 │ 2022-05-18 13:00:00 │
│ 102 │ sku_004 │      2500.00 │ 2022-05-18 12:00:00 │

手工合并再次查询

OPTIMIZE TABLE t_order_smt FINAL

Ok.

0 rows in set. Elapsed: 0.006 sec. 

hadoop201 :) sselect * from t_order_smt;select * from t_order_smt;

SELECT *
FROM t_order_smt

┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │      1000.00 │ 2022-05-18 12:00:00 │
│ 102 │ sku_002 │     16000.00 │ 2022-05-18 11:00:00 │
│ 102 │ sku_004 │      2500.00 │ 2022-05-18 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │       600.00 │ 2022-05-19 12:00:00 │

结论

以SummingMergeTree（）中指定的列作为汇总数据列
可以填写多列必须数字列，如果不填，以所有非维度列且为数字列的字段为汇总数据列
以order by 的列为准，作为维度列
其他的列按插入顺序保留第一行
不在一个分区的数据不会被聚合

设计聚合表的话，唯一键值、流水号可以去掉，所有字段全部是维度、度量或者时间戳。问题:能不能直接执行以下SQL得到汇总值

select total_amount from  XXX where province_name=’’ and create_date=’xxx’

不行，可能会包含一些还没来得及聚合的临时明细,如果要是获取汇总值，还是需要使用sum进行聚合，这样效率会有一定的提高，但本身ClickHouse是列式存储的，效率提升有限，不会特别明显。 

select sum(total_amount) from province_name=’’ and create_date=’xxx’