说明

能够在合并分区时，删除重复的数据，去重依据是 order by 排序字段，但是只能对 同一分区 的数据去重

注意： 与 primary key 无关

去重时机

• 合并临时分区时，合并时机见 链接
• 手动合并临时分区

去重范围

在分区内部去重，不能跨分区去重

应用场景

某些字段需要唯一时

建表语法

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

解释：

• ReplacingMergeTree([ver])：填入的参数为版本字段，重复数据保留版本字段值最大的，不填的情况下默认保留最新一条。

• partition by：根据该字段进行分区，对 分区内 进行 删除重复数据。不能跨分区 删除重复数据

• order by：去重依据

案例-分区内去重

建表

create table t_order_rmt(
    id UInt32 COMMENT '主键',
    sn String COMMENT '订单编号',
    total Decimal(11,2) COMMENT '总金额',
    create_time Datetime COMMENT '创建时间'
)
engine=ReplacingMergeTree(create_time)
partition by toYYYYMMDD(create_time)
primary key(id)
order by (id, sn);

解释：

• ReplacingMergeTree(create_time) ... order by (id, sn);：当 id、sn 的值相同时，删除重复的数据，保留 create_time 值最大的记录

• partition by toYYYYMMDD(create_time)：根据该字段的 YYYYMMDD 部分进行分区，对分区内进行 上面的操作。不能跨分区操作

第一次插入数据

insert into t_order_rmt values
(1,'0001',100,'2022-01-01 11:20:00'),
(2,'0002',200,'2022-01-02 11:30:00'),
(1,'0001',100,'2022-01-01 11:10:00'),
(3,'0003',300,'2022-01-03 10:00:00'),

查询：

select * from t_order_rmt

结果如下：

结论

1,'0001',100,'2022-01-01 11:10:00' 记录被删除了

原因

• 下面两条数据，id、sn 都相同，而且都是 2022-01-01 分区的数据，所以会删除一条数据
• 建表时有 ReplacingMergeTree(create_time)，所以会保留 create_time 值最大的记录

1,'0001',100,'2022-01-01 11:20:00'
1,'0001',100,'2022-01-01 11:10:00'

第二次插入数据

insert into t_order_rmt values
(1,'0001',100,'2022-01-01 11:20:00'),
(2,'0002',200,'2022-01-02 11:30:00'),
(1,'0001',100,'2022-01-01 11:10:00'),
(3,'0003',300,'2022-01-03 10:00:00'),

查询：

select * from t_order_rmt

结果如下：

id|sn  |total |create_time        |
--|----|------|-------------------|
 1|0001|100.00|2022-01-01 11:20:00|
 1|0001|100.00|2022-01-01 11:20:00|
 2|0002|200.00|2022-01-02 11:30:00|
 2|0002|200.00|2022-01-02 11:30:00|
 3|0003|300.00|2022-01-03 10:00:00|
 3|0003|300.00|2022-01-03 10:00:00|

结论

有重复记录

原因

第二次插入数据，不会自动删除重复数据

当 临时分区合并 时，才会删除

连续执行3-4次插入数据

会触发 临时分区合并，再次查询，结果如下：

id|sn  |total |create_time        |
--|----|------|-------------------|
 1|0001|100.00|2022-01-01 11:20:00|
 2|0002|200.00|2022-01-02 11:30:00|
 3|0003|300.00|2022-01-03 10:00:00|

案例-分区间不能去重

删除表

DROP table t_order_rmt

创建表

create table t_order_rmt(
    id UInt32 COMMENT '主键',
    sn String COMMENT '订单编号',
    total Decimal(11,2) COMMENT '总金额',
    create_time Datetime COMMENT '创建时间'
)
engine=ReplacingMergeTree(create_time)
partition by toYYYYMMDD(create_time)
primary key(id)
order by (id, sn);

插入数据

插入5次数据

insert into t_order_rmt values
(1,'0001',100,'2022-01-01 11:20:00'),
(1,'0001',100,'2022-01-04 11:10:00'),

查询结果如下：

在合并时，会删除 分区内重复 的数据，但 上面两条数据，在根据时间，分散在 2022-01-01 和 2022-01-04 2个分区，分区间的重复数据，不会去重

案例-手动合并临时分区

执行下面语句，立即合并分区

注意：不能跨分区合并

optimize table t_order_rmt  final;

引用：

https://blog.csdn.net/liuyingying0418/article/details/121547844

原文出处：/show_1IX3PzVZkb26.html