Puzzle55:競走馬の入賞回数 - SQLパズル演習

問題

• 突然だが、あなたはたった今競馬のDBマネージャーとして雇われた。
• 胴元は、統計を取るためにレースの記録を残しており、その記録先のテーブルは次のとおりだ。

CREATE TABLE RacingResults
(track_id CHAR(3) NOT NULL,
 race_date DATE NOT NULL,
 race_nbr INTEGER NOT NULL,
 win_name CHAR(30) NOT NULL,
 place_name CHAR(30) NOT NULL,
 show_name CHAR(30) NOT NULL,
 PRIMARY KEY (track_id, race_date, race_nbr));

RacingResults:レース結果テーブル、track_id:トラックID、
race_date:レース日、race_nbr:レース番号、win_name:１着の馬名、
place_name:２着の馬名、show_name:３着の馬名

• ある日、雇い主の胴元がやってきて、「各馬がこれまで何回入賞したかを知りたい」と言ってきた。彼の望みを叶えてあげて欲しい。

chiakiの解答

考え方

• 馬の名前を全て取り出して、名前でグループ化したい。
• UNIONでは一部うまくいかなかったが、UNION ALLを使って馬の名前を取り出すとうまくいった。

SQL文

• 馬の名前の取り出し方

(SELECT win_name AS name, count(win_name) AS freq
 FROM RacingResults R
 GROUP BY R.win_name)
UNION ALL
(SELECT place_name AS name, count(place_name) AS freq
 FROM RacingResults R
 GROUP BY R.place_name)
UNION ALL
(SELECT show_name AS name, count(show_name) AS freq
 FROM RacingResults R
 GROUP BY R.show_name);

↓UNIONの結果

             name              | freq
-------------------------------+------
 A                             |    1
 A                             |    2
 B                             |    1
 C                             |    1     ← C が 1 なのはおかしい!!
 E                             |    1
 F                             |    1
 O                             |    1
 P                             |    1
 P                             |    2
 Q                             |    2

↓UNION ALLの結果

             name              | freq
-------------------------------+------
 B                             |    1
 E                             |    1
 O                             |    1
 A                             |    2
 B                             |    1
 F                             |    1
 C                             |    1
 P                             |    2
 A                             |    1
 C                             |    1
 P                             |    1
 Q                             |    2

• 上の結果を利用して求める結果を表示

SELECT name, sum(freq) AS freq
FROM
((SELECT win_name AS name, count(win_name) AS freq
  FROM RacingResults R
  GROUP BY R.win_name)
 UNION ALL
 (SELECT place_name AS name, count(place_name) AS freq
  FROM RacingResults R
  GROUP BY R.place_name)
 UNION ALL
 (SELECT show_name AS name, count(show_name) AS freq
  FROM RacingResults R
  GROUP BY R.show_name)) AS HName
GROUP BY name;

実行結果

              name              | freq
--------------------------------+------
 B                              |    2
 E                              |    1
 O                              |    1
 A                              |    3
 F                              |    1
 C                              |    2
 P                              |    3
 Q                              |    2

うまくいきました☆