divine-mobile: Skill for Claude Code

ClickHouse AggregatingMergeTree State/Merge Pattern

Problem

When querying tables or materialized views that use AggregatingMergeTree with state functions (sumState, uniqState, countState), queries fail or return wrong results because the columns contain aggregate state objects, not regular numeric values.

Context / Trigger Conditions

• Query fails with type errors when using sum() on AggregateFunction columns
• Creating views that query AggregatingMergeTree materialized views
• Column types show as AggregateFunction(sum, UInt64) instead of UInt64
• Migration creates a view joining with an existing aggregating materialized view
• Getting NULL or unexpected values when aggregating pre-aggregated columns

Root Cause

AggregatingMergeTree stores intermediate aggregate states, not final values. When you define:

CREATE MATERIALIZED VIEW stats
ENGINE = AggregatingMergeTree()
ORDER BY (user_id)
AS SELECT
    user_id,
    sumState(amount) as total_amount,      -- AggregateFunction(sum, UInt64)
    uniqState(session_id) as unique_sessions, -- AggregateFunction(uniq, String)
    countState() as event_count           -- AggregateFunction(count)
FROM events
GROUP BY user_id;

The columns total_amount, unique_sessions, and event_count are NOT regular numbers. They're binary blobs representing the intermediate state of the aggregation.

Wrong:

SELECT user_id, sum(total_amount) FROM stats GROUP BY user_id;
-- Error: cannot use sum() on AggregateFunction type

Correct:

SELECT user_id, sumMerge(total_amount) FROM stats GROUP BY user_id;
-- Returns the finalized numeric value

Solution

Mapping State Functions to Merge Functions

State Function	Merge Function	Purpose
sumState(x)	sumMerge(x)	Sum aggregation
countState()	countMerge(x)	Count aggregation
uniqState(x)	uniqMerge(x)	Unique count (HyperLogLog)
avgState(x)	avgMerge(x)	Average
minState(x)	minMerge(x)	Minimum
maxState(x)	maxMerge(x)	Maximum
anyState(x)	anyMerge(x)	Any value
groupArrayState(x)	groupArrayMerge(x)	Array aggregation