はじめに
初投稿となります。
書き方が至らぬ点がありますが、ご了承ください！
タイトルの通り、同一テーブルで差分比較してみよう！
（使うときはなかなか限定されるかもしれません！）
超初心者用です。
この技術は過去に
レガシイなSQLでストアドプロシージャを作成していた頃に多用していました。
書き方古臭い所があるかも知れませんが、
一応今回はMySQLで記載しています。
・目次
1.SQLでデータ比較てなに？
2.実践-SQLでいろいろやってみよう！
3.比較SQL文の作成
4.実行結果やテーブルの中身の確認
5.まとめ
SQLでデータ比較てなんだ？
①システムの改修前後で発生するであろうDBのテーブル差分をSQLを駆使して抽出してみる！
②今回使うのはSELECTのみ。
※比較SQLって言ったりしますが、そんな言葉は一般的ないです。
実践-SQLでいろいろやってみよう！
①まず、例として以下のようなフローがあるとしましょう。
・販売会社はいろんな商品を売っています。
・販売員は、お客に販売会社が売り出している商品を紹介して、
お客様に販売します。
・契約が成立し、商品が売れた際は、商品の金額に対しての手数料が販売員に支払われます。
（手数料というよりも、紹介料って感じが分かりやすいですかね）
※POINT
①商品に対しての、販売員に支払われる手数料率は変更されることがある！
②私たちは手数料算出を行うシステムを開発してる
●流れ
1.2018年11月1日より現在発売中の商品が人気高騰の為、手数料率の変更が決定されました。
（商品code=BBQ1）
手数料は月次の処理で算出され、販売員に支払われる手数料がDBに登録されています。
（登録テーブル名：商品契約管理テーブル）
2.手数料率の変更前後で、正常に率の変更が行われ
手数料が正常に算出されてる事の確認を行いたいと思います。
3.本番テーブルと同一構成のテーブルを用意し、
既存処理結果・新処理結果を各テーブルに格納
４.比較SQLを実行して、差分を出してみよう！
テーブルの定義は以下のような感じ参考に。
とりあえず比較するテーブルを作成、準備しましょう。
例
| 使用テーブル |
|---|
| 比較元となるテーブル：CommissionHistory |
| 変更前テーブル：CommissionHistory_bef |
| 変更後テーブル：CommissionHistory_aft |
CREATE文
CREATE TABLE CommissionHistory_bef LIKE CommissionHistory;
CREATE TABLE CommissionHistory_aft LIKE CommissionHistory;)
INSERT文
旧処理実行後
SQL
INSERT INTO CommissionHistory_bef SELECT ＊ FROM CommissionHistory;
新処理の資産を環境に適用し、実行後
SQL
INSERT INTO CommissionHistory_aft SELECT ＊ FROM CommissionHistory;
比較SQL文の作成
実行するSQLは以下
SELECT
case
when BEF.produtNum is null then 'AFT'
when AFT.produtNum is null then 'BEF'
ELSE 'Diff'
END AS 差分,
COALESCE(BEF.dataseq,AFT.dataseq) AS データSEQ,
COALESCE(BEF.insertDate,AFT.insertDate) AS 登録日時,
COALESCE(BEF.insertPG,AFT.insertPG) AS 登録者,
COALESCE(BEF.updateDate,AFT.updateDate) AS 更新日時,
COALESCE(BEF.updatePG,AFT.updatePG) AS 更新者,
COALESCE(BEF.empNum,AFT.empNum) AS 社員番号,
COALESCE(BEF.produtCd,AFT.produtCd) AS 商品code,
COALESCE(BEF.produtNum,AFT.produtNum) AS 商品番号,
BEF.contractDate AS BEF_契約日時,
AFT.contractDate AS AFT_契約日時,
BEF.amount AS BEF_金額,
AFT.amount AS AFT_金額,
BEF.commRate AS BEF_手数料率,
AFT.commRate AS AFT_手数料率,
BEF.commission AS BEF_手数料,
AFT.commission AS AFT_手数料,
BEF.produtPoint AS BEF_商品POINT,
AFT.produtPoint AS AFT_商品POINT,
BEF.rank AS BEF_成績ランク,
AFT.rank AS AFT_成績ランク,
BEF.bonusFlg AS BEF_ボーナスフラグ,
AFT.bonusFlg AS AFT_ボーナスフラグ,
BEF.Notices AS BEF_特記事項,
AFT.Notices AS AFT_特記事項
FROM
CommissionHistory_bef BEF
LEFT OUTER JOIN
CommissionHistory_aft AFT ON
BEF.empNum = AFT.empNum AND
BEF.produtNum = AFT.produtNum AND
BEF.produtCd = AFT.produtCd
WHERE
(
COALESCE(BEF.contractDate,'9999-12-31') <> COALESCE(BEF.contractDate,'9999-12-31') OR
COALESCE(BEF.amount,0) <> COALESCE(AFT.amount,0) OR
COALESCE(BEF.commRate,0) <> COALESCE(AFT.commRate,0) OR
COALESCE(BEF.commission,0) <> COALESCE(AFT.commission,0) OR
COALESCE(BEF.produtPoint,0) <> COALESCE(AFT.produtPoint,0) OR
COALESCE(BEF.rank,'') <> COALESCE(AFT.rank,'') OR
COALESCE(BEF.bonusFlg,'') <> COALESCE(AFT.bonusFlg,'') OR
COALESCE(BEF.Notices,'') <> COALESCE(AFT.Notices,'')
)
ORDER BY
BEF.produtNum;
分解して簡単に説明します。
①SELECT句
●大きく分解して3点
①-1.差分のフラグを抽出するcase文
→記載の通り、抽出されたレコードが「diff」なのか、
「Bef」もしくは「aft」のみに存在するレコードなのかを表示する。
①-2.ユニーク項目＆確認が不要な項目
→差分検証に不必要な項目や、テーブルの結合条件に使用する
ユニーク等をCOALESEを使用して表示する。
①-3.差分比較項目
→差分が想定される項目（今回でいうと手数料率・手数料）
差分が想定されてはいけない項目
②FROM句
新・旧テーブルの結合を行う。
結合条件としてはユニークとなる項目を選定。
※検証するテーブルの構成をしっかりと理解していく必要有り。
③WHERE句
①-3.差分比較項目を条件にする。
NULL対応を行う。
各項目の型に合わせて、DEFALUT値を設定しておく。
以下のように変更してもOKですね！
WHERE → WHERE NOT
OR→AND
<> → =
実行結果やテーブルの中身の確認
手数料管理している、商品コードマスタ
古い手数料は2018年10月31日に終了し、新しい手数料は2018年11月1日に開始!
商品契約管理テーブル（旧手数料率結果） CommissionHistory_bef
いっぱいデータありますね。。
黄色くなってるところが変更される商品の情報です
手数料率が12.3%で登録・手数料計算されています。
金額 * 手数料率 = 手数料
商品契約管理テーブル（新手数料率結果） CommissionHistory_aft
（対象商品のcode=BBQ1のみに絞っています。）
2018年11月1日以降に契約された対象商品のデータのみ、
手数料と手数料率が変更されました。
上のSQLを実行し差分が発生したレコードだけ抽出！
WHERE句で比較した項目の中に、差分が発生したレコードのみ取ってきてます。
※手数料・手数料率に差分！！
結果はこんな感じになります。
SELECTで取得している項目は全て出していますが、
手数料・手数料率が横並びに表示されて差分があるのが分かります。
まとめ
・実際に活用する機会は余りないかもしれない。
・やり方は人それぞれ。
工夫すれば、数百万件のレコード差分を抽出可能！
必要な分だけサマリーしてbef・aftテーブルにいれるとか・・
csvでゴッソリexportしちゃうとか・・
・テーブル構成さえ理解すれば誰でも出来ます！
同じようなことしている人がいらしたら是非、勉強させていただきたいです！