HAVING句でのメジアンの求め方。

Question

「達人に学ぶSQL徹底指南書」(著者：ミック、発行：翔泳社)の
第1部、1-4 HAVING句の力 「HAVING句で自己結合：メジアンを求める」
の内容でつまずいています。

テーブル名：Graduates
列はincomeのみ、10行。
|income|
10,000
10,000
10,000
15,000
15,000
20,000
20,000
20,000
30,000
400,000

以下のsqlで、なぜ「Graduatesの5列目の値+6列目の値/2」
を求めることができるのかわかりません。

SELECT AVG(DISTINCT income)
FROM (SELECT T1.income
FROM Graduates T1, Graduates T2
GROUP BY T1.income
--S1の条件
HAVING SUM(CASE WHEN T2.income >= T1.income THEN 1
ELSE 0 END) >= COUNT() / 2
--S2の条件
AND SUM(CASE WHEN T2.income <= T1.income THEN 1
ELSE 0 END) >= COUNT() / 2) TMP;

Graduatesを2つクロス結合してT1.incomeでグループ化した後、
HAVING句の中でやっていることの目的がわかりません。
(文法事項はわかりますが、目的がわかりません。)

「達人に学ぶSQL徹底指南書」を読んだことのある方がいたら
お手数ですがヒントをお願いします。

Answer 1

基本的な考え方は、ある数xが中央値であるならば、集合内のそれぞれの数y （x自身を含む）と比較を行っていくと、X >= Yの件数とx <= yの件数はどちらも全体の件数の1/2以上になる、ということです。

以下の例では中央値の2の場合のみ、両方の比較結果が2件となっています。

income
1
2
3

T1.income	T2.income	>=	<=
1	1	1	1
1	2	0	1
1	3	0	1
2	1	1	0
2	2	1	1
2	3	0	1
3	1	1	0
3	2	1	0
3	3	1	1

全体の件数が偶数の場合は以下のようにHAVING句で2つの値が抽出されるので、AVGで平均をとって中央値が算出されます。

income
1
2
3
4

T1.income	T2.income	>=	<=
1	1	1	1
1	2	0	1
1	3	0	1
1	4	0	1
2	1	1	0
2	2	1	1
2	3	0	1
2	4	0	1
3	1	1	0
3	2	1	0
3	3	1	1
3	4	0	1
4	1	1	0
4	2	1	0
4	3	1	0
4	4	1	1

-- 冗長な説明だったので削除 --

なお、DISTINCTはGROUP BYが既に使用されているため不要なはずです。同じくミックさんによる「SQL実践入門」でも同様のSQLが掲載されていますが、そこではDISTINCTはつけられていません。

Answer 2

このメジアンの出し方には複数のSQL的要素が絡んでいるので理解しづらいですよね。

普通、メジアンを算出するためにやることは値をソートして中央の値を求めることです。
しかし、SQLで標準的な「X番目の列」を知る方法がありません。
X番目の列を得る方法というのは実はサーバー依存なのです。
サーバー依存な方法を除いた、一般的な関数のみでメジアンを求める方法になってます。

このために、このSQLは「Graduatesの5列目の値+6列目の値/2」を求めたのではありません。

まず、あるincomeの値Xから見て、同じ値以上T1 >= T2、以下T1 <= T2の数を数えます。
これを全ての行に対して実行するとどんな結果が作成されるか想像つくでしょうか。

SQL

id	income	T1 >= T2	T1 <= T2
1	10000	10	3
2	10000	10	3
3	10000	10	3
4	15000	7	5
5	15000	7	5
6	20000	5	8
7	20000	5	8
8	20000	5	8
9	30000	2	9
10	400000	1	10

（※分かりやすさのためにID列を振っています。）

上記表のように、あるincomeの値が重複していればその結果は全て同じになります。
この中で、T1 >= T2とT1 <= T2が全体の数の半分以上になる数は中央値の条件を満たす値だけになります。
中央値の対象以外は、必ず片方の条件は満たされますが、もう片方は半分未満の数になります。
今回の例で言えば、idの4, 5, 6, 7, 8が中央値の条件を満たす行になります。

しかし、この操作でできるのは、あくまで中央値の対象候補を導き出すことです。
このままid4-8列の平均を取ると値は正確になりませんよね。
そこで、GROUP BY句の等値を省く機能が効いてくるわけです。
ID列を除き、結果の重複を省くと上記結果は以下のようになります。

income	T1 >= T2	T1 <= T2
15000	7	5
20000	5	8

GROUP BY句で重複行が削除されることによって、以下のような結果が作成されます。
・テーブル全体の行数が奇数の場合
以上、以下の数が半分を超える数は1種類になります。→中央値になります

・テーブル全体の行数が偶数で中央値の対象2つが同じ値の場合
以上、以下の数が半分を超える数が1種類になります。→中央値になります

・テーブル全体の行数が偶数で中央値の対象2つが違う値の場合
以上、以下の数が半分を超える数が2種類になります。

2種類の結果が生成された時のために、SELECT句でAVGによる平均を出しています。

このクエリは中央の値が15000と20000の平均になるなら、15000と20000を発見できるクエリなのです。
ですから、正確にはGraduatesの5，6番目の値を探しているクエリではないのです。

---

達人に学ぶSQL徹底指南書の方法は、「Graduatesの5列目の値+6列目の値/2」を出しているわけではありません。
SQL的な別の確度から、中央値の対象を絞り込み、メジアンを導出しています。

Comments

[m.g2017]

ありがとうございました。
説明してもらった内容を何度か読んでるうちに
sqlで何をやっているかがわかりました。