連続記録を算出するSQLが難しかった

はじめに

連続ログイン記録のようなものは，多くのアプリケーションに実装されています．
しかし，いざログから連続記録を算出する機能を実装してみるとなると，かなり複雑なSQLが必要になりました．
chito_ngさんの「『継続して○○した日数』とその最大値をSQLで求める」を参考に実装しました．
結構苦労したので，備忘録がてら残そうと思います．
PostgreSQLを利用します．

SQLで連続記録を算出する

今回は問題演習アプリを例に取り，連続学習日数を取得しようと思います．
以下のようなanswersテーブルから，連続学習日数を取得します．

user_id	answer_id	answered_at	answer ...
1	1	2024-10-01T09:12:34+0900	...
1	2	2024-10-01T10:34:56+0900	...
1	3	2024-10-02T09:12:56+0900	...
1	4	2024-10-03T12:12:56+0900	...
1	5	2024-10-05T09:34:56+0900	...
1	6	2024-10-06T17:45:56+0900	...
2	...	...	...

また，複数ユーザーの連続学習日数が必要になることもあると思いますが，今回は特定ユーザーに対して取得しようと思います．

大まかな手順を示します．

• 日付だけのデータにする．
• 連続する日付の最初のレコードにフラグを立てる
• 累積和を用いて連続記録をグルーピングする
• グルーピングされた連続記録を数え上げる

日付だけのデータにする．

今回は正解・不正解問わず回答していれば連続する条件を満たしているとするので，まず日付だけを算出します．
もし連続条件が何か有れば，この段階で絞り込むことができます．

SELECT DISTINCT
  CAST(answered_at AT TIME ZONE 'Asia/Tokyo' AS DATE) as answered_date
FROM
  answers
WHERE
  user_id = 1
;

このような結果が得られると思います．

answered_date
2024-10-01
2024-10-02
2024-10-03
2024-10-05
2024-10-06

連続する日付の最初のレコードにフラグを立てる

次に，日付だけのデータから，連続する区間の最初の日付にフラグを立てます．

SELECT
  answered_date
  , LAG(answered_date) OVER(ORDER BY answered_date) IS NULL
    OR answered_date - LAG(answered_date) OVER(ORDER BY answered_date) > 1
    AS is_first_date_of_streak
FROM
  answered_dates
;

このような結果が得られると思います．

answered_date	is_first_date_of_streak
2024-10-01	true
2024-10-02	false
2024-10-03	false
2024-10-05	true
2024-10-06	false

累積和を用いて連続記録をグルーピングする

この表に対して，is_first_date_of_streakの累積和を取ることで，連続している部分のグルーピングができます．trueを1，falseを0として計算すると以下のようになります．

answered_date	streak_group_number
2024-10-01	1
2024-10-02	1
2024-10-03	1
2024-10-05	2
2024-10-06	2

これを算出するSQLは以下のとおりです．

SELECT
  answered_date
  , SUM(is_first_date_of_streak::int)
    OVER(
      ORDER BY answered_date
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    )
    AS streak_group_number

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWを使うことで，日付順に並べた最初から今のレコードまでのis_first_date_of_streakの合計値を取ることができます．
新しいグループの最初のレコードだけis_first_date_of_streakがtrueなので，連続している部分のグルーピングができます．

グルーピングされた連続記録を数え上げる

あとは簡単です．
例えば，今日を含む連続した部分の日数を数える場合は以下のようになります．

SELECT
  count(*)
  , BOOL_OR(answered_date = CURRENT_DATE AT TIME ZONE 'Asia/Tokyo') AS is_including_today
  , MIN(answered_date) AS start
  , MAX(answered_date) AS end
FROM
  grouped_answered_dates
GROUP BY
  streak_group_number
;

今日が10/6なら，結果は以下のようになります．

count	is_including_today	start	end
3	false	2024-10-01	2024-10-03
2	true	2024-10-05	2024-10-06

まとめ

chito_ngさんの「『継続して○○した日数』とその最大値をSQLで求める」が大変参考になりました．ありがとうございました．
今回の私のタスクではユーザー毎に取得する必要がなかったため，主にその部分を簡略化した形になります．
「連続部分の最初のレコードにフラグを立てる」「累積和でグルーピングする」の2点がポイントです．

カウンターを保存しておく方法

今回はログからSQLで算出する方針で実装したのですが，集約関数を使うため，アプリケーションや利用者の規模によっては別の方針を取る必要があると思います．
おそらく一番素朴な実装としては，以下のようなカウンターテーブルを用意し，それを適宜インクリメントする方法ではないでしょうか？

user_id	counter	updated_at
1	10	2024-10-01T12:34:56+0900
2	13	2024-10-01T23:45:01+0900
...	...	...

updated_atのようなカラムを用意すれば，インクリメントするべきなのか，リセットするべきなのか判断できると思います．
もし今までの最大記録が必要であれば，それを保存するカラムも用意すれば実現可能と思います．