Typorithm · hard

AggregatingMergeTree

aggregatingmergetreeclickhouseaggregatingstatemerge-combinator

Saved on this device

Full lesson · 8 learning steps Open any exercise for focused practice

Step 1 of 8 · intro card

Learn AggregatingMergeTree

Aggregation Engines

AggregatingMergeTree stores AggregateFunction states written with -State combinators (uniqState, sumState) and finalized on read with -Merge (uniqMerge).

AggregatingMergeTree columns hold AggregateFunction states — partial, mergeable snapshots of an aggregate, not finished numbers.

You write those states with -State combinators such as uniqState and sumState, and the engine merges the states for rows sharing the ORDER BY key.

At query time you finalize with the matching -Merge combinator, like uniqMerge or sumMerge, to turn the merged states into final values.

Columns are AggregateFunction(name, types) states.

Insert states with -State (uniqState, sumState).

Read finals with -Merge (uniqMerge, sumMerge).

CREATE TABLE visit_stats
(
    event_date  Date,
    page        LowCardinality(String),
    visitors    AggregateFunction(uniq, UInt64),
    total       AggregateFunction(sum, UInt64)
)
ENGINE = AggregatingMergeTree
ORDER BY (event_date, page);

SELECT
    page,
    uniqMerge(visitors) AS unique_visitors
FROM visit_stats
GROUP BY page;

Read the implementation

Connect each part of the code to the concept before you type.

1. Aggregating engine

AggregatingMergeTree merges aggregate-function states for rows sharing the key.

ENGINE = AggregatingMergeTree

2. uniq state column

AggregateFunction(uniq, UInt64) stores the intermediate state of a uniq aggregate.

visitors    AggregateFunction(uniq, UInt64)

3. sum state column

Each AggregateFunction column holds a partial aggregate state, not a final value.

total       AggregateFunction(sum, UInt64)

4. Read with -Merge

uniqMerge folds the stored states into the final unique count at read time.

uniqMerge(visitors) AS unique_visitors

A complete learning loop

Understand

Read the concept map and follow the code breakdown.

Reconstruct

Type the implementation, repair mistakes and work through every recall exercise.

Verify

Use the final check without hints; revisit weak concepts in short practice sessions.