0Pricing
SQL Interview Prep · レッスン

条件を満たすN行の連続

「3日連続で売上がXを超える」のような、典型的な連続期間パターンです。

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

LeetCodeの定番問題

これはSQL面接で特によく出る問題の1つです。「売上がしきい値を超えた日が3日以上連続する日付をすべて見つけなさい」、またはLeetCodeでおなじみの「来場者数が100を超える行が3行以上連続するスタジアムを報告しなさい」という問題です。

構造は常に同じです。ある行が条件を満たすのは、N行連続で条件を満たす区間の中にある場合だけです。このレッスンでは、2つの明快な解法と、多くの候補者がはまる落とし穴を説明します。

サンプルデータ

日次のsalesテーブルを使います。条件はamount > 100です。条件をすべて満たす3日以上の連続する暦日の区間に含まれる日を、すべて返す必要があります。

  • sale_date — 1日につき1行
  • amount — その日の総売上

重要な点は、行が並び順の上で連続しているだけでなく、日付を扱う場合は暦日の上でも連続していなければならないことです。

SELECT * FROM sales ORDER BY sale_date;
-- sale_date  | amount
-- 2024-03-01 |  120
-- 2024-03-02 |  150
-- 2024-03-03 |  130
-- 2024-03-04 |   90
-- 2024-03-05 |  200

アプローチ1：フィルタしてからアイランド化

堅牢なアプローチは、まず条件を満たす行だけを残し、次に残った行を連続するアイランドにグループ化し、最後に長さがN以上のアイランドだけを残す方法です。

1つ目のステップはWHEREによるフィルタです。2つ目のステップでは、ギャップとアイランドのアンカーを再利用します。先にフィルタしているため、ここでのアイランドは「条件を満たす日が連続する区間」を意味します。

WITH qualifying AS (
  SELECT sale_date
  FROM sales
  WHERE amount > 100
)
SELECT * FROM qualifying ORDER BY sale_date;

条件を満たす連続区間のアンカー付け

条件を満たす行に日付順で番号を付け、差を取ってアイランドのアンカーを作成します。暦日上連続していて、なおかつすべて条件を満たす行は、同じアンカーを共有します。条件を満たさない日は削除されているため、その日がある箇所で連続区間が正しく分断されます。

WITH qualifying AS (
  SELECT sale_date
  FROM sales
  WHERE amount > 100
),
numbered AS (
  SELECT sale_date,
    ROW_NUMBER() OVER (ORDER BY sale_date) AS rn
  FROM qualifying
)
SELECT sale_date, sale_date - rn AS grp
FROM numbered;

十分な長さのアイランドを残す

アンカーでグループ化して行数を数え、COUNT(*) >= 3を満たすグループだけを残します。条件を満たす個々の日付を返す必要がある場合は、残したアンカーを番号付きの行に結合します。

WITH qualifying AS (
  SELECT sale_date FROM sales WHERE amount > 100
),
numbered AS (
  SELECT sale_date,
    ROW_NUMBER() OVER (ORDER BY sale_date) AS rn
  FROM qualifying
),
islands AS (
  SELECT sale_date - rn AS grp, COUNT(*) AS len
  FROM numbered
  GROUP BY sale_date - rn
  HAVING COUNT(*) >= 3
)
SELECT n.sale_date
FROM numbered n
JOIN islands i ON n.sale_date - n.rn = i.grp
ORDER BY n.sale_date;

アプローチ2：スライディングCOUNTウィンドウ

Nが小さく固定されている場合は、より洗練されたアプローチとして、ウィンドウフレームを使って周囲の行のうち何行が条件を満たすかを数えられます。この行を含むN行連続のウィンドウのいずれかがすべて条件を満たしていれば、その行は結果に含まれます。

まず真偽フラグを追加し、そのフラグをスライディングフレーム上で合計します。

SELECT sale_date, amount,
  CASE WHEN amount > 100 THEN 1 ELSE 0 END AS ok
FROM sales;

3つのフレームで合計する

ちょうど3行の連続区間の場合、現在の行を含む3行のウィンドウが、現在の行で終わる場合、現在の行を中央に置く場合、または現在の行から始まる場合のいずれかで、合計が3になればその行は結果に含まれます。3つの移動合計を計算し、いずれかが3と等しいかを判定します。

これはLeetCode 601 (Human Traffic of Stadium)の解法で使われているテクニックです。

WITH flagged AS (
  SELECT sale_date, amount,
    CASE WHEN amount > 100 THEN 1 ELSE 0 END AS ok
  FROM sales
),
w AS (
  SELECT *,
    SUM(ok) OVER (ORDER BY sale_date
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS s_end,
    SUM(ok) OVER (ORDER BY sale_date
      ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS s_mid,
    SUM(ok) OVER (ORDER BY sale_date
      ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) AS s_start
  FROM flagged
)
SELECT sale_date, amount
FROM w
WHERE ok = 1 AND (s_end = 3 OR s_mid = 3 OR s_start = 3);

暦日の空白に関する落とし穴

ウィンドウ合計のアプローチではROWSを使います。これは隣接する結果行を数えるものであり、隣接する暦日を数えるものではありません。条件を満たさない日を先にフィルタで除外すると、2つの行が結果上では隣接していても、暦日上では連続していない可能性があります。

教訓：スライディングウィンドウは日次系列全体に適用して（事前にフィルタしないで）ください。または、暦日の空白を本質的に考慮できる日付アンカー方式を使います。このトレードオフを面接で説明しましょう。

任意のNへの一般化

アプローチ1（フィルタしてからアイランド化）は簡単に一般化できます。HAVING COUNT(*) >= Nに変更するだけです。Nが増えるほど追加のフレームが必要になる複数ウィンドウの合計に対する、この方法の大きな利点です。

パラメータ化されたNや大きなNを扱う場合は、アイランド方式を優先してください。N−1個のウィンドウを手書きする代わりに、しきい値を1か所変更するだけで済みます。

-- only the threshold changes for N = 5
HAVING COUNT(*) >= 5

アプローチの選択

面接で声に出して説明するための簡単な判断基準は、次のとおりです。

  • フィルタしてからアイランド化：暦日の空白を考慮でき、任意のNに一般化でき、連続区間全体を返せます。安全なデフォルトです。
  • スライディングウィンドウの合計：密な日次系列で、固定された小さなNを扱う場合は簡潔です。ただし、ROWSと暦日の違いによる落とし穴に注意してください。

両方の方法を挙げたうえで、選んだ方法の理由を説明することが、中堅からシニアレベルの面接官にまさに評価されるポイントです。

完全な解答

暦日上の連続性を考慮し、条件を満たす日付を返す、どのNにも使える移植性の高い解答です。

WITH qualifying AS (
  SELECT sale_date FROM sales WHERE amount > 100
),
numbered AS (
  SELECT sale_date,
    ROW_NUMBER() OVER (ORDER BY sale_date) AS rn
  FROM qualifying
),
islands AS (
  SELECT sale_date - rn AS grp, COUNT(*) AS len
  FROM numbered
  GROUP BY sale_date - rn
  HAVING COUNT(*) >= 3
)
SELECT n.sale_date
FROM numbered n
JOIN islands i ON n.sale_date - n.rn = i.grp
ORDER BY n.sale_date;

確認問題

微妙なバグを見つけてください。

まとめ

条件を満たすN行連続を求めるには、次のようにします。

  • フィルタしてからアイランド化：条件を満たす行を残し、date - ROW_NUMBER()でアンカーを作成し、グループ化して、HAVING COUNT(*) >= Nを適用します。一般化しやすく、暦日の空白も考慮できます。
  • スライディングウィンドウの合計：行にフラグを付け、固定されたN行フレーム上で合計します。簡潔ですが、事前にフィルタしたデータではROWSと暦日の違いに注意が必要です。

次は、今日時点でのユーザーの現在の連続日数の計算です。

よくある質問

「条件を満たすN行の連続」レッスンは無料ですか？

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

「条件を満たすN行の連続」で何を学びますか？

「3日連続で売上がXを超える」のような、典型的な連続期間パターンです。 ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

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

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

「条件を満たすN行の連続」レッスンにはどのくらい時間がかかりますか？

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

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

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

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

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