4-14 レコードを一意に特定する列の組み合わせ

SQLパズル

LeadUniqueテーブル
ColA  ColB  ColC  ColD  ColE  ColF
----  ----  ----  ----  ----  ----
1     a     null  J     1     A
1     b     null  K     2     B
2     c     null  L     2     A
2     d     e     M     3     B
2     d     f     N     3     C

レコードを一意に特定する列の組み合わせを出力する。

出力結果
ColA  ColB  ColC  ColD  ColE  ColF
----  ----  ----  ----  ----  ----
ColA        ColC        ColE
ColA                          ColF
      ColB  ColC
      ColB                    ColF
                  ColD
                        ColE  ColF

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

データ作成スクリプト

create table LeadUnique as
select 1 as ColA,'a' as ColB,null as ColC,'J' as ColD,1 as ColE,'A' as ColF from dual
union select 1,'b',null,'K',2,'B' from dual
union select 2,'c',null,'L',2,'A' from dual
union select 2,'d','e' ,'M',3,'B' from dual
union select 2,'d','f' ,'N',3,'C' from dual;

SQL

--■■■冗長な列の組み合わせを排除しないクエリ■■■
with Temp as(
select distinct GA,GB,GC,GD,GE,GF
from (select ColA,ColB,ColC,ColD,ColE,ColF,
      case grouping(ColA) when 0 then 'ColA' else ' ' end as GA,
      case grouping(ColB) when 0 then 'ColB' else ' ' end as GB,
      case grouping(ColC) when 0 then 'ColC' else ' ' end as GC,
      case grouping(ColD) when 0 then 'ColD' else ' ' end as GD,
      case grouping(ColE) when 0 then 'ColE' else ' ' end as GE,
      case grouping(ColF) when 0 then 'ColF' else ' ' end as GF,
      max(count(*))
      over(partition by Grouping_ID(ColA,ColB,ColC,ColD,ColE,ColF)) as maxRecordCount
      from LeadUnique
      group by cube(ColA,ColB,ColC,ColD,ColE,ColF))
 where maxRecordCount = 1)
select GA,GB,GC,GD,GE,GF from temp;

--■■■冗長な列の組み合わせを排除するクエリ■■■
with Temp as(
select distinct GID,GA,GB,GC,GD,GE,GF
from (select ColA,ColB,ColC,ColD,ColE,ColF,
      Grouping_ID(ColA,ColB,ColC,ColD,ColE,ColF) as GID,
      case grouping(ColA) when 0 then 'ColA' else ' ' end as GA,
      case grouping(ColB) when 0 then 'ColB' else ' ' end as GB,
      case grouping(ColC) when 0 then 'ColC' else ' ' end as GC,
      case grouping(ColD) when 0 then 'ColD' else ' ' end as GD,
      case grouping(ColE) when 0 then 'ColE' else ' ' end as GE,
      case grouping(ColF) when 0 then 'ColF' else ' ' end as GF,
      max(count(*))
      over(partition by Grouping_ID(ColA,ColB,ColC,ColD,ColE,ColF)) as maxRecordCount
      from LeadUnique
      group by cube(ColA,ColB,ColC,ColD,ColE,ColF))
 where maxRecordCount = 1)
select GID,GA,GB,GC,GD,GE,GF from temp a
 where not exists(select 1 from temp b
                   where a.GID != b.GID
                     and BitAnd(a.GID,b.GID) = a.GID);

解説

3-32 Grouping_ID関数

以下の
ソフトウェア開発技術者か基本情報技術者で、
見かけた考え方を使ってます。

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■

X BitAnd Y = X
⇔
Xのビットが立っている位置の、Yのビットは立っている

例
1100 BitAnd X = 1100
⇔
Xは 1100,1101,1110,1111 のどれかである(この場合は、11100001100とかがありえますが)

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■

X BitOr Y = X
⇔
Xのビットが立っていない位置の、Yのビットは立っていない

例
1100 BitOr X = 1100
⇔
Xは 1100,1000,0100,0000 のどれかである(この場合は、100000000とかはありえません)

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■

X XOR Y = X
⇔
Xのビットを反転させたものがYである

例
1100 XOR X = 1100
⇔
Xは 0011である

---

マニュアル(BitAnd関数)(英語)
マニュアル(BitAnd関数)

---