SQL指南書 HAVING句② 最頻値を求める

SQL 指南書集 目次 - ray88’s diary
■最頻値
・単純平均は「外れ値（outlier）」に影響を受けやすい欠点がある
・集団の傾向をもっと正確に示す指標の一つに「最頻値（mode）」がある
　※最頻値を流行値と呼ぶ場合もある
・DBMSによっては最頻値を求める独自の関数を用意しているものもあるが、
　標準SQLでも簡単に求められる
■例題
以下の卒業生テーブル（ｇｒａｄｕａｔｅｓ）より最頻値を求める

--最頻値を求めるSQL　その１：ALL述語の利用
SELECT income,COUNT(*) AS cnt
	FROM graduates
	GROUP BY income
HAVING COUNT(*) >= ALL(SELECT COUNT(*)
				    FROM graduates
				    GROUP BY income);

※ALL述語は、NULLと空集合のケースに気をつければ極値関数で代用できる。

--最頻値を求めるSQL　その２：極値関数の利用
SELECT income,COUNT(*) AS cnt
	FROM graduates
	GROUP BY income
HAVING COUNT(*) >= (SELECT MAX(cnt)
			FROM(SELECT COUNT(*) AS cnt
				FROM graduates
				GROUP BY income) TMP);

　今回は「最も多い」のでMAX関数を使用する
【参考】テーブル作成SQL

/* HAVING 句でサブクエリ：最頻値を求める(メジアンも同じサンプルを使用) */
CREATE TABLE Graduates
(name   VARCHAR(16) PRIMARY KEY,
 income INTEGER NOT NULL);

INSERT INTO Graduates VALUES('サンプソン', 400000);
INSERT INTO Graduates VALUES('マイク',     30000);
INSERT INTO Graduates VALUES('ホワイト',   20000);
INSERT INTO Graduates VALUES('アーノルド', 20000);
INSERT INTO Graduates VALUES('スミス',     20000);
INSERT INTO Graduates VALUES('ロレンス',   15000);
INSERT INTO Graduates VALUES('ハドソン',   15000);
INSERT INTO Graduates VALUES('ケント',     10000);
INSERT INTO Graduates VALUES('ベッカー',   10000);
INSERT INTO Graduates VALUES('スコット',   10000);