MySQLで期間の重複をグループ化するクエリの解説

今日は、MySQLを使って期間の重複をグループ化する方法について解説します。具体的には、利用開始日 (PERIOD_STT) と利用終了日 (PERIOD_END) が重複する期間を統合し、一意のグループとして扱う方法をご紹介します。

テーブルの構成

まず、以下のようなテーブル tbl を例にして考えます：

NUMBER	PERIOD_STT	PERIOD_END
1	2024-01-01	2024-03-01
2	2024-02-01	2024-03-01
3	2024-03-01	2024-06-01

このテーブルには、利用開始日と利用終了日が記録されています。これらの期間が重複している場合には、それらを統合して1つのグループとしてまとめたいと考えています。

クエリの構成

以下に示すクエリでは、期間が重複するデータをグループ化し、最終的に統合した期間を取得する方法を説明します。

WITH t1 AS (
    SELECT
        tbl.NUMBER AS number,
        tbl.PERIOD_STT AS period_stt,
        tbl.PERIOD_END AS period_end,
        LAG(tbl.PERIOD_STT) OVER (PARTITION BY tbl.NUMBER ORDER BY tbl.PERIOD_STT, tbl.PERIOD_END) AS lag_STT,
        LAG(tbl.PERIOD_END) OVER (PARTITION BY tbl.NUMBER ORDER BY tbl.PERIOD_STT, tbl.PERIOD_END) AS lag_END
    FROM
        tbl
),
t2 AS (
    SELECT
        number,
        period_stt,
        period_end,
        MIN(lag_STT) OVER (PARTITION BY number ORDER BY period_stt, period_end) AS min_lag_STT,
        MAX(lag_END) OVER (PARTITION BY number ORDER BY period_stt, period_end) AS max_lag_END
    FROM
        t1
),
t3 AS (
    SELECT
        number,
        period_stt,
        period_end,
        SUM(
            CASE
                WHEN period_stt <= max_lag_END AND period_end >= min_lag_STT THEN 0
                ELSE 1
            END
        ) OVER (PARTITION BY number ORDER BY period_stt, period_end) AS grp_key
    FROM
        t2
),
t4 AS (
    SELECT
        MAX(number) AS number,
        MIN(period_stt) AS period_stt,
        MAX(period_end) AS period_end
    FROM
        t3
    GROUP BY
        grp_key
),
t5 AS (
    SELECT
        t4.number,
        t4.period_stt,
        t4.period_end
    FROM
        t4
    INNER JOIN
        t1
    ON  t4.number = t1.number
)
SELECT
    t1.*
FROM
    t1
    LEFT OUTER JOIN
        t5
    ON  t5.number = t1.number
WHERE
    t5.number IS NULL;

クエリの解説

t1サブクエリ

各行に対して、前の行の利用開始日 (lag_STT) と利用終了日 (lag_END) を取得します。これにより、重複する期間を検出する準備をします。

t2サブクエリ

各行に対して、グループ化の基準となる最小の利用開始日 (min_lag_STT) と最大の利用終了日 (max_lag_END) を計算します。

t3サブクエリ

period_stt が max_lag_END 以下で、かつ period_end が min_lag_STT 以上の場合には、同じグループに属するものとしてカウントします。重複がある場合には grp_key が同じになります。

t4サブクエリ

grp_key でグループ化し、各グループの最小の利用開始日と最大の利用終了日を取得します。

t5サブクエリ

t4 の結果と t1 を内部結合し、最終的なグループ化された結果を取得します。

最終クエリ

t1 と t5 を左外部結合し、t5 に含まれていない行を抽出します。これにより、グループ化されなかった（つまり、重複がなかった）元の期間データが取得されます。

結果

例えば、以下のようにデータがあった場合：

NUMBER	PERIOD_STT	PERIOD_END
1	2024-01-01	2024-03-01
2	2024-02-01	2024-03-01
3	2024-03-01	2024-06-01

このクエリの結果、以下のように重複した期間が統合された結果が得られます：

NUMBER	PERIOD_STT	PERIOD_END
1	2024-01-01	2024-03-01
3	2024-03-01	2024-06-01

このようにして、利用開始日と利用終了日が重複しているデータを一つのグループとして統合することができます。クエリの各ステップを理解し、適切な集約処理を行うことで、重複を適切に処理できます。