2-3-1 モード(最頻値)を取得

SQLパズル

ModeTable
Col1
----
  10
  20
  20
  30
  30
  30
  40
  40
  40
  40
  50
  50
  50
  50
  60
  60
  60
  60

ModeTableの、
Col1の最頻値(最も多く存在する値)と、
最頻値のレコード数を取得する

出力結果
ModeVal  ModeCount
-------  ---------
    40           4
    50           4
    60           4

データ作成スクリプト

create table ModeTable(Col1 integer);

insert into ModeTable values
(10),
(20),
(20),
(30),
(30),
(30),
(40),
(40),
(40),
(40),
(50),
(50),
(50),
(50),
(60),
(60),
(60),
(60);
commit;

SQL

--■■■allを使う方法■■■
select Col1 as ModeVal,count(*) as ModeCount from ModeTable
group by Col1
having count(*) >= all(select count(*) from ModeTable group by Col1);

--■■■分析関数を使う方法■■■
select Col1,ModeCount
from (select Col1,
      count(*) as RecordCount,
      max(count(*)) over() as ModeCount
      from ModeTable
      group by Col1) dummy
where RecordCount = ModeCount
order by Col1;

解説

having句で、最も多く存在する値かをチェックする方法や、
分析関数を使う方法があります

allを使う方法では、
最初のクエリの、射影を返すクエリを、all述語の引数で使ってます

---

CodeZine：HAVING句の力

---