- PR -

レコードを一意に特定することのできるカラムの組み合わせ

投稿者投稿内容
明智重蔵
大ベテラン
会議室デビュー日: 2005/09/05
投稿数: 127
投稿日時: 2007-07-05 12:39
コード:
create table WorkView 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;

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 WorkView
      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 bitand(a.GID,b.GID) = a.GID
                     and a.GID != b.GID);

GID■GA  ■GB  ■GC  ■GD  ■GE  ■GF
---■----■----■----■----■----■----
 21■ColA■    ■ColC■    ■ColE■
 30■ColA■    ■    ■    ■    ■ColF
 39■    ■ColB■ColC■    ■    ■
 46■    ■ColB■    ■    ■    ■ColF
 59■    ■    ■    ■ColD■    ■
 60■    ■    ■    ■    ■ColE■ColF



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

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

---例---
1100 BitAnd X = 1100
⇔ X は 1100,1101,1110,1111 のどれかである(まあ11100001100とかそんなのもありえますが )


とんくま
ベテラン
会議室デビュー日: 2005/08/02
投稿数: 56
お住まい・勤務地: 東京
投稿日時: 2007-07-07 15:10
GROUPING_ID は、Oracle 固有の関数と思います(少なくとも、DB2, MS SQL Server, PostgreSQL, MySQL には無いようです)。
GROUPING(ColA)*32+GROUPING(ColB)*16+GROUPING(ColC)*8+GROUPING(ColD)*4+GROUPING(ColE)*2+GROUPING(ColF) とすれば同等の結果を得ることが出来ますが、非常に煩わしく、特に今回のように複数回使用する場合、GROUPING_ID の便利さがよく分かりました。
また、BITAND はいまさら BIT 操作が必要か?と思っていましたが、明智重蔵さんの例で有効性が理解できました。(そういえば、Oracle のマニュアルにも OLAP で有用であるというような記述が有ったように思います。)
大変勉強になりました。

DB2 では上記関数が使えませんが、X BitAnd Y = X の考えを使わせていただければ、私の最初の例よりは、簡略化できそうです。例えば、
コード:
WITH redundant_combination AS (

SELECT ga,gb,gc,gd,ge,gf
, ga*32+gb*16+gc*8+gd*4+ge*2+gf AS GID
FROM (SELECT DISTINCT
GROUPING(a)
, GROUPING(b)
, GROUPING(c)
, GROUPING(d)
, GROUPING(e)
, GROUPING(f)
, COUNT(*)
FROM Test
GROUP BY CUBE(a,b,c,d,e,f)
) C(ga,gb,gc,gd,ge,gf,cnt)
GROUP BY ga,gb,gc,gd,ge,gf
HAVING MAX(cnt) = 1
)
SELECT SUBSTR('A ',ga+1,2)
||SUBSTR('B ',gb+1,2)
||SUBSTR('C ',gc+1,2)
||SUBSTR('D ',gd+1,2)
||SUBSTR('E ',ge+1,2)
||SUBSTR('F ',gf+1,2) AS Minimum_Combination
FROM redundant_combination r
WHERE NOT EXISTS
(SELECT *
FROM redundant_combination s
WHERE r.ga*s.ga = r.ga
AND r.gb*s.gb = r.gb
AND r.gc*s.gc = r.gc
AND r.gd*s.gd = r.gd
AND r.ge*s.ge = r.ge
AND r.gf*s.gf = r.gf
AND r.GID <> s.GID
)
ORDER BY Minimum_Combination DESC;
------------------------------------------------------------------------------

MINIMUM_COMBINATION
-------------------
A C E
A F
B C
B F
D
E F

6 record(s) selected.




[ メッセージ編集済み 編集者: とんくま 編集日時 2007-07-07 16:05 ]

スキルアップ/キャリアアップ(JOB@IT)