ClickHouse未改配置却出现物化视图（Materialized View）重复行问题

排查步骤 #

1. 验证核心表主键唯一性 #

尽管你说明ContainerLocationsId是唯一递增ID，仍需确认近期ELT流程是否产生了重复数据：

SELECT ContainerLocationsId, count(*) AS row_count
FROM containerLocations
WHERE IsBillableTransaction = 1
GROUP BY ContainerLocationsId
HAVING row_count > 1

如果返回结果，说明containerLocations表已存在重复主键记录，这是物化视图重复行的直接诱因。

2. 逐步排查JOIN关联的多匹配问题 #

单独执行查询无重复但物化视图出现重复，需逐环节验证JOIN后的行数变化，定位是否某张关联表新增了多匹配记录：

• 先统计核心表基础行数：

  SELECT count(*) FROM containerLocations WHERE IsBillableTransaction = 1
  

• 逐步添加JOIN并统计行数：

  -- 加入userLinks后
  SELECT count(*)
  FROM containerLocations 
  LEFT JOIN userLinks ON containerLocations.OwnerId = userLinks.UserId
  WHERE containerLocations.IsBillableTransaction = 1
  
  -- 再加入containers后
  SELECT count(*)
  FROM containerLocations 
  LEFT JOIN userLinks ON containerLocations.OwnerId = userLinks.UserId
  LEFT JOIN containers ON containers.Id = containerLocations.ContainerId
  WHERE containerLocations.IsBillableTransaction = 1
  

对比每一步的行数变化，找到导致行数翻倍的关联环节（比如userLinks表中同一个OwnerId对应多条StoreId记录）。

3. 检查物化视图创建历史 #

确认创建当前物化视图前，是否存在同名物化视图未删除。若原有物化视图未清理，使用POPULATE创建会在已有数据基础上追加新数据，最终导致重复。

临时解决方案 #

若底层数据暂时无法修复，可改用ReplacingMergeTree引擎创建物化视图，利用其按排序键自动去重的特性：

CREATE MATERIALIZED VIEW transactions
ENGINE = ReplacingMergeTree()
ORDER BY (ContainerLocationsId) POPULATE AS 
    SELECT
        stores.Id AS StoreId,
        stores.Type as StoreSubtype,
        stores.Name AS StoreName,
        stores.City AS City,
        containerLocations.Id AS ContainerLocationsId,
        containerLocations.OwnerSince AS OwnerSince,
        containerLocations.OwnerTill AS OwnerTill,
        containerLocations.OwnerId AS OwnerId,
        containerLocations.NextOwnerId AS NextOwnerId,
        containerLocations.ContainerId AS ContainerId,
        containerLocations.TransactionType AS TransactionType,
        containerTypes.Name AS ContainerTypeName
    FROM containerLocations 
    LEFT JOIN userLinks ON containerLocations.OwnerId = userLinks.UserId
    LEFT JOIN containers ON containers.Id = containerLocations.ContainerId
    LEFT JOIN stores ON userLinks.StoreId = stores.Id 
    LEFT JOIN storeCategories ON storeCategories.Id = stores.CategoryId    
    LEFT JOIN containerTypes ON containerTypes.Id = containers.TypeId    
    WHERE containerLocations.IsBillableTransaction = 1

注：ReplacingMergeTree会在后台合并分区时自动去重，若需立即生效，可手动触发合并：

OPTIMIZE TABLE transactions FINAL;

内容的提问来源于stack exchange，提问作者Popolytics