New: 98,000+ skills indexed from GitHub repos with 100+ stars · every skill safety-checkedBrowse top skillsTop skills
Esc to closeBrowse all skills
Submit a skillBrowse skills
Submit a skillBrowse skills

Agent skill

Clickhouse Aggregatingmergetree State Merge

by divinevideo in divinevideo/divine-mobile

Fix ClickHouse query errors when querying AggregatingMergeTree tables that use state functions.

MPL-2.0Auto-check passedDatabases

Install Clickhouse Aggregatingmergetree State Merge

skills CLI
$ npx skills add divinevideo/divine-mobile --skill clickhouse-aggregatingmergetree-state-merge -a claude-code

Project install by default; add -g for ~/.claude/skills/.

GitHub CLI
$ gh skill install divinevideo/divine-mobile clickhouse-aggregatingmergetree-state-merge --agent claude-code

Project scope by default; add --scope user for a personal install. Needs GitHub CLI 2.90.0 or later (public preview).

Manual copy
$ git clone --depth 1 https://github.com/divinevideo/divine-mobile.git skills-src && mkdir -p .claude/skills && cp -r skills-src/.agents/skills/clickhouse-aggregatingmergetree-state-merge .claude/skills/clickhouse-aggregatingmergetree-state-merge && rm -rf skills-src

Use ~/.claude/skills/ instead of .claude/skills for a personal install. The folder must contain SKILL.md.

Claude Code skills documentation · loads skills from .claude/skills/

Facts

Skill name
clickhouse-aggregatingmergetree-state-merge
GitHub stars
266
Token cost
~1.7k tokens
SKILL.md length
355 words
Files
1
Skills in repo
103
Repo updated
First seen
Licence
MPL-2.0

At a glance

Fix ClickHouse query errors when querying AggregatingMergeTree tables that use state functions.

  • Works in 3 steps: Check your materialized view definition… → Ensure all queries use matching *Merge()… → Test the query returns expected numeric…
  • Query fails with type mismatch on AggregateFunction columns
  • SKILL.md covers Problem, Context / Trigger Conditions, Root Cause and Solution, plus 4 more sections
  • Instructions only: no scripts, shell commands, URLs or credentials in SKILL.md

What it does

Clickhouse Aggregatingmergetree State Merge is an agent skill from divinevideo/divine-mobile. Fix ClickHouse query errors when querying AggregatingMergeTree tables that use state functions. Use when: (1) Query fails with type mismatch on AggregateFunction columns, (2) Using sum/count on columns created with sumState/countState/uniqState, (3) Creating views that JOIN with materialized views using AggregatingMergeTree, (4) Getting unexpected results from aggregate columns that show as AggregateFunction(sum, ...) type. The State() functions store intermediate aggregate states, not final values - you must use…

Its SKILL.md is about 1.7k tokens, which your agent loads only when the skill is triggered. It is a single SKILL.md file with no bundled scripts.

It sits in Databases, covering Data warehousing. It works with ClickHouse. The licence is MPL-2.0.

When your agent uses it

  • Query fails with type mismatch on AggregateFunction columns
  • Using sum/count on columns created with sumState/countState/uniqState
  • Creating views that JOIN with materialized views using AggregatingMergeTree
  • Getting unexpected results from aggregate columns that show as AggregateFunction(sum

Example prompts

  • “/clickhouse-aggregatingmergetree-state-merge”

Workflow steps

3 steps, taken from the first numbered list in SKILL.md.

  1. Check your materialized view definition for *State() functions
  2. Ensure all queries use matching *Merge() functions
  3. Test the query returns expected numeric values, not NULL or binary blobs

What it can do on your machine

Read from SKILL.md and the folder at commit c3d6f7e. It shows what the files ask for, not the result of running them.

  • Tool permissions

    Pre-approves nothing: there is no allowed-tools line, so your agent's usual permission prompts apply.

    From allowed-tools in the SKILL.md frontmatter.

  • Runs code

    No scripts in the folder and no shell commands in SKILL.md (its code samples are sql).

    From the folder's file list and the shell code blocks in SKILL.md.

  • Network

    Links to these hosts (documentation or services it may open):

    • clickhouse.com

    From URLs in SKILL.md, links to its own repository left out.

  • Credentials

    Names no API keys, tokens, secrets or passwords.

    From names ending in _API_KEY, _TOKEN, _SECRET, _KEY or _PASSWORD in SKILL.md.

Context cost

Clickhouse Aggregatingmergetree State Merge loads about 1.7k tokens when it runs. Until then it costs about 150 tokens; SKILL.md has 355 words of instructions outside code blocks.

Always · name and description, kept in context so the agent knows when to use it
~150
When it runs · the whole SKILL.md, loaded when a task matches
~1.7k

Estimates: characters ÷ 4, the usual rule of thumb; real counts depend on the model's tokenizer. Scripts and assets cost tokens only if the agent reads them.

Safety

Auto-check passed

The automated check found no risky patterns in SKILL.md.

Automated static check — not a guarantee. Review scripts before installing. It scans the text of SKILL.md for risky patterns (piping downloads into a shell, reading credential files, hidden Unicode, destructive commands); files beside SKILL.md are not scanned.

SKILL.md

The full file from divinevideo/divine-mobile at commit c3d6f7e, republished under its MPL-2.0 licence (© divinevideo). 355 words, ~1,700 tokens.

Download SKILL.mdSave it as .claude/skills/clickhouse-aggregatingmergetree-state-merge/SKILL.md (or your agent's skills folder).
name
clickhouse-aggregatingmergetree-state-merge
description
Fix ClickHouse query errors when querying AggregatingMergeTree tables that use state functions. Use when: (1) Query fails with type mismatch on AggregateFunction columns, (2) Using sum/count on columns created with sumState/countState/uniqState, (3) Creating views that JOIN with materialized views using AggregatingMergeTree, (4) Getting unexpected results from aggregate columns that show as AggregateFunction(sum, ...) type. The *State() functions store intermediate aggregate states, not final values - you must use *Merge() functions to finalize them.
author
Claude Code
version
1.0.0
date
2026-01-30

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:

sql
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:

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

Correct:

sql
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 FunctionMerge FunctionPurpose
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
Example Fix

Before (broken):

sql
CREATE VIEW leaderboard AS
SELECT
    stats.user_id,
    sum(stats.daily_views) AS views,        -- WRONG
    sum(stats.daily_unique) AS uniques,     -- WRONG
    sum(stats.videos_watched) AS videos     -- WRONG
FROM daily_stats stats
GROUP BY stats.user_id;

After (fixed):

sql
CREATE VIEW leaderboard AS
SELECT
    stats.user_id,
    sumMerge(stats.daily_views) AS views,        -- Correct
    uniqMerge(stats.daily_unique) AS uniques,    -- Correct
    countMerge(stats.videos_watched) AS videos   -- Correct
FROM daily_stats stats
GROUP BY stats.user_id;
Identifying Affected Columns

Check the table schema to see which columns are aggregate states:

sql
DESCRIBE TABLE your_table;

Output shows column types like:

daily_views       AggregateFunction(sum, UInt64)
daily_unique      AggregateFunction(uniq, String)
videos_watched    AggregateFunction(count)

Any column with AggregateFunction(...) type requires the corresponding *Merge() function.

Show full SKILL.md (156 more words)Show less

Verification

  1. Check your materialized view definition for *State() functions
  2. Ensure all queries use matching *Merge() functions
  3. Test the query returns expected numeric values, not NULL or binary blobs
sql
-- Should return actual numbers
SELECT sumMerge(total_views), uniqMerge(unique_visitors)
FROM aggregated_stats
WHERE stat_date >= today() - 7;

Example

Migration 020 (creates the aggregating table):

sql
CREATE MATERIALIZED VIEW creator_daily_stats
ENGINE = AggregatingMergeTree()
ORDER BY (video_author_pubkey, stat_date)
AS SELECT
    video_author_pubkey,
    toDate(created_at) as stat_date,
    sumState(view_count) as daily_views,           -- State function
    uniqState(viewer_hash) as daily_unique_viewers, -- State function
    sumState(toFloat64(total_loops)) as daily_loops,-- State function
    countState() as videos_watched                  -- State function
FROM view_counts
GROUP BY video_author_pubkey, toDate(created_at);

Migration 033 (queries the aggregating table - FIXED):

sql
CREATE VIEW leaderboard_creators_day AS
SELECT
    cds.video_author_pubkey AS pubkey,
    p.name,
    sumMerge(cds.daily_views) AS views,            -- Merge function
    uniqMerge(cds.daily_unique_viewers) AS unique_viewers, -- Merge function
    sumMerge(cds.daily_loops) AS loops,            -- Merge function
    countMerge(cds.videos_watched) AS videos_with_views -- Merge function
FROM creator_daily_stats cds
LEFT JOIN user_profiles p ON cds.video_author_pubkey = p.pubkey
WHERE cds.stat_date >= today() - 1
GROUP BY cds.video_author_pubkey, p.name
ORDER BY views DESC;

Notes

  • SummingMergeTree is different: It stores regular values and sums them during merges. With SummingMergeTree, you use regular sum() in queries. Only AggregatingMergeTree uses the State/Merge pattern.

  • Why use AggregatingMergeTree?: For unique counts (uniq), you can't simply sum the counts from different parts—that would overcount. AggregatingMergeTree preserves the HyperLogLog state so merging gives correct unique counts across partitions.

  • Performance: The *Merge() functions are efficient—they're designed to combine pre-computed aggregate states, not reprocess raw data.

  • Migration ordering matters: If migration A creates an AggregatingMergeTree view, and migration B creates a view that queries it, migration B must use *Merge() functions.

References

© divinevideo, MPL-2.0. Rendered from Markdown: HTML in the file is shown as text, images as links, and headings moved down two levels. Raw file

Files

Just SKILL.md in .agents/skills/clickhouse-aggregatingmergetree-state-merge of divinevideo/divine-mobile.

Open the folder on GitHubat commit c3d6f7e

Compare with similar skills

Clickhouse Aggregatingmergetree State Merge next to the 5 skills that share the most tags, products or categories with it. Stars are the repository's; “used in” counts other GitHub owners with a copy.

Clickhouse Aggregatingmergetree State Merge compared with similar skills
SkillStarsUsed inTokensAuto-checkLicenceRepo updated
Clickhouse Aggregatingmergetree State Merge this skilldivinevideo/divine-mobile266—~1.7kAutomated safety check: PassMPL-2.0
Keeper Stress AnalysisClickHouse/ClickHouse50k—~4.7kAutomated safety check: PassApache-2.0
Perf ComparisonClickHouse/ClickHouse50k—~3.9kAutomated safety check: NotesApache-2.0
Patch Release CheckClickHouse/ClickHouse50k—~4kAutomated safety check: NotesApache-2.0
Clickhouse Architecture Advisorvemetric/vemetric3952 repos~791Automated safety check: PassApache-2.0
Decompress BinaryClickHouse/ClickHouse50k—~1.1kAutomated safety check: PassApache-2.0

Similar skills

  • Keeper Stress Analysis

    ClickHouse/ClickHouse

    Analyze ClickHouse Keeper stress-test results from play.clickhouse.com / keeperstresstests data warehouse.

    50k GitHub stars~4.7k tokensUpdated today
    DatabasesAuto-check passed

  • Perf Comparison

    ClickHouse/ClickHouse

    Evaluate ClickHouse performance test results from existing CI/dashboard data or local perf.py runs.

    50k GitHub stars~3.9k tokensUpdated today
    DatabasesAuto-check: notes

  • Patch Release Check

    ClickHouse/ClickHouse

    Check whether ClickHouse's supported versions (last 3 majors + latest LTS) have recent stable patch releases, diagnose why the scheduled AutoReleases pipeline failed, and identify which releases…

    50k GitHub stars~4k tokensUpdated today
    DatabasesAuto-check: notes

  • Clickhouse Architecture Advisor

    vemetric/vemetric

    MUST USE when designing ClickHouse architectures, selecting between ingestion or modeling patterns, or translating best practices into workload-specific system designs.

    395 GitHub starsUsed in 2 repos~791 tokens
    DatabasesAuto-check passed

  • Decompress Binary

    ClickHouse/ClickHouse

    Extract the inner ELF from a ClickHouse self-extracting clickhouse binary, including when its architecture differs from the host (e.g.

    50k GitHub stars~1.1k tokensUpdated today
    DatabasesAuto-check passed

  • Good PRs

    ClickHouse/ClickHouse

    Show a report of open ClickHouse PRs whose only non-green CI check is "CH Inc sync" (or that are fully green) — i.e.

    50k GitHub stars~1.8k tokensUpdated today
    DatabasesAuto-check passed

More from divinevideo/divine-mobile

All 103 skills in this repo

  • Argocd Externalsecret Namespace Permission

    divinevideo/divine-mobile

    Fix ArgoCD ExternalSecret deployment failing with "namespace X is not permitted in project Y".

    266 GitHub stars~931 tokensUpdated today
    Auto-check passed

  • Art Direct

    divinevideo/divine-mobile

    Art direction for any content — reads text, PDF, Word, HTML, PPT, then proposes 2-3 creative directions with photography style, mood, and visual language.

    266 GitHub stars~4.8k tokensUpdated today
    Auto-check passed

  • Async Await Null Race Condition

    divinevideo/divine-mobile

    Fix "Null check operator used on a null value" errors when an object is set to null during an async await.

    266 GitHub stars~881 tokensUpdated today
    Auto-check passed

  • AWS V4 Signing Custom Headers Gcs

    divinevideo/divine-mobile

    Add custom metadata headers (x-amz-meta-) to AWS v4 signed requests for GCS S3-compatible API.

    266 GitHub stars~1k tokensUpdated today
    Auto-check passed

  • Bash Herestring Newline Secrets

    divinevideo/divine-mobile

    Fix password/secret authentication failures caused by trailing newlines when creating Google Cloud secrets (or similar) with bash here-strings.

    266 GitHub stars~791 tokensUpdated today
    Auto-check passed

  • Blossom Content Addressed URL Filtering

    divinevideo/divine-mobile

    Fix silent video/media processing failures caused by URL extraction code that filters on file extensions (.mp4, .webm, .webp).

    266 GitHub stars~1.1k tokensUpdated today
    Auto-check passed

Works with

Categories

Questions about Clickhouse Aggregatingmergetree State Merge

What does Clickhouse Aggregatingmergetree State Merge do?

Fix ClickHouse query errors when querying AggregatingMergeTree tables that use state functions. Clickhouse Aggregatingmergetree State Merge is an agent skill from divinevideo/divine-mobile. Fix ClickHouse query errors when querying AggregatingMergeTree tables that use state functions.

When should I use Clickhouse Aggregatingmergetree State Merge?

Clickhouse Aggregatingmergetree State Merge fits situations like: query fails with type mismatch on AggregateFunction columns; using sum/count on columns created with sumState/countState/uniqState; creating views that JOIN with materialized views using AggregatingMergeTree; getting unexpected results from aggregate columns that show as AggregateFunction(sum.

How do I install Clickhouse Aggregatingmergetree State Merge in Claude Code?

Run `npx skills add divinevideo/divine-mobile --skill clickhouse-aggregatingmergetree-state-merge -a claude-code`. Or copy the skill folder (.agents/skills/clickhouse-aggregatingmergetree-state-merge in divinevideo/divine-mobile) into .claude/skills/clickhouse-aggregatingmergetree-state-merge in your project. Claude Code loads it when a task matches its description.

How do I install Clickhouse Aggregatingmergetree State Merge in Codex?

Run `npx skills add divinevideo/divine-mobile --skill clickhouse-aggregatingmergetree-state-merge -a codex`. Or copy the skill folder (.agents/skills/clickhouse-aggregatingmergetree-state-merge in divinevideo/divine-mobile) into .agents/skills/clickhouse-aggregatingmergetree-state-merge in your project. Codex loads it when a task matches its description.

Can I use Clickhouse Aggregatingmergetree State Merge in Cursor, Gemini CLI or GitHub Copilot?

Cursor, Gemini CLI, GitHub Copilot and OpenCode also load SKILL.md folders. With the skills CLI, run `npx skills add divinevideo/divine-mobile --skill clickhouse-aggregatingmergetree-state-merge -a cursor` (or -a gemini-cli, github-copilot or opencode for the others). To copy it by hand, put the folder in .cursor/skills/clickhouse-aggregatingmergetree-state-merge, .gemini/skills/clickhouse-aggregatingmergetree-state-merge, .github/skills/clickhouse-aggregatingmergetree-state-merge and .opencode/skills/clickhouse-aggregatingmergetree-state-merge in your project.

What does Clickhouse Aggregatingmergetree State Merge need to run?

SKILL.md names no scripts, command-line tools or credentials: Clickhouse Aggregatingmergetree State Merge is instructions for the agent only.

Does Clickhouse Aggregatingmergetree State Merge access the network?

SKILL.md names 1 domain. As links in the text: clickhouse.com. This is read from the text; nothing was executed.

Is Clickhouse Aggregatingmergetree State Merge safe to install?

Our automated static check of SKILL.md found no risky patterns, such as piping downloads into a shell, reading credential files or hidden Unicode. It is not a guarantee. Review the folder before installing.

What licence does Clickhouse Aggregatingmergetree State Merge use?

Clickhouse Aggregatingmergetree State Merge is published under the MPL-2.0 licence (the repository's licence). It allows redistribution, so the full SKILL.md is shown on this page.

How many tokens does Clickhouse Aggregatingmergetree State Merge use?

About 1.7k tokens (SKILL.md is roughly 6.8k characters). Agents keep only the skill's name and description in context until a task matches; then they load SKILL.md in full.

What are the alternatives to Clickhouse Aggregatingmergetree State Merge?

Skills that share tags, products or a category with Clickhouse Aggregatingmergetree State Merge: Keeper Stress Analysis (ClickHouse/ClickHouse, 50k stars), Perf Comparison (ClickHouse/ClickHouse, 50k stars), Patch Release Check (ClickHouse/ClickHouse, 50k stars) and Clickhouse Architecture Advisor (vemetric/vemetric, 395 stars). The comparison table on this page puts their stars, adoption, token cost, safety result and licence side by side.

Who maintains Clickhouse Aggregatingmergetree State Merge?

divinevideo (a GitHub organization) maintains it in divinevideo/divine-mobile, which has 266 GitHub stars. The repository holds 103 skills in this directory. The repository was last updated on October 10, 2026.

Source: divinevideo/divine-mobile on GitHub. Facts on this page come from the repository at the commit we read; the author's words are quoted as theirs.