1. Introduction

The table engine inherits from MergeTree and can use AggregatingMergeTree to aggregate incremental data statistics. If you want to combine and reduce the number of rows by a set of rules, using AggregatingMergeTree is appropriate. AggregatingMergeTree computs data through predefined aggregation functions and stores data into tables in binary format. SummingMergeTree is an enhanced version of SummingMergeTree. SummingMergeTree does sum aggregation of non-primary key columns. AggregatingMergeTree specifies various aggregation functions.

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 = AggregatingMergeTree() [PARTITION BY expr] [ORDER BY expr] [SAMPLE BY expr] [TTL expr] [SETTINGS name=value, ...].Copy the code

Example 3.

Background: Uid is used to retrieve attributes such as a user’s nickname or rank. These attributes are stored in the wide table, but are not available when the wide table is updated. Therefore, by extracting the user attributes of the wide table and creating a new AggregatingMergeTree table, the advantage is that the updated user attributes can be updated in the form of insert.

Build tables:

CREATE TABLE user_group_test.aggregating_table_test1 on cluster ck_cluster ( `uid` Int64 COMMENT 'uid', 'nickname' AggregateFunction(argMaxIf, String, DateTime, UInt8) COMMENT 'nickname ',' phone 'AggregateFunction(argMaxIf, String, DateTime, UInt8) COMMENT 'mobile phone ',' experience_level 'AggregateFunction(argMaxIf, String, DateTime, UInt8) UInt8) COMMENT 'experience level') ENGINE = ReplicatedAggregatingMergeTree('/clickhouse/user_group_test/tables/{shard}/aggregating_table_test1', '{replica}') ORDER BY uid SETTINGS index_granularity = 8192; CREATE TABLE user_group_test.aggregating_table_test1_all on cluster ck_cluster as user_group_test.aggregating_table_test1 ENGINE = Distributed('ck_cluster', 'user_group_test', 'aggregating_table_test1', intHash64(uid));Copy the code

Insert data using aggregate functions:

SELECT uid, argMaxIfState(nickName,regTime,nickName! ='') nickName, argMaxIfState(phone,regTime,phone! ='') phone, argMaxIfState(level,regTime,level! ='') level FROM default.dwd_user_wide_all where totalDate=yesterday() group by uid limit 20;Copy the code

Query:

select uid,argMaxIfMerge(nickname),
       argMaxIfMerge(experience_level)
from user_group_test.aggregating_table_test1_all
where uid=123456 group by uid;
Copy the code