複数レコードのデータをリクエストで受け取って、DBにアクセス（CRUD）する機能ってありますよね。こんなとき、1レコードずつ処理するSQL（以下、連射SQL）ではなく、まとめて1クエリで処理したいと思いませんか。
今日は複数レコードのデータを一つのSQLで処理する「MultiRowクエリ（複数行クエリ）」の書き方をご紹介します。
宣伝
最近、GolangでMultiRowクエリを簡単に導入できるライブラリ をGit公開しました。
本記事を読んで、GolangでMultiRowクエリを使いたいなって思った方、是非ご利用ください！！！
連射SQLとMultiRowクエリの比較
本記事では連射SQLやMultiRowクエリの是非について言及しません。それぞれ長所短所があるので、どちらを使うかはご判断ください。
連射SQL
- 処理がシンプルでわかりやすいです。
- 処理レコード数による処理時間変動が顕著です（DBとのNW通信周りが速度のボトルネックになるため）。速度イメージ：1件 10 mSec → 1000件 10 sec。
- レコード数が増えても全て処理できます（時間がかかるが）。
MultiRowクエリ（特にMultiRow-SELECT-INSERT）
- バインド変数の準備もSQLも、処理をこねくり回すので連射SQLよりもコードが複雑になります。
- 連射SQLに比べると、レコード数に応じた処理時間増加は緩やかです。速度イメージ：1件 10 msec → 1000件 100 msecオーダー。
- 1回に処理できるバインド変数に上限があるため(製品によって違うが大体3万ぐらい)、レコード数が増えるとエラーが出ます。上限を超えそうな場合は1クエリで処理するレコードを区切って、複数回クエリを実行する必要があります（これもコードを複雑にする要因）。
但し書き
- 今回はPostgreSQLの構文で記載しました。RDBMSの製品によっては若干記載方法を変える必要があります。
- 例えばORACLEだとFROM句が省略できないので DUAL表というものを使う必要があります。
- INSERT ON CONFLICT構文（UPSERT構文）も製品によってはMERGEを使うなど、方言が含まれています。
- 今回のメインとなる、VALUES句のところやUNION ALLでSELECTするところは製品に依らないため、そこに着目して頂ければ。
今回使うテーブル定義
CREATE TABLE item_master (
item_id NUMERIC(25) PRIMARY KEY --商品マスタのID
, item_name VARCHAR(10) --商品名
, price NUMERIC(10) --価格
);
CREATE TABLE sales_trun (
sales_id NUMERIC(25) PRIMARY KEY --売上トランのID
, sales_date DATE --売った日
, quantity NUMERIC(2) --商品数量
, actual_amount NUMERIC(10) --実際に売った金額
, item_id NUMERIC(25) --商品マスタのID
, standard_price NUMERIC(10) --売った日の商品1個あたりの標準価格
);
書き方
それでは本題に。
一般的なMultiRow-INSERT/UPSERT
テーブル更新に必要な情報を全てパラメータとして渡せるときに使える書き方です。
INSERT INTO item_master (item_id, item_name, price ) VALUES
('1', 'foo', 10 )
, ('2', 'bar', 20 )
, ('3', 'hoge', 30 )
/*UPDATEもしたいときはここから下を加える*/
ON CONFLICT (item_id) DO
UPDATE SET
item_id = EXCLUDED.item_id
, item_name = EXCLUDED.item_name
, price = EXCLUDED.price
;
MultiRow-SELECT-INSERT/UPDATE
パラメータだけではテーブル更新に必要な情報が揃わないため、他のテーブルの結合が必要なときの書き方です。
今回はsales_trunのstarndard_price項目には、データ挿入時のitem_masterのprice項目の値をセットする必要があるというケースでクエリを書きました。
* UNION ALL でSELECTをつなげたMultiRow-SELECTを準備する。
* その後、SELECT結果に対してJOINしたり、WHEREで条件指定する。
という考え方で作成します。
INSERT INTO sales_trun
( sales_id
, sales_date
, quantity
, actual_amount
, item_id
, standard_price
)
SELECT
request.sales_id
, request.sales_date
, request.quantity
, request.actual_amount
, request.item_id
, item.price
FROM
( /*型とカラム名を指定するためのダミー行。後で除外するためにNOT NULL制約の項目にNULLを設定しておく。*/
SELECT
CAST(NULL AS NUMERIC(25)) AS sales_id
, CAST(NULL AS DATE) AS sales_date
, CAST(NULL AS NUMERIC(4)) AS quantity
, CAST(NULL AS NUMERIC(10)) AS actual_amount
, CAST(NULL AS NUMERIC(25)) AS item_id
/*実際に登録したいデータをUNION ALLでつなげる*/
UNION ALL SELECT 100, CURRENT_DATE, 2, 30, 1
UNION ALL SELECT 200, CURRENT_DATE, 4, 70, 2
UNION ALL SELECT 300, '2020-08-20', 6, 200, 3
) request
INNER JOIN
item_master item
ON
item.item_id = request.item_id
WHERE
request.sales_id IS NOT NULL /*ダミー行を除去*/
/*このEXISTS述語で既存のデータに絞るとMultiRowUPDATEの処理になる（INSERTデータは無くなる）。
AND EXISTS(
SELECT 'X' FROM sales_trun st
WHERE st.sales_id = request.sales_id
)
*/
/*UPDATEやUPSERTのときはここから下を加える。*/
ON CONFLICT (sales_id) DO
UPDATE SET
sales_date = EXCLUDED.sales_date
, quantity = EXCLUDED.quantity
, actual_amount = EXCLUDED.actual_amount
, item_id = EXCLUDED.item_id
, standard_price = EXCLUDED.standard_price
;
MultiRow-SELECT
上記のクエリから UNION ALLでSELECTをつなげたMultiRow-SELECT 部分をピックアップしました。
これにテーブルをJOINしてINSERT処理にすると上記のクエリができます。
SELECT
*
FROM
( /* 型とカラム名を指定するためのダミー行。後で除外するためにNOT NULL制約の項目にNULLを設定しておく*/
SELECT
CAST(NULL AS NUMERIC(25)) AS sales_id
, CAST(NULL AS DATE) AS sales_date
, CAST(NULL AS NUMERIC(4)) AS quantity
, CAST(NULL AS NUMERIC(10)) AS actual_amount
, CAST(NULL AS NUMERIC(25)) AS item_id
/*実際のデータをUNION ALLでつなげる*/
UNION ALL SELECT 100, CURRENT_DATE, 2, 30, 1
UNION ALL SELECT 200, CURRENT_DATE, 4, 70, 2
UNION ALL SELECT 300, '2020-08-20', 6, 200, 3
) request
WHERE
/*ダミー行を除去*/
request.sales_id IS NOT NULL
以上です。
こじらせている感は否めないので、性能改善の案の一つとして考えて頂ければ。
性能問題の解消に役立つと嬉しいです。