同じ構造のテーブルの差分を取得するSQL exceptとunion allを使う

boxcodeadmin

2021年7月16日

今回もSQL。

同じ構造のテーブルの差分を取得するようなSQLをご紹介。

例えば、会員情報のステータスの変化状況を知りたいということございませんか？

4月から5月にかけて、退会された会員が何名いるのか、どう推移したのか。などなど。

毎回、会員の更新のたびに変化の履歴を取るのは大変です。

そこで今回は以下のテーブルを用意し、会員マスタで変更のあった会員情報のみ会員履歴マスタに挿入する（会員マスタと会員履歴マスタの差分を取得し、履歴に保存するという）流れを考えます。

①会員マスタ＝今時点の会員情報

②会員履歴マスタ＝過去からの変更履歴を登録（毎日一回夜間3時等に①と比較して差分があったもののみ抽出してセットする）

データの準備

--①会員マスタ＝今時点の会員情報
create table 会員(
       会員番号 varchar(3)
      ,ステータス varchar(4)
      ,ランク varchar(1)
      ,primary key(会員番号)
);

insert into 会員
values
('001','退会','2'),
('002','会員','1'),
('003','会員','2'),
('005','会員','1');

--②会員履歴マスタ＝過去からの変更履歴を登録（毎日一回夜間3時等に①と比較して差分があったもののみ抽出してセットする）
create table 会員履歴(
       会員履歴日 date
      ,会員番号 varchar(3)
      ,ステータス varchar(4)
      ,ランク varchar(1)
      ,更新内容 varchar(10)
      ,primary key(会員履歴日,会員番号)
);

insert into 会員履歴
values
('2021/1/1','001','会員','1','更新'),
('2021/2/1','001','会員','2','更新'),
('2021/1/10','002','会員','1','更新'),
('2021/3/1','003','会員','1','更新'),
('2021/3/2','004','会員','1','更新');

データの確認

①会員マスタ

会員001は退会ステータスになっています。

そのほかの会員はアクティブ状態で、

会員004のレコードは存在しません。

②会員履歴マスタ

会員001はランク1からランク2への更新が過去になされています。

その他の会員は過去に一度も変更がなされていないようです。

つまり。。。

それぞれの会員に対して以下の変更がなされます。

その結果、新たに会員履歴マスタに登録されるのは、以下のような結果になるSQLを考えればOKです。

※会員マスタに存在しない会員004は退会扱いとしました。通常物理削除はあり得ませんが、一応こういうケースがあった場合ということで。

SQL

SQLは以下のようなものを作りました。

with a as (select 会員番号
                 ,ステータス
                 ,ランク
                 ,'更新'::varchar(4) as 更新内容
             from 会員),
b      as (select 会員番号
                   ,max(会員履歴日) as 会員履歴日 
               from 会員履歴
              group by 会員番号),
c      as (select c1.会員番号
                 ,c1.ステータス
                 ,c1.ランク
                 ,'更新'::varchar(4) as 更新内容
             from 会員履歴 c1
            inner join b 
               on c1.会員番号 = b.会員番号
              and c1.会員履歴日 = b.会員履歴日),
d      as (select c.会員番号
                 ,'退会'::varchar(4) as ステータス
                 ,c.ランク
                 ,'削除'::varchar(4) as 更新内容
             from c
             left join a
               on c.会員番号 = a.会員番号
            where a.会員番号 is null)
select * from a

except

select * from c

union all

select * from d

それぞれのwith句内のクエリの解説です。

a＝単純な現時点での会員マスタを抽出

b＝会員履歴マスタの各会員の最新会員履歴日（要は更新日）を取得

c＝会員履歴マスタの各会員の最新の状態を抽出

d＝会員履歴マスタに存在し、会員マスタに存在しない会員を取得（退会扱い）

結果は

となり、想定通りの結果を得られました。

そして、insert into selectで会員履歴テーブルにセットするようなscriptを作成し、毎日夜間で動くようにすれば、OKです。

with a as (select 会員番号
                 ,ステータス
                 ,ランク
                 ,'更新'::varchar(4) as 更新内容
             from 会員),
b      as (select 会員番号
                   ,max(会員履歴日) as 会員履歴日 
               from 会員履歴
              group by 会員番号),
c      as (select c1.会員番号
                 ,c1.ステータス
                 ,c1.ランク
                 ,'更新'::varchar(4) as 更新内容
             from 会員履歴 c1
            inner join b 
               on c1.会員番号 = b.会員番号
              and c1.会員履歴日 = b.会員履歴日),
d      as (select c.会員番号
                 ,'退会'::varchar(4) as ステータス
                 ,c.ランク
                 ,'削除'::varchar(4) as 更新内容
             from c
             left join a
               on c.会員番号 = a.会員番号
            where a.会員番号 is null)
insert into 会員履歴
 select now()::date as 会員履歴日
       ,e.会員番号
       ,e.ステータス
       ,e.ランク
       ,e.更新内容
   from (
select * from a

except

select * from c

union all

select * from d) e

結果は