第 8 回 SQL コンテストに参加しました。

SQL コンテストとは？

PG BATTLE で使われる TOPSIC で開催されている SQL 界の 4 問 ABC みたいなコンテストです。7 月に tabr くんから存在を聞いて、それ以降少しずつ過去問を埋めたりして勉強しています。コンテストは 8/18(金) 12:00 から 8/21(月) 12:00 の間のうち、任意の 1 時間で参加できるという形式でした。

現状の実力

コンテスト開始前までに過去 9 回のコンテストの 1,2,3 問目の合計 27 問目を解説 AC も込みで埋めました。1,2 問目は余裕を持って解けて、3 問目は時間を書ければ解けることも多いという感じです。やりたい操作をどうコードとして書けばいいのかすぐに思い起こせないことがまだ多いです。

コンテストの振り返り

問題 1

これは簡単です。WHERE 句を使ってあげれば良いです。すぐに AC できました。

SELECT
    MEMBER_ID AS ID,
    SCORE1 - SCORE2 AS DIFF
FROM
    CONTEST_RESULTS
WHERE
    ABS(SCORE1 - SCORE2) >= 20
ORDER BY
    DIFF DESC,
    ID DESC

問題 2

一見 SUM を使うだけに見えますが、同一日の注文は 1 件とカウントするというところが面倒です。本番では WITH 構文を使って強引に通しましたが、解説を見ると DISTINCT を使えば簡単に書けるようです。

WITH TMP_TABLE AS(
    SELECT
        ORDERS.CUST_CODE AS CODE,
        ORDER_DATE AS DATE,
        CUST_NAME AS NAME
    FROM
        ORDERS
        INNER JOIN
            CUSTOMER ON CUSTOMER.CUST_CODE = ORDERS.CUST_CODE
    WHERE
        ORDER_DATE BETWEEN '2023-07-01' AND '2023-07-31'
    GROUP BY
        ORDERS.CUST_CODE,
        ORDER_DATE
)
SELECT
    CODE,
    NAME,
    COUNT(*) AS CNT
FROM
    TMP_TABLE
GROUP BY
    CODE
ORDER BY
    CNT DESC,
    CODE DESC

問題 3

まずはグループごとに平均を計算して、平均以上の人数の平均を取ればよいです。コンテスト本番のときは、平均以上の人数の割合を計算するのに、(平均以上の人数) / (全体の人数) で計算する必要があるかと思っていたのですが、普通に AVG 関数を使ってあげれば良かったです。CASE 句や複数のキーによる INNER JOIN を書いたことがなかったので、調べたりしながらなんとかコンテスト 10 分前くらいにはサンプルの数値が合うところまでいけました。しかし、このあと GENDER を数値から文字列に変更するのと、パーセンテージを小数第 1 位まで表示して % をつけないといけません。まず、GENDER の方については先程と同じく CASE 句を使えば良いということに 5 分程度で気づきます。次に、パーセンテージの方ですが、数値を VARCHAR 型に CAST して +'%' するコードを書いていたものの % は表示されず、ネットで検索したものを試してみても解決できず、ここで時間切れとなりました。コンテスト後に SQLite の構文表示を見たところ、文字列の結合演算子は + ではなく || であることを知りました。最後に付け忘れていた WHERE 句を付けるなどして無事 AC となりました。

WITH TMP_TABLE AS(
    SELECT
        AGE,
        GENDER_CODE,
        SUM(
            CASE CATEGORY_CODE
                WHEN '10' THEN AVERAGE_VALUE
                ELSE 0
            END
        ) AS H_SUM,
        SUM(
            CASE CATEGORY_CODE
                WHEN '10' THEN 1
                ELSE 0
            END
        ) AS H_CNT,
        SUM(
            CASE CATEGORY_CODE
                WHEN '20' THEN AVERAGE_VALUE
                ELSE 0
            END
        ) AS W_SUM,
        SUM(
            CASE CATEGORY_CODE
                WHEN '20' THEN 1
                ELSE 0
            END
        ) AS W_CNT
    FROM
        SCHOOL_HEALTH
    WHERE
        SURVEY_YEAR = '2019'
    GROUP BY
        AGE,
        GENDER_CODE
)
SELECT
    SCHOOL_HEALTH.AGE,
    CASE SCHOOL_HEALTH.GENDER_CODE
        WHEN '20' THEN 'MALE'
        ELSE 'FEMALE'
    END AS GENDER,
    ROUND(H_SUM / CAST(H_CNT AS FLOAT), 1) AS H_AVG,
    CAST(
        ROUND(
            SUM(
                CASE
                    WHEN CATEGORY_CODE = '10' AND AVERAGE_VALUE >= ROUND(H_SUM / CAST(H_CNT AS FLOAT), 1) THEN 1
                    ELSE 0
                END
            ) * 100.0  / (H_CNT * 1.0), 1
        ) AS VARCHAR
    ) || '%' AS H_PER,
    ROUND(W_SUM / CAST(W_CNT AS FLOAT), 1) AS W_AVG,
    CAST(
        ROUND(
            SUM(
                CASE
                    WHEN CATEGORY_CODE = '20' AND AVERAGE_VALUE >= ROUND(W_SUM / CAST(W_CNT AS FLOAT), 1) THEN 1
                    ELSE 0
                END
            ) * 100.0  / (W_CNT * 1.0), 1
        ) AS VARCHAR
    ) || '%' AS W_PER
FROM
    SCHOOL_HEALTH
    INNER JOIN
        TMP_TABLE
        ON TMP_TABLE.AGE = SCHOOL_HEALTH.AGE
        AND TMP_TABLE.GENDER_CODE = SCHOOL_HEALTH.GENDER_CODE
WHERE
    SURVEY_YEAR = '2019'
GROUP BY
    SCHOOL_HEALTH.AGE,
    SCHOOL_HEALTH.GENDER_CODE
ORDER BY
    SCHOOL_HEALTH.AGE DESC,
    SCHOOL_HEALTH.GENDER_CODE DESC

問題 4

開いていません。また今度やろうと思います。

結果

14:32 で問題 1,2 の 2 完で 166 人中 48 位でした。

tabr くんは全完で優勝していたので実力差を見せつけられる形となりました。

感想

問題 3 はほぼ正解のところまで行っていたので、間に合わなくて悔しいです。ただ、CASE 句や複数キーの INNER JOIN、文字列の結合方法など、SQLの文法に関して新たな知識を多く身に着けることができたのは良かったです。タイピングが遅いのとデバッグの手間があり、コンテスト中はかなり時間が経つのが早いという印象があったので、そのあたりも改善していきたいです。

まとめ

次回は 3 完以上します。

トークサクライ

第8回 SQLコンテスト (2023/08/19)