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

近況報告

最近はデータベースを使って AOJ の提出検索をするアプリを作っているのですが、API でいつ提出データを取得するか (一括で取得するか、クエリが飛んできたタイミングで取得するか) の設計で悩んでいます。余談ですが、かっつくんが平日の日中に投げまくってるのを観測できて面白いです。

コンテストの振り返り

問題 1

データベースに新たに行を挿入する問題です。INSERT 句を使えばよいですが、列がたくさんあるのと追加行が 2 つあるので結構面倒です。

列名と値を 1 個ずつ写経して開始約 5 分後に AC。

INSERT
    INTO DEPARTMENT
    (
        DEPT_CODE,
        START_DATE,
        END_DATE,
        DEPT_NAME,
        LAYER,
        UP_DEPT_CODE,
        VALID_FLG,
        UPDATE_USER_NAME,
        USER_UPDATE_DATETIME
    )
    VALUES
    (
        '1100',
        '2024-04-01',
        NULL,
        '営業1課',
        2,
        '1000',
        '1',
        'TANAKA',
        '2024-04-01 00:00:00'
    ),
    (
        '2200',
        '2024-04-10',
        NULL,
        '製造2課',
        2,
        '2000',
        '1',
        'YAMADA',
        '2024-04-10 00:00:00'
    )

問題 2

文字列から空白を取り除き、さらにその先頭 10 文字を取得する問題です。前者は REPLACE 関数を、後者は SUBSTR 関数を使うことで実現できます。また、値が NULL のときの場合分けは CASE 句や IFNULL 関数を使うことでできます。

開始約 11 分後に AC。実装は問題 1 よりも軽かったです。

SELECT
    EMP_CODE AS CODE,
    EMP_LAST_NAME || EMP_FIRST_NAME AS NAME,
    EMP_ENG_NAME AS ENG_NAME,
    IFNULL(SUBSTR(REPLACE(EMP_ENG_NAME, ' ', ''), 1, 10), '未入力') AS CUTOUT_STR
FROM
    EMP
ORDER BY
    CUTOUT_STR DESC,
    CODE DESC

問題 3

2 つのテーブル ITEM と ITEM_HISTORY を比較し、どちらかのテーブルにだけある行や、両方にあるが変更されている行を検出する問題です。2 つのテーブルを ITEM_CODE をキーとして外部結合したいですが、SQLite では左外部結合しかできないので、ITEM に ITEM_HISTORY を左外部結合、ITEM_HISTORY に ITEM を左外部結合の 2 通りを試し、それぞれの結果を UNION 句で結合させればよいです。

開始約 26 分後に AC。ここもノーペナで乗り切ることができました。

WITH T1 AS(
    SELECT
        ITEM.ITEM_CODE AS CODE,
        ITEM.ITEM_NAME AS NAME,
        CASE
            WHEN ITEM_HISTORY.USER_UPDATE_DATETIME IS NULL THEN 'ADDED'
            WHEN ITEM_HISTORY.USER_UPDATE_DATETIME != ITEM.USER_UPDATE_DATETIME THEN 'UPDATED'
            ELSE 'UNCHANGED'
        END AS COMP_RSLT
    FROM
        ITEM
    LEFT OUTER JOIN
        ITEM_HISTORY ON ITEM.ITEM_CODE = ITEM_HISTORY.ITEM_CODE
    WHERE
        COMP_RSLT != 'UNCHANGED'
), T2 AS(
    SELECT
        ITEM_HISTORY.ITEM_CODE AS CODE,
        ITEM_HISTORY.ITEM_NAME AS NAME,
        CASE
            WHEN ITEM.USER_UPDATE_DATETIME IS NULL THEN 'DELETED'
            ELSE 'UNCHANGED'
        END AS COMP_RSLT
    FROM
        ITEM_HISTORY
    LEFT OUTER JOIN
        ITEM ON ITEM.ITEM_CODE = ITEM_HISTORY.ITEM_CODE
    WHERE
        COMP_RSLT != 'UNCHANGED'
), T3 AS(
    SELECT
        *
    FROM
        T1
    UNION ALL
    SELECT
        *
    FROM
        T2
    ORDER BY
        CODE DESC
)
SELECT
    *
FROM
    T3

問題 4

2024-03-01 から 2024-03-28 の 4 週間分のデータを曜日ごとに集計するという問題です。初動でカレンダーを調べた結果、2024-03-01 が水曜日であることがわかったので、まずは日付から曜日の数値を (日曜が 0、月曜が 1 というように) 算出して、曜日ごとに件数と売上の総和を計算しました。ここで問題となるのが件数が 0 の曜日も表示しなければならないという点ですが、0 から 6 の値のみからなる別のテーブルに左外部結合し、IFNULL 関数を使って NULL を 0 に置き換えるという方法を取りました。ここまで来れば後は表示フォーマットを整えるだけですが、売上額を 3 桁ずつカンマ区切りするのが思いの外面倒です。問題をよく見ると売上額が $10^9$ 未満であるという制約があるので、 $10^3$ 未満のとき、 $10^6$ 未満のとき、 $10^9$ 未満のときの 3 種類で場合分けをしました。各場合の表示方法については競技プログラミングで慣れていたので、時間が切迫する中でもミスなく書くことができました。

前半パートで文字列や小数を整数に変換する必要があるなど手間取ってしまったため最後は時間との勝負になりましたが、終了 2 分前に書き終えて一発 AC を取ることができました。

WITH T1 AS(
    SELECT
        (CAST(SUBSTR(SALES_DATE, 9, 2) AS INTEGER) + 4) % 7 AS TYPE,
        COUNT(*) AS CNT,
        SUM(SALES_AMT) AS AMT
    FROM
        SALES
    WHERE
        SALES_TYPE != 2
        AND UPDATED_NO IS NULL
        AND SALES_DATE BETWEEN '2024-03-01' AND '2024-03-28'
    GROUP BY
        TYPE
), T2 AS(
    SELECT 0 AS TYPE
    UNION ALL
    SELECT
        TYPE + 1 AS TYPE
    FROM
        T2
    WHERE
        TYPE < 6
), T3 AS(
    SELECT
        T2.TYPE,
        IFNULL(CAST(ROUND(CNT * 0.25, 0) AS INTEGER), 0) AS AVG_CNT,
        IFNULL(CAST(ROUND(AMT * 0.25, 0) AS INTEGER), 0) AS AVG_AMT
    FROM
        T2
    LEFT OUTER JOIN
        T1 ON T1.TYPE = T2.TYPE
), T4 AS(
    SELECT
        CASE TYPE
            WHEN 0 THEN '日'
            WHEN 1 THEN '月'
            WHEN 2 THEN '火'
            WHEN 3 THEN '水'
            WHEN 4 THEN '木'
            WHEN 5 THEN '金'
            WHEN 6 THEN '土'
        END AS WEEK,
        AVG_CNT,
        CASE
            WHEN AVG_AMT < 1000 THEN AVG_AMT
            WHEN AVG_AMT < 1000000 THEN (AVG_AMT / 1000) || ',' || (AVG_AMT % 1000)
            WHEN AVG_AMT < 1000000000 THEN (AVG_AMT / 1000000) || ',' || ((AVG_AMT / 1000) % 1000) || ',' || (AVG_AMT % 1000)
        END || '円' AS AVG_AMT
    FROM
        T3
    ORDER BY
        TYPE ASC
)
SELECT
    *
FROM
    T4