0Pricing
SQL Interview Prep · レッスン

連続する暦日を検出する

日付の算術演算と行番号を使って、途切れない日付の連続を見つけます。

「連続する暦日を検出する」はCoddyKit上の無料SQL Interview Prepレッスンです。 これはレッスン1/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはSQL Interview Prep学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Interview Prepコースには全4レッスンが含まれています。

面接問題の設定

面接官が連続記録に関する問題を好むのは、ウィンドウ関数と日付計算を本当に理解しているかが分かるからです。典型的な質問は次のとおりです。「ユーザーのログイン日付のテーブルが与えられたとき、途切れずに続く暦日の連続区間をそれぞれ求めてください。」

最初に思いつきやすいのは、各行を次の行と比較する自己結合ですが、大きなテーブルでは処理量が急増し、記述も扱いにくくなります。プロフェッショナルな解答では、ギャップとアイランドの手法を使います。このレッスンでは、行番号と日付の減算を使って、連続する日をすっきり検出する方法を学びます。

サンプルデータ

このレッスンでは、ユーザーがアクティブだった日ごとに1行を持つloginsテーブルを使います。重複はすでに除去されており、1つの暦日につき1回のログインだけがあるものとします。

  • user_id — ログインしたユーザー
  • login_date — DATE型の値

ユーザー1の日付は1月1日、2日、3日、次に空白があり、1月6日、7日です。期待する連続区間は2つで、3日間の区間と2日間の区間です。

SELECT * FROM logins ORDER BY user_id, login_date;
-- user_id | login_date
--    1    | 2024-01-01
--    1    | 2024-01-02
--    1    | 2024-01-03
--    1    | 2024-01-06
--    1    | 2024-01-07

中核となる考え方

これが、連続する日を扱う問題を解くための重要な仕掛けです。日付順に行を並べて、それぞれに連続した行番号を割り当てると、連続する日からなる区間では、日付と行番号の差が一定になります。

なぜでしょうか。連続する日では、日付も行番号も毎回ちょうど1ずつ増えるため、差は変わりません。空白が現れると、日付は飛びますが行番号は飛ばないため、一定値が崩れて新しいグループが始まります。

差分を確認する

ユーザー1について、手計算で確認してみましょう。ROW_NUMBERは1、2、3、4、5を数えます。行番号を日数として日付から引き、結果を見てみます。

  • 1月1日 − 1 = 12月31日
  • 1月2日 − 2 = 12月31日
  • 1月3日 − 3 = 12月31日
  • 1月6日 − 4 = 1月2日
  • 1月7日 − 5 = 1月2日

最初の3行は12月31日を共有し、最後の2行は1月2日を共有します。この共通の基準値がグループキーです。

ROW_NUMBERを追加する

最初の具体的な手順は、行番号を付けることです。ユーザーごとにパーティション分割することで、連続記録がユーザーの境界をまたがないようにし、日付順に並べます。

PARTITION BY user_idによってユーザーごとにカウンターがリセットされ、ORDER BY login_dateによってカレンダー順の並びが保証されます。

SELECT
  user_id,
  login_date,
  ROW_NUMBER() OVER (
    PARTITION BY user_id
    ORDER BY login_date
  ) AS rn
FROM logins;

グループの基準値を計算する

次に、login_dateからrn日を引きます。PostgreSQLでは、日付から整数の日数を直接引けます。その結果が、各アイランドを識別する一定の基準値になります。

ただし、rnという別名は、それを定義している同じSELECTでは参照できません。そのため、まず前のクエリをCTEまたはサブクエリで囲みます。

WITH numbered AS (
  SELECT
    user_id,
    login_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ) AS rn
  FROM logins
)
SELECT
  user_id,
  login_date,
  login_date - rn AS grp
FROM numbered;

アイランドをグループ化する

基準値が分かれば、各連続区間は同じgrp値を共有します。user_idとgrpでグループ化し、集約によって各区間の開始日、終了日、長さを取得します。

  • MIN(login_date) — 連続区間の初日
  • MAX(login_date) — 連続区間の最終日
  • COUNT(*) — 連続区間の日数
WITH numbered AS (
  SELECT user_id, login_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ) AS rn
  FROM logins
)
SELECT
  user_id,
  MIN(login_date) AS streak_start,
  MAX(login_date) AS streak_end,
  COUNT(*)        AS streak_len
FROM numbered
GROUP BY user_id, login_date - rn
ORDER BY user_id, streak_start;

SQL方言による違い

日付計算の構文はSQL方言によって異なります。幅広い知識を示すため、面接ではこの点にも触れてください。

  • PostgreSQL： login_date - rn（日付から整数の日数を減算）
  • MySQL： DATE_SUB(login_date, INTERVAL rn DAY)
  • SQL Server： DATEADD(day, -rn, login_date)

ロジックは同じで、関数名だけが変わります。移植可能な考え方は、「各日付をその位置の分だけ後ろにずらすと、連続区間が1つの一定値にまとまる」というものです。

-- SQL Server version of the anchor
DATEADD(day, -1 * rn, login_date) AS grp

なぜ自己結合ではないのか

面接官から、l1.login_date = l2.login_date + 1のような自己結合を使わなかった理由を聞かれることがあります。答えるべき理由は次のとおりです。

  • 自己結合で確認できるのは隣接性だけで、区間全体ではありません。完全な連続記録を組み立てるには、結局グループ化が必要です。
  • 適切なインデックスがなければ、行の組み合わせが増えて O(n²) になる可能性があります。
  • 行番号を使う方法は、順序付けされた1回の処理で済むため、はるかにスケーラブルです。

この種の問題では、ウィンドウ関数を使うのが現代的で期待される解答です。

重複に備える

この手法全体は、ユーザーごとに1日1行であることを前提としています。1日に複数回ログインするデータがあると、同じ日付の2行に異なる行番号が付与され、基準値が壊れてしまいます。

まず重複を除去して対処します。タイムスタンプを日付に変換してDISTINCTを適用するか、同じ日付に同じ番号を共有させるため、ROW_NUMBERの代わりに日付に対してDENSE_RANKを使います。

WITH days AS (
  SELECT DISTINCT user_id, login_ts::date AS login_date
  FROM raw_logins
)
SELECT * FROM days;

完全な解法

すべてを組み合わせると、各連続日区間の開始日、終了日、長さを一覧にする、面接ですぐ使える簡潔な解答になります。

この「重複排除、番号付け、減算、グループ化」という同じ骨格で、ほとんどあらゆる「連続する」問題を解けます。

WITH days AS (
  SELECT DISTINCT user_id, login_ts::date AS login_date
  FROM raw_logins
),
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,
  MIN(login_date) AS streak_start,
  MAX(login_date) AS streak_end,
  COUNT(*)        AS streak_len
FROM numbered
GROUP BY user_id, login_date - rn
ORDER BY user_id, streak_start;

クイックチェック

中核となる仕掛けを理解できているか確認します。

まとめ

連続する日を扱う基本パターンを学びました。

  • 重複排除して、ユーザーごとに1日1行にします。
  • 日付順に並べ、ユーザーごとにパーティション分割したROW_NUMBERを付けます。
  • 日付から行番号を引いて、各連続区間に共通する基準値を取得します。
  • 基準値でGROUP BYし、集約して開始日、終了日、長さを求めます。

このギャップとアイランドの骨格は1回の処理でスケールし、自己結合より優れています。次はこれを使って、ユーザーごとの最長連続記録を計算します。

よくある質問

「連続する暦日を検出する」レッスンは無料ですか？

はい。「連続する暦日を検出する」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Interview Prepコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Interview Prepコースには全4レッスンが含まれています。

「連続する暦日を検出する」で何を学びますか？

日付の算術演算と行番号を使って、途切れない日付の連続を見つけます。 ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

SQL Interview Prepを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのSQL Interview Prepは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン1/4です。

「連続する暦日を検出する」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

このSQL Interview Prepレッスンでコードを書いて実行できますか？

はい。すべてのSQL Interview Prepレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

  1. 連続する暦日を検出する
  2. ユーザーごとの最長連続記録
  3. 条件を満たすN行の連続
  4. 今日時点の現在の連続記録
← SQL Interview Prepに戻る