2 · MERGETREE · PRE-AGGREGATION
run
Materialized view: DAU without raw events
CREATE TABLE daily_stats (
day Date, mode LowCardinality(String),
players AggregateFunction(uniq, UInt32),
damage SimpleAggregateFunction(sum, UInt64)
) ENGINE = AggregatingMergeTree ORDER BY (day, mode);
CREATE MATERIALIZED VIEW daily_stats_mv TO daily_stats AS
SELECT toDate(ts) AS day, mode,
uniqState(player_id) AS players,
sum(toUInt64(damage)) AS damage
FROM events GROUP BY day, mode;
MV — an INSERT trigger.
Every block inserted into `events` goes through `SELECT`; the result is appended to `daily_stats`.
MV does not see old data
uniqState / uniqMerge
GROUP BY during a read operation
Winnings
An intermediate state is stored and compiled during reading: 63,126 players on October 1.
The lines of a single day lie in different parts until they merge.
186 rows of data from the unit instead of 5.1 million events: the dashboard displays completed days (P5).
MV counts only the entries inserted after creation—and as many times as they were inserted. Repeating the same batch will double the sums (next slide). lab, Section 6.