9-35 count関数の引数にRowID

SQLパズル

fishesテーブル
sampleID  fish    num
--------  ------  ---
   A      minnow   10
   A      pike     20
   B      carp     40
   B      pike     60
   C      carp     70
   D      carp     80
   D      minnow   90
   D      pike     30

WaterGroupsテーブル
WaterGroupID  descript     sampleID
------------  -----------  --------
           1  muddy water     A
           1  muddy water     B
           2  fresh water     A
           2  fresh water     C
           2  fresh water     D
           3  salt water      B

WaterGroupIDごとの、
numの平均(小数点以下を四捨五入)を列挙する

出力結果
WaterGroupID  descript     fish    numAvg
------------  -----------  ------  ------
           1  muddy water  carp        20  ← ( 0+40)/2
           1  muddy water  minnow       5  ← (10+ 0)/2
           1  muddy water  pike        40  ← (20+60)/2
           2  fresh water  carp        50  ← ( 0+70+80)/3
           2  fresh water  minnow      33  ← (10+ 0+90)/3
           2  fresh water  pike        17  ← (20+ 0+30)/3
           3  salt water   carp        40
           3  salt water   minnow       0
           3  salt water   pike        60

SQLパズル(日本語版)のパズル38 [魚を数える] を参考にさせていただきました
SQLパズル 第2版のパズル42 [魚のサンプリング調査] を参考にさせていただきました

データ作成スクリプト

create table fishes(
sampleID char(1),
fish     varchar2(6),
num      number(2),
primary key(sampleID,fish));

insert into fishes values('A','minnow',10);
insert into fishes values('A','pike'  ,20);
insert into fishes values('B','carp'  ,40);
insert into fishes values('B','pike'  ,60);
insert into fishes values('C','carp'  ,70);
insert into fishes values('D','carp'  ,80);
insert into fishes values('D','minnow',90);
insert into fishes values('D','pike'  ,30);

create table WaterGroups(
WaterGroupID number(1),
descript     varchar2(11),
sampleID     char(1),
primary key(WaterGroupID,sampleID));

insert into WaterGroups values(1,'muddy water','A');
insert into WaterGroups values(1,'muddy water','B');
insert into WaterGroups values(2,'fresh water','A');
insert into WaterGroups values(2,'fresh water','C');
insert into WaterGroups values(2,'fresh water','D');
insert into WaterGroups values(3,'salt water' ,'B');
commit;

SQL

--■■■count関数の引数にRowIDを使う方法■■■
select b.WaterGroupID,b.descript,a.fish,
round(sum(decode(b.sampleID,a.sampleID,a.num,0))/count(distinct b.RowID)) as numAvg
  from fishes a,WaterGroups b
group by b.WaterGroupID,b.descript,a.fish
order by b.WaterGroupID,a.fish;

--■■■count関数の引数にRowIDを使わない方法■■■
select b.WaterGroupID,b.descript,a.fish,
round(sum(decode(b.sampleID,a.sampleID,a.num,0))/count(distinct b.sampleID)) as numAvg
  from fishes a,WaterGroups b
group by b.WaterGroupID,b.descript,a.fish
order by b.WaterGroupID,a.fish;

解説

count関数の引数にRowIDを使って、
結合元のレコード数を求めてます(WaterGroupsを結合の駆動表と考えてます)

---