HAVING句でのAVGの使い方

Question

「達人に学ぶSQL徹底指南書」(著者：ミック、発行：翔泳社)の
第1部、1-10 帰ってきたHAVING句 「集合にきめ細やかな条件を設定する」の内容でつまずいています。

テーブル名：TestResults
列はstudent_id|class|sex|scoreの4列で15行
student_id|class|sex|score
001 A 男 100
002 A 女 100
003 A 女 49
004 A 男 30
005 B 女 100
006 B 男 92
007 B 男 80
008 B 男 80
009 B 女 10
010 C 男 92
011 C 男 80
012 C 女 21
013 D 女 100
014 D 女 0
015 D 女 0

SELECT class
FROM TestResults
GROUP BY class
HAVING AVG(CASE WHEN sex = '男'
THEN score
ELSE 0 END)
< AVG(CASE WHEN sex = '女'
THEN score
ELSE 0 END);

実行結果
class

---

A
D

上記のsqlのHAVING句の部分で、
例えばclassがAの行の処理でなぜ男子の平均が(100+30+0+0)/4=32.5ではなく(100+30)/2=65になるのか？
また、女子の平均が(100+49+0+0)/4=37.25ではなく
(100+49)/2=74.5になるのか？

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

Answer 1

仰る通り
平均を出すためには、0の代わりにnullにする必要がありますよね。

HAVING AVG(CASE WHEN sex = '男' THEN score ELSE null END) 
< AVG(CASE WHEN sex = '女' THEN score ELSE null END)

Answer 2

Postgresでもやってみました、AとDで良いと思います。

SELECT class,
AVG(CASE WHEN sex='男' THEN score ELSE 0 END),
AVG(CASE WHEN sex='女' THEN score ELSE 0 END)
FROM TestResults
GROUP BY class;

 class |          avg           |         avg
-------+------------------------+---------------------
 D     | 0.00000000000000000000 | 33.3333333333333333
 B     |    50.4000000000000000 | 22.0000000000000000
 C     |    57.3333333333333333 |  7.0000000000000000
 A     |    32.5000000000000000 | 37.2500000000000000
(4 rows)

但し、NULLを使えは答えは異なります。NULLはAVEの対象外
SELECT class,
AVG(CASE WHEN sex='男' THEN score ELSE NULL END),
AVG(CASE WHEN sex='女' THEN score ELSE NULL END)
FROM TestResults
GROUP BY class;

 class |         avg         |         avg
-------+---------------------+---------------------
 D     |                     | 33.3333333333333333
 B     | 84.0000000000000000 | 55.0000000000000000
 C     | 86.0000000000000000 | 21.0000000000000000
 A     | 65.0000000000000000 | 74.5000000000000000

Comments

[m.g2017]

わざわざ実行結果まで確認してくれてありがとうございます。
nullの場合AVGの対象に含まないという点には気を付けます。

Answer 3

書籍の誤りです。書かれているSQLについてはあなたの理解で合っています。
しかし、男女別々に正しく平均を出すには、hihijijiさんが回答されているようにCASEで値を置き換える際に0ではなくNULLにする必要があります。COUNT以外の集約関数はNULLを対象から除外するためです。

Answer 4

「集合にきめ細やかな条件を設定する 男 女」で検索すると、Googleブックスで該当部分のサンプルが表示されてビックリしました。「翔泳社の許可を受けてページを表示しています. 」だそうです。

で、その部分ですが、本側の記述ミスじゃないでしょうか？
もし環境があるならAVG(CASE WHEN sex = '男' THEN score ELSE 0 END)これもselect文で抽出してみてはどうでしょうか？

paizaのMySQLで次のコードをrunしてみたところでは、結果は43.3333でした

create table Test(class1 integer, class2 integer, val integer);
insert into Test(class1, class2, val) values(1, 1, 100);
insert into Test(class1, class2, val) values(1, 1, 30);
insert into Test(class1, class2, val) values(1, 2, 100);
select class1,AVG(CASE WHEN class2 = 1 THEN val ELSE 0 END) 
from Test 
GROUP BY class1;

Comments

[hirohiro]

といいますか、「男子の平均は」と書かれているだけで、「AVGの結果が」とは書かれていませんでしたので、記述ミスというわけでもないのかもしれませんね。混乱はしますけど