「TOPSIC SQL CONTEST」の練習コンテストにチャレンジ!

2023.12.19 株式会社システムインテグレータ

　今回の初心者訓練所#11は、以前のブログで「おすすめの学習コンテンツ」として紹介しておりました「TOPSIC SQL CONTEST（TSC）」の難易度1から4の全4問の練習用コンテスト問題にチャレンジしていきます。皆さんも、是非、「TSC」にログインして一緒にチャレンジしましょう。（会員登録がまだの方も、簡単に登録できます！）

問題１「都道府県の人口」

難易度1の問題です。シンプルな一覧出力の問題になります。

【問題】

sql11-01

【使用テーブル】

sql11-02

【解答例】

SELECT
DISTRICT_NAME AS 都道府県名 -- ①
, TOTAL_AMT AS 総人口
FROM POPULATION
WHERE LVL = 2 -- ②
ORDER BY TOTAL_AMT DESC; -- ③

【解説】（解答例のコメント番号に対応した説明になります）
① 表示項目は、AS句を使用して別名にしています。
② 階層= 2のデータが対象なので、WHERE句に条件を記載しています。
③ 総人口の降順に表示なので、ORDER BYにはDESCを指定しています。

【解答記入】

sql11-03

【コードテスト結果】

実際は、コードテスト結果に問題ないことを確認後に「提出」ボタンを押下することで採点されます。

問題２「世帯入院率」

難易度2ということで、テーブル結合を利用して２つのテーブルを結合し、小数点の計算を行う問題となっています。

【問題】

sql11-05

【使用テーブル】

sql11-06

ER図を見てみると、入院状況テーブルと都道府県テーブルが実線で繋がれているため、２つのテーブルは依存型リレーションであることを確認できます。入院状況テーブルの都道府県コードが（FK）外部キーとなっているので、都道府県テーブルが親で入院状況テーブルが子という親子関係となります。子テーブルの都道府県コードが親テーブルの都道府県コードの存在に依存していることがわかります。

【解答例】

SELECT
HP.PF_CODE AS 都道府県コード
, PF.PF_NAME AS 都道府県名
, ROUND( -- ①
CAST(HP.INP_YES AS REAL) / -- ②
CAST( (HP.INP_YES + HP.INP_NO + HP.UNIDENTIFIED) AS REAL )
* 100
, 1 -- ①
) AS 入院率
FROM
HOSPITALIZATION AS HP
INNER JOIN PREFECTURE AS PF -- ③
ON PF.PF_CODE = HP.PF_CODE
ORDER BY -- ④
入院率 DESC
, HP.PF_CODE ASC;

【解説】
① 入院率は、小数点第２位を四捨五入なのでROUND関数のパラメータに1を指定しています。
② 小数点の計算を行うため、INTEGER型（符号付整数）の計算項目をREAL型（不動小数点数）に変換して計算しています。項目を利用して計算する場合は、項目のデータ型に注意して計算を行ってください。
③ 都道府県名を取得するため、都道府県テーブルを内部結合しています。
④ 表示順は、降順と昇順が混在しているので注意してください。

【解答記入】

sql11-07

【コードテスト結果】

sql11-08

問題３「人口増加率分析」

難易度3となるこの問題は、先程と同様にテーブル結合を利用しますが、同じテーブルを結合（自己結合）して、調査年毎に総人口を取得しています。

【問題】
sql11-09
【使用テーブル】
sql11-10

【解答例】

SELECT
PO.PF_CODE AS 都道府県コード
, PF.PF_NAME AS 都道府県名
, PO.TOTAL_AMT AS 総人口2015年
, PO2020.TOTAL_AMT AS 総人口2020年
, ROUND( -- ①
CAST(PO2020.TOTAL_AMT AS REAL) -- ②
　　 / CAST(PO.TOTAL_AMT AS REAL) * 100
) AS 人口増加率
FROM
POPU_TRANSITION AS PO -- ③
INNER JOIN PREFECTURE AS PF -- ④
ON PF.PF_CODE = PO.PF_CODE
INNER JOIN POPU_TRANSITION AS PO2020 -- ⑤
ON PO2020.PF_CODE = PO.PF_CODE
AND PO2020.SURVEY_YEAR = 2020 -- ⑤
WHERE
PO.SURVEY_YEAR = 2015 -- ③
AND PO2020.TOTAL_AMT >= PO.TOTAL_AMT -- ⑥
ORDER BY -- ⑦
人口増加率 DESC
, 都道府県コード ASC;

【解説】
① 人口増加率の小数点以下は四捨五入なので、ROUND関数のパラメータは指定していません。
② 先程と同様に、小数点の計算を行うため、INTEGER型（符号付整数）の計算項目をREAL型（不動小数点数）に変換して計算しています。
③ メインテーブルで調査年が2015年のデータを取得しています。
④ 都道府県名を取得するため、内部結合で都道府県テーブルを結合しています。
⑤ もう一つの内部結合で、メインテーブルと同じ人口推移テーブルを結合して、調査年が2020年のデータを取得しています。どちらのテーブルの項目か判断できるように、AS句を使用して違う名称にしています。
⑥ 人口が増加した都道府県を抽出するために、WHERE句でメインテーブルから取得した2015年の総人口と内部結合で取得した2020年の総人口を比較しています。
⑦ 表示順は、降順と昇順が混ざっているので注意してください。

【解答記入】
sql11-11

【コードテスト結果】

sql11-12

問題４「年齢別睡眠時間分析」

それでは最後の問題、難易度4にチャレンジしてみましょう。問題に登場してくる睡眠時間詳細データのように縦持ちのデータを、表形式にする場合によく利用される方法なので、是非、覚えてください。

【問題】
sql11-13
【使用テーブル】
sql11-14

【解答例】

SELECT
AGE_GRP.AGE_NAME AS 年齢階層,
SUM( -- ④
　　　　CASE WHEN TIME_CODE = 120 -- ①
　　　　　　　THEN TARGET_POP
　　　　　　　ELSE 0 END
　　　　) AS "5時間未満", -- ②
SUM(CASE WHEN TIME_CODE = 130 THEN TARGET_POP ELSE 0 END)
　　　　AS "5時間以上6時間未満",
SUM(CASE WHEN TIME_CODE = 140 THEN TARGET_POP ELSE 0 END)
　　　　 AS "6時間以上7時間未満",
SUM(CASE WHEN TIME_CODE = 150 THEN TARGET_POP ELSE 0 END)
　　　　AS "7時間以上8時間未満",
SUM(CASE WHEN TIME_CODE = 160 THEN TARGET_POP ELSE 0 END)
　　　　AS "8時間以上9時間未満",
SUM(CASE WHEN TIME_CODE = 170 THEN TARGET_POP ELSE 0 END)
　　　　AS "9時間以上",
SUM(CASE WHEN TIME_CODE = 180 THEN TARGET_POP ELSE 0 END)
　　　　AS 不詳
FROM SLEEP_TIME_DTL
INNER JOIN AGE_GRP -- ③
　　ON AGE_GRP.AGE_CODE = SLEEP_TIME_DTL.AGE_CODE
GROUP BY AGE_GRP.AGE_CODE -- ④
ORDER BY AGE_GRP.AGE_CODE;

【解説】
① CASE句を用いて時間コードの値で、対象人数を対応するカラムに振分けます。時間コードが条件外の場合はゼロをセットしています。
② AS句で変更している表示項目名の先頭が数字の場合は、ダブルクォーテーションで項目名を囲む必要がありますので注意しましょう。
③ 年齢階層名を取得するために、年齢階層テーブルを内部結合しています。
④ 年齢コードでグルーピングを行い、振分けた値をサマリして表示します。

【解答記入】
sql11-15
【コードテスト結果】

sql11-16

上記の解説ではちょっと分りにくいと思いますので、下記のSELECT文でグルーピングする前の状態を見てみましょう。（SUM関数とGROUP BY句が無い状態です）

【グルーピング前】
sql11-17

【サンプルデータ】
sql11-18

【グルーピング前実行結果】
sql11-19

睡眠時間詳細データの１レコード毎の対象人数が、年齢階層と睡眠時間に振り分けられていることが確認できます。この結果を年齢階層で集計することにより、最終的に表示フォーマット例のように表形式で表示することができます。それほど難しい関数等を使用していないので、実装方法さえ覚えてしまえば色々と活用できると思います。

解説は以上となります。

あとがき

初心者訓練所の第11弾となる今回は、「TSC」の練習コンテスト問題に挑戦してみました。皆さんはどうでしたか？４問目はちょっと難しかったかも知れません。でも、これで同じタイプの問題にも対応できるようになったと思います。「TSC」は、会員になると過去のコンテスト問題にも挑戦できます。さらにスキルを磨くチャンスですので、ぜひ、トライしてみてください。

(株)システムインテグレータ　インキュベーション部　元鬼軍曹　久保　司