2-3-29 グループごとのモード(最頻値)を取得

SQLパズル

modeTable
key  seq  Val
---  ---  ---
  1    1   10
  1    2   10
  1    3   10
  1    4   20
  1    5   20
  2    1   30
  2    2   30
  2    3   30
  2    4   30
  2    5   40
  2    6   40
  2    7   40
  2    8   40
  3    1   50
  3    2   60
  4    1   70
  4    2   70
  4    3   70

keyごとの
Valの最頻値(最も多く存在する値)と、
最頻値のレコード数を取得する。

出力結果
key  Val  ModeCount
---  ---  ---------
  1   10          3
  2   30          4
  2   40          4
  3   50          1
  3   60          1
  4   70          3

データ作成スクリプト

create table modeTable as
select 1 as Key,1 as seq,10 as Val from dual
union select 1,2,10 from dual
union select 1,3,10 from dual
union select 1,4,20 from dual
union select 1,5,20 from dual
union select 2,1,30 from dual
union select 2,2,30 from dual
union select 2,3,30 from dual
union select 2,4,30 from dual
union select 2,5,40 from dual
union select 2,6,40 from dual
union select 2,7,40 from dual
union select 2,8,40 from dual
union select 3,1,50 from dual
union select 3,2,60 from dual
union select 4,1,70 from dual
union select 4,2,70 from dual
union select 4,3,70 from dual;

SQL

--■■■all述語を使う方法■■■
select Key,Val,
count(*) as ModeCount
  from modeTable a
group by Key,Val
having count(*) >= all(select count(*)
                         from modeTable b
                        where b.Key=a.Key
                       group by Val)
order by Key,Val;

--■■■集合関数のネストを使う方法■■■
select Key,Val,
count(*) as ModeCount
  from modeTable a
group by Key,Val
having count(*) = (select max(count(*))
                     from modeTable b
                    where b.Key=a.Key
                   group by Val)
order by Key,Val;

--■■■分析関数を使う方法1■■■
select Key,Val,ModeCount
from (select Key,Val,
      count(*) as ModeCount,
      max(count(*)) over(partition by Key) as MaxModeCount
        from modeTable a
      group by Key,Val)
where ModeCount = MaxModeCount
order by Key,Val;

--■■■分析関数を使う方法2■■■
select Key,Val,ModeCount
from (select Key,Val,
      count(*) as ModeCount,
      rank() over(partition by Key order by count(*) desc) as Rn
        from modeTable a
      group by Key,Val)
where Rn = 1
order by Key,Val;

解説

all述語を使う方法と集合関数のネストを使う方法では、
having句での相関サブクエリで、group by句で指定した式を使用することができる、
ということをふまえて、
where b.Key=a.Key
を、having句での相関サブクエリで使ってます。

9-38 期間内の合計の最大値
2-3-5 モード(最頻値)を取得
group by句

Get max of sum(col)(英語)