0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

「AI時代のデータベース、何が変わる？」について語ろう！
Qiita Tech Festa20262026年7月13日まで開催中！
@asahideTeam Coffee

とっつきにくい ClickHouse の集計関数、AI に任せれば安心して使える

0
Posted at

ClickHouse の独自集計関数（argMax / quantiles / windowFunnel / retention など）を自然言語で AI に書いてもらい、AI 自身に検算までさせて、結果が信頼できるかを確かめた記録です。

1. はじめに

ここ 1 か月ほど、ClickHouse をいくつかの記事で触ってきました。集計は確かに速いのですが、その集計は独自の関数に強く依存しています。ユニークカウントは uniq、最新レコードは argMax、分位点は quantiles、ファネル分析は windowFunnel、コホートの残存は retention といった具合で、標準的な SQL に慣れた目には最初とっつきにくいものでした。

一方で、AI や MCP 経由のクエリアシストを使うと、この独自関数による壁が大きく下がると感じています。ただし独自関数には「自分で書けても、正しく書けたのか自信が持てない」という別の壁があります。そこで今回は、7 つの集計のお題を自然言語で AI に頼み、事前に用意した正解 SQL と突き合わせて答え合わせをしました。さらに、AI 自身に「この結果は正しいか」を検算させると何が起きるかも見ています。

1.1. 結論（先出し）

  • 独自関数は自然言語で書ける。uniq / argMax / countIf / quantiles は素直に選ばれた
  • ただし LIMIT BY / windowFunnel / retention は、頼み方や環境によって標準 SQL に置き換えられる（迂回する）ことがある
  • AI に検算まで任せると、頼まなくても「合計が母数と一致するか」などの確認クエリを自分で立てて確かめた
  • それでも近似関数の誤差や揺れなど、人間が最後に押さえるべき注意点は残る。独自関数で解かせる → AI 自身に別経路で検算させる → 人間は近似の誤差・揺れだけ最終確認、という型で使うと安心して使える

なお、7 つのお題は ClickHouse Academy1 のトレーニングで扱われる集計関数をもとにしており、各お題の「本命の関数」もそれに沿って決めています。

2. 検証環境

項目 値
ClickHouse Cloud 26.2.1.474（AWS ap-northeast-1、3 vCPU）
MCP サーバ mcp-clickhouse 0.4.0
データ GH Archive2 2026-06-28〜2026-07-04 の 7 日分
データ規模 27,237,240 行 / 317.83 MiB（ディスク上）
クライアント A Claude Desktop（チャット）: Sonnet 5、thinking: low
クライアント B Claude Code（エージェント）: Opus 4.8、thinking: xhigh

同じお題を 2 つのクライアントで解かせています。クライアント A とクライアント B は、モデルも推論の設定も、渡したプロンプトの詳しさも違います。ですので本記事は「どちらの製品が優れているか」ではなく、環境や頼み方で AI の書き方がどう変わるかを見るものとして読んでください。

テーブルは GitHub のイベントを 4 列に絞った単純な構造です。

-- github_events: GitHub のイベント 7 日分
CREATE TABLE github_events
(
    event_type  LowCardinality(String),  -- PushEvent, CreateEvent, PullRequestEvent など
    actor_login String,                   -- 行動したユーザー
    repo_name   String,                   -- "owner/repo" 形式
    created_at  DateTime
)
ENGINE = MergeTree
ORDER BY (event_type, repo_name, created_at);

データは GH Archive の公開 URL から url() 関数で直接取り込みます。次の SQL の日付部分を 2026-06-28 から 2026-07-04 まで変えながら、1 日分ずつ 7 回実行しました。1 日分は 24 ファイル・約 390 万行で、mcp-clickhouse の run_query 経由でもタイムアウトせずに完走しています。

-- GH Archive を 1 日分取り込む（日付部分を 2026-06-28 〜 2026-07-04 に変えて 7 回実行）
INSERT INTO github_events
SELECT
    JSONExtractString(json, 'type')                                AS event_type,
    JSONExtractString(json, 'actor', 'login')                      AS actor_login,
    JSONExtractString(json, 'repo', 'name')                        AS repo_name,
    parseDateTimeBestEffort(JSONExtractString(json, 'created_at')) AS created_at
FROM url('https://data.gharchive.org/2026-06-28-{0..23}.json.gz', 'JSONAsString', 'json String')
SETTINGS max_execution_time = 1800, max_insert_threads = 4;

このデータのイベント種類の分布は、PushEvent が 86.12%、CreateEvent が 8.49%、DeleteEvent が 3.42% で、PullRequestEvent は 0.76%、WatchEvent は 0.21%、ForkEvent は 0.05% でした。自動化された定期コミットが多くを占める、近年の GitHub らしい偏りです。この分布は後のお題設計にも関わってきます。

3. お題の設計

集計のお題を 7 つ用意しました。それぞれ「ClickHouse なら本命の独自関数がある」一方で「標準 SQL 脳だと別の書き方になりがち」なものを Opus に選んでもらいました（選定は解答とは別セッションで行い、解く側には引き継いでいません）。

# 頼んだこと 本命の独自関数 標準 SQL 脳だと書きがちな形
1 期間全体のユニークユーザー数（近似でよい＋厳密との差） uniq / uniqExact COUNT(DISTINCT)
2 リポジトリ別イベント数の p50 / p90 / p99 quantiles PERCENTILE_CONT 相当
3 上位 10 リポジトリの最後のイベント種類と時刻 argMax ROW_NUMBER() OVER
4 日別 × イベント種類別の件数を横 3 列で countIf CASE WHEN の入れ子
5 オーナーごとのイベント数上位 3 リポジトリ LIMIT BY 窓関数＋サブクエリ
6 Create → Push → PR の到達段階別の数 windowFunnel 多段の自己結合
7 初日に活動したユーザーの日次残存率 retention 複雑な自己結合

答え合わせを公平にするため、次の 2 点を守りました。

  • 各お題を頼む文には、関数名や ClickHouse 独自の用語を入れない（windowFunnel を使ってほしいときも「Create → Push → PR の順に到達した数」と日常語で頼む）
  • 正解 SQL は AI に頼む前に自分で実行し、結果を固定しておく（後出しの答え合わせにしない）

なお、お題 6 のファネル系列は、GitHub なら Watch → Fork → PR も考えられますが、前述のとおり WatchEvent と ForkEvent は合わせても全体の 0.26% しかなく、実データでは第 3 段階への到達が 0 件になります。そのため、実際の開発の流れに沿う Create → Push → PR にしています。

4. 手順

流れはこうです。

  1. クライアント A（チャット）で、1 お題ごとに新しいチャットを開いてお題文をそのまま貼る。前のお題の文脈が次に混ざらないようにする
  2. クライアント B（エージェント）に同じ 7 お題を渡し、加えて「エラーが出たら自分で直してよい」「答えが正しいか自分で確認してよい」という前提で解かせる
  3. どちらも mcp-clickhouse 経由で SQL を実行させ、実際に投げられた SQL を system.query_log から回収して、正解 SQL と突き合わせる

生成 SQL の回収では、log_comment を使うと診断クエリと本番クエリを取り違えずに済みます。回収したクエリで、AI が本当にその関数を使ったのかを裏取りしました。

5. 実行結果

5.1. 採点のまとめ

クライアント B（Opus 4.8 のエージェント）の結果を主に採点します。7 お題すべて完走し、リトライが必要だったのは retention の 1 問だけでした。

# お題 選んだ書き方 結果
1 ユニーク数 uniq ＋ uniqExact 近似 2,287,642 / 厳密 2,264,011（誤差 +1.04%）
2 p50/p90/p99 quantiles の複数指定 p50=2 / p90=10 / p99 は後述の揺れ
3 最終イベント argMax(event_type, created_at) 正解と完全一致
4 日別横持ち countIf ×3 正解と完全一致
5 オーナー別上位3 LIMIT 3 BY owner 正解と一致
6 ファネル windowFunnel(604800) レベル別 755,528 / 552,418 / 33,411
7 残存率 retention day0=519,461、翌日 39.1%→6 日目 26.9%

uniq / argMax / countIf / quantiles は、独自関数がそのまま選ばれました。たとえばお題 3 の「最後のイベント」は、標準 SQL なら ROW_NUMBER() OVER (PARTITION BY ... ORDER BY created_at DESC) を書きがちなところ、argMax の一行で済んでいます。

-- お題3: 上位10リポジトリの最終イベント（argMax を選択）
SELECT
    repo_name,
    count()                        AS events,
    argMax(event_type, created_at) AS last_event_type,
    max(created_at)                AS last_event_time
FROM github_events
GROUP BY repo_name
ORDER BY events DESC
LIMIT 10;

お題 5・6・7 も、エージェント（Opus 4.8）は LIMIT BY・windowFunnel・retention という独自の書き方を選びました。お題 6 のファネルは次のとおりです。

-- お題6: Create → Push → PullRequest のファネル（windowFunnel を選択、7日=604800秒）
SELECT level, count() AS sequences
FROM (
    SELECT actor_login, repo_name,
           windowFunnel(604800)(
               created_at,
               event_type = 'CreateEvent',
               event_type = 'PushEvent',
               event_type = 'PullRequestEvent'
           ) AS level
    FROM github_events
    WHERE event_type IN ('CreateEvent', 'PushEvent', 'PullRequestEvent')
    GROUP BY actor_login, repo_name
)
GROUP BY level
ORDER BY level;

5.2. AI が自分で検算の問いを立てた

今回いちばん興味深かったのは、エージェントが頼まれてもいないのに 結果が正しいかを自分で確かめた ことです。

お題 6 のファネルでは、レベル 0〜3 の件数を出したあとに「レベル別の合計が、3 種のイベントを含む (ユーザー × リポジトリ) の組の数と一致するか」を自分で照合していました。実際、2,734,524 + 755,528 + 552,418 + 33,411 = 4,075,881 となり、組の総数と一致します。取りこぼしなく全部の組を数えられている、という確認です。

お題 7 の残存率でも、分母となる初日のユーザー数 519,461 が、uniqExact で直接数えた値と一致するかを別クエリで確かめていました。

独自関数は「書けても合っているか不安」になりがちですが、AI に「別の数え方でも同じ答えになるか」を確認させると、この不安を大きく減らせます。これは MCP サーバや DB の機能ではなく、AI（クライアント側）が自分で検証の段取りを組んだ結果です。

5.3. チャットでの依頼と応答

クライアント A（チャット）で実際に依頼したときの画面です。お題 1 では、頼んでいないのに近似 uniq（HyperLogLog）と厳密 uniqExact を両方出し、標準誤差にも触れています。

お題1: uniq と uniqExact を選び、近似の標準誤差まで説明した応答

お題 3 では argMax で上位 10 リポジトリの最終イベントを取得し、7 位だけ DeleteEvent で毛色が違う点を自分で補足しています。

お題3: argMax による上位10リポジトリの最終イベント一覧

お題 6 のファネルは、windowFunnel ではなく配列関数による手書きで実装し、各段階の到達数と判定ロジックを説明しています。

お題6: 配列関数で手書きしたファネルの到達段階別件数

6. 考察

6.1. 近似関数には「誤差」と「揺れ」がある

ClickHouse の速い集計の一部は、近似アルゴリズムで成り立っています。ここが、人間が最後に確認するポイントです。

ユニークカウントの uniq は、今回 2,287,642 を返しました。厳密な uniqExact は 2,264,011 で、差は +1.04% です。uniq は HyperLogLog 系の近似3で、メモリと速度の代わりに数%の誤差を受け入れる関数です。1% 前後の誤差は、用途によっては十分許容できますが、「ほぼ正確」と思い込むと少しずれます。

分位点の quantile / quantiles はさらに注意が要ります。お題 2 の p99 は、実行するたびに 47 / 50 / 51 / 54 / 56 と変わりました。quantile は決まったメモリ内でサンプリングして近似する関数4で、並列実行のマージ順序によって結果が毎回わずかに動きます。p50=2・p90=10 は小さい値なので毎回同じでしたが、裾の p99 だけが揺れます。厳密に固定したいときは quantilesExact を使うと、今回は 53 で安定しました。

つまりお題 2 で「AI の答えが自分の正解と少し違う」ように見えたのは、AI の間違いではなく近似関数の性質です。近似関数を含むお題を答え合わせするときは、厳密版と両方を出して「揺れの範囲か、本当の間違いか」を切り分ける必要があります。

6.2. 専用関数と手書きは「数え方」が変わることがある

お題 6 のファネルは、windowFunnel5 を使うと第 3 段階まで到達した組が 33,411、クライアント A が書いた配列関数による手書き版では 33,050 と、約 1.1% ずれました。windowFunnel は連鎖の起点を自動で選ぶのに対し、手書き版は「最初の Create」に起点を固定したため、最初の Create のあとに連鎖がない組を取りこぼしていました。同じ「7 日以内に Create → Push → PR」という日本語でも、専用関数と素朴な手書きで数え方の定義がずれる、という例です。差は小さいものの、こういうところは正解を知っている人間が最後に見る意味が残ります。

6.3. 安心して使うための確認の型

ここまでをまとめると、独自関数を AI に任せるときは次の 3 段で回すと安心して使えます。

  1. 独自関数で解かせる: uniq / argMax / windowFunnel などを自然言語で頼む
  2. AI 自身に別経路で検算させる: 近似 uniq は uniqExact と突き合わせ、ファネルはレベル合計と母数を照合、残存率の分母は uniqExact と照合、というように「別の数え方で同じ答えになるか」を確認させる
  3. 人間は近似の注意点だけ最終確認する: 近似の誤差・揺れ、専用関数と手書きの数え方の違いなど、AI の検算でも気づきにくいところを押さえる

6.4. 書き方はクライアントや頼み方で変わる

同じお題でも、クライアント A（Sonnet 5、thinking: low、短い依頼）とクライアント B（Opus 4.8、詳しい依頼）で書き方が分かれました。

お題 クライアント A クライアント B
5 オーナー別上位3 row_number() OVER（標準 SQL） LIMIT 3 BY owner
6 ファネル 配列関数で手書き windowFunnel
7 残存率 DISTINCT ＋ 自己結合 retention

クライアント B は独自関数を素直に選び、A は動く標準 SQL に寄りました。ただし両者はモデルも推論の設定も依頼の詳しさも違うため、これはモデルの優劣ではありません。どの関数を選ぶかは、DB や MCP の機能ではなくクライアント側の AI の判断であり、頼み方や環境で変わる、という切り分けだけが言えます。独自関数を引き出すのに関数名まで知っている必要はなく、エラー修正や検証まで任せる・「ClickHouse らしく」と方向づける、といった頼み方に効果があると考えられます。

なお、ClickHouse には公式のエージェント向けナレッジ集（agent-skills）6があります。今回これを入れて同じお題を解かせても、集計関数の選び方は変わりませんでした。このナレッジ集は本記事執筆時点（2026-07）ではスキーマ設計やクエリ最適化・データ投入が主で、windowFunnel や retention の使い方は含まないためと考えられます。

7. まとめ

ClickHouse の独自集計関数は、標準 SQL に慣れた目には最初とっつきにくいものですが、自然言語で AI に頼むと uniq / argMax / countIf / quantiles などはそのまま書いてもらえました。LIMIT BY / windowFunnel / retention は頼み方や環境で標準 SQL に置き換えられることもありますが、関数名を知らなくても、エラー修正や検証まで任せる頼み方で今回は 3 つとも独自関数で解けています。

そのうえで、独自関数は「書けても合っているか不安」という別の壁があります。今回は AI に検算の問いまで立てさせると、レベル合計と母数の照合のように、別経路で答えを裏取りしてくれました。近似関数の誤差や揺れなど人間が押さえる点は残りますが、独自関数で解かせる → AI に検算させる → 人間が注意点を最終確認するという型で使えば、独自関数も安心して使えると感じています。

もともと SQL という標準的な言語で成り立ってきた世界で独自関数を使おうとすると、最初の学習コストがかかるため敬遠されることも多かったと思います。ただ、今は AI のアシストがあるので、学び切ってから使うのではなく、まず使い始められる。それを今回の検証で改めて確認できました。

参考

  1. ClickHouse Academy（ClickHouse 公式のトレーニング）. https://learn.clickhouse.com/ ↩

  2. GH Archive（GitHub の公開イベントを時間単位で配布するアーカイブ）. https://www.gharchive.org/ ↩

  3. ClickHouse Docs, Aggregate Functions Reference, uniq. https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/uniq ↩

  4. ClickHouse Docs, Aggregate Functions Reference, quantile / quantiles. https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/quantiles ↩

  5. ClickHouse Docs, Parametric Aggregate Functions, windowFunnel / retention. https://clickhouse.com/docs/sql-reference/aggregate-functions/parametric-functions ↩

  6. ClickHouse agent-skills（公式）. https://github.com/ClickHouse/agent-skills ↩

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?