clickhouse查询数据去重筛选出最后一次创建时间的数据。
-- 按天去重,选择最后一次插入数据
select argMax(toDate(tdba.start_date),tdba.start_date) as reportDate,argMax(tdba.username,tdba.start_date) as username,
from testdata tdba
group by toDate(start_date)
ORDER by toDate(start_date) DESC
对clickhouse数据库执行建表语句时快速去重
在创建数据表时:
#创建本地表:
CREATE TABLE default.t_test ON CLUSTER ck_cluster
(
`request_id` String COMMENT '提案id',
`login_name` String COMMENT '玩家名称',
`last_update` DateTime COMMENT '更新时间',
`created_date` DateTime DEFAULT now() COMMENT '创建时间'
)
ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/{uuid}/{shard}', '{replica}')
PARTITION BY toYYYYMMDD(created_date)
ORDER BY request_id
SETTINGS index_granularity = 8192,max_parts_in_total = 250000;
#创建分布式表:
CREATE TABLE default.t_test ON CLUSTER ck_cluster AS default.t_test
ENGINE = Distributed(ck_cluster, default, t_test, rand())
//ON CLUSTER ck_cluster:创建分布式
//ENGINE 引擎ReplicatedReplacingMergeTree:引擎可以支持去重
//PARTITION BY:按什么分区表创建
//index_granularity :索引间隔粒度,每index_granularity个数据产生一条索引
//max_parts_in_total :最大分区数量
optimize table testdata final;