ORACLE SQL分析関数で受注を分割

　今日も見に来てくださって、ありがとうございます。石川さんです。

　今日は、ちょっとお仕事でどうしようかなぁ、というのをSQLで解決することにしましたので、その実験のまとめを記録しておきます。

解決したいこと

　外部から自社商品の注文明細データが送付されてくるのですが、一つの注文取引の中に、同じ商品の明細が含まれて送られてきます。こちら側のシステムでは、同じ商品を一つの受注単位で複数件登録できない仕様なので、その場合は二つ以上の受注単位に分割して登録する必要があります。

サンプルテーブル

サンプルデータ

　解決したいことの具体例を示します。取引番号1のデータは、商品コードの重複はないので、そのまま登録可能です。取引番号2のデータについては、商品コード2と3が重複していますので、取引2-1と2-2に分割して登録する必要があります。取引番号3のデータは商品コード1が3重になっていますので取引を3-1、3-2、3-3と分割して登録する必要があります。

　この問題を解決するのにいろいろな方法がありますが、今回はORACLE分析関数のROW_NUMBER()を使いたいと思います。分析関数は、各行を取り出しつつ、全体に対する分析結果を同時に取得するための関数です。

解決方法

　以下のSQLを実行して、取引を分割するためのキー情報を作成しつつ、データを取り出します。このキー情報がこちら側の受注単位になります。

SELECT 取引番号||'-'||ROW_NUMBER() OVER (PARTITION BY 取引番号, 取引日, 商品コード ORDER BY 取引番号, 取引日, 商品コード) 取引
      ,取引番号
      ,取引日
      ,商品コード
      ,数量
  FROM 取引
 WHERE 取引日 = '20-05-27'
 ORDER BY 1;

実行結果

　これで、取引を分割することができました。

スクリプト

今回使用したスクリプトです。

CREATE TABLE 取引 ( 取引番号 NUMBER, 取引日 DATE, 商品コード NUMBER, 数量 NUMBER);

INSERT INTO 取引 (
 SELECT 1, '20-05-27', 1, 1 FROM DUAL UNION ALL
 SELECT 1, '20-05-27', 2, 1 FROM DUAL UNION ALL
 SELECT 1, '20-05-27', 3, 1 FROM DUAL UNION ALL
 SELECT 1, '20-05-27', 4, 2 FROM DUAL UNION ALL
 SELECT 2, '20-05-27', 2, 1 FROM DUAL UNION ALL
 SELECT 2, '20-05-27', 2, 3 FROM DUAL UNION ALL
 SELECT 2, '20-05-27', 3, 4 FROM DUAL UNION ALL
 SELECT 2, '20-05-27', 4, 2 FROM DUAL UNION ALL
 SELECT 2, '20-05-27', 3, 3 FROM DUAL UNION ALL
 SELECT 3, '20-05-27', 1, 2 FROM DUAL UNION ALL
 SELECT 3, '20-05-27', 1, 1 FROM DUAL UNION ALL
 SELECT 3, '20-05-27', 1, 3 FROM DUAL
);

COMMIT;

SELECT 取引番号||'-'||ROW_NUMBER() OVER (PARTITION BY 取引番号, 取引日, 商品コード ORDER BY 取引番号, 取引日, 商品コード) BREAK
      ,取引番号
      ,取引日
      ,商品コード
      ,数量
  FROM 取引
 WHERE 取引日 = '20-05-27'
 ORDER BY 1
;

まとめ

　分析関数、いろんな用途に使えて便利ですので、ぜひ、使い方を覚えましょう。