タグ: exists

先日投稿した記事のテーブルを使って、商品別売上実績のレコードが必ず存在するようにしてみます。

月末処理で売上平均金額を求める前に売上のない商品について0円の商品別売上実績レコードを作成する状況を想定しています。

ここではSELECTした結果をINSERTするINSERT SELECTを使ってデータを登録します。

INSERT SELECTとは

INSERT SELECTとは、INSERTするデータをSELECTで作成する手法のことです。例えば、テーブルAにテーブルBから抽出したレコードをそのままINSERTする場合、以下のように書きます。

INSERT INTO A
SELECT
  *
FROM
  B

この例では、テーブルAとテーブルBは同じカラムを持っており、テーブルBから全件抽出してそのままテーブルAにINSERTしています。このやり方はバックアップ対象と同じカラムを持つバックアップ用のテーブルを作成して、そこにテックアップ対象のレコードを全件抽出しておく、というときによく使います。

SELECT時に対象のカラムと行を絞り込む

今回実現したいのは、SELECTするテーブルとINSERTするテーブルのカラムは異なっています。これはSELECTするカラムをINSERTするカラムに合わせることで対応します。

もう一つ、すべてのカラムを抽出するのではなく、すでにINSERTするテーブルに存在している商品コードはINSERTする必要がない（INSERTすると一意制約違反となる）ため、商品テーブルにあって商品別売上実績テーブルにない商品コードだけINSERTするように対象レコードを絞り込みます。

作成したSQLは以下のようになります。

INSERT INTO sales_by_product
SELECT
    T1.product_code,
    0 total_sales_amount
FROM
    products T1
WHERE
    NOT EXISTS (
        SELECT
            1
        FROM
            sales_by_product T2
        WHERE
            T1.product_code = T2.product_code
    );

INSERTするテーブルのカラムと同じカラムを抽出する

SELECTではINSERTするテーブルと同じカラムを抽出します。

SELECT
    T1.product_code,
    0 total_sales_amount
FROM
    products T1

INSERT先の商品別売上実績テーブルはproduct_codeとtotal_sales_amountを持っているため、これらのカラムだけになるように抽出しています。product_codeはproductsテーブルのカラムを使用し、total_sales_amountは今回やりたいことに合わせてデフォルト値の0を設定するようにしています。0 total_sales_amountのように固定値を直接記述することができるかどうかは使用しているRDBMSによって異なりますので注意してください。

INSERTするテーブルに存在していないレコードのみ抽出する

少しややこしいですが、NOT EXISTSを使ってsales_by_productに存在しない商品コードのみ抽出するようにします。

FROM
    products T1
WHERE
    NOT EXISTS (
        SELECT
            1
        FROM
            sales_by_product T2
        WHERE
            T1.product_code = T2.product_code
    );

SELECTしているproducts T1とNOT EXISTS内のSELECT文のsales_by_product T2をproduct_codeで結合することで、T1、T2双方に存在するsales_by_productが抽出できます。ということは、T1はNOT EXISTS以外に絞り込み条件を持たないので、すべてのT1のレコード（すべての商品）について、それぞれのレコードに対応するT2（商品別売上実績）を抽出してみて、それが存在しなかったら（NOT EXISTS）、SELECTされるということになります。

NOT EXISTS内のSELECTしている列が1となっていますが、これは行が抽出できていること自体にしか意味がないので、無駄なデータを抽出しないために適当な1という値を指定しているだけです。'x'とか3とかでも問題ありませんし、データ量のことを気にしないのであれば*でも構いません。

INSERTする前にSELECTだけを実行してみる

実際にINSERTをする前にINSERT部分以外のSELECT文単独で実行結果を確認しておきます。実務においてもまずはINSERTしたい形と同じかをSELECTだけで確認し、それが確認できたらINSERTするのが安全です。

SELECT
    T1.product_code,
    0 total_sales_amount
FROM
    products T1
WHERE
    NOT EXISTS (
        SELECT
            1
        FROM
            sales_by_product T2
        WHERE
            T1.product_code = T2.product_code
    );

まずは、INSERT前の商品別売上実績テーブルを確認してみます。

SELECT * FROM sales_by_product;

=>
S001,50
S003,250
S004,350
S006,450

商品コードS002、S005が欠損していることがわかります。これが売上合計金額0で抽出できればINSERTしたい内容と一致します。

SELECT
    T1.product_code,
    0 total_sales_amount
FROM
    products T1
WHERE
    NOT EXISTS (
        SELECT
            1
        FROM
            sales_by_product T2
        WHERE
            T1.product_code = T2.product_code
    );

=>
S002,0
S005,0

問題なさそうです。

では実際にINSERTまでやって、再度全件抽出してみます。

INSERT INTO sales_by_product
SELECT
    T1.product_code,
    0 total_sales_amount
FROM
    products T1
WHERE
    NOT EXISTS (
        SELECT
            1
        FROM
            sales_by_product T2
        WHERE
            T1.product_code = T2.product_code
    );

SELECT * FROM sales_by_product;

=>
S001,50
S002,0
S003,250
S004,350
S005,0
S006,450

商品コードS002とS005が売上合計金額0で登録されていることが確認できました。

トランザクション機能を使って確認後にコミットする

今回は自動トランザクションでINSERTしたら自動コミットされるようにしています。

実務では手動トランザクションで明示的にコミットするまではDBにコミットされないようにしておくことで、INSERT後の確認でOKになるまでは作業のやり直しができるようにしておくことが重要です。

まとめ

INSERT SELECTを使ってあるテーブルから直接INSERTする方法について確認しました。バッチなどでのバルクインサートやテーブルのバックアップ・リストアなどINSERT SELECTを行うシーンは意外とあります。

INSERTしたい結果を抽出できるSELECT文を書いて、その前にINSERT INTO テーブル名を付けるということを覚えておけば、それほど悩むこともないと思います。細かい点を抜きにすればUPDATE SELECTとは異なり、RDBMSによって構文が変わるということもないので、どれかのRDBMSでやり方を覚えておけば他のRDBMSでもほぼそのまま適用できます。