9-69 結合が先か、Sum関数が先か その2

SQLパズル

mainT                       sumT
staTime  EndTime  name      Time  Code  Val
-------  -------  ----      ----  ----  ---
      1        1  AAAA         1  A     10
      1        3  BBBB         1  B     20
      1        4  CCCC         1  C     40
      2        2  DDDD         1  A     80
      2        4  EEEE         1  A     70
                               1  B     10
                               2  B     30
                               2  C     50
                               3  A     11
                               3  B     22
                               4  C     30
                               4  A     44
                               4  C     50

mainTにsumTを、Time between staTime and EndTime
を結合条件として外部結合させ、
CodeがAのValの合計
CodeがBのValの合計
CodeがCのValの合計
CodeがDのValの合計
を求める。

出力結果
staTime  EndTime  name  ValA  ValB  ValC  ValD
-------  -------  ----  ----  ----  ----  ----
      1        1  AAAA   160    30    40  null
      1        3  BBBB   171    82    90  null
      1        4  CCCC   215    82   170  null
      2        2  DDDD  null    30    50  null
      2        4  EEEE    55    52   130  null

達人に学ぶ SQL徹底指南書の92ページを参考にさせていただきました

データ作成スクリプト

create table mainT(staTime,EndTime,name) as
select 1,1,'AAAA' from dual union all
select 1,3,'BBBB' from dual union all
select 1,4,'CCCC' from dual union all
select 2,2,'DDDD' from dual union all
select 2,4,'EEEE' from dual;

create table sumT(Time,Code,Val) as
select 1,'A',10 from dual union all
select 1,'B',20 from dual union all
select 1,'C',40 from dual union all
select 1,'A',80 from dual union all
select 1,'A',70 from dual union all
select 1,'B',10 from dual union all
select 2,'B',30 from dual union all
select 2,'C',50 from dual union all
select 3,'A',11 from dual union all
select 3,'B',22 from dual union all
select 4,'C',30 from dual union all
select 4,'A',44 from dual union all
select 4,'C',50 from dual;

SQL

--■■■スカラーサブクエリを何度も使う■■■
select staTime,EndTime,name,
(select sum(decode(b.Code,'A',Val)) from sumT b where b.Time between a.staTime and a.EndTime) as valA,
(select sum(decode(b.Code,'B',Val)) from sumT b where b.Time between a.staTime and a.EndTime) as valB,
(select sum(decode(b.Code,'C',Val)) from sumT b where b.Time between a.staTime and a.EndTime) as valC,
(select sum(decode(b.Code,'D',Val)) from sumT b where b.Time between a.staTime and a.EndTime) as valD
from mainT a
order by staTime,EndTime;

--■■■外部結合して、分析関数とdistinct■■■
select distinct a.staTime,a.EndTime,a.name,
sum(decode(b.Code,'A',Val)) over(partition by a.RowID) as valA,
sum(decode(b.Code,'B',Val)) over(partition by a.RowID) as valB,
sum(decode(b.Code,'C',Val)) over(partition by a.RowID) as valC,
sum(decode(b.Code,'D',Val)) over(partition by a.RowID) as valD
  from mainT a,sumT b
 where b.Time(+) between a.staTime and a.EndTime
order by a.staTime,a.EndTime;

--■■■外部結合して、グループ化■■■
select a.staTime,a.EndTime,a.name,
sum(decode(b.Code,'A',Val)) as valA,
sum(decode(b.Code,'B',Val)) as valB,
sum(decode(b.Code,'C',Val)) as valC,
sum(decode(b.Code,'D',Val)) as valD
  from  mainT a,sumT b
 where b.Time(+) between a.staTime and a.EndTime
group by a.staTime,a.EndTime,a.name
order by a.staTime,a.EndTime;

解説

この場合は、スカラーサブクエリを何度も使うのもありかもしれません。

---