SQL で連続区間を検出して連番をつける

これは何

「SQL で連続区間を検出してグルーピングする」方法を考えていたところ、まさにドンピシャな記事を見つけました。
こちらの記事は非常に参考になり、役立ちました。
ただ、結構煩雑な処理を行っており自分の頭では理解に時間がかかってしまいました。もう少しシンプルにできる気がしたので、そのアイデアと実装を書いてみます。

やりたいこと

• SQLで連続区間を検出してグルーピングする
  • このとき、「連続しているかどうか」の定義は与えられており、その判定もクエリ内で行えるものとする
• グルーピングされた区間が何個目の区間であるか数える

例

以下のようなデータがあるとする。

user_id	login_date
1	2022-01-01
1	2022-01-01
1	2022-01-01
1	2022-01-02
1	2022-01-03
1	2022-01-11
1	2022-01-13
1	2022-01-14
2	2022-01-01
2	2022-01-03
2	2022-01-04

このテーブルから以下のようなテーブルを作成したい。
ここでの「連続」の定義は「レコードのタイムスタンプの間隔が1日以内」とする。

user_id	login_date_start	login_date_end	nth
1	2022-01-01	2022-01-03	1
1	2022-01-11	2022-01-11	2
1	2022-01-13	2022-01-14	3
2	2022-01-01	2022-01-01	1
2	2022-01-03	2022-01-04	2

実装方法

ロジック

1. 「前のレコードと連続していない」とき1、「前のレコードと連続している」とき0となるようなフラグのカラムを追加する
2. 1で付加したカラムの累積和をとる
3. 累積和の値が「連続区間の何番目か」となる

つまり、以下のような中間テーブルを作ればよい。
データは時系列順に並んでいるものとする。

• 1番に対応するテーブル

user_id	login_date	flag
1	2022-01-01	1
1	2022-01-01	0
1	2022-01-01	0
1	2022-01-02	0
1	2022-01-03	0
1	2022-01-11	1
1	2022-01-13	1
1	2022-01-14	0
2	2022-01-01	1
2	2022-01-03	1
2	2022-01-04	0

• 2番に対応するテーブル

user_id	login_date	running_sum
1	2022-01-01	1
1	2022-01-01	1
1	2022-01-01	1
1	2022-01-02	1
1	2022-01-03	1
1	2022-01-11	2
1	2022-01-13	3
1	2022-01-14	3
2	2022-01-01	1
2	2022-01-03	2
2	2022-01-04	2

クエリ

DBはSnowflakeです。

with flagged_table AS (
    SELECT
        user_id,
        login_date,
        IFF(
            DATEDIFF('DAY',
                LAG(login_date, 1, 1) OVER(PARTITION BY user_id ORDER BY login_date),
                login_date
            ) > 1,
            1, -- 前のレコードと連続でない=前回ログインから2日以上経っている場合
            0  -- 前のレコードと連続である=前回ログインから1日以内の場合
            ) flag
    FROM table
),
runningsum_table AS (
    SELECT
        user_id,
        login_date,
        SUM(flag) OVER(OARTITION BY user_id ORDER BY login_date) running_sum
    FROM flagged_table
)
SELECT
    user_id,
    MIN(login_date) login_date_start,
    MAX(login_date) login_date_end,
    running_sum nth
GROUP BY user_id, nth
FROM runningsum_table

IFFで書いている切れ目の判定条件を適切に設定することで、様々な形で連続区間のグルーピングおよびカウントができるようになります。

感想

最初にこの問題を考えたとき「imos法っぽいな……」と思ったので、それっぽく実装してみたらこうなりました。
本記事の方法は「切れ目にフラグを付ける」→「累積和を取る」というシンプルな方法でしかありません。自分が適切に理解できているのであれば（あまり自信はありませんが）、元記事もベースとなるロジックは同じはずです。
ただ、適切なドメイン知識を適用して「切れ目の条件」をうまく決めることができれば広く適用できるパワフルなものだと思います。