10-108 パターン数を取得

SQLパズル

ValListテーブル
ID  Value
--  -----
 1  A
 1  B
 1  C
 2  B
 2  C
 3  A
 3  B
 3  C
 4  A
 4  B
 4  D

IDから見たValueのパターン数(ABC、BC、ABD で3)と、
同一パターンを除いた、Valueの総数(3+2+3 で8)
を求める。

出力結果
pattern  elements
-------  --------
      3         8

データ作成スクリプト

create table ValList(
ID    number(1),
Value char(1));

insert into ValList values(1,'A');
insert into ValList values(1,'B');
insert into ValList values(1,'C');
insert into ValList values(2,'B');
insert into ValList values(2,'C');
insert into ValList values(3,'A');
insert into ValList values(3,'B');
insert into ValList values(3,'C');
insert into ValList values(4,'A');
insert into ValList values(4,'B');
insert into ValList values(4,'D');
commit;

SQL

select count(*) as pattern,sum(elements) as elements
from (select distinct substr(max(sys_connect_by_path(Value,',')),2),
      count(*) as elements
      from (select ID,Value,
            Lag(RowID) over(partition by ID order by Value) as LagRowID
            from ValList)
      Start With LagRowID is null
      connect by prior RowID = LagRowID
      group by ID);

解説

distinctでパターンの重複を排除してます。

---