ReplacingMergeTree: Duplicates and Corrections

15 / 34Machine translation

← →

2 · MERGETREE · DUPLICATES

run

Redelivery and Correction

How to Read

CREATE TABLE match_results (
  match_id UInt64, player_id UInt32,
  kills UInt16,    version UInt32
) ENGINE = ReplacingMergeTree(version)
ORDER BY (match_id, player_id);
-- доставка, её повтор,
-- исправление: 2001 -> 5 убийств, version 2

-- в базе после OPTIMIZE FINAL дубли уже схлопнуты;
-- до слияния (П4): без FINAL 49, с FINAL 20
SELECT sum(kills) FROM match_results FINAL; -- 20

SELECT sum(k) FROM (
  SELECT argMax(kills, version) AS k
  FROM match_results
  GROUP BY match_id, player_id);             -- 20

KEY

WHEN

READING

ORDER BY = duplicate

Only on merge

FINAL или argMax

Rows with the same (match_id, player_id) will be merged, and the highest version will remain.

Duplicates are visible before merging. Merging may not occur; duplicates always remain between partitions.

FINAL collapses rows on read, argMax does it explicitly. Without them: 49 kills instead of 20.

ReplacingMergeTree. Fits reloads and corrections; queries must account for duplicates.