実践! 「TOPSIC SQL CONTEST」の練習問題にチャレンジしよう

データベース

技術解説

連載 [第10回] :

初心者のための SQL-BOOTCAMP

2023年7月25日(火)

久保司

最終回となる今回は、これまでの訓練の成果を計るために、SQLの腕試しができるオンラインコンテスト「TOPSIC SQL CONTEST(TSC)」の練習問題にチャレンジしてみましょう。

人口増加率分析

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

【問題】

【使用テーブル】

【解答例】

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年のデータを取得しています
④都道府県名を取得するため、内部結合で都道府県テーブルを結合しています
⑤もう1つの内部結合で、メインテーブルと同じ人口推移テーブルを結合して調査年が2020年のデータを取得しています。どちらのテーブルの項目か判断できるように、AS句を使用して違う名称にしています
⑥人口が増加した都道府県を抽出するために、WHERE句でメインテーブルから取得した2015年の総人口と内部結合で取得した2020年の総人口を比較しています
⑦表示順は、降順と昇順が混ざっているので注意してください

【解答記入】

【コードテスト結果】

年齢別睡眠時間分析

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

【問題】

【使用テーブル】

【解答例】

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句で変更している表示項目名の先頭が数字の場合は、ダブルクォーテーションで項目名を囲む必要があるので注意しましょう
③年齢階層名を取得するために、年齢階層テーブルを内部結合しています。
④年齢コードでグルーピングを行い、振分けた値をサマリして表示します

【解答記入】

【コードテスト結果】

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

SELECT
    AGE_GRP.AGE_NAME AS 年齢階層,
(CASE WHEN TIME_CODE = 120
THEN TARGET_POP
ELSE 0 END
) AS "5時間未満",
    (CASE WHEN TIME_CODE = 130 THEN TARGET_POP ELSE 0 END)
 AS "5時間以上6時間未満",
    (CASE WHEN TIME_CODE = 140 THEN TARGET_POP ELSE 0 END)
 AS "6時間以上7時間未満",
    (CASE WHEN TIME_CODE = 150 THEN TARGET_POP ELSE 0 END)
 AS "7時間以上8時間未満",
    (CASE WHEN TIME_CODE = 160 THEN TARGET_POP ELSE 0 END)
 AS "8時間以上9時間未満",
    (CASE WHEN TIME_CODE = 170 THEN TARGET_POP ELSE 0 END)
 AS "9時間以上",
    (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
ORDER BY AGE_GRP.AGE_CODE;

【グルーピング前】