如何在SQL（Clickhouse）中删除重复行？

Question

我使用ClickHouse创建了一个表，但是它里面有重复的内容。

下面的查询语句可以给出我表中的重复项。

select *, count() AS cnt from my_table   GROUP BY *
HAVING cnt > 1 

在ClickHouse中，显然您需要通过更改表来执行此操作：https://clickhouse.com/docs/en/sql-reference/statements/alter/delete/ 因此，我尝试了以下操作：

ALTER TABLE my_table DELETE WHERE (select *, count() AS cnt from my_table  GROUP BY *
HAVING cnt > 1 ); 

但我遇到了以下错误：

异常：函数isZeroOrNull的参数必须是简单数值类型，可能是Nullable类型：

有人在使用ClickHouse时遇到过这个问题吗？

在这个视频中，他们明确提到ClickHouse不是最擅长这种操作的：https://www.youtube.com/watch?v=FsVrFbcyb84&t=1865s

但我想知道是否有人找到了解决方案。

- Dude

---

你使用了哪些表引擎？ - Erwan Daniel

Answer 1

首先，答案取决于您使用的表引擎。ClickHouse上最常见的是MergeTree系列。

如果您使用任何MergeTree系列表、MaterializedView或Buffer引擎，您可以使用OPTIMIZE查询：

OPTIMIZE TABLE table DEDUPLICATE BY name -- you can put any expression here

https://clickhouse.com/docs/en/sql-reference/statements/optimize/

在您考虑上述查询作为答案之前，您必须了解为什么以及为什么这不是正确的方法。

在Clickhouse中，对于相同的主键，有多个行是正常的，与大多数DB引擎不同，在插入行时根本没有检查。这允许在表中非常快速地插入数据。

"MergeTree"的名称不是无意义的，实际上，当Clickhouse认为有必要或/和有时间时，表会自动进行“优化”。

在ClickHouse中，“优化”是什么意思？ 此操作只是强制表合并其数据。根据您构建表的方式，ClickHouse将查找重复的行，基于您的设置，并应用您请求的函数。

两个例子：

• ReplacingMergeTree，这里可选参数设置为datetime，并向ClickHouse提供提示，指示哪一行最近。然后在重复项中，保留最新的行。

create table radios
(
    id                UInt64,
    datetime          DateTime,
    name              Nullable(String) default NULL
)
    engine = ReplicatedReplacingMergeTree(datetime)
    ORDER BY id -- it's the primary key
-- example
INSERT INTO radios VALUES (1, now(), 'Some name'), (1, now(), 'New name')
-- after merging:
id,              datetime,       name
 1, '2022-04-04 15:15:00', 'New name'

• AggregatingMergeTree，这里应用了一个函数来计算最终行。这是最接近 UPDATE 语句的内容。

create table radio_data
(
    datetime                        DateTime,
    id                              UInt64,
    power                           SimpleAggregateFunction(anyLast, Nullable(Float64)) default NULL,
    access                          SimpleAggregateFunction(sum, Nullable(UInt64))    default NULL
)
    engine = ReplicatedAggregatingMergeTree()
        ORDER BY (id, datetime) -- the primary key

-- example
INSERT INTO radio_data VALUES ('2022-04-04 15:15:00', 1, NULL, 1), ('2022-04-04 15:15:00', 1, 12, 2)
-- will give after merging :
datetime           , id, power, access
2022-04-04 15:15:00,  1,    12,      3

您选择的表格和函数必须与最终要处理的数据非常接近。如果您要在更新时替换所有行，则使用ReplacingMergeTree是最好的选择；如果您要部分更新一行并对其应用某些函数，则AggregatingMergeTree是最佳选择...等等。

话虽如此，您可能会遇到一些需要使您的数据保持“新鲜”而不重复的情况。 当您的表格配置良好时，简单的“OPTIMIZE TABLE ...”就足够了。但是这是很昂贵的，如果您不想破坏服务器性能，必须明智地进行操作。 您也可以实时合并数据，但同样昂贵，必须针对一小部分数据进行操作，否则最好进行优化。

SELECT * FROM radio_data FINAL WHERE id = 1

例如，我们对所有未合并的分区进行优化，这些分区“过去”了，例如前一天。目标是尽可能少地执行OPTIMIZE操作。

我最后要说的是关于ALTER TABLE语句的使用。它允许DELETE和UPDATE。但它们是变异（https://clickhouse.com/docs/en/sql-reference/statements/alter/#mutations），不是同步的！如果您需要新鲜数据，请不要依赖它们。

您可以在此处找到更多材料：

https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree/#mergetree https://clickhouse.com/docs/en/sql-reference/statements/optimize/ https://clickhouse.com/docs/en/sql-reference/statements/alter/

- Erwan Daniel

Answer 2

这对我来说很有效（在MergeTree引擎上）

OPTIMIZE TABLE <table> [on cluster ...] final DEDUPLICATE BY <partition by cols ...>, <order by cols>

- Jackssn