1. Introduction

The summation engine is inherited from MergeTree. The difference is that when combining data fragments from the SummingMergeTree table, ClickHouse merges all rows with the same primary key into a single row that contains the combined values of columns with numeric data types in the merged row. If the primary key is combined in such a way that a single key value corresponds to a large number of rows, it can significantly reduce storage space and speed up data queries.

2. Build a predicate sentence

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = SummingMergeTree([columns])
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]
Copy the code

Columns – A tuple containing the column names of the columns to be summarized. This parameter is optional. The selected column must be of numeric type and cannot be in a primary key.

Example 3.

Summing_table_test1 on cluster ck_cluster (v1 Int32, v2 Int32, name String, total_date DateTime ) ENGINE = SummingMergeTree((v1,v2)) order by (name) partition by toDate(total_date) SETTINGS index_granularity = 8192;Copy the code

Write test data:

Insert into temp. Summing_table_test1 values (1, 2, 'a', now ()), (2, 2, 'a', now * 60 * 60 () - 1), (3, 4, 'b', now ());Copy the code

Query data:

SELECT * FROM temp.summing_table_test1 Query id: 2 da82c96 a90-2-496 - a - 83, fe - 8 a6528ba336c ┌ ─ v1 ─ ┬ ─ ─ v2 ┬ ─ name ─ ┬ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ total_date ─ ┐ 3 4 │ │ │ │ a 2021-10-13 11:41:12 │ │ │ 3 4 b │ │ the 2021-10-13 11:41:12 │ └ ─ ─ ─ ─ ┴ ─ ─ ─ ─ ┴ ─ ─ ─ ─ ─ ─ ┴ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ┘Copy the code