集約関数のネスト

2段階の集約

集約関数は、ネストさせることができます。
集約関数のネストを使えば、2段階の集約を行えます。

create table nestedAggSample(ID,Val) as
select 111,1 from dual union all
select 111,3 from dual union all
select 111,8 from dual union all
select 222,5 from dual union all
select 222,6 from dual union all
select 333,9 from dual;

IDごとのデータ件数の最大値,
IDごとのデータ件数の最小値,
IDごとのValの合計の最大値,
IDごとのValの最大値の合計を求めてみます。

select max(count(*)) as maxCount,
min(count(*)) as minCount,
max(sum(Val)) as maxSumVal,
sum(max(Val)) as sumMaxVal
  from nestedAggSample
group by ID;
出力結果
maxCount  minCount  maxSumVal  sumMaxVal
--------  --------  ---------  ---------
       3         1         12         23

上記のSQLの第1段階のイメージは下記になります。group by句のgroup by IDに対応する赤線をイメージしてます。

集約関数のネストのイメージ(第1段階

上記のSQLの第2段階のイメージは下記になります。内側の集約関数に対応する黄緑の楕円をイメージしてます。

集約関数のネストのイメージ(第2段階)

▲ ページTOPに戻る

集約関数と分析関数

集約関数と分析関数の併用

集約関数と分析関数は併用することができます。

create table aggOlapSample(ID,Val) as
select 111,1 from dual union all
select 111,1 from dual union all
select 111,1 from dual union all
select 111,8 from dual union all
select 222,3 from dual union all
select 222,3 from dual union all
select 222,4 from dual union all
select 222,4 from dual union all
select 333,9 from dual;

IDごとのValの最頻値を求めてみます。
最頻値が複数ある場合には、複数の最頻値を出力します。

ちなみに、avgとmedianとstats_modeで扱ったstats_mode関数は、最頻値が複数ある場合に、複数ある最頻値の中のどれかを返します。

select ID,Val,cnt
from (select ID,Val,count(*) as cnt,
      max(count(*)) over(partition by ID) as maxCnt
        from aggOlapSample
      group by ID,Val)
where cnt=maxCnt
order by ID,Val;
出力結果
 ID  Val  cnt
---  ---  ---
111    1    3
222    3    2
222    4    2
333    9    1

上記のSQLのインラインビューでは、分析関数のmax関数の引数に集約関数のcount関数を使用してます。

上記のSQLの第1段階のイメージは下記になります。group by句のgroup by ID,Valに対応する赤線をイメージしてます。

集約関数と分析関数のイメージ(第1段階)

上記のSQLの第2段階のイメージは下記になります。partition by IDに対応する超極太赤線をイメージしてます。

集約関数と分析関数のイメージ(第2段階)

▲ ページTOPに戻る

参考リソース

マニュアル --- 集計ファンクション

▲ ページTOPに戻る

"図でイメージするOracle DatabaseのSQL全集" インデックスに戻る

Copyright © 2011, Oracle Corporation Japan. All rights reserved.
無断転載を禁ず

この文書はあくまでも参考資料であり、掲載されている情報は予告なしに変更されることがあります。日本オラクル社は本書の内容に関していかなる保証もいたしません。また、本書の内容に関連したいかなる損害についても責任を負いかねます。

Oracleは米国Oracle Corporationの登録商標です。文中に参照されている各製品名及びサービス名は米国Oracle Corporationの商標または登録商標です。その他の製品名及びサービス名はそれぞれの所有者の商標または登録商標の可能性があります。

山岸 賢治山岸 賢治(やまぎし けんじ)
Oracle ACEの1人。
OracleSQLパズルの運営者。



Left Curve
図でイメージする
Oracle DatabaseのSQL全集
Right Curve