SQLで行う連続判定

概要

SQLの連続判定の方法を自分なりに思い付いたので備忘のためメモ

前提知識

SQLの基本文法（集約関数、分析関数）

前提データと求めたい結果

以下のように、customer_id（顧客番号）とym（注文年月）の列を持つOrderHistory（注文履歴）テーブルがある。
customer_idごとに、最大何か月連続で注文しているか求めたい。

注文履歴テーブル

求めたいデータ

どうやって求めるか？

自分か書いたSQL

顧客ごとの最大連続購入月数を求めるSQL

WITH tmp1 AS (
    -- 注文年月と、2000年1月からの通し月数(monthly_seq)を集計
    SELECT customer_id
           , ym
           -- 2000年1月からの通し月数を計算 (連続性判定の基礎)
           , PERIOD_DIFF(DATE_FORMAT(order_date, '%Y%m'), 200001) AS monthly_seq
      FROM OrderHistory
),
tmp2 AS (
    -- 各レコードの前後の通し月数を取得
    SELECT  customer_id
            ,ym
            ,monthly_seq
            ,LAG(monthly_seq) OVER (PARTITION BY customer_id ORDER BY monthly_seq) AS lag_seq
            ,LEAD(monthly_seq) OVER (PARTITION BY customer_id ORDER BY monthly_seq) AS lead_seq
    FROM tmp1
),
tmp3 AS (
    -- 各レコードについて、1つ前の差分と1つ後の差分を出す
    SELECT
            customer_id,
            ,ym
            ,monthly_seq
            ,lag_seq
            ,lead_seq
            -- 前の月との差分。連続なら必ず -1
            ,COALESCE(lag_seq - monthly_seq, 0) AS lag_diff
            -- 次の月との差分。連続なら必ず +1
            ,COALESCE(lead_seq - monthly_seq, 0) AS lead_diff 
      FROM tmp2
),
tmp4a AS (
    -- 連続期間の先頭レコードを絞る (直前の月との差が -1 ではない)
    SELECT customer_id
           ,ym AS bgn_ym
           ,monthly_seq AS bgn_seq
           -- 各グループの開始点に連番を振る (後の結合用)
           ,ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ym) AS rn
     FROM tmp3
    WHERE lag_diff <> -1
),
tmp4b AS (
    -- 連続期間の最終レコードを絞る (直後の月との差が +1 ではない)
    SELECT customer_id
           ,ym AS end_ym
           ,monthly_seq AS end_seq
           -- 最終レコードにも同じく連番を振る
           ,ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ym) AS rn
      FROM tmp3
     WHERE lead_diff <> 1
),
tmp5 AS (
    -- 開始点と終了点を、連番 (rn) をキーに結合
    SELECT ta.customer_id
           ,ta.bgn_ym
           ,tb.end_ym
           -- 期間の長さを通し月数で計算 (end_seq - bgn_seq + 1)
           ,tb.end_seq - ta.bgn_seq + 1 AS consecutive_months
      FROM tmp4a ta
     INNER JOIN tmp4b tb
        ON ta.customer_id = tb.customer_id
       AND ta.rn = tb.rn
)
, tmp6 as (
    -- 最終的な結果：顧客IDごとに最長の連続期間を求める
    SELECT customer_id
           , MAX(consecutive_months) AS max_consecutive_months
      FROM tmp5
     GROUP BY customer_id
)
select *
  from tmp6;

SQLは以上の通りですが、with句の連発で何をしているかサッパリなので、次の項でそれぞれのWITH句で何をしているか一つずつ解説していきます。

詳細解説

WITH句1つ目（tmp1）

各行のymに対して、2001年1月1日を起点として経過月数を求めています。
これは次以降のWITH句で生きてきます。

WITH句2つ目（tmp2）

各行に対して、分析関数を用いて、顧客番号ごとに前後の行の通し月数を付けます。

WITH句3つ目（tmp3）

各行に対して、monthly_seqとlag_seq、monthly_seqとlead_seqの差分を取得します。
lag_diff = lag_seq - monthly_seq　⇒　これが-1でない行は連続の開始行
lead_diff = lead_seq - monthly_seq　⇒　これが1でない行は連続の終了行

WITH句4つ目の一（tmp4a）

tmp3のデータを連続開始行（lag_diff<>-1の行）だけに絞り込んだテーブルを作ります。
ついでにあとで結合キーに使うための連番もにふっておきます。

WITH句4つ目の二（tmp4b）

tmp3のデータを連続終了行（lead_diff<>1の行）だけに絞り込んだテーブルを作ります。
ついでにあとで結合キーに使うための連番もにふっておきます。

WITH句5つ目（tmp5）

tmp4aとtmp4bを結合します。これにより連続の開始年月と終了年月が1行にまとめられるため、連続期間が求められます。

WITH句6つ目（tmp6）

ここは流石に自明なので省略。

おわりに

自分で思い付いた方法ですが、記録しておかないと翌日には忘れそうなので記事にまとめました。
Geminiに聞いたところ「Gap-and-Islands」と呼ばれる、より優れた解法があるそうです。