8-50 SQLServerのgroup by allを模倣

SQLパズル

ValTable
Key  Val
---  ---
  1   10
  1   20
  1   30
  1   40
  1   50
  2   30
  2   50
  3   10
  3   40
  3   50
  4   80
  4   90

SQLServerの"group by all"
を使った下記のクエリをOracleで模倣する。

select count(*) as cnt,
max(Val) as maxVal,
min(Val) minVal,
sum(Val) as sumVal
  from ValTable
 where Val in(10,20,30)
group by all Key;

出力結果
Key  cnt  maxVal  minVal  sumVal
---  ---  ------  ------  ------
  1    3      30      10      60
  2    1      30      30      30
  3    1      10      10      10
  4    0    null    null    null

SQLデータをソート、グループ化、要約化するための10 Tips
mdsnのマニュアル gropu by all

データ作成スクリプト

create table ValTable(Key,Val) as
select 1,10 from dual union all
select 1,20 from dual union all
select 1,30 from dual union all
select 1,40 from dual union all
select 1,50 from dual union all
select 2,30 from dual union all
select 2,50 from dual union all
select 3,10 from dual union all
select 3,40 from dual union all
select 3,50 from dual union all
select 4,80 from dual union all
select 4,90 from dual;

SQL

--■■■case式を使う方法■■■
select Key,
count(decode(willAgg,1,1)) as cnt,
max(decode(willAgg,1,Val)) as maxVal,
min(decode(willAgg,1,Val)) as minVal,
sum(decode(willAgg,1,Val)) as sumVal
from (select Key,Val,
      case when Val in(10,20,30)
           then 1 else 0 end as willAgg
        from ValTable)
group by Key
order by Key;

--■■■model句を使う方法(10g以降)■■■
select Key,cnt,maxVal,minVal,sumVal
  from ValTable
 model return updated rows
 partition by(Key)
 dimension by(Val)
 measures(Val as copyVal,0 as cnt,0 as maxVal,0 as minVal,0 as sumVal)
 rules(
 cnt[null]   = count(*)    [Val in(10,20,30)],
 maxVal[null]= max(copyVal)[Val in(10,20,30)],
 minVal[null]= min(copyVal)[Val in(10,20,30)],
 sumVal[null]= sum(copyVal)[Val in(10,20,30)])
order by Key;

解説

model句を使う方法は、
Laurent Schneiderさんの本の263ページを意識しました。

---