第1回SQLコンテスト

練習

用意されたテーブルから要求されたビューになるようなSQLを書くコンテストがありました。
第4問は縦に並んだレコードを横にする問題で、`GROUP BY`と`SUM(CASE WHEN 条件1 then x ELSE 0 END)`を使えば解けました。

自分の回答
topsic-contest.jp

参加記

第1回SQLコンテストに参加しました。
第4問では、各グループの順位を取得するSQLを作りました。
Window関数を用いて各都道府県の順位カラムを作ったviewを作り、そこから1,2,3位のレコードのみを取得するというやり方で取得しました。

SELECT
    PF_CODE AS '都道府県コード',
    PF_NAME AS '都道府県名',
    MAX(CASE WHEN RANK = 1 THEN NATION_NAME ELSE '' END) AS '1位 国名',
    SUM(CASE WHEN RANK = 1 THEN AMT ELSE 0 END) AS '1位 人数',
    MAX(CASE WHEN RANK = 2 THEN NATION_NAME ELSE '' END) AS '2位 国名',
    SUM(CASE WHEN RANK = 2 THEN AMT ELSE 0 END) AS '2位 人数',
    MAX(CASE WHEN RANK = 3 THEN NATION_NAME ELSE '' END) AS '3位 国名',
    SUM(CASE WHEN RANK = 3 THEN AMT ELSE 0 END) AS '3位 人数',
    SUM(AMT) AS '合計人数'
FROM
(
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY f.PF_CODE ORDER BY AMT DESC) AS 'RANK'
    FROM
        FOREIGNER f
    INNER JOIN
        PREFECTURE p
    ON
        f.PF_CODE = p.PF_CODE
    INNER JOIN
        NATIONALITY n
    ON
        n.NATION_CODE = f.NATION_CODE
    WHERE
        NATION_NAME <> 'その他'
)
GROUP BY
    PF_CODE
ORDER BY
    SUM(AMT) DESC,
    PF_CODE;

topsic-contest.jp