rex0220 kSQL アクションプラグイン - ③ 集計編（顧客別集計とテーブルへの書き戻し）

「rex0220 kSQL アクションプラグイン」の実践編です。

kSQL（Flow 形式 dialect 1）の書き方と、関連レコード集計のような「集計先の 1 行と明細をまとめて書き直す」形をまとめます。明細は別アプリにも、親レコードのテーブルにも書けます。

シリーズ: 概要 / ① 詳細画面 / ② 一覧 / ③ 集計（この記事） / ④ 運用 / ⑤ 事例 / ⑥ イベント / ⑦ オーサリング / ⑧ 請求データ / ⑨ 締めの画面 / ⑩ どこに出すか / ⑪ 多言語 / ⑫ オーサリング改善

SQL の書き方

kSQL の Flow 形式（dialect 1）で書きます。1 本のアクションに最大 20 文、各文は ; で区切ります。

例: 顧客コードを受け取り、その顧客の受注を集計アプリへ反映するアクションです。

1. 指定した顧客コードの受注レコードを取得する
2. 対象があることを確かめる（無ければメッセージを出して終わり）
3. 売上合計と件数を集計アプリへ UPSERT する（あれば更新、無ければ追加）

-- @ksql dialect: 1
DECLARE @code = '';   -- パラメーター（顧客コード）。実行時に値が入り、無ければこの既定値
CREATE TEMP TABLE t AS
SELECT 顧客コード, 金額 FROM LAPP_受注 WHERE 顧客コード = @code;
ASSERT (SELECT COUNT(*) FROM t) >= 1, '対象のレコードがありません';
UPSERT INTO LAPP_集計 (顧客コード, 売上, 件数, 集計日時)
SELECT @code AS 顧客コード, SUM(金額) AS 売上, COUNT(*) AS 件数, @NOW() AS 集計日時 FROM t
ON DUPLICATE (顧客コード);

• アプリは LAPP_<論理アプリ名> で書き、プラグイン設定の対応表でアプリ ID に解決します。設定を他の環境に移しても SQL はそのまま使えます
• 使える文: DECLARE / SET / CREATE TEMP TABLE / DROP TEMP TABLE / ASSERT / EXIT / INSERT / UPDATE / DELETE / UPSERT / REORDER（トップレベルの SELECT、IMPORT などは使えません）
• @NOW() / @TODAY() は実行の開始時に 1 回決まる基準時刻です（一括処理でも全レコードで同じ値）
• INSERT に注入値を入れるときは INSERT ... SELECT @x AS 列名 ... の形で書きます（VALUES の中には置けません）
• 実行は押した人の権限（セッション認証）で行います。押した人が読めない・書けないレコードには届きません

集計と明細をまとめて書き直す

関連レコード集計のような使い方では、集計先の 1 行（親）と、担当者別などの明細を一度に書き直すことが多いです。この形は 4 文で書けます（@cust は実行時に渡す顧客コードのパラメーターです）。

-- @ksql dialect: 1
DECLARE @cust = '';

-- 集計先（親）。キーは書き込み先アプリで「値の重複を禁止する」に
UPSERT INTO LAPP_集計 (顧客コード, 件数, 売上, 集計日時)
SELECT @cust AS 顧客コード, COUNT(*) AS 件数, SUM(金額) AS 売上, @NOW() AS 集計日時
  FROM LAPP_受注 WHERE 顧客コード = @cust
ON DUPLICATE (顧客コード);

-- 明細（別アプリ）を全置換
DELETE FROM LAPP_集計明細 WHERE 顧客コード = @cust;
INSERT INTO LAPP_集計明細 (顧客コード, 担当者, 件数, 金額)
SELECT @cust AS 顧客コード, 担当者, COUNT(*), SUM(金額) FROM LAPP_受注
 WHERE 顧客コード = @cust GROUP BY 担当者;

ここでの勘どころは 親の UPSERT … SELECT に GROUP BY を使わないことです。キーを @cust AS 顧客コード、ほかを集計関数で書くと、集計元が 0 件でも 1 行が書かれます（COUNT(*) は 0、SUM(金額) も空集合で NULL にならず 0 になります）。GROUP BY 顧客コード で書くと、0 件のときは 1 行も返らないので集計先は古い値のまま残ります。

つまり「その顧客で実行すれば、受注が 0 件になっていても集計先が 0 に直る」ということです。SQL が自動で「消えた顧客」を見つけてくれるわけではないので、変えた・消したキーの側でも実行する必要があります。イベントで動かすときは、次の「キーの配列」で新旧をまとめて渡せます。件数を見て処理を分ける必要はありません。

並び順を付けたいときは、SUM(金額) AS 金額 のように別名を付けて ORDER BY 金額 DESC と書きます。ORDER BY SUM(金額) DESC のように集計式そのものを並べ替えのキーにすることはできません。

明細は DELETE → INSERT … SELECT の全置換なので、最後まで通れば何度流しても同じ結果になります。ただしトランザクションではないので、DELETE のあとで INSERT が失敗すると、明細が消えたままになります（原因を取り除いてもう一度実行すれば、明細を作り直せます）。

全置換は、押すたびに明細の行を作り直すので、レコード番号（ID）をどんどん使います。 イベントで何度も動かすなら、後述の「キーを変えたとき・消したときも 1 回で」の形（UPSERT で上書きする「0 更新」）にしてください。この節の形は、まず動かして仕組みを確かめるときの最小形です。

明細を親レコードのサブテーブルに持たせる構成なら、次の「テーブルに書き戻す」形で親と明細を 1 回の書き込みにまとめられます（サブテーブルの行はレコード番号を使いません）。集計先の詳細画面に関連レコード一覧を置けば、明細アプリでもテーブルと同じ見え方になります。

キーを変えたとき・消したときも 1 回で（キーの配列）

顧客コードのようなキーの項目を直すと、集計先が変わります。新しい顧客は直っても、前の顧客が古いまま残るのが困りどころです。

そこでパラメーターの源泉を「キーの配列」にします。式を 1 つ（例: 顧客コード）書いておくと、プラグインがその起動で関係するレコードすべてに式を当て、重複と空を落とした配列で渡します。

起動	配列の中身
ボタン（詳細画面）・一覧の 1 件ずつ	[そのレコード]
一覧の「まとめて 1 回」「グループごと」	選んだレコード全部
保存後（追加）	[新しい値]
保存後（編集）	[変更後, 変更前]（同じなら 1 つに縮みます）
削除したあと	[消したレコードの値]

SQL は 1 本で済みます（起動の種類で書き分けなくてよい）。配列は DECLARE @keys = []; で受け、IN @keys で使います。

受注が 0 件になったキー（最後の 1 件を動かした・消した顧客）も直すため、今の集計の行を「0 の行」として UNION ALL で足してから集計します（⑤ 事例編と同じ形）。

処理は「集める → 集計する → 書き込む」の 3 つに分けます。1 文にまとめても動きますが、分けておくと読む人が追いやすく、あとで実行計画を見るときも 1 文ずつ確かめられます。

-- @ksql dialect: 1
DECLARE @keys = [];   -- パラメーター（源泉「キーの配列」。式は 顧客コード）

-- 1. 集める。受注の 1 行を「件数 1」として並べ、今の集計を「0 の行」として足す
CREATE TEMP TABLE #raw AS
SELECT 顧客コード, 1 AS 件数, 金額 AS 売上
  FROM LAPP_受注 WHERE 顧客コード IN @keys
UNION ALL
SELECT 顧客コード, 0 AS 件数, 0 AS 売上
  FROM LAPP_集計 WHERE 顧客コード IN @keys;

-- 2. 集計する。顧客コードごとに足すだけ（kintone には行きません）
CREATE TEMP TABLE #agg AS
SELECT 顧客コード, SUM(件数) AS 件数, SUM(売上) AS 売上
  FROM #raw GROUP BY 顧客コード;

-- 3. 書き込む。受注が 0 件になったキーは「0 の行」だけが残るので 0 になる
UPSERT INTO LAPP_集計 (顧客コード, 件数, 売上, 集計日時)
SELECT 顧客コード, 件数, 売上, @NOW() AS 集計日時
  FROM #agg
ON DUPLICATE (顧客コード);

-- 4. 集める（担当者別の明細も同じ形）。キーは 顧客コード と 担当者 をつないで作る
CREATE TEMP TABLE #draw AS
SELECT CONCAT(顧客コード, '-', 担当者) AS 明細キー, 顧客コード, 担当者, 1 AS 件数, 金額
  FROM LAPP_受注 WHERE 顧客コード IN @keys
UNION ALL
SELECT 明細キー, 顧客コード, 担当者, 0 AS 件数, 0 AS 金額
  FROM LAPP_集計明細 WHERE 顧客コード IN @keys;

-- 5. 集計する
CREATE TEMP TABLE #dagg AS
SELECT 明細キー, 顧客コード, 担当者, SUM(件数) AS 件数, SUM(金額) AS 金額
  FROM #draw GROUP BY 明細キー, 顧客コード, 担当者;

-- 6. 書き込む（既にある行は更新されるので、レコード番号は変わりません）
UPSERT INTO LAPP_集計明細 (明細キー, 顧客コード, 担当者, 件数, 金額)
SELECT 明細キー, 顧客コード, 担当者, 件数, 金額
  FROM #dagg
ON DUPLICATE (明細キー);

-- 7. 0 件になった行だけを消す（集計先と明細の両方。消すのは集計のあと）
DELETE FROM LAPP_集計 WHERE 顧客コード IN @keys AND 件数 = 0;
DELETE FROM LAPP_集計明細 WHERE 顧客コード IN @keys AND 件数 = 0;

明細も「消してから入れ直す」のではなく、集計先と同じ「0 更新」にしています。 DELETE → INSERT だと押すたびに明細の全行が作り直され、レコード番号（ID）をむやみに使います。UPSERT なら既にある行はそのまま更新されるので、ID も行ごとの更新履歴も残ります。

実機で確かめた動きです（受注 22 件のアプリ、C003 は 3 人の担当者で 6 件）。

操作	結果
そのまま押し直す	追加 0 / 更新 4 / 削除 0。明細のレコード番号は変わらない（全置換なら毎回 追加 3 / 削除 3）
担当者を 鈴木 → 田中 に変えて保存	鈴木の行は同じレコード番号のまま 3 → 2 件に減り、田中の行が1 つだけ増える
田中 → 鈴木 に戻して保存	田中の行が 0 件になり、7 の DELETE でその行だけ消える

API 回数と所要は全置換と同じ（13 回・2 秒）でした。UPSERT の照合の先読みと、DELETE が減った分が相殺します。

書き方の勘どころ:

• 1 文に 1 つの役割を持たせています。1 が「集める」、2 が「集計する」、3 が「書き込む」です。SQL に慣れていない人でも上から順に読めますし、設定画面の［実行計画］も 1 文ずつ対応します（1 の文に絞り込みと読む項目、2 の文に group key 顧客コード: PHYSICAL、3 の文に書き込みの回数が出ます）

• 集計を独立した文にしておくと、集計キーを実行計画で確かめられます。UPSERT … SELECT に GROUP BY を書くと、そのキーは実行計画に出ません

• 先に UPDATE … SET 件数 = 0 で埋める形より安全です。0 にしてから集計する形は、0 にしたあとで失敗すると 0 のまま残ります。UNION ALL の形は消す文も 0 にする文も無いので、途中で失敗しても前の値のままです。書き込みの件数も半分で済みます

• 消すなら集計のあとです（件数 = 0 の行だけ）。先に消すと、消したあとで失敗したときに行ごと無くなります

• SUM(件数) でまとめます。#raw を COUNT(*) で数えると 0 の行まで数えてしまいます

• #raw には受注の明細がそのまま入ります。一時テーブルの上限はプラグインの共通設定にあり、1 表 10,000 行・全表の合計 20,000 行が既定です。超えるとエラーになります（黙って切り捨てることはありません）。数えるのは受注の取得件数と、UNION ALL で足す集計側の行数の合計です。何千件もある顧客をまとめて直すときは、1 の受注側を COUNT(*) AS 件数, SUM(金額) AS 売上 … GROUP BY 顧客コード にして先に集計してください

• @NOW() は SELECT の列に書けます。実行の開始時に 1 回決まるので、何件書いても同じ値になります。ただし DECLARE で受けたパラメーターは VALUES の中には置けないので、INSERT … SELECT @x AS 列名 … の形で渡します

• 変更前のキーは、詳細・編集画面を開いたときに取っておく控えから拾います（そのタブを閉じるまで残ります）。控えが無いときは変更後のキーだけで動きます。

• DELETE … WHERE キー NOT IN (SELECT …) は使えません。SQL としては書けますが（検査は通ります）、今の kSQL はこれを kintone のクエリに変換しません。書ける形と、変換できる形は別です。IN @keys や 件数 = 0 のような項目と値の比較なら変換できます。

明細アプリに「明細キー」を用意する

明細は「顧客 × 担当者」で 1 行ですが、UPSERT のキーに指定できるのは 1 項目だけです（ON DUPLICATE (顧客コード, 担当者) のような複合キーは使えません）。そこでつないだ値を入れる項目を明細アプリに 1 つ作ります。

項目	設定
明細キー	文字列（1 行）。「値の重複を禁止する」にチェック

値は SQL 側で作るので、入力欄は画面から隠して構いません。CONCAT(顧客コード, '-', 担当者) のほか、顧客コード || '-' || 担当者 とも書けます。

すでに明細のレコードがあるアプリでは、一度だけキーを埋める作業が要ります。 空のままだと UPSERT が既存の行に当たらず、同じ内容の行が二重に増えます。CSV の書き出し・読み込みで埋めるか、埋めるだけのアクションを 1 回実行してください。

レコードを消したとき

起動に「削除したあと」を選ぶと、レコードを消した後に実行できます。消す直前に控えを取り、削除が成功してからその控えのキーで集計し直すので、上の SQL がそのまま使えます（レコードはもう無いので、除外の指定は要りません）。

ただし拾えるのは画面から消したときだけです（REST API や CSV の削除では動きません）。できれば削除せず、「削除フラグ」の項目を作って集計から外すことをおすすめします。

-- 削除フラグで外す（フラグを立てるのは編集なので、保存後のイベントで拾えます）
-- 直すのは上の SQL の 1「集める」だけ。2 と 3 はそのままです
CREATE TEMP TABLE #raw AS
SELECT 顧客コード, 1 AS 件数, 金額 AS 売上
  FROM LAPP_受注 WHERE 顧客コード IN @keys AND 削除フラグ <> '削除'
UNION ALL
SELECT 顧客コード, 0 AS 件数, 0 AS 売上
  FROM LAPP_集計 WHERE 顧客コード IN @keys;

「0 の行」はそのまま残します。最後の 1 件にフラグを立てると受注側が 1 行も返らなくなるので、0 の行が無いと集計先が古い値のまま残ります。

履歴が残り、誤操作も戻せます。

明細を親レコードのテーブルに書き戻す

明細を別アプリにせず、集計先レコードのテーブル（サブテーブル）に直接書き戻すこともできます。UPDATE に APPLY を付けた形です。

UPDATE LAPP_集計 SET 集計日時 = @NOW()
 WHERE 顧客コード = @cust
  APPLY 明細 (
    REMOVE ALL ROWS;
    APPEND (担当者, 明細件数, 明細金額)
    SELECT 担当者, COUNT(*) AS 件数, SUM(金額) AS 金額 FROM LAPP_受注
     WHERE 顧客コード = @cust GROUP BY 担当者 ORDER BY 金額 DESC
  );

この形には決まりが 4 つあります。

• WHERE は対象が 1 件に決まる条件にします（キーの等値など）。2 件以上に当たると、書き込む前にエラーで止まります
• REMOVE ALL ROWS と APPEND … SELECT の組み合わせだけです（テーブルの全置換）。行を選んで直す書き方はできません
• テーブルの行の並びは ORDER BY の順になります
• APPEND に並べるのはテーブルの子項目の項目コードで、SELECT の列と並び順で対応します。上の例で 件数 / 金額 ではなく 明細件数 / 明細金額 にしているのは、kintone ではテーブルの子項目に、同じアプリの他の項目と同じ項目コードを付けられないためです（画面に出るラベルは「件数」「金額」のままにできます）

全置換なので、何度実行しても結果は同じです。実行の前の確認では、親の件数と一緒にテーブルの追加行数・消える行数も出ます（元の行は戻せないため）。

この SQL を設定画面に入れて「SQL を検査」したところです。

関連記事

• rex0220 kSQL アクションプラグイン
• ① 詳細画面編（ボタンとイベント）
• ② 一覧編（選択したレコードをまとめて処理する）
• ③ 集計編（顧客別集計とテーブルへの書き戻し）（この記事）
• ④ 運用編（ログアプリ・二重実行の防止・権限）
• ⑤ 事例編（10 万件を集計して Dashboard Pro で見る）
• ⑥ イベント編（保存・ステータス変更・削除で自動実行する）
• ⑦ オーサリング編（設定を AI に作ってもらう）
• ⑧ 請求データ編（全体再計算で整合性を保つ）
• ⑨ 締めの画面編（押し忘れに気づくダッシュボード）
• ⑩ 画面のどこに出すか（ボタン・メニュー・パネルの使い分け）
• ⑪ 日本語・英語・中国語で使う（共通の文と多言語）
• ⑫ オーサリング改善編（AI に使わせて見つけた穴）
• 事例集（やりたいことから探す）