連続した数値を検出するSQLクエリの実装は、一見すると複雑に思えるが、適切なウィンドウ関数を活用すれば効率的に解決できる。本稿では、idが連番でないデータセットにおいても正確に連続性を判定する手法を解説する。

問題概要

本問題では、同一の数値が少なくとも3回連続して登場するレコードを特定することが求められる。idが連番でない場合（データの削除等により欠番が存在する場合）も考慮する必要があるため、単なる等値結合や単純なWHERE句では対応できない。

アプローチ概要

解決策の核心は、row_number()ウィンドウ関数を用いて「擬似的な連続番号」を生成することにある。具体的には以下の3つのステップで処理を進める。まず、idに基づく全体通しの連番を付与する。次に、数値（num）単位での連番を付与する。最後に、これら2つの連番の差分を算出し、連続区間を識別可能なグループ化する。

全体連番の生成

idカラムを基準とした順序付けにより、データ全体を通じた連番を付与する。

SELECT
    id,
    num,
    ROW_NUMBER() OVER (ORDER BY id) AS total_sequence
FROM sequential_data

同一数値内の連番生成

numの値ごとにグループ化し、そのグループ内での連番を生成する。これにより、同一数値内での相対的な位置を把握できる。

SELECT
    id,
    num,
    ROW_NUMBER() OVER (PARTITION BY num ORDER BY id) AS group_sequence
FROM sequential_data

連続区間の識別

全体連番からグループ内連番を減算することで、連続したレコード群が同一の差分値を持つという性質を利用する。連続していない場合、この差分値は異なる結果となる。

SELECT
    id,
    num,
    ROW_NUMBER() OVER (ORDER BY id) -
    ROW_NUMBER() OVER (PARTITION BY num ORDER BY id) AS segment_marker
FROM sequential_data

最終クエリの構築

前述の処理により生成されたsegment_markerを 기준으로グループ化し、同一区間内に3件以上のレコードが存在するnumを特定する。重複除去のためにDISTINCT句を使用し、複数の区間で3回以上登場する数字も正しく抽出する。

SELECT DISTINCT target_num
FROM (
    SELECT
        num AS target_num,
        segment_marker,
        COUNT(*) AS consecutive_count
    FROM (
        SELECT
            num,
            ROW_NUMBER() OVER (ORDER BY id) -
            ROW_NUMBER() OVER (PARTITION BY num ORDER BY id) AS segment_marker
        FROM sequential_data
    ) numbered_data
    GROUP BY num, segment_marker
    HAVING COUNT(*) >= 3
) grouped_result

アルゴリズムの適用例

idが1, 3, 5, 6, 7というように欠番を含むデータセットを想定する。numの値が「1, 1, 2, 2, 2, 3」であった場合、6, 7番目のidを持つ「2」のレコードが3回連続していると判定される。全体連番とグループ内連番の差分計算により、欠番の影響を受けることなく正確に連続性を識別できることが本手法の最大の特徴である。

本解決策は、大規模データセットにおいても効率的な処理が可能であり、ウィンドウ関数の特性を活かした代表的な適用例として広く知られている。