10-302 Partitioned Outer Joinして分析関数

SQLパズル

w_acctテーブル
acct  vers
----  ----
  A1     0
  A2     0
  A3     0
  A1     1
  A2     1
  A1     2
  A4     2

versの昇順でacctの履歴ログを下記の形で出力する。

・LD列
NO_CHANGEならacctの値、NO_CHANGEでなければnull

・ADD_REMOVE列
新規なら、NEW
2回目の登場なら、NO_CHANGE
消えたら、REMOVED

出力結果
acct  vers  LD    ADD_REMOVE
----  ----  ----  ----------
A1       0  null  NEW
A2       0  null  NEW
A3       0  null  NEW
A1       1  A1    NO_CHANGE
A2       1  A2    NO_CHANGE
A3       1  null  REMOVED
A1       2  A1    NO_CHANGE
A2       2  null  REMOVED
A4       2  null  NEW

こちらを参考にさせていただきました(英語)

データ作成スクリプト

create table w_acct as
select 'A1' acct,0 vers from dual union all
select 'A2' acct,0 vers from dual union all
select 'A3' acct,0 vers from dual union all
select 'A1' acct,1 vers from dual union all
select 'A2' acct,1 vers from dual union all
select 'A1' acct,2 vers from dual union all
select 'A4' acct,2 vers from dual;

SQL

--■■■Partitioned Outer Joinを使う方法(10g以降)■■■
select acct,vers,
case when appearCnt=1 and preCnt = 0 then 'null'
     when appearCnt=preCnt then 'null'
     else acct end as LD,
case when appearCnt=1 and preCnt = 0 then 'NEW'
     when appearCnt=preCnt then 'REMOVED'
     else 'NO_CHANGE' end as ADD_REMOVE
from (select a.acct,b.vers,
      count(b.acct)
      over(partition by a.acct order by b.vers) as appearCnt,
      count(b.acct)
      over(partition by a.acct
           order by b.vers rows between unbounded preceding
                                    and 1 preceding) as preCnt,
      Lag(b.acct) over(partition by a.acct order by b.vers) as LagVal
        from (select distinct acct from w_acct) a
        Left Join w_acct b
      partition by (b.vers)
          on a.acct = b.acct)
 where appearCnt > preCnt
    or LagVal is not null
order by vers,acct;

--■■■Partitioned Outer Joinを使わない方法■■■
select acct,vers,
case when appearCnt=1 and preCnt = 0 then 'null'
     when appearCnt=preCnt then 'null'
     else acct end as LD,
case when appearCnt=1 and preCnt = 0 then 'NEW'
     when appearCnt=preCnt then 'REMOVED'
     else 'NO_CHANGE' end as ADD_REMOVE
from (select a.acct,b.vers,
      count(c.acct)
      over(partition by a.acct order by b.vers) as appearCnt,
      count(c.acct)
      over(partition by a.acct
           order by b.vers rows between unbounded preceding
                                    and 1 preceding) as preCnt,
      Lag(c.acct) over(partition by a.acct order by b.vers) as LagVal
        from (select distinct acct from w_acct) a
        cross Join (select distinct vers from w_acct) b
        Left Join w_acct c
          on a.acct = c.acct
         and b.vers = c.vers)
 where appearCnt > preCnt
    or LagVal is not null
order by vers,acct;

解説

分析関数のoount関数で登場数を求めておいて、
case式で場合分けしてます。

3-34  Partitioned Outer Join

---