GROUP BY ROLLUPとスカラーサブクエリでtext-to-SQLエージェントの文中計算をなくす — 禁止ルールでは止まらなかった集計とファンアウトのミス

はじめに

私たちのtext-to-SQLエージェントが、本来は約61万2千であるはずの合計を約71万2千と報告しました。実行したSQLは正しく、回答の書式も正しく、エラーも出ていませんでした。エージェントは10行を取得し、それを回答の文中で自分で足し合わせ、十万の位で1桁ずれていたのです。

この件を追いかける価値があったのは、システムプロンプトがすでにそれを禁じていたからです。規則は「計算はSQLで行う」というセクションに存在し、誤った例と正しい例が両方とも用意されていました。例まで添えた規則が、それでも破られていたのであり、その場合にありがちな対応(規則をもっと強く書き直す)は、そのセクションを書いた人物がすでに試していました。

修正できたのは、より良い禁止規則ではありませんでした。エージェントが書くクエリの形を2つ変えることで、そもそも間違いの起きる余地をなくしたのです。合計にはGROUP BY ROLLUPを、JOINの代わりにはスカラーサブクエリを使いました。この記事では、この2つの失敗と2つの修正、そして計測によって却下されたもう2つの変更を扱います。

禁止ルールはすでにあった

プロンプトの該当箇所には、要するに「回答文中では計算しない」と書かれていました。比率もパーセンテージも単位換算も、そして小計も、すべてSELECT句の中で行うべきものであり、書く数値はクエリが返した数値そのものであるべきだ、というものです。

例は2つ添えられていました。一つは、別々に取得した2つの数値から単価を文中で計算してしまう誤った回答例。もう一つは、内訳を手で足してしまう誤った回答例で、1,951 + 278 = 2,229 という計算がそのまま書かれていました。

その2つ目の例こそ、実際に起きた失敗そのものにほぼ一致していました。

これにより、都合の良い説明はすべて排除されます。規則が存在しなかったわけでも、曖昧だったわけでも、例もなく長文の中に埋もれていたわけでもありません。モデルは規則を伝えられ、誤った例も正しい例も見せられていたのに、それでも誤った方をやってしまったのです。

失敗1:3行を文中で足した

質問は全国合計を求めるものでした。プロンプトの別の箇所にある取り決めにより、この種の合計は2段階で報告する必要がありました。まず連結子会社の合計、次に持分法適用会社を含めた数値です。

エージェントは1本のクエリを実行しました。

SELECT company_group, consolidation, stock_count
FROM stock
WHERE fiscal_year = 2026

10行が返ってきました。エージェントはそれを回答の中で足し合わせ、合計は1桁分間違っていました。

エージェントは、2段階報告の取り決めが求めることと、計算禁止規則が禁じることの両方を、そのまま実行してしまいました。自分の書いたクエリでは、その両方を同時には満たせなかったからです。

内訳を返すクエリが返すのは部分であって全体ではありません。 2段目を報告するには、どのセルにも入っていない数値が必要で、エージェントの選択肢は2本目のクエリを実行するか、自分で行を足すかのどちらかでした。エージェントは行を足す方を選びました。

これを「規則違反」として読むと、誤った修正に向かってしまいます。規則は無視されていたのではなく、その位置では満たしようがなかったのであり、規則をいくら書き直しても、その位置自体は変わりません。

GROUP BY ROLLUP:合計がセルとして返る

GROUP BY ROLLUPはエージェントが提案し、私がそれを採用しました。PrestoもTrinoも対応しているため、Athena上でもそのまま動きます。

SELECT COALESCE(consolidation, 'total') AS tier,
       SUM(stock_count) AS stock_count
FROM stock
WHERE fiscal_year = 2026
GROUP BY ROLLUP(consolidation)
ORDER BY 1

tier             stock_count
consolidated         524,000
equity_method         88,000
total                612,000

2段階の回答に必要な2つの数値は、どちらもセルとして手に入るようになりました。加算は「推奨されない」のではなく、そもそも手順として存在しなくなったのです。エージェントは2つの値を引用するだけで済みます。

プロンプト側の変更は、2段階の回答を説明するセクションに実例入りのテンプレートを追加し、さらに制約を狭く保つ一文を足しただけでした。すなわち、小計と合計を2本の別々のクエリで取得してもよいが、禁じられているのは回答文中で内訳の行を足すことだけである、というものです。特定の1本のクエリを義務づけるのではなく、禁止する動作を1つだけ名指しすることで、エージェントに余地を残しつつ、この失敗は塞がれました。

変更後、テストスイート内の2段階回答を必要とする4問を、それぞれ3回ずつ実行しました。12回すべてが合格し、それまで失敗していた問いは、3回ともROLLUPテンプレートを使い、ツール呼び出しも4回から1回に減っていました。

失敗2:1行が3行にJOINされ、売上が3倍になった

2つ目の失敗は、1人当たりの比率を求める質問から生じました。ある事業セグメントの年間売上を、その時点でのそのグループの営業人員数で割るというものです。売上は月次のfinancialsテーブルに、人員は月次のheadcountスナップショットにそれぞれ格納されています。

エージェントは売上を正しく1行に集計したうえで、それを人員テーブルにJOINしました。

SELECT SUM(r.revenue) / NULLIF(MAX(p.sales_staff), 0)
FROM (
  SELECT company_group, SUM(revenue) AS revenue
  FROM financials
  WHERE fiscal_year = 2025 AND segment = 'construction'
  GROUP BY 1
) r
JOIN headcount p ON r.company_group = p.company_group
WHERE p.fiscal_year = 2025 AND p.month = 3

このグループには3社が属しているため、headcountは3行返ってきます。1行しかない売上が、その3行それぞれに複製される形になり、SUMはそれを3回数えてしまいました。約21,000だった数値は約63,000になり、比率はおよそ3倍大きく算出されました。

この失敗を最初のものより厄介にしていた点が2つありました。人員側の数値は正しかったのです。3社のうち2社の営業人員が0人だったため、SUMとMAXが一致していたからで、半分正しい回答は、完全に間違った回答より疑われにくくなります。さらに間欠的でもありました。この問いを14回観測したうち、失敗が現れたのは1回だけで、1回だけ合格したところで、それは何の証拠にもなりませんでした。

プロンプトにはすでにファンアウトについてのセクションがあり、JOINの前に両側をサブクエリで集計しておくという標準的な対処法もすでに書かれていました。計算禁止規則のときと同様、指針は存在していたのに、それでも失敗は起きたのです。

スカラーサブクエリ:単一エンティティならJOINしない

この失敗を解決した気づきは、このクエリにはそもそもJOINが不要だったというものです。質問の対象は1つのグループだけです。分子は1つの数値、分母も1つの数値であり、JOINは集合同士を突き合わせるための仕組みにすぎません。

SELECT ROUND(
  (SELECT SUM(revenue) FROM financials
    WHERE company_group = 'north' AND fiscal_year = 2025 AND segment = 'construction')
  / NULLIF((SELECT SUM(sales_staff) FROM headcount
    WHERE company_group = 'north' AND fiscal_year = 2025 AND month = 3), 0)
, 2) AS revenue_per_head

それぞれのサブクエリは、正確に1つの値だけを返します。 複製されうる行がそもそも存在しないため、ここではファンアウトは回避されているのではなく、表現しようがないのです。これは、サブクエリでJOINする従来のパターンとは異なる保証です。従来のパターンも正しくはありますが、エージェントが毎回両側をきちんと集計することに依存したままです。

書き直したセクションでは、単一エンティティの比率にはまずこの形を使うよう先頭に置き、JOINパターンは本来の用途、すなわち複数グループを比較したり順位付けしたりする場合に格下げしました。そうした場合こそJOINが本当に必要であり、GROUP BYが自然とエージェントを両側の集計へ導きます。

変更後、テストスイート内の比率に関する11問はすべて合格しました。単一エンティティを扱う8問のうち5問では、JOINが完全になくなりました。 残る3問がJOINを残していたのは、年次スナップショットに対してMAXでJOINしていたためで、MAXは行が重複しても値が変わらないため複製の影響を受けません。危険な組み合わせである、複数行にわたるJOINをまたいだSUMは、生成されるSQLから姿を消しました。

ベンチマークだけを動かしていた修正

2つのプロンプト変更に先立って、エージェントは別のものをすでに実装していました。SQLツールの裏にあるLambda内で列の合計を計算する仕組みです。複数行の結果には、加算可能な数値列の合計が自動で添えられて返るようになり、合計は計算するものではなく、常に引用するだけの値になるはずでした。除外すべき列の型、1行だけの結果、数値でないセルなどを網羅する8個のユニットテストも付けて実装されていました。

しかしこれは、デプロイ前に撤回されました。アシスタントプラットフォームが実際にどうSQLを実行しているかを確認したところ、クエリツールはプラットフォーム組み込みの機能であり、フラグとデータベース名で設定され、Athenaを直接実行していることが分かったのです。改良したばかりのLambdaは、私たちのベンチマーク実行環境からしか到達できないものでした。

そのままデプロイしていれば、本番環境は何も変わらないまま、ベンチマークのスコアだけが上がっていたはずです。計測装置そのものが改善されたことで良くなった計測値は、計測しないより悪いものです。 起きていない進歩を報告してしまうからです。この発見のあと、私は変更の対象をメインプロンプトとレビュアーだけに限ると決め、上記の2つの形はその制約のもとで作られました。

状況を悪化させた変更

もう1つの却下された変更も、報告する価値があります。この手法が「確証」だけでなく「反証」もできることを示しているからです。

いくつかの失敗は、暦月を会計年度に変換する処理に関わるものでした。そこでエージェントは、計算に関する規則を、対象範囲内の各年について対応関係を明示したルックアップテーブルに置き換えました。計算そのものを取り除くやり方は、直前の2件でうまくいったばかりだったので、この発想を広げるのは一見して明らかに正しく見えました。

この変更は、日付に依存する13問について4ラウンドにわたって計測されました。それまで一貫して合格していた3問が失敗し始め、いずれも同じ誤った年になっていました。そのうち1つは、SQLコメントには正しい年を書きながら、WHERE句には別の年を入れていました。原因として考えられるのは干渉です。プロンプトには1月から3月の対応関係が、重なり合う2つの表として書かれることになり、モデルは適用すべき1つの規則ではなく、競合する2つのパターンと照合することになったのです。

変更を元に戻すと以前の挙動が復元され、影響を受けた3問について18回連続で合格することを確認しました。2つの良い変更を生んだのと同じ推論が、1つの悪い変更も生んだのであり、両者を見分けられたのは計測だけでした。

まとめ

これらの変更を反映した実行では、テストスイートは57問中57問で合格し、計算とファンアウトに関する失敗はなくなりました。試みた変更は4つで、そのうち採用したのは2つです。

一般化できる部分は、採用した2つの変更と、それらが置き換えた規則との違いにあります。禁止規則は、将来のすべての実行において遵守を求めるものです。一方、間違いを表現しようのないクエリの形は、何も求めません。 例まで添えた規則が破られているとき、3つ目の例を追加するのは、たいてい最も弱い手です。より有効な問いは、モデルに代わりに何を書かせるべきか、そしてその位置で失敗そのものを表現不可能にできないか、ということです。

このほかに、持ち越すべき小さな教訓が2つあります。1つは、規則が無視されたと決めつける前に、それが破られている位置で満たしうるものかどうかを確認することです。ここで最初に起きた失敗は、2つの正しい規則同士の本物の衝突だったからです。もう1つは、スイート全体の合計を信用せず、問いごとに繰り返し実行して計測することです。およそ14回に1回という間欠的な失敗は、1回きりの実行では見えません。何も役に立たなかった変更と、害になった変更のどちらも、繰り返し計測して初めて見つかりました。

この作業がエージェント支援によるものだったため、判断がどこにあったかを記しておきます。規則同士の衝突を診断し、2つの仕組みを提案し、プロンプトの変更を書き、Lambdaが本番の経路から外れていることを見つけたのはエージェントです。その発見のあとで対象範囲を決め、ファンアウトの修正については効果の証明を求めずに採用を決め、最終的に何を残すかを判断したのは私です。エージェントの提案そのものは良かった一方、修正をどこに置くべきかという最初の直感は誤っていました。 これがおおよそ期待すべき配分でしょう。

参考リンク

• Presto の GROUP BY ドキュメント。ROLLUP と、それが展開される GROUPING SETS を扱う
• Amazon Athena の SQL リファレンス。Trino の GROUP BY 拡張を引き継いでいる
• in-context learning ベースの text-to-SQL エラーに関する研究。集計構造の不整合を独立したエラー種別に分類している
• 大規模言語モデルのエージェントは臨床計算にツールを使える、というモデルから計算処理を切り離す話