今回はCASEとHAVINGを組み合わせて、そこそこの難問を解いていきます。
まず下記のようなテーブル(testresults)があるとします。
student | 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
下記３題をCASEフラグとHAVINGで求めていきます。
コツとしては下記のようなことがあげられます。
- 特定条件のの生徒をCASEで条件分岐させてフラグを立てる
- さらにそれのSUMをとり、全体数と比較し、HAVINGで合致しているものを出力
- SQLは割り算が整数になってしまうなどに注意。
なお下記SQLではSELECTの列で計算式をかいていますが、ここは書かなくてもOKです。(私が途中計算するうえでの確かめのために出してます。)
問題1
クラスの75%以上が80点以上のクラスを求めよ
答えはクラスBですが、
|
1 2 3 4 5 6 7 8 9 10 |
SELECT class, --下３列はもちろんなくてもOKです。比較のためにわかりやすく出しています。 COUNT(*) , SUM( CASE WHEN score >= 80 THEN 1 ELSE 0 END ) AS score_status, SUM( CASE WHEN score >= 80 THEN 1 ELSE 0 END ) * 100 / COUNT(*) as attained_stundent_rate FROM testresults GROUP BY class HAVING SUM( CASE WHEN score >= 80 THEN 1 ELSE 0 END ) * 100 / COUNT(*) >= 75 |
問題2
５０点以上を取った生徒のうち、男子の数が女子の数よりも多いクラスを求めよ
クラスBとクラスCが該当。
|
1 2 3 4 5 6 7 8 9 10 |
SELECT class, --例によってここから下3列は書かなくてもOKです。 COUNT(*) , SUM( CASE WHEN score >= 50 and sex = '男' THEN 1 ELSE 0 END ) AS attain_man_count, SUM( CASE WHEN score >= 50 and sex = '女' THEN 1 ELSE 0 END ) AS attain_woman_count FROM testresults GROUP BY class HAVING SUM( CASE WHEN score >= 50 and sex = '男' THEN 1 ELSE 0 END ) > SUM( CASE WHEN score >= 50 and sex = '女' THEN 1 ELSE 0 END ) |
第3問
女子の平均点が、男子の平均点より高いクラスを選択せよ。
|
1 2 3 4 5 6 7 8 9 10 |
SELECT class, COUNT(*) , -- ELSEの後に0とすると平均値がかわるのでやらないようにしましょう AVG( CASE WHEN sex = '男' THEN score ELSE NULL END ) AS man_avg, AVG( CASE WHEN sex = '女' THEN score ELSE NULL END ) AS woman_avg FROM testresults GROUP BY class HAVING AVG( CASE WHEN sex = '男' THEN score ELSE NULL END ) < AVG( CASE WHEN sex = '女' THEN score ELSE NULL END ); |
