質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

Q&A

解決済

2回答

7525閲覧

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

m.g2017

総合スコア19

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

1グッド

1クリップ

投稿2017/04/24 14:56

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

ikuwow👍を押しています

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答2

0

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

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

income
1
2
3
T1.incomeT2.income>=<=
1111
1201
1301
2110
2211
2301
3110
3210
3311

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

income
1
2
3
4
T1.incomeT2.income>=<=
1111
1201
1301
1401
2110
2211
2301
2401
3110
3210
3311
3401
4110
4210
4310
4411

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

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

投稿2017/04/24 15:59

編集2017/04/24 16:22
SVC34

総合スコア1149

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

0

ベストアンサー

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

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

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

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

SQL

idincomeT1 >= T2T1 <= T2
110000103
210000103
310000103
41500075
51500075
62000058
72000058
82000058
93000029
10400000110

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

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

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

incomeT1 >= T2T1 <= T2
1500075
2000058

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

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

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

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

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


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

投稿2017/04/25 02:47

編集2017/04/25 02:54
haru666

総合スコア1591

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

m.g2017

2017/04/25 15:01

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

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問