# GROUP BY ROLLUPとスカラーサブクエリでtext-to-SQLエージェントの文中計算をなくす — 禁止ルールでは止まらなかった集計とファンアウトのミス > text-to-SQLエージェントが、行を手で足すなという規則を破りました。GROUP BY ROLLUPとスカラーサブクエリは、規則ではなく手順そのものを消して直しました。 - Source: https://oharu121.com/ja/blog/text-to-sql-rollup-scalar-subquery-fan-out/ - Published: 2026-08-28T07:57:42+09:00 - Tags: Text-to-SQL, LLM, AWS --- **要点** - 禁止規則は、モデルに毎回の遵守を求めます。間違いを表現できないクエリの形は、遵守を一切求めません。 - 手計算での合算は、指示違反ではありませんでした。2つのプロンプト規則が同時には満たせず、モデルはどちらか一方を黙って選んでいたのです。 - `GROUP BY ROLLUP`は小計と合計を1つの結果として返すため、2段階の回答に加算が不要になります。 - 単一エンティティに関する比率では、ファンアウトの修正はそもそもJOINしないことです。2つのスカラーサブクエリには、掛け合わされる行がありません。 - 計測によって2つの変更が却下されました。一方はベンチマークだけを動かすものであり、もう一方は合格していた3問を失敗させるものでした。 ## はじめに 私たちの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本のクエリを実行しました。 ```sql SELECT company_group, consolidation, stock_count FROM stock WHERE fiscal_year = 2026 ``` 10行が返ってきました。エージェントはそれを回答の中で足し合わせ、合計は1桁分間違っていました。 エージェントは、2段階報告の取り決めが求めることと、計算禁止規則が禁じることの両方を、そのまま実行してしまいました。自分の書いたクエリでは、その両方を同時には満たせなかったからです。 *Figure — RuleConflict: どちらの規則も間違ってはいません。ただし両方を合わせると、エージェントには書きようのないクエリを求めることになり、結果としてエラーの出ない方の規則が破られました。* **内訳を返すクエリが返すのは部分であって全体ではありません。** 2段目を報告するには、どのセルにも入っていない数値が必要で、エージェントの選択肢は2本目のクエリを実行するか、自分で行を足すかのどちらかでした。エージェントは行を足す方を選びました。 これを「規則違反」として読むと、誤った修正に向かってしまいます。**規則は無視されていたのではなく、その位置では満たしようがなかった**のであり、規則をいくら書き直しても、その位置自体は変わりません。 ## GROUP BY ROLLUP:合計がセルとして返る `GROUP BY ROLLUP`はエージェントが提案し、私がそれを採用しました。PrestoもTrinoも対応しているため、Athena上でもそのまま動きます。 ```sql 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 ``` ```text 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しました。 ```sql 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倍大きく算出されました。 *Figure — FanOut: 人員側の列はどちらの読み方でも正しい値のままです。誤っているのは複製された側だけであり、それがこの失敗を見つけにくくしています。* この失敗を最初のものより厄介にしていた点が2つありました。**人員側の数値は正しかった**のです。3社のうち2社の営業人員が0人だったため、`SUM`と`MAX`が一致していたからで、半分正しい回答は、完全に間違った回答より疑われにくくなります。さらに間欠的でもありました。この問いを14回観測したうち、失敗が現れたのは1回だけで、1回だけ合格したところで、それは何の証拠にもなりませんでした。 プロンプトにはすでにファンアウトについてのセクションがあり、JOINの前に両側をサブクエリで集計しておくという標準的な対処法もすでに書かれていました。計算禁止規則のときと同様、指針は存在していたのに、それでも失敗は起きたのです。 ## スカラーサブクエリ:単一エンティティならJOINしない この失敗を解決した気づきは、**このクエリにはそもそもJOINが不要だった**というものです。質問の対象は1つのグループだけです。分子は1つの数値、分母も1つの数値であり、JOINは集合同士を突き合わせるための仕組みにすぎません。 ```sql 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から姿を消しました。** *Figure — ProhibitionVsShape: 左列は毎回成り立っていなければなりません。右列には、成り立たせるべきものがそもそもありません。* ## ベンチマークだけを動かしていた修正 2つのプロンプト変更に先立って、エージェントは別のものをすでに実装していました。SQLツールの裏にあるLambda内で列の合計を計算する仕組みです。複数行の結果には、加算可能な数値列の合計が自動で添えられて返るようになり、合計は計算するものではなく、常に引用するだけの値になるはずでした。除外すべき列の型、1行だけの結果、数値でないセルなどを網羅する8個のユニットテストも付けて実装されていました。 しかしこれは、デプロイ前に撤回されました。アシスタントプラットフォームが実際にどうSQLを実行しているかを確認したところ、クエリツールは**プラットフォーム組み込みの機能**であり、フラグとデータベース名で設定され、Athenaを直接実行していることが分かったのです。**改良したばかりのLambdaは、私たちのベンチマーク実行環境からしか到達できないものでした。** *Figure — WhereFixLives: 最初の修正が狙っていた層は、本番が通る経路の上にはありませんでした。* そのままデプロイしていれば、本番環境は何も変わらないまま、ベンチマークのスコアだけが上がっていたはずです。**計測装置そのものが改善されたことで良くなった計測値は、計測しないより悪いものです。** 起きていない進歩を報告してしまうからです。この発見のあと、私は変更の対象をメインプロンプトとレビュアーだけに限ると決め、上記の2つの形はその制約のもとで作られました。 ## 状況を悪化させた変更 もう1つの却下された変更も、報告する価値があります。この手法が「確証」だけでなく「反証」もできることを示しているからです。 いくつかの失敗は、暦月を会計年度に変換する処理に関わるものでした。そこでエージェントは、計算に関する規則を、対象範囲内の各年について対応関係を明示したルックアップテーブルに置き換えました。**計算そのものを取り除くやり方は、直前の2件でうまくいったばかりだったので、この発想を広げるのは一見して明らかに正しく見えました。** この変更は、日付に依存する13問について4ラウンドにわたって計測されました。**それまで一貫して合格していた3問が失敗し始め**、いずれも同じ誤った年になっていました。そのうち1つは、SQLコメントには正しい年を書きながら、`WHERE`句には別の年を入れていました。原因として考えられるのは干渉です。プロンプトには1月から3月の対応関係が、重なり合う2つの表として書かれることになり、モデルは適用すべき1つの規則ではなく、競合する2つのパターンと照合することになったのです。 変更を元に戻すと以前の挙動が復元され、影響を受けた3問について18回連続で合格することを確認しました。**2つの良い変更を生んだのと同じ推論が、1つの悪い変更も生んだ**のであり、両者を見分けられたのは計測だけでした。 ## まとめ これらの変更を反映した実行では、テストスイートは57問中57問で合格し、計算とファンアウトに関する失敗はなくなりました。試みた変更は4つで、そのうち採用したのは2つです。 | 変更 | 結果 | | --- | --- | | 2段階の合計に`GROUP BY ROLLUP` | 採用。3ラウンドで12/12、毎回テンプレートが使われた | | 単一エンティティの比率にスカラーサブクエリ | 採用。11/11で、危険なJOINの形が生成されなくなった | | SQLツール内での列合計 | 撤回。本番はそのツールを経由しない | | 日付変換用のルックアップテーブル | 差し戻し。合格していた3問を壊した | 一般化できる部分は、採用した2つの変更と、それらが置き換えた規則との違いにあります。**禁止規則は、将来のすべての実行において遵守を求めるものです。一方、間違いを表現しようのないクエリの形は、何も求めません。** 例まで添えた規則が破られているとき、3つ目の例を追加するのは、たいてい最も弱い手です。より有効な問いは、モデルに代わりに何を書かせるべきか、そしてその位置で失敗そのものを表現不可能にできないか、ということです。 このほかに、持ち越すべき小さな教訓が2つあります。1つは、規則が無視されたと決めつける前に、それが破られている位置で*満たしうる*ものかどうかを確認することです。ここで最初に起きた失敗は、2つの正しい規則同士の本物の衝突だったからです。もう1つは、スイート全体の合計を信用せず、問いごとに繰り返し実行して計測することです。およそ14回に1回という間欠的な失敗は、1回きりの実行では見えません。何も役に立たなかった変更と、害になった変更のどちらも、繰り返し計測して初めて見つかりました。 この作業がエージェント支援によるものだったため、判断がどこにあったかを記しておきます。規則同士の衝突を診断し、2つの仕組みを提案し、プロンプトの変更を書き、Lambdaが本番の経路から外れていることを見つけたのはエージェントです。その発見のあとで対象範囲を決め、ファンアウトの修正については効果の証明を求めずに採用を決め、最終的に何を残すかを判断したのは私です。**エージェントの提案そのものは良かった一方、修正をどこに置くべきかという最初の直感は誤っていました。** これがおおよそ期待すべき配分でしょう。 ## 参考リンク - [Presto の GROUP BY ドキュメント。ROLLUP と、それが展開される GROUPING SETS を扱う](https://prestodb.io/docs/current/sql/select.html#group-by-clause) - [Amazon Athena の SQL リファレンス。Trino の GROUP BY 拡張を引き継いでいる](https://docs.aws.amazon.com/athena/latest/ug/ddl-sql-reference.html) - [in-context learning ベースの text-to-SQL エラーに関する研究。集計構造の不整合を独立したエラー種別に分類している](https://arxiv.org/html/2501.09310v2) - [大規模言語モデルのエージェントは臨床計算にツールを使える、というモデルから計算処理を切り離す話](https://www.nature.com/articles/s41746-025-01475-8)