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

近況報告

最近はあまり SQL に触れられていないですが、いくつか初心者用の SQL コンテストがあったので、それで少しリハビリをして臨んでいます。

コンテストの振り返り

問題 1

名前の 2 文字目が田であるような行だけ抽出する問題です。これに対しては、SUBSTR 関数を使って一致判定すればよいです。

開始約 1 分後に AC。

SELECT
    MEMBER_CODE AS CODE,
    LAST_NAME AS L_NAME,
    FIRST_NAME AS F_NAME
FROM
    MEMBER_MST
WHERE
    SUBSTR(LAST_NAME, 2, 1) = '田'
ORDER BY
    MEMBER_CODE DESC

問題 2

ITEM テーブルにあるデータについて、同じデータが ITEM_HISTORY テーブルにも存在するか、また存在するならば名前が変わっているかを判定する問題です。2 つの表を結合したいですが、ITEM テーブルにのみ含まれる情報も表示したいので、INNER JOIN ではなく LEFT OUTER JOIN を使うのが適切です。

開始約 5 分後に AC。この位置の問題としてはいつもより簡単な印象を受けました。

SELECT
    ITEM.ITEM_CODE AS CODE,
    CASE
        WHEN ITEM_HISTORY.ITEM_NAME IS NULL THEN ITEM.ITEM_NAME
        WHEN ITEM.ITEM_NAME != ITEM_HISTORY.ITEM_NAME THEN ITEM.ITEM_NAME
        ELSE NULL
    END AS NAME
FROM
    ITEM
LEFT OUTER JOIN
    ITEM_HISTORY ON ITEM.ITEM_CODE = ITEM_HISTORY.ITEM_CODE
ORDER BY
    CODE DESC

問題 3

LINE_ID と SEAT_NO の組に対して 0 か 1 が割り当てられているデータに対して、LINE_ID が同じかつ SEAT_NO が 3 連続している箇所であって、全てに 0 が割り当てられているものを列挙するという問題です。各 LINE_ID, SEAT_NO の組 $(x,y)$ に対して、LINE_ID が同じ $x$ で、SEAT_NO が $y$ 以上 $y+2$ 以下の座席に対する 0 の個数を求めるというアプローチを取りました。この値が 3 であることが、 $(x,y),(x,y+1),(x,y+2)$ 全てに 0 が割り当てられていることの必要十分条件となります。また、ウィンドウ関数を用いることによって、簡単かつ効率的にこの値を求めることができます。

開始約 12 分後に AC。いい方針をすぐに思いつけたので素早く解くことができました。また、私自身が書いた記事が自分の役に立ったので良かったです。

WITH T1 AS(
    SELECT
        LINE_ID,
        SEAT_NO,
        SUM(1 - RSV_STATUS) OVER(
            PARTITION BY LINE_ID
            ORDER BY SEAT_NO
            ROWS BETWEEN 0 FOLLOWING AND 2 FOLLOWING
        ) AS CNT
    FROM
        SEAT_RESERVE
), T2 AS(
    SELECT
        LINE_ID AS LINE,
        SEAT_NO AS SEAT_F,
        SEAT_NO + 2 AS SEAT_T
    FROM
        T1
    WHERE
        CNT = 3
    ORDER BY
        LINE DESC,
        SEAT_F ASC
)
SELECT
    *
FROM
    T2

問題 4

同じ人に対するデータが 2 つ以上あるものに対して、直近 2 つのデータの差分を計算するという問題です。これはかなり素直な問題で、RANK 関数を用いて直近 2 つのデータを抽出し、さらに前問同様にウィンドウ関数を使って 2 つの差分を計算することができます。

開始約 12 分後に AC。最終問題としてはかなり容易でしたが、問題文の読み取りとエラー処理に時間がかかった影響で出遅れてしまいました。

WITH T1 AS(
    SELECT
        MEMBER_CODE AS CODE,
        CHECKUP_DATE AS CK_DATE,
        WEIGHT,
        RANK() OVER(
            PARTITION BY MEMBER_CODE
            ORDER BY CHECKUP_DATE DESC
        ) AS RANK,
        COUNT() OVER(
            PARTITION BY MEMBER_CODE
        ) AS CNT
    FROM
        HEALTH_CHECKUP
), T2 AS(
    SELECT
        CODE,
        CK_DATE,
        RANK,
        WEIGHT - SUM(WEIGHT) OVER(
            PARTITION BY CODE
            ORDER BY RANK ASC
            ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING
        ) AS CHG_WT
    FROM
        T1
), T3 AS(
    SELECT
        CK_DATE,
        T2.CODE,
        LAST_NAME || FIRST_NAME AS NAME,
        ROUND(CHG_WT, 1) AS CHG_WT
    FROM
        T2
    INNER JOIN
        MEMBER_MST
            ON T2.CODE = MEMBER_MST.MEMBER_CODE
    WHERE
        RANK = 1
        AND ABS(CHG_WT) >= 4.999 
    ORDER BY
        CHG_WT DESC,
        T2.CODE DESC
)
SELECT
    *
FROM
    T3