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?

@rex0220

【kSQL 実践 #1】アプリをまたいで集計する — JOIN と「押し下げ」の 2 種、FROM に書く順で取得量が変わる

0
Posted at

結論（3 行）

  • 顧客×案件のようなアプリをまたぐ集計は、ルックアップのコピー先を結合キーにした INNER JOIN + GROUP BY の 1 文で書けます
  • kSQL の JOIN には押し下げが 2 種あります。WHERE の条件を各アプリへ送るものと、先に取った側の結合キーで JOIN 先を絞るものです。後者は INNER JOIN で、FROM に書いた側から JOIN 先へしか効きません
  • だから FROM には絞り込める側（今回は案件）を書く。同じ結果でも取得量が変わり、上限 10 件でも通るか、100 件で止まるかの差になります（実測）

前回（第 0 回: 標準 SQL との差分と実行モデル）で、SQL が REST API の呼び出し計画に翻訳される仕組みと EXPLAIN の読み方を見ました。
今回から業務課題 1 つにつき 1 本です。最初は、kintone で一番よく詰まる「複数アプリをまたぐ集計」を扱います。

課題

営業支援パックの顧客管理（APP4148）と案件管理（APP4149）で、顧客ランク別に受注額と件数を出したい。

kintone の関連レコード一覧は「その顧客の案件を一覧に出す」ことはできますが、ランクをまたいで足し上げることはできません。
一覧のグラフ機能も 1 アプリの中で閉じるので、案件アプリのグラフに顧客ランクの軸は作れません。
REST で両方を全件取って自前で突き合わせる、というのが今までの答えでした。

標準 SQL ならこう書く

SELECT c.rank, COUNT(*) AS deals, SUM(d.amount) AS won_amount
FROM customers c
INNER JOIN deals d ON c.id = d.customer_id
WHERE d.status = 'won'
GROUP BY c.rank
ORDER BY won_amount DESC

kSQL ではこう書く

案件管理の「会社名」はルックアップで、顧客管理の顧客No.（レコード番号）を 顧客No_ にコピーしています。これが結合キーです。

SELECT a.顧客ランク, COUNT(*) AS 件数, SUM(b.売上) AS 受注額
FROM APP4148 AS a
INNER JOIN APP4149 AS b ON a.顧客No = b.顧客No_
WHERE b.商談フェーズ IN ('受注')
GROUP BY a.顧客ランク
ORDER BY 受注額 DESC
顧客ランク 件数 受注額
A 5 22,800,000
B 3 18,000,000

標準 SQL との差分はこれだけです。

  • テーブル名が APP4148。別名は AS a で付けられ、以降 a.顧客ランク のように参照する
  • ON はフィールド同士の等式 1 本。a.顧客No = b.顧客No_ は可、AND で 2 本目は不可、式や関数も不可
  • ドロップダウンの 商談フェーズ は IN ('受注') と書く（= でも動くが、複数選択系では IN が必須なので、本シリーズでは選択系の条件を IN に揃える）
  • INNER は省略可能。JOIN だけなら INNER JOIN

結果に C ランクと D ランクがいません。受注が 1 件も無いランクは、WHERE で案件が落ちた時点で行ごと消えます。
「0 だった」のか「そもそも無かった」のか区別が付かない、という問題です。対処は後述の LEFT JOIN と、次の集計内 CASE です。

フェーズ別の内訳を 1 文で出す（集計内 CASE）

「受注だけでなく、内示・提案中も横に並べたい」なら、WHERE で絞らずに集計関数の中で CASE を使います。

SELECT a.顧客ランク,
       SUM(CASE WHEN b.商談フェーズ = '受注'   THEN b.売上 ELSE 0 END) AS 受注額,
       SUM(CASE WHEN b.商談フェーズ = '内示'   THEN b.売上 ELSE 0 END) AS 内示額,
       SUM(CASE WHEN b.商談フェーズ = '提案中' THEN b.売上 ELSE 0 END) AS 提案中額,
       COUNT(*) AS 案件数
FROM APP4148 AS a
INNER JOIN APP4149 AS b ON a.顧客No = b.顧客No_
GROUP BY a.顧客ランク
ORDER BY a.顧客ランク
顧客ランク 受注額 内示額 提案中額 案件数
A 22,800,000 6,250,000 12,600,000 13
B 18,000,000 6,500,000 4,850,000 5
C 0 0 3,600,000 1
D 0 0 7,200,000 1

C と D が出ました。ただしこれは「案件はあるが受注が無い」ランクが出ただけで、案件が 1 件も無い顧客はまだ出ません。そこは LEFT JOIN の仕事です。

仕組み — 押し下げは 2 種ある

最初の SQL を EXPLAIN します（プラグインなら EXPLAIN ボタン）。
以下の EXPLAIN 出力は、プラグインの EXPLAIN ボタンの表記（アプリ行が APP4148 AS a (4148)）に揃えています。CLI や MCP では同じ行が APP4148@prod AS a のように profile 名付きで出ますが、実行計画の意味は同じです。

fetch summary: ALL
  mode:          FULL_SCAN
  complete input: required (onLimit=truncate disabled)
  complete input reason: GROUP_BY, LOCAL_ORDER, AGGREGATE
  reason:        JOIN あり, GROUP BY あり, 集計関数（COUNT / SUM 等）あり
  join pushdown plan: applied (runtime metadata resolved)
  client residual: b.商談フェーズ IN ('受注')
  app:           APP4148 AS a (4148)
  kintone query: (全件取得)
  fetch:         ALL
  fields:        顧客ランク, 顧客No
  JOIN:        APP4149 AS b (4149)
  kintone query: 商談フェーズ in ("受注")
  fetch:         EXACT
  pushdown applied: 商談フェーズ in ("受注")
  fields:        売上, 顧客No_, 商談フェーズ

ソースが 2 つあるので、fetch が 2 回出ます。読むべき点は 3 つです。

  1. 案件（b）には WHERE が押し下がった。 pushdown applied: 商談フェーズ in ("受注") で、案件は受注の 8 件しか取りません。これが押し下げの 1 種目、WHERE の単一 alias 述語を各アプリへ送る動きです。述語を書いた側のアプリにしか効きません
  2. 顧客（a）は全件取得。 215 件を丸ごと取っています。JOIN は INNER なのに、なぜ絞られないのか
  3. client residual: b.商談フェーズ IN ('受注') は、押し下げた条件を JOIN 後にもう一度インメモリで評価する、という宣言です。押し下げは取得量の最適化であって、結果を変えません

2 の答えが、押し下げの 2 種目です。

結合キーによる絞り込みは「FROM に書いた側 → JOIN 先」にしか効かない

kSQL は、先に取得した側の結合キーの実値を使って、JOIN 先の取得候補を in (...) で絞ります（50 キー単位・最大 300 キー）。
「先に取得した側」とは FROM に書いた側です。

ただし、最初の SQL ではこの最適化は使われていません。JOIN 先の案件側に押し下げ可能な WHERE 条件があるため、v3.77.0 は案件をその条件で独立に取得し、FROM 側の顧客と並列に読みます。結合キーの絞り込みは試しません。
その結果、案件は受注 8 件に絞れますが、FROM 側の顧客には条件が無いので 215 件を全件取得します。
先ほどの EXPLAIN に join key prefilter 行が無かったのはそのためです。join pushdown plan: applied は WHERE 述語の押し下げ計画を指していて、2 種目の結合キー最適化は別の join key prefilter 行で確認します。

そこで順番を入れ替え、WHERE で 8 件に絞れる案件を FROM に、顧客を JOIN 先にします。JOIN 先の顧客には独立した WHERE 条件が無いので、今度は案件の取得完了後に、その結合キーで顧客を絞れます。

SELECT a.顧客ランク, COUNT(*) AS 件数, SUM(b.売上) AS 受注額
FROM APP4149 AS b
INNER JOIN APP4148 AS a ON b.顧客No_ = a.顧客No
WHERE b.商談フェーズ IN ('受注')
GROUP BY a.顧客ランク
ORDER BY 受注額 DESC

fetch summary: PREFILTERED
  ...
  app:           APP4149 AS b (4149)
  kintone query: 商談フェーズ in ("受注")
  fetch:         EXACT
  pushdown applied: 商談フェーズ in ("受注")
  fields:        売上, 顧客No_, 商談フェーズ
  JOIN:        APP4148 AS a (4148)
  kintone query: (runtime source keys)
  fetch:         PREFILTERED (未確定)
  join key prefilter: runtime candidate
  join key prefilter reason: JOIN_KEY_VALUES_RUNTIME
  fields:        顧客ランク, 顧客No

顧客側が PREFILTERED (未確定) に変わり、join key prefilter: runtime candidate が出ました。
「未確定」なのは、EXPLAIN はレコードを取らないので、実行時に案件から得たキーが何個になるか分からないためです。
実行すると、受注 8 件の 顧客No_ で顧客を in (...) 取得するので、顧客は最大 8 件しか取りません。

順番を入れ替えた SQL の EXPLAIN 出力。顧客側が fetch: PREFILTERED (未確定)、join key prefilter: runtime candidate

実測: 上限を下げると差が出る

結果は 2 つの SQL で同じです。違いは取得量なので、取得上限を下げて見ます
（プラグインなら「⚙ オプション → 取得」の「最大取得件数」。手元は顧客 215 件・案件 20 件）。
上限は JOIN 全体の合計ではなく、アプリごとに判定されます。上限 10 件なら、案件 8 件と顧客 8 件をそれぞれ取っても成功します。

最大取得件数 顧客を FROM に 案件を FROM に
3,000（既定） 成功 成功
100 エラー（顧客 215 件で上限超過） 成功
10 エラー 成功（案件 8 件＋顧客 8 件以下）

エラーはこう出ます。

クエリの正しい結果には完全な候補集合が必要です。complete input reason: GROUP_BY, LOCAL_ORDER, AGGREGATE。
onLimit=truncateは使用できません。取得件数が上限（100 件）を超えました。WHERE 句で絞り込むか、maxRecords を引き上げてください。

CLI や MCP では同じメッセージが FetchAllLimitError として返ります。

集計なので、上限に達したら部分結果ではなくエラーです（第 0 回の fail-closed）。
同じ結果を返す 2 つの SQL のうち、片方は 10 件の上限でも動き、片方は 100 件で止まる。FROM に書く順は、kSQL では性能ではなく成否の問題です。

最大取得件数 100 で顧客を FROM にした SQL を実行したときのエラー表示。取得件数が上限（100 件）を超えました

基本方針は、WHERE で絞れる側、件数の少ない側を FROM に書くことです。結合キーはそちらから JOIN 先へ流れます。
そのうえで EXPLAIN の join key prefilter 行を確認してください。後述のとおり、キーの空値・キー数・JOIN 先のフィールド型によっては全件取得へ戻ります。

LEFT JOIN — 案件の無い顧客も出す

「案件が 1 件も無い顧客」を含めてランク別に数えるなら、顧客を起点にした LEFT JOIN です。

SELECT a.顧客ランク, COUNT(b.案件No_) AS 案件数, SUM(b.売上) AS 売上合計
FROM APP4148 AS a
LEFT JOIN APP4149 AS b ON a.顧客No = b.顧客No_
GROUP BY a.顧客ランク
ORDER BY a.顧客ランク

ここで EXPLAIN を見ると、押し下げが 2 種とも消えます。

fetch summary: ALL
  ...
  join pushdown plan: not applied (join key/WHERE prefilters are reported per source below)
  join pushdown not applied: OUTER_JOIN
  app:           APP4148 AS a (4148)
  kintone query: (全件取得)
  fetch:         ALL
  LEFT JOIN:        APP4149 AS b (4149)
  kintone query: (全件取得)
  fetch:         ALL

v3.77.0 では、複数 JOIN を含む場合も外部結合の空行（相手なし）の意味を確実に保つため、LEFT / RIGHT JOIN を WHERE 述語の押し下げと結合キー最適化の対象外にしています。これは実装上の契約で、原理的に不可能という意味ではありません。
保持されない側（LEFT なら右）の入力が取得上限などで欠けると、本当は相手がいる行を「相手なし」と判定して空行を作ってしまうからです。
そのため LEFT JOIN は両方のアプリを丸ごと取りに行き、上限に掛かりやすいです。上限 100 件では、この SQL も止まります。

しかも、保持側（顧客）に WHERE を付けても押し下がりません。WHERE a.顧客ランク IN ('A', 'B') を足して EXPLAIN しても、両方 ALL のままです（実測）。述語の押し下げは INNER / CROSS JOIN 限定です。

対処は 3 つです。

  • 2 文に分ける。 先に顧客を一時テーブルへ絞って実体化し（この SELECT は単独なので WHERE が押し下がる）、それを LEFT JOIN の起点にする

    CREATE TEMP TABLE #cust AS
SELECT 顧客No, 顧客ランク FROM APP4148 WHERE 顧客ランク IN ('A', 'B');

SELECT c.顧客ランク, COUNT(b.案件No_) AS 案件数, SUM(b.売上) AS 売上合計
FROM #cust AS c
LEFT JOIN APP4149 AS b ON c.顧客No = b.顧客No_
GROUP BY c.顧客ランク
ORDER BY c.顧客ランク

    EXPLAIN では 1 文目が fetch: EXACT（顧客ランク in ("A","B")）になります。プラグインは ; 区切りのバッチをそのまま実行でき、表示されるのは最後の SELECT の結果です。
    この方法で減るのは顧客側の取得量だけです。2 文目の案件側は LEFT JOIN の結合相手なので、引き続き全件取得です（上限 10 件で実行すると 2 文目が FetchAllLimitError になることを確認しました）。今回の案件は 20 件なので上限 100 件で通りますが、案件側も大きい場合はこの方法だけでは解決しません

  • 上限を上げる（顧客が数千件までなら現実的）

  • 「案件はあるが受注が無い」までで足りるなら、前述の集計内 CASE を INNER JOIN で使う

LEFT JOIN の COUNT(*) は空行も数える

案件が無い顧客は、LEFT JOIN の結果では「顧客の行 1 つ ＋ 案件側の列が空文字」になります。
COUNT(*) はこの行を 1 と数えます。案件数を数えるなら COUNT(b.案件No_) のように案件側の列を数えてください。

SELECT a.会社名, COUNT(*) AS count_star, COUNT(b.案件No_) AS count_col
FROM APP4148 AS a
LEFT JOIN APP4149 AS b ON a.顧客No = b.顧客No_
GROUP BY a.会社名
HAVING COUNT(b.案件No_) = 0
ORDER BY a.会社名
LIMIT 2
会社名 count_star count_col
サンプル株式会社 第100支店 1 0
サンプル株式会社 第101支店 1 0

上の表は、顧客を 215 件に増やした手元環境での結果です。素の SFA パックでは該当件数や会社名が異なります。
HAVING COUNT(b.案件No_) = 0 が「案件の無い顧客」の抽出条件になります。HAVING は集計後の絞り込みで、ここは標準 SQL と同じです。

落とし穴

  • FROM の順で取得量が変わる。 結合キーの絞り込みは FROM 側から JOIN 先へ。絞れる側を FROM に。JOIN 先に押し下がる WHERE があると結合キーの絞り込みは試されない（2 節。JOIN 先はその条件で独立に取得され、FROM 側は全件になる）

  • LEFT / RIGHT JOIN では押し下げが 2 種とも効かない。 join pushdown not applied: OUTER_JOIN。保持側に WHERE を付けても全件取得のまま（実測）。絞りたければ一時テーブルに実体化してから結合する

  • 今回の 顧客No_ → 顧客No の結合では、FROM 側のキーに空値が混ざると JOIN 先の全件取得へ戻る。 JOIN 先がレコード番号のように in ("") を受けない型だと、ルックアップ未設定の案件（下書きやゴミデータ）が 1 件あるだけで起きます。JOIN 先がテキスト・数値・選択系なら空値は in に載るので起きません（言語リファレンス §7 の「結合キーの空値」の項）。
    JOIN の WHERE に b.顧客No_ != '' を足しても守れません。 JOIN 文脈ではこの条件は kintone クエリに載らず（join pushdown not applied: UNSAFE_RELATION）、結合キーは取得直後の行から集めるためです。守るなら、案件を先に一時テーブルへ実体化します。単独の SELECT なら 顧客No_ != '' は押し下がるので、空値の行はキーに入りません

    CREATE TEMP TABLE #deals AS
SELECT 顧客No_, 売上 FROM APP4149 WHERE 商談フェーズ IN ('受注') AND 顧客No_ != '';

SELECT a.顧客ランク, SUM(d.売上) AS 受注額
FROM #deals AS d
INNER JOIN APP4148 AS a ON d.顧客No_ = a.顧客No
GROUP BY a.顧客ランク
ORDER BY a.顧客ランク

    実体化済みの一時テーブルから物理アプリへの INNER JOIN でも結合キーの絞り込みは効きます。上限 10 件で実行し、案件 8 行の実体化と顧客の絞り込み取得が通ることを確認しました

  • in 方式は重複を除いたキー 300 件まで。 50 キーずつ最大 6 リクエストで取り、301 件以上では最適化をスキップして JOIN 先を全件取得します。そのとき警告が出ます: JOINキーが N 件のため ON 最適化をスキップし、JOIN先を全件取得します（上限 300 件）。FROM 側の件数が数百を超える JOIN では、WHERE でさらに絞るか、上限を上げる前提で設計する

  • 結合キーの型で方式が変わる。 テキスト・数値・選択系は in (...)、日付・日時は in を受けないので最小値〜最大値の範囲取得（広めに取って JOIN 後に照合）

  • COUNT(*) は LEFT JOIN の空行を数える。 相手側の列を COUNT する

  • ON に 2 本目の条件は書けない。 「顧客 No かつ年度」のような複合キーは、CTE か一時テーブルで連結キー列を作ってから結ぶ（第 0 回の早見表）

  • SUM は倍精度。 売上のような整数の合計は問題になりませんが、小数の按分を JOIN 集計で行うと表現誤差が出ます（第 0 回）

運用に載せる

プラグインの専用アプリでは、実行した SQL をレコードとして保存し、履歴から再実行できます。毎週、手動で実行して共有する運用なら、それで足ります。

一覧画面に貼っておきたいなら、無償版の kSQL Dashboard プラグイン の棒グラフにします。棒は「ラベル列 + 値列」の 2 列を返せばよく、ペインは ; 区切りのバッチも実行できます（描画されるのは最後に行を返した文）。
落とし穴で示した 2 文の形をそのまま置きます。

CREATE TEMP TABLE #deals AS
SELECT 顧客No_, 売上 FROM APP4149 WHERE 商談フェーズ IN ('受注') AND 顧客No_ != '';

SELECT a.顧客ランク, SUM(d.売上) AS 受注額
FROM #deals AS d
INNER JOIN APP4148 AS a ON d.顧客No_ = a.顧客No
GROUP BY a.顧客ランク
ORDER BY a.顧客ランク

案件を先に絞って実体化し、そのキーで顧客を取ります。ルックアップ未設定の案件が混ざっても最適化が外れません。
ダッシュボードは一覧を開くたびに実行されるので、取得量の少ない形で置くのが作法です。
プラグインで同じバッチを実行すると、顧客ランクと受注額の 2 列・2 行（A と B）の表になります（上の図）。Dashboard の棒グラフは、この 2 列をそのままラベルと値にして描いたものです（下の図）。

2 文のバッチをプラグインで実行した結果。顧客ランク A 22,800,000、B 18,000,000 の 2 行

同じバッチを置いた Dashboard の棒グラフ「顧客ランク別グラフ」。A 22,800,000、B 18,000,000

Claude に頼むなら（MCP 導入済みの読者向け・任意）

kSQL MCP は第 9 回で導入します。導入済みなら、次の依頼文でこの回の内容を再現できます。

kSQL MCP で、APP4148（顧客管理）と APP4149（案件管理）を顧客No / 顧客No_ で結合し、
商談フェーズが受注の案件について顧客ランク別の件数と売上合計を出す SQL を書いてください。
ksql_explain で、顧客側に fetch: PREFILTERED（未確定）と join key prefilter: runtime candidate が表示される向き（FROM に書く側）を選び、
その理由を説明してください。実行はしないでください。

次回は第 2 回「期間で集計する」です。月別の推移と前月比を出し、商談の無い月を GENERATE_SERIES で 0 として並べます。

リポジトリ・ドキュメント:

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?