Typorithm · medium

SummingMergeTree

summingmergetreeclickhousesummingmergepre-aggregation

Saved on this device

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

Step 1 of 8 · intro card

Learn SummingMergeTree

Aggregation Engines

SummingMergeTree collapses rows with the same ORDER BY key on merge, summing the numeric columns into one pre-aggregated row.

SummingMergeTree is a MergeTree variant: it stores raw inserts, then during background merges it folds rows that share the ORDER BY key into a single row.

The numeric columns that are not part of the ORDER BY key are summed together, so you keep running totals without rewriting data.

Because summing happens at merge time, a query may still see un-merged duplicates — use sum() in the SELECT or FINAL to get the fully collapsed result.

ENGINE = SummingMergeTree pre-aggregates by key.

ORDER BY key = the grouping key.

Numeric non-key columns are summed on merge.

CREATE TABLE daily_revenue
(
    event_date  Date,
    product_id  UInt64,
    revenue     UInt64,
    orders      UInt64
)
ENGINE = SummingMergeTree
ORDER BY (event_date, product_id);

Read the implementation

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

1. Summing engine

On merge, rows sharing the ORDER BY key collapse into one, summing the numeric columns.

ENGINE = SummingMergeTree

2. Grouping key

The ORDER BY key is the grouping key — rows with equal keys are summed together.

ORDER BY (event_date, product_id)

3. Summed metric

Numeric columns outside the ORDER BY key are the ones that get summed.

revenue     UInt64

4. Second metric

Every numeric non-key column is summed independently on merge.

orders      UInt64

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.