「何日連続でログインしているか」「最長の連続記録は何日か」は、ゲームや学習アプリでよく求められる集計です。1行ずつ前日と見比べるループを書きたくなりますが、SQLなら1本のクエリで出せます。鍵になるのは、日付から連番（ROW_NUMBER）を引くと、連続した日だけが同じ値になる性質です。この「ギャップ＆アイランド（gaps and islands）」と呼ばれる定番テクニックを使い、連続日数・最長記録・現在の連続記録を順に求めます。あわせて、同じ日に何度もログインしたときの落とし穴と、LAG関数で途切れた日を探す方法も紹介します。
このシナリオで考える
ログイン履歴を1回ごとに記録する login_logs テーブルを使います。実務のログと同じく、日時はDATETIME型で持ち、同じ日に複数回ログインする行も含めておきます。
CREATE TABLE login_logs (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
login_at DATETIME NOT NULL,
INDEX idx_user_login (user_id, login_at)
);
INSERT INTO login_logs (user_id, login_at) VALUES
(1, '2026-10-01 08:10:00'),
(1, '2026-10-02 21:30:00'),
(1, '2026-10-03 07:45:00'),
(1, '2026-10-05 12:00:00'),
(1, '2026-10-06 09:20:00'),
(2, '2026-10-02 10:00:00'),
(2, '2026-10-03 08:00:00'),
(2, '2026-10-03 22:15:00'),
(2, '2026-10-04 19:40:00'),
(2, '2026-10-08 06:30:00');ユーザー1は10月4日に、ユーザー2は10月5〜7日にログインしていません。ユーザー2は10月3日に2回ログインしている点も覚えておいてください。
日付からROW_NUMBERを引くと連続がまとまる
連続した日付から1, 2, 3…と連番を引くと、結果はすべて同じ日付になります。日付が1日進むたびに連番も1増えるため、差が変わらないからです。途中で1日でも抜けると、日付だけが余分に進みます。そこで差がずれ、別のグループ（島）に分かれる仕組みです。
手順は次の3段階です。
- DATETIMEを日付に変え、ユーザー×日付で重複をなくす
- ユーザーごとに日付順の連番（ROW_NUMBER）を振る
- 日付から連番の日数を引き、その値を島のキー（
grp）にする
WITH days AS (
SELECT DISTINCT user_id, DATE(login_at) AS login_date
FROM login_logs
),
numbered AS (
SELECT user_id, login_date,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
FROM days
)
SELECT user_id, login_date, rn,
DATE_SUB(login_date, INTERVAL rn DAY) AS grp
FROM numbered
ORDER BY user_id, login_date;+---------+------------+----+------------+
| user_id | login_date | rn | grp |
+---------+------------+----+------------+
| 1 | 2026-10-01 | 1 | 2026-09-30 |
| 1 | 2026-10-02 | 2 | 2026-09-30 |
| 1 | 2026-10-03 | 3 | 2026-09-30 |
| 1 | 2026-10-05 | 4 | 2026-10-01 |
| 1 | 2026-10-06 | 5 | 2026-10-01 |
| 2 | 2026-10-02 | 1 | 2026-10-01 |
| 2 | 2026-10-03 | 2 | 2026-10-01 |
| 2 | 2026-10-04 | 3 | 2026-10-01 |
| 2 | 2026-10-08 | 4 | 2026-10-04 |
+---------+------------+----+------------+ユーザー1の10月1〜3日は grp がすべて9月30日です。10月4日が抜けたため、5日以降は10月1日に変わりました。grp の値そのものに意味はありません。「同じ値なら同じ連続期間」とだけ覚えてください。
あとは user_id と grp でGROUP BYすれば、連続期間ごとの開始日・終了日・日数が出ます。
WITH days AS (
SELECT DISTINCT user_id, DATE(login_at) AS login_date
FROM login_logs
),
numbered AS (
SELECT user_id, login_date,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
FROM days
),
islands AS (
SELECT user_id, login_date,
DATE_SUB(login_date, INTERVAL rn DAY) AS grp
FROM numbered
)
SELECT user_id,
MIN(login_date) AS start_date,
MAX(login_date) AS end_date,
COUNT(*) AS streak_days
FROM islands
GROUP BY user_id, grp
ORDER BY user_id, start_date;+---------+------------+------------+-------------+
| user_id | start_date | end_date | streak_days |
+---------+------------+------------+-------------+
| 1 | 2026-10-01 | 2026-10-03 | 3 |
| 1 | 2026-10-05 | 2026-10-06 | 2 |
| 2 | 2026-10-02 | 2026-10-04 | 3 |
| 2 | 2026-10-08 | 2026-10-08 | 1 |
+---------+------------+------------+-------------+ウィンドウ関数（OVER句で行のまとまりを指定する関数）の基本は、ウィンドウ関数入門の記事で解説しています。
最長記録と「今の連続記録」を出す
連続期間の一覧ができれば、残りは集計の組み合わせです。上のクエリの最後のSELECTを streaks というCTEにして、その外側で絞り込みます。
ユーザーごとの最長連続日数
-- days / numbered / islands は前のクエリと同じ
WITH days AS (...), numbered AS (...), islands AS (...),
streaks AS (
SELECT user_id,
MIN(login_date) AS start_date,
MAX(login_date) AS end_date,
COUNT(*) AS streak_days
FROM islands
GROUP BY user_id, grp
)
SELECT user_id, MAX(streak_days) AS max_streak
FROM streaks
GROUP BY user_id;+---------+------------+
| user_id | max_streak |
+---------+------------+
| 1 | 3 |
| 2 | 3 |
+---------+------------+今も続いている連続記録
「現在◯日連続」は、終了日が今日か昨日の連続期間だけを残せば求まります。昨日まで含めるのは、今日まだログインしていない人の記録を0にしないためです。例では基準日を2026年10月8日に固定しています。実際には CURDATE() に置き換えてください。
-- streaks までは前のクエリと同じ
SELECT user_id, streak_days AS current_streak
FROM streaks
WHERE end_date >= DATE_SUB('2026-10-08', INTERVAL 1 DAY);+---------+----------------+
| user_id | current_streak |
+---------+----------------+
| 2 | 1 |
+---------+----------------+ユーザー1は最終ログインが10月6日のため、記録が途切れた扱いになります。
LAGで途切れた日（ギャップ）を探す
「島」の裏返しが「ギャップ」、つまりログインしなかった期間です。こちらはLAG関数で前回のログイン日を横に並べると簡単に出せます。前回との差が2日以上あれば、その間が空白期間です。
WITH days AS (
SELECT DISTINCT user_id, DATE(login_at) AS login_date
FROM login_logs
),
with_prev AS (
SELECT user_id, login_date,
LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) AS prev_date
FROM days
)
SELECT user_id, prev_date, login_date,
DATEDIFF(login_date, prev_date) - 1 AS gap_days
FROM with_prev
WHERE DATEDIFF(login_date, prev_date) > 1;+---------+------------+------------+----------+
| user_id | prev_date | login_date | gap_days |
+---------+------------+------------+----------+
| 1 | 2026-10-03 | 2026-10-05 | 1 |
| 2 | 2026-10-04 | 2026-10-08 | 3 |
+---------+------------+------------+----------+LAGは島を作る別解にも使えます。前回との差が1日でなければ1を立て、その累積和（SUM() OVER）を島番号にする方法です。
-- days / with_prev は前のクエリと同じ
SELECT user_id, login_date,
SUM(CASE WHEN DATEDIFF(login_date, prev_date) = 1 THEN 0 ELSE 1 END)
OVER (PARTITION BY user_id ORDER BY login_date) AS island_no
FROM with_prev;条件を <= 2 に変えれば「1日休みまでは連続扱い」のような緩いルールにも対応できます。2つの方法を表で比べておきます。
| 方法 | 考え方 | 向いている場面 |
|---|---|---|
| 日付 − ROW_NUMBER | 差が同じ行を同じ島にする | 「1日ずつ連続」の厳密な判定。クエリが短い |
| LAG ＋ SUM() OVER | 前回との差で区切りフラグを立て、累積和で番号を振る | 許容する間隔を変えたいとき。セッション分割（30分空いたら別セッション）など |
LAG関数そのものの使い方は、LAG・LEADで前日比を出す記事も参考にしてください。
よくある落とし穴
1. 同じ日の重複で連続が割れる
最も多い間違いが、重複をなくさずにROW_NUMBERを振ることです。ユーザー2の10月3日のように同じ日が2行あると、日付は進まないのに連番だけが進みます。その結果、1つの連続期間が2つに割れてしまいます。
-- DISTINCTを忘れた場合のユーザー2
| login_date | rn | grp |
| 2026-10-02 | 1 | 2026-10-01 |
| 2026-10-03 | 2 | 2026-10-01 |
| 2026-10-03 | 3 | 2026-09-30 | ← ここで島が割れる
| 2026-10-04 | 4 | 2026-09-30 |対策は、最初のCTEで DISTINCT を取ることです。ROW_NUMBERの代わりにDENSE_RANK（同じ値に同じ順位を振る関数）を使う手もあります。ただしその場合は、日数を COUNT(DISTINCT login_date) で数えないと重複分が多く数えられます。
2. 日付をそのまま数値として引かない
login_date - rn と書くと、MySQLは日付を 20261031 のような数値に変えて計算します。月の中では正しく見えますが、10月31日と11月1日の間で差がずれ、月をまたぐ連続が割れます。必ず DATE_SUB(..., INTERVAL rn DAY) を使ってください。PostgreSQLでは login_date - rn::int のように日付と整数を引けば日付が返ります。
3. DATETIMEのまま・タイムゾーンのずれ
日時のまま連番を振ると、1日の判定ができません。DATE() で日付に変えてから扱います。UTCで保存している場合は、日本時間に直してから日付にしないと、朝9時前のログインが前日扱いになります。
4. 性能
全ユーザーの全履歴を毎回集計すると重くなります。(user_id, login_at) の複合インデックスを貼り、対象期間を WHERE で絞ってから集計しましょう。利用者が多いサービスでは、日次バッチで「ユーザー×日付」の集計テーブルを作っておくと楽になります。
まとめ
連続ログイン日数は「日付 − ROW_NUMBER」で島のキーを作り、GROUP BYするだけで求まります。最長記録はMAX、今の記録は終了日の絞り込みで出せます。空白期間や緩い連続ルールにはLAGと累積和が便利です。重複の除去と、DATE_SUBでの日付計算の2点だけは忘れないでください。データのない日も表に出したいときは、連続した日付を作る記事と組み合わせると便利です。
参考リンク
- MySQL 8.0 リファレンス: Window Function Descriptions（ROW_NUMBER・LAG）
- MySQL 8.0 リファレンス: Date and Time Functions（DATE_SUB・DATEDIFF）
- MySQL 8.0 リファレンス: WITH (Common Table Expressions)
アイキャッチ画像: Photo by Towfiqu barbhuiya on Unsplash
