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

SQLパズル

最頻値テーブル
Col1
----
  10
  20
  20
  30
  30
  30
  40
  40
  40
  40
  50
  50
  50
  50
  60
  60
  60
  60

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

出力結果
最頻値  最頻値のレコード数
------  ------------------
    40                   4
    50                   4
    60                   4

データ作成スクリプト

create table 最頻値テーブル(Col1 number(2));

insert into 最頻値テーブル values(10);
insert into 最頻値テーブル values(20);
insert into 最頻値テーブル values(20);
insert into 最頻値テーブル values(30);
insert into 最頻値テーブル values(30);
insert into 最頻値テーブル values(30);
insert into 最頻値テーブル values(40);
insert into 最頻値テーブル values(40);
insert into 最頻値テーブル values(40);
insert into 最頻値テーブル values(40);
insert into 最頻値テーブル values(50);
insert into 最頻値テーブル values(50);
insert into 最頻値テーブル values(50);
insert into 最頻値テーブル values(50);
insert into 最頻値テーブル values(60);
insert into 最頻値テーブル values(60);
insert into 最頻値テーブル values(60);
insert into 最頻値テーブル values(60);
commit;

SQL

--■■■集合関数のネストを使う方法■■■
select Col1 as 最頻値,count(*) as 最頻値のレコード数 from 最頻値テーブル
group by Col1
having count(*) = (select max(count(*)) from 最頻値テーブル group by Col1);

--■■■allを使う方法■■■
select Col1 as 最頻値,count(*) as 最頻値のレコード数 from 最頻値テーブル
group by Col1
having count(*) >= all(select count(*) from 最頻値テーブル group by Col1);

--■■■分析関数を使う方法■■■
select Col1,最頻値のレコード数
from (select Col1,
      count(*) as レコード数,
      max(count(*)) over() as 最頻値のレコード数
      from 最頻値テーブル
      group by Col1)
where レコード数 = 最頻値のレコード数
order by Col1;

--■■■stats_mode関数を使う方法(10g以降)■■■
select
(select stats_mode(Col1) from 最頻値テーブル) as 最頻値,
(select max(count(*)) from 最頻値テーブル group by Col1) as 最頻値のレコード数
from dual;

--■■■stats_mode関数を使う方法(10g以降)■■■
select 最頻値,最頻値のレコード数 from
(select stats_mode(Col1) as 最頻値 from 最頻値テーブル),
(select max(count(*)) as 最頻値のレコード数 from 最頻値テーブル group by Col1);

解説

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

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

マニュアルによると、stats_mode関数は、最頻値が複数あった時に任意の1つしか返さないそうです。
また、stats_mode関数は、集合関数の特性として、null値を無視します。

SQL> with WorkView as (
  2  select 1 as ID,null as Val from dual
  3  union all select 1,null from dual
  4  union all select 1,null from dual
  5  union all select 1,50 from dual
  6  union all select 1,50 from dual
  7  union all select 1,30 from dual)
  8  select STATS_MODE(Val)
  9    from WorkView
 10  group by ID;

STATS_MODE(VAL)
---------------
             50

---

マニュアル(stats_mode関数)(英語)
マニュアル(stats_mode関数)

CodeZine：HAVING句の力
CodeZine：分析関数の衝撃（前編）

---