今更SQL 連射SQLをワンクエリに！バルククエリ（MultiRowクエリ）の書き方

複数レコードのデータをリクエストで受け取って、DBにアクセス（CRUD）する機能ってありますよね。こんなとき、1レコードずつ処理するSQL（以下、連射SQL）ではなく、まとめて1クエリで処理したいと思いませんか。

今日は**複数レコードのデータを一つのSQLで処理する「MultiRowクエリ（複数行クエリ）」**の書き方をご紹介します。

---
**宣伝**
最近、[**GolangでMultiRowクエリを簡単に導入できるライブラリ**](https://qiita.com/bubu_suke/items/7c04c924bf194994d6b7) を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表**] (https://style.potepan.com/articles/21818.html)というものを使う必要があります。 
    * INSERT ON CONFLICT構文（UPSERT構文）も製品によってはMERGEを使うなど、方言が含まれています。
    * 今回のメインとなる、VALUES句のところやUNION ALLでSELECTするところは製品に依らないため、そこに着目して頂ければ。


## 今回使うテーブル定義

```sql
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
テーブル更新に必要な情報を全てパラメータとして渡せるときに使える書き方です。

```sql
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で条件指定する。
という考え方で作成します。

```sql
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処理にすると上記のクエリができます。

```sql
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	

```

以上です。
こじらせている感は否めないので、性能改善の案の一つとして考えて頂ければ。
性能問題の解消に役立つと嬉しいです。