第11回 SQLコンテスト (2024/02/19)

SQL

第 11 回 SQL コンテストに参加しました。最終結果は 4 完 100 点で 6 位でした。

最終結果

近況報告

例によって SQL を書くのはほぼ 2 ヶ月振りになります。前回からの進歩としては SQL の本を一冊概ね読んだので、ウィンドウ関数などの文法や SQL の思想についての理解が少し上がったという点があります。

コンテストの振り返り

問題 1

1 問目から表示ではなく削除を要求される問題で意表を突かれました。基本的な DELETE 文を使えるかが問われている問題ですが、日付範囲を 2024-01-01 から 2024-01-03 までと読み違えて 1 回 WA を出してしまいました。

開始約 3 分後に AC。幸先の悪いスタートとなりました。

DELETE
FROM
    STOCK
WHERE
    LAST_DELIVERY_DATE BETWEEN '2023-01-01' AND '2023-01-31'
    AND ACTUAL_AMT = 0

問題 2

こちらはメールアドレスの '@' 以降から '.co.jp' を除いた部分を表示することに主眼を置いた問題です。SUBSTR 関数を使って部分文字列を抽出したいですが、'@' が何番目にあるかがわからないと使えません。調べてみると、INSTR 関数で部分文字列の位置を検索できることがわかりました。具体的には、文字列 S,T について INSTR(S,T) は S[i:i+|T|] = T となる最小の i（存在しなければ 0）を出力します。今回の場合は INSTR(EMAIL, '@') で '@' の index を取得できます。'@' がもし複数あったら面倒なことになっていましたが、出してみると無事に通って安心しました。

開始約 12 分後に AC。

SELECT
    MEMBER_CODE AS MEMBER,
    EMAIL AS EMAIL,
    SUBSTR(EMAIL, INSTR(EMAIL, '@') + 1, LENGTH(EMAIL) - INSTR(EMAIL, '@') - 6) AS DOMAIN
FROM
    MEMBER_MST
ORDER BY
    DOMAIN DESC,
    MEMBER DESC

問題 3

各月の総売上を計算し、直近 12 ヶ月の総和と年内の総和を計算する問題です。累積和の問題なので、ウィンドウ関数が効力を発揮します。まず 2023 年以外のデータも残したまま直近 12 ヶ月の和を SUM(MONTH_AMT) OVER(ORDER BY YEAR_MONTH ASC ROWS BETWEEN 11 PRECEDING AND 0 PRECEDING) で計算し、その後 2023 年のデータのみを残して今度は SUM(MONTH_AMT) OVER(ORDER BY YEAR_MONTH ASC) でシンプルに累積和を計算します。

サクッと書けて開始約 26 分後に AC。私が先月書いたウィンドウ関数の記事が参考になりました。

WITH T1 AS(
    SELECT
        SUBSTR(SALES_DATE, 1, 7) AS YEAR_MONTH,
        SUM(SALES_AMT) AS MONTH_AMT
    FROM
        SALES
    WHERE
        UPDATED_NO IS NULL
    GROUP BY
        SUBSTR(SALES_DATE, 1, 7)
), T2 AS(
    SELECT
        YEAR_MONTH,
        MONTH_AMT,
        SUM(MONTH_AMT) OVER(
            ORDER BY YEAR_MONTH ASC
            ROWS BETWEEN 11 PRECEDING AND 0 PRECEDING
        ) AS YEAR_MOVE_AMT
    FROM
        T1
), T3 AS(
    SELECT
        *
        FROM T2
    WHERE
        YEAR_MONTH BETWEEN '2023-01' AND '2023-12'
), T4 AS(
    SELECT
        YEAR_MONTH,
        MONTH_AMT,
        SUM(MONTH_AMT) OVER(
            ORDER BY YEAR_MONTH ASC
        ) AS CUML_AMT,
        YEAR_MOVE_AMT
    FROM T3
    ORDER BY YEAR_MONTH
)
SELECT
    *
    FROM T4    WITH T1 AS(
    SELECT
        SUBSTR(SALES_DATE, 1, 7) AS YEAR_MONTH,
        SUM(SALES_AMT) AS MONTH_AMT
    FROM
        SALES
    WHERE
        UPDATED_NO IS NULL
    GROUP BY
        SUBSTR(SALES_DATE, 1, 7)
), T2 AS(
    SELECT
        YEAR_MONTH,
        MONTH_AMT,
        SUM(MONTH_AMT) OVER(
            ORDER BY YEAR_MONTH ASC
            ROWS BETWEEN 11 PRECEDING AND 0 PRECEDING
        ) AS YEAR_MOVE_AMT
    FROM
        T1
), T3 AS(
    SELECT
        *
        FROM T2
    WHERE
        YEAR_MONTH BETWEEN '2023-01' AND '2023-12'
), T4 AS(
    SELECT
        YEAR_MONTH,
        MONTH_AMT,
        SUM(MONTH_AMT) OVER(
            ORDER BY YEAR_MONTH ASC
        ) AS CUML_AMT,
        YEAR_MOVE_AMT
    FROM T3
    ORDER BY YEAR_MONTH
)
SELECT
    *
    FROM T4

問題 4

ここまで特にエラーに苦しむこともなく、実際 3 問目までのスピードは過去最速だったので全完のチャンスだと感じていました。問題文は長くてかなりわかりにくいですが、全ての商品の組 (A,B) について

  • A,B を両方買った人数
  • A を買った人数
  • B を買った人数
  • 全体の人数

がわかれば求めたい値を全て計算できます。まず商品の組を列挙しなければいけませんが、これには交差結合 (cross join) が使えます。さらに、交差結合した後に 2 つの SESSION_ID が一致する行のみを残し、DISTINCT 文を使うことで A,B を両方買った人数を求めることができます。同じく DISTINCT 文を使うことで、各商品を買った人数、全体の人数を求めることもできるので、これらの結果を内部結合 (inner join)、外部結合 (outer join) を駆使して商品の組の表に適切に組み込むことで、欲しかった 4 つの値を全てまとめることができます。

実装は少し長くなりましたが開始約 52 分後に AC。余裕を持って正解することができました。

WITH T1 AS(
    SELECT
        COUNT(DISTINCT SESSION_ID) AS CNT_ALL
    FROM
        PURCHASE_HISTORY
), T2 AS(
    SELECT
        *,
        COUNT(DISTINCT SESSION_ID) AS CNT_A
    FROM
        PURCHASE_HISTORY
    GROUP BY
        ITEM_CODE
), T3 AS(
    SELECT
        P1.ITEM_CODE AS ITEM_A,
        P2.ITEM_CODE AS ITEM_B,
        COUNT(*) AS CNT_AB
    FROM
        PURCHASE_HISTORY AS P1
    CROSS JOIN
        PURCHASE_HISTORY AS P2
    WHERE
        P1.SESSION_ID = P2.SESSION_ID
    GROUP BY
        P1.ITEM_CODE,
        P2.ITEM_CODE
), T4 AS(
    SELECT
        ITEM_A,
        ITEM_B,
        CNT_AB,
        TA.CNT_A AS CNT_A,
        TB.CNT_A AS CNT_B
    FROM
        T3
    INNER JOIN
        T2 AS TA ON ITEM_A = TA.ITEM_CODE,
        T2 AS TB ON ITEM_B = TB.ITEM_CODE
    WHERE
        ITEM_A != ITEM_B
), T5 AS(
    SELECT
        *
    FROM T4
    CROSS JOIN
        T1
), T6 AS(
    SELECT
        ITEM_A,
        ITEM_B,
        ROUND(100.0 * CNT_AB / CNT_ALL, 5) AS SUPPORT,
        ROUND(100.0 * CNT_AB / CNT_A, 5) AS CONFIDENCE,
        ROUND(1.0 * CNT_AB * CNT_ALL / (1.0 * CNT_A * CNT_B), 5) AS LIFT
    FROM
        T5
    ORDER BY
        LIFT DESC,
        SUPPORT DESC,
        CONFIDENCE DESC,
        ITEM_A DESC
)
SELECT
    *
    FROM T6

感想

初全完でかなり嬉しいです。次もまた頑張ります。