トップページに戻る
次のSQLパズルへ
前のSQLパズルへ
10-259 重複を除いた集計の累計
SQLパズル
StrTable
PID SampleID Name NO Str
--- -------- ------ -- ---
1 1 Riffle 1 A
2 1 Riffle 1 B
3 1 Riffle 1 C
4 1 Riffle 2 B
5 1 Riffle 2 D
6 1 Pool 3 C
7 1 Pool 3 D
8 1 Pool 3 E
9 1 Riffle 4 F
10 2 Pool 1 A
30 5 AAAA 1 X
31 5 AAAA 1 X
32 5 BBBB 9 Y
33 5 CCCC 8 Z
SampleIDごとにPIDの昇順で、
Name,NOごとの、その行までの重複を除いたStrの数をcntとして、求める。
出力結果
SampleID Name NO Cnt
-------- ------ -- ---
1 Riffle 1 3 ← A,B,C なので3
1 Riffle 2 4 ← A,B,C,B,D なので4
1 Pool 3 5 ← A,B,C,B,D,C,D,E なので5
1 Riffle 4 6 ← A,B,C,B,D,C,D,E,F なので6
2 Pool 1 1 ← A なので1
5 AAAA 1 1 ← X,X なので1
5 BBBB 9 2 ← X,X,Y なので2
5 CCCC 8 3 ← X,X,Y,Z なので3
データ作成スクリプト
create table StrTable(PID,SampleID,Name,NO,Str) as
select 1,1, 'Riffle', 1, 'A' from dual union all
select 2,1, 'Riffle', 1, 'B' from dual union all
select 3,1, 'Riffle', 1, 'C' from dual union all
select 4,1, 'Riffle', 2, 'B' from dual union all
select 5,1, 'Riffle', 2, 'D' from dual union all
select 6,1, 'Pool' , 3, 'C' from dual union all
select 7,1, 'Pool' , 3, 'D' from dual union all
select 8,1, 'Pool' , 3, 'E' from dual union all
select 9,1, 'Riffle', 4, 'F' from dual union all
select 10,2, 'Pool' , 1, 'A' from dual union all
select 30,5, 'AAAA' , 1, 'X' from dual union all
select 31,5, 'AAAA' , 1, 'X' from dual union all
select 32,5, 'BBBB' , 9, 'Y' from dual union all
select 33,5, 'CCCC' , 8, 'Z' from dual;
SQL
--■■■分析関数を使う方法■■■
select SampleID,Name,NO,
sum(sum(willSum)) over(partition by SampleID order by min(PID)) as cnt
from (select PID,SampleID,Name,NO,Str,
case Row_Number() over(partition by SampleID,Str order by PID)
when 1 then 1 else 0 end as willSum
from StrTable)
group by SampleID,Name,NO
order by SampleID,min(PID);
--■■■分析関数を使わない方法1■■■
select SampleID,Name,NO,
(select count(distinct b.str)
from StrTable b
where b.SampleID = a.SampleID
and b.PID <= a.maxPID) as Cnt
from (select SampleID,Name,NO,max(PID) as maxPID
from StrTable
group by SampleID,Name,NO) a
order by SampleID,maxPID;
--■■■分析関数を使わない方法2■■■
select a.SampleID,a.Name,a.NO,
count(distinct b.str) as cnt
from StrTable a,StrTable b
where a.SampleID = b.SampleID
and a.PID >= b.PID
group by a.SampleID,a.Name,a.NO
order by a.SampleID,min(a.PID);
--■■■model句を使う方法(10g以降)■■■
select SampleID,Name,NO,cnt
from (select PID,SampleID,Name,NO,cnt,
max(PID) over(partition by SampleID,Name,NO) as maxPID
from (select PID,SampleID,Name,NO,Str,cnt
from StrTable
model
partition by (SampleID)
dimension by (PID)
measures(Name,NO,Str,0 as cnt)
rules(cnt[any] = count(distinct Str)[CV() >= PID])))
where PID = maxPID
order by SampleID,PID;
解説