最頻値を求めるSQL

分析関数（ウインドウ関数）でグループ内の件数の多い順を求めて、最初のアイテムの値を取得する。
出現数が同じアイテムが複数ある場合にどれか１つではなく全ての最頻値を取得したい場合、ROW_NUMBER()の代わりにRANK()を使う。
分析関数のOVER(ORDER BY xxx)にcount(*)を指定してグループ内の件数順に番号を振ることで実現している。

-- テスト用テーブル作成
CREATE TABLE test (
    val VARCHAR(10)
);
INSERT INTO test VALUES ('A');
INSERT INTO test VALUES ('B');
INSERT INTO test VALUES ('B');
INSERT INTO test VALUES ('C');
INSERT INTO test VALUES ('C');
INSERT INTO test VALUES ('C');
INSERT INTO test VALUES ('D');
INSERT INTO test VALUES ('D');
INSERT INTO test VALUES ('D');

-- 最頻値を求める
SELECT val_summary.val AS mode_val
  FROM (
          SELECT val, count(*), ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS row_num
            FROM test
           GROUP BY val
       ) AS val_summary
  WHERE val_summary.row_num = 1;

分析関数が使えない場合、HAVINGを使う。

-- 最頻値を求める（HAVING）
WITH val_max_count as (
  SELECT MAX(cnt) AS count_max
    FROM (
            SELECT val, COUNT(*) AS cnt
              FROM test
             GROUP BY val
         ) val_summary
)
SELECT val AS mode_val
  FROM test
 GROUP BY val
HAVING COUNT(*) = (select count_max from val_max_count)
 LIMIT 1;