「達人に学ぶSQL徹底指南書」の演習問題 1-2をブラウザで実行してみた

この記事の概要

• この記事は「達人に学ぶSQL徹底指南書/ミック著」の演習問題をブラウザのSQL実行環境(db-fiddle.com)で動かしてみたというものになります。
• 解説は書籍の方を参照してください。
• カラム名がアルファベット表記になっていることに注意してください

db-fiddle.comの使い方

• テーブルの作成: Scheme SQLの欄にCREATE TABLEを書いてテーブルを作成
• データの挿入: Query SQLの欄にINSERT文を書いてデモデータを挿入する
• データの検索: Query SQLの欄にSELCT文を書く

問題文

本文中のPopTbl2をサンプルに使って、行もちから列もちの水平展開の練習をもう少ししておきましょう。
今度は、次のように表頭に合計や再掲の列を持つようなクロス表を作ってください。

• 求めたい表

sex   zenkoku tokushima kagawa ehime kouti sikoku(saikei)
---   ------- --------- ------ ----- -----  --------------
men   855     60        100    100   100    360
women 845     40        100    50    100    290

• PopTbl2

pref_name	sex	population
tokushima	1	60
tokushima	2	40
kagawa	1	100
kagawa	2	100
ehime	1	100
ehime	2	50
kouti	1	100
kouti	2	100
hukuoka	1	100
hukuoka	2	200
saga	1	20
saga	2	80
nagasaki	1	125
nagasaki	2	125
tokyo	1	250
tokyo	2	150

ここでzenkokuというのは、tokyoなども含めたテーブルに存在するデータ全ての合計人口です
(足りない都道府県がたくさんありますが、気にしないでください)。
一方、右端の「sikoku(saikei)」は四国4県(tokushima, kagawa, ehime, kouti)の合計値です。

データセットの準備

• Schema SQL

CREATE TABLE PopTbl2 (
  pref_name varchar(255) NOT NULL,
  sex int NOT NUll,
  population int NOT NULL
);

• Query SQL

INSERT INTO PopTbl2 VALUES 
('tokushima', '1', '60'),
('tokushima', '2', '40'),
('kagawa', '1', '100'),
('kagawa', '2', '100'),
('ehime', '1', '100'),
('ehime', '2', '50'),
('kouti', '1', '100'),
('kouti', '2', '100'),
('hukuoka', '1', '100'),
('hukuoka', '2', '200'),
('saga', '1', '20'),
('saga', '2', '80'),
('nagasaki', '1', '125'),
('nagasaki', '2', '125'),
('tokyo', '1', '250'),
('tokyo', '2', '150');

解答

詳しい解説は書籍を参照してください。

SELECT sex,
       SUM(population) AS total,
       SUM(CASE WHEN pref_name = 'tokushima' THEN population ELSE 0 END) AS tokushima,
       SUM(CASE WHEN pref_name = 'kagawa' THEN population ELSE 0 END) AS kagawa,
       SUM(CASE WHEN pref_name = 'ehime' THEN population ELSE 0 END) AS ehime,
       SUM(CASE WHEN pref_name = 'kouti' THEN population ELSE 0 END) AS kouti, 
       SUM(CASE WHEN pref_name IN ('tokushima', 'kagawa', 'ehime', 'kouti') THEN population ELSE 0 END) AS saikei
FROM PopTbl2
GROUP BY sex;

参考

• 達人に学ぶSQL徹底指南書/ミック著