MENU

SQL Serverで前行の計算結果を使う連番更新（Basin単位）を最速化する方法：再帰CTE・カーソル・LAG徹底比較

Microsoft

SQL Serverで「前行の計算結果」を参照しながら、Basin単位で連番（NumberInBasinNew）を更新したい――この手の要件は、ウィンドウ関数だけで完結しないことが多く、再帰CTEやカーソルの検討に入ります。本記事では、実装のひな形から、2500行でも極端に遅くなる原因の切り分け、再帰を避けて高速化できる落としどころまで、実務目線で整理します。

目次

SQL Serverで「前行の計算結果」を使う連番更新が難しい理由

今回の要件は、Basin（basin）ごとに並んだ行に対して、新しい列 NumberInBasinNew を次のルールで求めるものです。

  • 先頭行（basin≠prebasin扱い / Basin内の最初の行）：NumberInBasinNew = NumberInBasin
  • 同一Basin内の2行目以降：直前行の NumberInBasinNew を基準に、条件を満たすと +1、満たさないと 据え置き

ポイントは「各行の値が、直前行で計算した結果（NumberInBasinNew）に依存する」ことです。SQLは本来“集合（セット）”をまとめて計算するのが得意ですが、この要件は“逐次（ステート）”の性質を持つため、単純な CASE や SUM() だけでは表現しにくくなります。

テーブル設計の前提を整理する

例として、集計後の一時テーブル（または作業用テーブル）に次のような列がある想定です。

列名例役割
basinA / B / …グルーピング単位。Basinごとに連番をリセットする。
prebasinA前行のBasin（または事前計算した“前”の区分）。先頭判定に使うことが多い。
NumberInBasin（または WellNumberInBasin）1,2,3…Basin内での並び順のキー。順序が決まらないと結果が再現不能。
Value10.5条件判定に使う値（例：変化検知、しきい値判定など）。
CumulativeBasin100, 110…累積値（例：累積距離、累積量）。条件判定の材料になりやすい。
NumberInBasinNew計算結果今回作りたい新しい連番列（行ごとに更新）。

最重要：Basin内の「並び順」が一意に確定しているか

この問題で一番つまずきやすいのが、「前行」が何を指すかが曖昧なまま実装してしまうことです。ORDER BY が一意でないと、同じSQLでも実行のたびに順序が変わり、結果が揺れます。

確認ポイント実務での対策
(basin, NumberInBasin) が一意か一意でないなら ID 等のタイブレーク列を追加して ORDER BY NumberInBasin, ID にする。
並び順を支えるインデックスがあるか作業用テーブルに (basin, NumberInBasin, ID) のクラスタ化（または適切な索引）を作る。
前段のCTEやJOINで行が膨らんでいないか「最終的に2500行」でも、前段が巨大だと ROW_NUMBER() のソート・スプールが激重になる。

ルールを目で確認できるサンプル

条件は案件ごとに異なりますが、動きのイメージが掴めるように、よくある「値が変わったら+1」の例で示します。

basinNumberInBasinValueNumberInBasinNew（期待）理由
A1101先頭行なのでそのまま
A2101Valueが前行と同じなので据え置き
A3122Valueが変わったので+1
A4122Valueが同じなので据え置き
B151別Basinの先頭行なのでリセット
B272Valueが変わったので+1

解決案A：再帰CTEで前行→次行へ計算結果を伝播する

「前行の計算結果（NumberInBasinNew）を参照して次行を決める」ことをSQL Serverだけで表現する定番が 再帰CTE です。Basin内の並び順を ROW_NUMBER() で固定し、1行目（アンカー）から2行目以降へ計算結果を伝播します。

実装のひな形（まずはSELECTで結果確認）

実務では、元テーブルを直接更新する前に、作業用の#tempに落としてインデックスを張るのが安定します（後述の性能対策にも直結します）。

-- 作業用テーブル（例）
DROP TABLE IF EXISTS #Work;

SELECT
    WorkId = IDENTITY(int, 1, 1),
    t.basin,
    t.prebasin,
    t.NumberInBasin,
    t.Value,
    t.CumulativeBasin,
    NumberInBasinNew = CAST(NULL AS int)
INTO #Work
FROM dbo.MyTable AS t;

-- 並び順を支えるインデックス（重要）
CREATE UNIQUE CLUSTERED INDEX IX_Work_Order
ON #Work (basin, NumberInBasin, WorkId);

;WITH Ordered AS (
    SELECT
        WorkId, basin, prebasin, NumberInBasin, Value, CumulativeBasin,
        rn = ROW_NUMBER() OVER (
                PARTITION BY basin
                ORDER BY NumberInBasin, WorkId
             )
    FROM #Work
),
Rec AS (
    -- アンカー：Basin内の先頭行
    SELECT
        WorkId, basin, rn, NumberInBasin, Value, CumulativeBasin,
        NumberInBasinNew = NumberInBasin
    FROM Ordered
    WHERE rn = 1

    UNION ALL

    -- 再帰：前行（Rec）から次行（Ordered）へ
    SELECT
        o.WorkId, o.basin, o.rn, o.NumberInBasin, o.Value, o.CumulativeBasin,
        NumberInBasinNew =
            CASE
                WHEN ( /* +1する条件をここに記述 */ ) THEN r.NumberInBasinNew + 1
                ELSE r.NumberInBasinNew
            END
    FROM Rec AS r
    INNER JOIN Ordered AS o
        ON o.basin = r.basin
       AND o.rn    = r.rn + 1
)
SELECT *
FROM Rec
ORDER BY basin, rn
OPTION (MAXRECURSION 0);

条件の書き方の例

上の /* +1する条件 */ に入れる式は要件に合わせて変えます。以下は例です。

  • 例1：Valueが変わったら+1
    → o.Value <> r.Value
  • 例2：累積が「前行のNumberInBasinNew×100」を超えたら+1（前行の計算結果を参照）
    → o.CumulativeBasin >= r.NumberInBasinNew * 100.0

UPDATEに変換する（#Workに反映）

計算結果を #Work に書き戻すには、再帰CTEの結果と WorkId をJOINしてUPDATEします。

;WITH Ordered AS (
    SELECT
        WorkId, basin, prebasin, NumberInBasin, Value, CumulativeBasin,
        rn = ROW_NUMBER() OVER (
                PARTITION BY basin
                ORDER BY NumberInBasin, WorkId
             )
    FROM #Work
),
Rec AS (
    SELECT
        WorkId, basin, rn, NumberInBasin, Value, CumulativeBasin,
        NumberInBasinNew = NumberInBasin
    FROM Ordered
    WHERE rn = 1

    UNION ALL

    SELECT
        o.WorkId, o.basin, o.rn, o.NumberInBasin, o.Value, o.CumulativeBasin,
        NumberInBasinNew =
            CASE
                WHEN ( /* +1する条件 */ ) THEN r.NumberInBasinNew + 1
                ELSE r.NumberInBasinNew
            END
    FROM Rec AS r
    INNER JOIN Ordered AS o
        ON o.basin = r.basin
       AND o.rn    = r.rn + 1
)
UPDATE w
SET w.NumberInBasinNew = r.NumberInBasinNew
FROM #Work AS w
INNER JOIN Rec AS r
    ON r.WorkId = w.WorkId
OPTION (MAXRECURSION 0);

再帰CTE運用の注意点

  • MAXRECURSION：デフォルトは100なので、Basin内の行数が多いと途中で止まります。必要に応じて OPTION (MAXRECURSION 0)（無制限）または上限値を指定します。
  • 無限ループ対策：rn = r.rn + 1 のように、必ず前進する結合条件にして“循環”を作らないことが重要です。
  • キーの一意性：更新JOINに使うキーは一意である必要があります。迷ったら作業用に WorkId を作るのが安全です。

解決案B：カーソルで順次更新する（最後の手段として）

再帰CTEが極端に遅い、または条件が複雑で集合演算に落とせない場合、カーソルで“前行の状態”を保持しながら計算するのは現実的な選択肢です。ただし、SQL Serverのカーソルは基本的にRBAR（Row By Agonizing Row）になりやすいので、使いどころを絞ります。

カーソルが許容されやすい条件理由
対象行が小さい（例：数千〜数万）更新コストが許容できる範囲に収まりやすい。
前段で“対象を絞り込んだ作業用テーブル”が作れている巨大テーブルに対して逐次更新するとロック・ログ・I/Oが重くなる。
順序キーが一意で、インデックスで順次読めるカーソルの読み取りがソート依存になると、それだけで遅くなる。

実装例：更新は最後にまとめて行う（ロック/ログを軽くする）

「カーソルで計算」「結果は一時表に貯めて最後にUPDATE」を分離すると、体感が良くなることが多いです。

-- 計算結果の受け皿（行数が多いなら#temp推奨）
DROP TABLE IF EXISTS #Result;
CREATE TABLE #Result (
    WorkId int NOT NULL PRIMARY KEY,
    NumberInBasinNew int NOT NULL
);

DECLARE
    @WorkId int,
    @basin  sql_variant,   -- basinの型に合わせて変更（int, varchar等）
    @NumberInBasin int,
    @Value decimal(18, 6),
    @CumulativeBasin decimal(18, 6);

DECLARE
    @prevBasin sql_variant = NULL,
    @prevNew int = NULL,
    @New int;

DECLARE cur CURSOR LOCAL FORWARD_ONLY FOR
SELECT WorkId, basin, NumberInBasin, Value, CumulativeBasin
FROM #Work
ORDER BY basin, NumberInBasin, WorkId;

OPEN cur;

FETCH NEXT FROM cur
INTO @WorkId, @basin, @NumberInBasin, @Value, @CumulativeBasin;

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @prevBasin IS NULL OR @basin <> @prevBasin
    BEGIN
        -- 先頭行扱い
        SET @New = @NumberInBasin;
    END
    ELSE
    BEGIN
        -- 2行目以降：前行の計算結果(@prevNew)を使って更新
        SET @New =
            CASE
                WHEN ( /* +1する条件（前行の計算結果が必要なら@prevNewを参照） */ )
                    THEN @prevNew + 1
                ELSE @prevNew
            END;
    END

    INSERT INTO #Result(WorkId, NumberInBasinNew)
    VALUES (@WorkId, @New);

    SET @prevBasin = @basin;
    SET @prevNew   = @New;

    FETCH NEXT FROM cur
    INTO @WorkId, @basin, @NumberInBasin, @Value, @CumulativeBasin;
END

CLOSE cur;
DEALLOCATE cur;

-- 最後にまとめて更新
UPDATE w
SET w.NumberInBasinNew = r.NumberInBasinNew
FROM #Work AS w
INNER JOIN #Result AS r
    ON r.WorkId = w.WorkId;

カーソルでも遅いときの見直しポイント

  • 並び替えが発生していないか：ORDER BY basin, NumberInBasin を支えるインデックスが無いと、カーソル開始時点で大きなソートが走ります。
  • 更新対象が#Workではなく巨大テーブルになっていないか：まず#Workに計算結果を作り、最後に本テーブルへ反映する方が安定します。
  • 条件式の中で暗黙変換が起きていないか：型不一致（例：varcharとint比較）でインデックスが効かず、想像以上に遅くなります。

「2500行で3時間」のような極端な遅さが起きる典型原因

結論から言うと、“最終的に2500行”でも、遅さの主因は前段（集計・結合・ソート・重い式）にあるケースが大半です。再帰CTEやカーソル自体が悪いというより、そこに渡すまでの準備が重すぎる状態です。

症状ありがちな原因実務での対策
ROW_NUMBERで急に遅くなるPARTITION BY basin ORDER BY NumberInBasin を支える索引がなく、巨大なソートが発生している作業用#tempを作り (basin, NumberInBasin, ID) にクラスタ化インデックスを付ける。並び順に必要な列を揃える。
CTEを重ねるほど遅いCTEは“結果を保持する”とは限らず、参照されるたびに評価されて重くなることがある段階的に #tempへ落として固定（材料の中間結果を物理化）し、必要なインデックスを作る。
自己結合や前行探索で爆発する「前行を探す」ための相関サブクエリ/自己結合が行数に対して二乗的に増え、O(n²)に近い動きになる前行参照は LAG() / ROW_NUMBER() を使う。どうしても逐次なら再帰CTEかカーソルで“前行状態を保持”する。
見た目よりI/Oが多い前段で結合対象が膨張（重複行が増える）し、最後にGROUP BYで潰している結合順序・フィルタを早い段階へ移動。必要列だけを先に抽出してから集計する。
一時領域(tempdb)待ちが多いソート/ハッシュ/スプールがtempdbを使い切っている中間結果を小さくする、索引でソートを回避する、統計情報を更新する。tempdb構成も見直し対象。

多段CTEで重いなら「中間結果を#tempに固定」する

CTEは読みやすい一方で、複雑化するとオプティマイザの判断次第で「同じ計算を何度もやる」プランになりがちです。実務では、次のように 材料を固定してから 連番更新に入ると改善しやすいです。

-- 例：前段の集計結果を固定する
DROP TABLE IF EXISTS #Stage1;

SELECT
    t.basin,
    t.NumberInBasin,
    t.Value,
    t.CumulativeBasin,
    t.prebasin
INTO #Stage1
FROM dbo.SourceTable AS t
WHERE t.SomeFlag = 1;   -- 早い段階で絞る

-- 連番更新で使う順序を支える索引
CREATE UNIQUE CLUSTERED INDEX IX_Stage1
ON #Stage1 (basin, NumberInBasin);

-- 必要ならここで統計更新（大量データで効くことがある）
-- UPDATE STATISTICS #Stage1;

-- #Stage1 → #Work など、段階分離してから再帰CTE/ウィンドウ関数へ

再帰を避けられるなら最優先：LAG + 累積SUMで集合演算に落とす

要件の形としては、次の式にできるケースがよくあります。

NumberInBasinNew = 先頭値 +（条件を満たした回数の累積）

この形に落とせるなら、再帰CTEもカーソルも不要になり、SQL Serverが得意なウィンドウ関数で一気に高速化できます。鍵は「+1する条件」が前行の計算結果そのものではなく、前行の生データ（ValueやCumulativeなど）で判定できるかどうかです。

例：Valueが変わったら+1（ウィンドウ関数で完結）

-- #Work に WorkId, basin, NumberInBasin, Value がある想定
;WITH Ordered AS (
    SELECT
        WorkId,
        basin,
        NumberInBasin,
        Value,
        rn = ROW_NUMBER() OVER (
                PARTITION BY basin
                ORDER BY NumberInBasin, WorkId
             ),
        prev_value = LAG(Value) OVER (
                PARTITION BY basin
                ORDER BY NumberInBasin, WorkId
             ),
        first_num = FIRST_VALUE(NumberInBasin) OVER (
                PARTITION BY basin
                ORDER BY NumberInBasin, WorkId
             )
    FROM #Work
),
Flags AS (
    SELECT
        WorkId,
        basin,
        rn,
        first_num,
        inc = CASE
                WHEN rn = 1 THEN 0
                WHEN Value <> prev_value THEN 1
                ELSE 0
              END
    FROM Ordered
),
Calc AS (
    SELECT
        WorkId,
        NumberInBasinNew =
            first_num
            + SUM(inc) OVER (
                PARTITION BY basin
                ORDER BY rn
                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
              )
    FROM Flags
)
UPDATE w
SET w.NumberInBasinNew = c.NumberInBasinNew
FROM #Work AS w
INNER JOIN Calc AS c
    ON c.WorkId = w.WorkId;

「前行の計算結果に依存しているように見える」条件を分解するコツ

本当に再帰が必要かは、条件式の正体を分解すると判断しやすくなります。実務で効く見直し観点を挙げます。

  • しきい値跨ぎは“直接計算”できないか
    例：累積が100ごとに+1なら、逐次で数えるより FLOOR(CumulativeBasin / 100) のようにバケット番号を作れます。
  • 「変化点」や「境界」だけをフラグ化できないか
    例：前行との差分、前行との比較は LAG() で作れます。境界フラグが作れれば累積SUMに落とせます。
  • “前行の計算結果”ではなく“前行の状態”で判定できないか
    前行のValueや前行のCumulativeなど、生データの前行参照で済むなら再帰を外せる可能性が高いです。

インデックス設計チェックリスト（Basin単位の連番更新）

再帰CTEでもカーソルでもウィンドウ関数でも、結局は「並び順」と「参照列」を支える索引が効きます。特に#temp上に適切な索引を作るだけで、劇的に改善することがあります。

目的推奨インデックス例期待効果
Basin内の順序を高速に読むCLUSTERED (basin, NumberInBasin, ID)ROW_NUMBER やカーソルの ORDER BY がソートなしで進みやすい。
条件判定に使う列を取りこぼさない上記キー + INCLUDE (Value, CumulativeBasin, prebasin)余計なKey Lookupを減らし、I/Oを抑える。
更新JOINのキーを一意にするWorkId（IDENTITY等）を主キー化更新が安全になり、重複JOINで行が増える事故を防ぐ。

よくある落とし穴

「前行を探す自己結合」をUPDATEに書くと、なぜ遅くなりやすいのか

よくある失敗例が「各行ごとに、同一Basinの直前行をサブクエリで探す」形です。例えば TOP(1) ... ORDER BY NumberInBasin DESC を行ごとに回すと、データ量や索引次第で実質的に二乗的な動きになり、行数が増えた瞬間に破綻します。前行参照は基本的に LAG/ROW_NUMBER へ寄せるのが鉄則です。

「2500行しかないのに遅い」場合、見るべきはどこか

  • 最終結果が2500行でも、前段のJOINが何十万〜何千万行を作っていないか
  • ROW_NUMBER のためのソートが巨大になっていないか（索引で回避できないか）
  • 暗黙変換、スカラーUDF、複雑な計算式が行数分評価されていないか
  • CTE多段で同じ計算が再評価されていないか（#tempへ固定）

まとめ：最短で安定稼働させる実務手順

  1. 「前行」が何かを一意に定義し、(basin, NumberInBasin, ID) で順序を確定する
  2. 前段の集計結果は #tempに固定し、必要なインデックスを作る（多段CTEのまま突っ込まない）
  3. 条件が前行の生データ参照で済むなら、LAG + 累積SUM の集合演算で一気に計算する
  4. どうしても前行の計算結果が必要なら、再帰CTE（まずは#Work上で）で計算結果を伝播する
  5. 再帰CTEが厳しい/複雑すぎる場合のみ、カーソルで計算→結果をまとめてUPDATEにする
Microsoft
SQL Server パフォーマンス ウィンドウ関数 再帰CTE LAG

この記事を書いた人

佐藤健司のアバター 佐藤健司

実務の現場で詰まりがちなポイントを地図にするITブログ「IT trip」を運営。Windows／Office（Teams・Excel）からSQL、サーバ運用、ガジェットまで、再現性のある手順と“なぜそうなるか”を丁寧に解説します。読んだらすぐ試せること、そして迷った人の次の一歩が見えることを大切にしています。

関連記事

コメント

コメントする

目次