第 10 回 SQL コンテストに参加しました。最終結果は 3 完 60 点で 10 位でした。

近況報告

10 月以降はほとんど何もしてなかったので、結構忘れている部分が多いです。2 ヶ月に 1 回というペースだと、ちょうど忘れた頃にやってくるので厳しいところはありますが、定期的に復習できるという面では良いかもしれません。

コンテストの振り返り

問題 1

文字列から _ を取り除くことができればよいです。これを実現できる関数がありそうなので、調べてみると TRIM 関数でできることがわかります。開始 4 分後に AC。

SELECT
    EMP_CODE AS CODE,
    "'" || TRIM(EMP_KANA_NAME, '_') || "'" AS KANA_NAME
FROM
    EMP
ORDER BY
    EMP_CODE DESC

問題 2

受注件数と受注金額の合計を GROUP BY で求めれば良いです。上位 5 件のみを表示するという部分が面倒ですが、順位付けをして 5 位以上のみを残すという方法を取りました。開始 17 分後に AC。2 問目という位置にしては要求される事項が多いと思いました。

後で知りましたが LIMIT 5 を最後につけることで上位 5 件のみを表示できるようです。

WITH T AS(
    SELECT
        ORDERS.CUST_CODE AS CODE,
        CUST_NAME AS NAME,
        COUNT(*) AS CNT,
        SUM(ORDER_AMNT) AS TTL_AMT,
        ROUND(1.00 * SUM(ORDER_AMNT) / COUNT(*)) AS AVG_AMT,
        RANK() OVER(ORDER BY
            COUNT(*) DESC,
            ROUND(1.00 * SUM(ORDER_AMNT) / COUNT(*)) DESC,
            ORDERS.CUST_CODE ASC
        ) AS RNK
    FROM
        ORDERS
        INNER JOIN
            CUSTOMER ON ORDERS.CUST_CODE = CUSTOMER.CUST_CODE
    WHERE
        ORDER_DATE BETWEEN '2023-09-01' AND '2023-09-30'
    GROUP BY
        ORDERS.CUST_CODE
    ORDER BY
        CNT DESC,
        AVG_AMT DESC,
        CODE ASC
)
SELECT
    CODE,
    NAME,
    CNT,
    TTL_AMT,
    AVG_AMT
FROM
    T
WHERE
    CNT >= 5 AND RNK <= 5

問題 3

全ての年の情報が含まれるテーブルから、各年のデータを別々の列に表示するというタイプの問題です。こういうときは CASE 句が非常に便利です。順位は欠番なしなので DENSE_RANK を使わなければいけない点に要注意。コピペと修正を繰り返すのがかなり面倒でしたが、割と素直な問題だったので開始 33 分後に AC。

WITH T AS(
    SELECT
        HOUSEHOLD_SURVEY.AREA_CODE AS CODE,
        AREA.AREA_NAME AS NAME,
        ROUND(100.0 * SUM(
            CASE SURVEY_YEAR
                WHEN 2022 THEN FOOD_EXP
                ELSE 0
            END
        ) / SUM(
            CASE SURVEY_YEAR
                WHEN 2022 THEN CONSUMPTION_EXP
                ELSE 0
            END
        ), 1) AS "2022_RATIO",
        ROUND(100.0 * SUM(
            CASE SURVEY_YEAR
                WHEN 2017 THEN FOOD_EXP
                ELSE 0
            END
        ) / SUM(
            CASE SURVEY_YEAR
                WHEN 2017 THEN CONSUMPTION_EXP
                ELSE 0
            END
        ), 1) AS "2017_RATIO",
        ROUND(100.0 * SUM(
            CASE SURVEY_YEAR
                WHEN 2012 THEN FOOD_EXP
                ELSE 0
            END
        ) / SUM(
            CASE SURVEY_YEAR
                WHEN 2012 THEN CONSUMPTION_EXP
                ELSE 0
            END
        ), 1) AS "2012_RATIO"
    FROM
        HOUSEHOLD_SURVEY
        INNER JOIN
            AREA ON HOUSEHOLD_SURVEY.AREA_CODE = AREA.AREA_CODE
    GROUP BY
        HOUSEHOLD_SURVEY.AREA_CODE
)
SELECT
    CODE,
    NAME,
    DENSE_RANK() OVER(ORDER BY "2022_RATIO" ASC) AS "2022_RANK",
    "2022_RATIO" || '%' AS "2022_RATIO",
    DENSE_RANK() OVER(ORDER BY "2017_RATIO" ASC) AS "2017_RANK",
    "2017_RATIO" || '%' AS "2017_RATIO",
    DENSE_RANK() OVER(ORDER BY "2012_RATIO" ASC) AS "2012_RANK",
    "2012_RATIO" || '%' AS "2012_RATIO"
FROM
    T
ORDER BY
    "2022_RANK" ASC,
    CODE DESC

問題 4

3 問終わって残り 26 分だったので、上手くいけば全完できそうかという状況です。

問題としては、各 SESSION_ID について EX_TIMESTAMP でソートした後に、STEP1, STEP2,..., STEP5 がこの順に最大で何個並ぶかを求められればよいです。この問題に対して、以下のようなアルゴリズムを考えました。

SESSION_ID と PROCESS_ID の組であって、自身より番号が小さい全ての STEP と比べて EX_TIMESTAMP が大きく、自身より番号が大きい全ての STEP と比べて EX_TIMESTAMP が小さいもののみを残す。
SESSION_ID と PROCESS_ID の組であって、自身より番号が小さい全ての STEP がテーブルに含まれているもののみを残す。

この考察自体は正しかったのですが、正当性がそれほど明らかではないのと、実装に手こずったので時間内には間に合いませんでした。問題文に明記されていものの、答えが 0 になる STEP も表示しないといけないという制約があり、そこをどうするかはすぐには考え付きませんでした。最終的には全て 0 の表を作ってから外部結合するという手法を取りました。

WITH T1 AS(
    SELECT
        SESSION_ID,
        CAST(SUBSTR(PROCESS_ID, -1) AS INTEGER) AS PROCESS_ID,
        EX_TIMESTAMP,
        MAX(EX_TIMESTAMP) OVER(
            PARTITION BY SESSION_ID
            ORDER BY PROCESS_ID ASC
        ) AS PRE_TIME,
        MIN(EX_TIMESTAMP) OVER(
            PARTITION BY SESSION_ID
            ORDER BY PROCESS_ID DESC
        ) AS NEXT_TIME
    FROM
        PROCESS_LOG
),
T2 AS(
    SELECT
        SESSION_ID,
        PROCESS_ID,
        COUNT(*) OVER(
            PARTITION BY SESSION_ID
            ORDER BY PROCESS_ID ASC
        ) AS PRE_CNT
    FROM
        T1
    WHERE
        EX_TIMESTAMP = PRE_TIME
        AND EX_TIMESTAMP = NEXT_TIME
),
T3 AS(
    SELECT
        MAX(PROCESS_ID) AS PROCESS_ID,
        COUNT(*) AS CNT
    FROM
        T2
    WHERE
        PROCESS_ID = PRE_CNT
    GROUP BY
        PROCESS_ID
),
T4 AS(
    SELECT
        1 AS PROCESS_ID
    UNION ALL
        SELECT
            PROCESS_ID + 1 AS PROCESS_ID
        FROM T4
        WHERE
            PROCESS_ID < 5
)
SELECT
    'STEP' || T4.PROCESS_ID AS PROCESS,
    CASE
        WHEN T3.CNT IS NULL THEN 0
        ELSE T3.CNT
    END AS CNT
FROM
    T4
    LEFT OUTER JOIN
        T3 ON T4.PROCESS_ID = T3.PROCESS_ID

感想

実装力が足りない。。。

トークサクライ

第10回 SQLコンテスト (2023/12/16)