CollapsingMergeTree

Прохожу бесплатный курс по кх "Managed Service for ClickHouse". Дошла до модуля 2 про виды движков, и меня настигло озарение

Вот есть движок CollapsingMergeTree - он сворачивает пары строк, у которых в ключе сортировки одинаковые значения, но при этом поле sign у одной из них равно 1, а у другой -1. Без паники! Сейчас поясню на примере

CREATE TABLE user_activity (

  UserID UInt64,   --есть пользак

  PageViews UInt8, --он посетил столько страниц

  Sign Int8        --системное поле

)

ENGINE = CollapsingMergeTree(Sign)

ORDER BY UserID;   --будем сворачивать по этому полю

Наш пользак 999 захотел что-то купить:

INSERT INTO user_activity VALUES (999, 10, 1); --сначала просмотрел 10 страниц

INSERT INTO user_activity VALUES (999, 10, -1), --а теперь 10 страниц уже неактуальны (видите -1?)

(999, 11, 1);  --потому что он просмотрел 11 страниц

Т.е. тут явно видно, что update = delete + insert, где sign - это флажок удаления

Но есть нюанс - теперь в нашей таблице 3 строки, а нужна 1. Почему 3, ведь CollapsingMergeTree как раз и должен сам все объединить?

Потому что кх свои слияния производит не сразу, а когда-то потом. Но есть несколько способов получить то, что нужно:

1. FINAL - как будто слияние уже произошло

SELECT * FROM user_activity FINAL;

2. OPTIMIZE - заставляем сделать слияние прямо сейчас. Но у вас может не быть прав на это в продовой базе

OPTIMIZE TABLE user_activity;

SELECT * FROM user_activity; --FINAL уже не нужен

3. Посчитать самим

SELECT UserID, sum(PageViews * Sign)

FROM user_activity

group by UserID;

А ведь действительно 10*1 + 10*(-1) + 11*1 дает то, что нужно…