← ナレッジベースに戻る← 前の質問次の質問 →
SQL (ANSI)ProgrammingSQL開発者

連続したデータ内の隣接した範囲（アイランド）を、ANSI SQLのウィンドウ関数のみを使用して特定する方法は？行ごとの処理は禁止されています。

Hintsage AIアシスタントで面接を突破

質問への回答

質問の歴史

この質問は、ウィンドウ関数が正式に規格に導入されたSQL:2003に至るまでのSQL標準の進化から生まれました。この進歩以前は、開発者は手続き型カーソルや計算コストの高い自己結合に依存して、シーケンス指向の問題を解決していました。ギャップとアイランドのパターンは、手続き型アルゴリズムから、結果セットを定義する宣言型の集合ベースのロジックへのパラダイムシフトを示しています。

問題

タイムスタンプ、ID、または日付などの連続値を含むテーブルで作業する際は、連続する値を隣接ブロック（アイランド）にグループ化し、ギャップから区別する必要があります。根本的な課題は、ANSI SQLテーブルが順序付けされていない数学的集合を表しているにもかかわらず、シーケンスを検出するためには明示的な順序付けが要求されることです。従来のGROUP BY句は、類似の値を集約しますが、隣接性を特定するために必要なシーケンシャルな関係を破壊します。

解決策

データセット全体にわたる**ROW_NUMBER()と、グルーピングキーでパーティション化されたROW_NUMBER()**との算術的差を利用して、一定のアイランド識別子を生成します。この手法により、同じ隣接シーケンス内のすべての行に対して同一の計算値が作成され、標準的な集約によりアイランドが再構成されます。

WITH numbered AS (
  SELECT 
    event_date,
    ROW_NUMBER() OVER (ORDER BY event_date) AS rn_global,
    event_date - ROW_NUMBER() OVER (ORDER BY event_date) AS island_grp
  FROM events
)
SELECT 
  MIN(event_date) AS island_start,
  MAX(event_date) AS island_end,
  COUNT(*) AS consecutive_days
FROM numbered
GROUP BY island_grp;

実生活からの状況

小売分析チームは、PostgreSQLに保存されたクリックストリームデータから顧客のショッピングセッションを再構築する必要がありました。このシステムは、user_idとevent_timeを含む数百万のイベントを記録しましたが、事前に計算されたセッション識別子はありませんでした。ビジネス要件では、ギャップが30分を超えることのないイベントのシーケンスを1つのセッションと定義しました。

最初のアプローチでは、相関サブクエリを使用した自己結合を考慮し、各イベントの直前のイベントを特定しました。この方法ではO(n²)の行比較が必要で、500万行を超える日次バッチを処理する際にクエリタイムアウトが発生しましたが、最新のウィンドウ関数を欠くレガシーSQL-92システムとの互換性は保たれました。

チームはその後、pl/pgSQLカーソルを使用してイベントを行ごとに反復処理し、手続き型変数でセッション状態を維持する方法を評価しました。このアプローチはアプリケーション開発者にとって直感的なロジックを提供しましたが、集合ベースの処理原則を放棄し、日次バッチを完了するのに4時間以上かかり、受け入れ難いETLレイテンシと重大なテーブルロックの問題を引き起こしました。

選択された解決策は、ANSI SQLウィンドウ関数を独占的に使用しました。LAG()を適用してユーザーごとの前のタイムスタンプをキャプチャし、時間の差を計算することで、ギャップが30分を超える場所でセッションの境界を特定しました。条件付きの累積和がユニークなセッション識別子を生成し、集合ベースの集約を可能にしました。この方法は、データセット全体を8分で処理し、ボリュームに対して線形にスケールし、ベンダー固有の構文の変更なしにOracle、SQL Server、およびPostgreSQL間で移植性を保ちました。

候補者が見落としがちな点

タイムスタンプを単純に切り捨てて時間単位でグループ化するだけでセッションを見つけることはできないのはなぜですか？

DATE_TRUNCや類似の関数を使用してタイムスタンプを切り捨てると、カレンダーの境界に対して人工的な境界が強制され、相対的な時間差ではなくなります。10:55と11:05に発生した2つのイベントは、わずか10分の差にもかかわらず異なるグループに分けられますが、10:01と10:59のイベントは58分のギャップにもかかわらず一緒にグループ化されます。本当のセッション検出には、カレンダーの境界に整列するのではなく、各イベントの直前のイベントからの間隔を計算する必要があります。

LAGやLEADを使用したとき、順序付けの列にNULL値があるとアイランド検出にどのように影響しますか？

LAGおよびLEADは、それぞれ各パーティションの最初および最後の行に対してNULLを返します。現在のタイムスタンプから遅延タイムスタンプを引いてギャップを計算する際に、NULLを使用した算術操作はNULL結果を生み出し、これによってアイランド全体が集約から消失する可能性があります。アイランドの断片化を防ぐためには、LAGのオプションのdefaultパラメータを利用する（例：LAG(event_time, 1, event_time) OVER (...)）か、COALESCEを明示的に使用する必要があります。

複数のカテゴリを同時に横断してアイランドを検出する場合、ユーザーごとまたはデバイスごとの何が変わりますか？

候補者は、ウィンドウ関数でPARTITION BY句をしばしば省略し、テーブル全体でグローバルにROW_NUMBERを計算します。user_idやそれに相当するグルーピング列でパーティション分けをしない場合、異なるユーザーのアイランドは、時間的に一致する場合に不正に結合されます。アイランド計算に関与するすべてのウィンドウ関数は、各異なるエンティティに対して算術がリセットされることを保証するために、PARTITION BY user_idを含む必要があります。