SQL Server 再帰CTEエラー解決：MIN(created_at)起点で1年分の週区切り・週番号（current week）を生成する方法

SQL Serverでユーザーごとの最初のcreated_atを起点に、1年分の週区切り（週番号付き）を生成して「今日が属する週」を取りたい。再帰CTEでMIN(created_at)を使うと発生するエラーの原因と、起点日を再帰の外に出して解決する実装を、コピペできるSQL例と運用の注意点込みで整理します。

やりたいこと：ユーザーの最初の作成日から「相対週」を1年分生成し、current week（今日の週番号）を求める

本記事で扱う「週」は、カレンダー上の週（ISO週番号や、週の開始曜日に依存する週）ではありません。ユーザーごとの最初の作成日（MIN(created_at)）を“週1の開始点”とし、そこから7日単位で区切る「相対週」です。

要件を噛み砕くと次のとおりです。

• 対象ユーザーは @UserId
• 起点は MIN(created_at)（そのユーザーが最初に作成された日時）
• 週1：起点〜起点+6日（7日間）
• 週2：起点+7日〜起点+13日
• …を、起点から1年間ぶん生成
• GETDATE()（または SYSDATETIME()）が属する週の週番号を取得

例として、起点が 2025-01-10 の場合、区間は次のようになります（終端は「含む」と「未満」を両方載せています）。

週番号	開始（start_of_week）	終了（含む）	終了（未満：end_exclusive）
1	2025-01-10	2025-01-16	2025-01-17
2	2025-01-17	2025-01-23	2025-01-24
3	2025-01-24	2025-01-30	2025-01-31

この「相対週」を作ると、ユーザー開始日を基準にしたオンボーディング、課金開始後の週次利用率、コホート分析（ユーザーの経過週別の行動）などが作りやすくなります。

再帰CTEでエラーになる理由：再帰部（recursive member）に集計が置けない

再帰CTEは、アンカー（最初の1行）と、そこから行を増やす再帰部（recursive member）で構成されます。

ところがSQL Serverでは、再帰部の中に GROUP BY / HAVING / 集計関数（MIN など）を含めることができません。そのため、再帰部の WHERE などで MIN(created_at) を含むサブクエリを書いた瞬間に、次のエラーが出ます。

GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part of a recursive common table expression

よくある「失敗例」を簡略化すると、イメージはこうです（実際にはテーブル名や条件が違っても、構造が同じなら同じエラーになります）。

DECLARE @UserId int = 123;

;WITH Weeks AS
(
  -- アンカー
  SELECT
    1 AS week_no,
    (SELECT MIN(created_at) FROM dbo.UserLog WHERE user_id = @UserId) AS start_of_week

  UNION ALL

  -- 再帰部（ここが問題）
  SELECT
    week_no + 1,
    DATEADD(day, 7, start_of_week)
  FROM Weeks
  WHERE DATEADD(day, 7, start_of_week) < DATEADD(year, 1,
        (SELECT MIN(created_at) FROM dbo.UserLog WHERE user_id = @UserId)  -- 集計が再帰部に混入
      )
)
SELECT * FROM Weeks;

再帰のたびに「MIN(created_at)」を取り直したくなる気持ちは自然ですが、SQL Serverのルール上それはできません。解決の鍵は「起点日（MIN）を再帰の外で確定させる」ことです。

解決方針：MIN(created_at) は先に1回だけ計算し、再帰では参照するだけにする

エラーを避ける最短ルートはこれです。

• 起点日（最初の作成日）を、再帰の外側で1回だけ求める
• 再帰CTEの中では、その値を使って週を増やすだけにする
• 「今日が属する週」の判定も、起点を再計算せずに行う

具体的には次の2パターンが実務で使いやすいです。

• 解決策A：起点日を変数に退避してから再帰CTE（ストアドやバッチで特に読みやすい）
• 解決策B：起点日CTEを作ってCROSS JOIN（単一SQLにしたい／ビュー化したいときに有利）

解決策A：起点日を変数に退避してから再帰CTEで週区切り（週番号付き）を生成する

まずは採用されやすい「変数方式」です。MIN(created_at) を変数に入れてしまえば、再帰部に集計は登場しません。

サンプル前提テーブル（例）

テーブルは例えば次のようなものを想定します。もちろん列名が違っても置き換えればOKです。

-- 例：ユーザーのイベントログ
-- user_id: ユーザー識別子
-- created_at: イベントが作られた日時
CREATE TABLE dbo.UserLog
(
  user_id    int           NOT NULL,
  created_at datetime2(0)  NOT NULL,
  payload    nvarchar(200) NULL
);

-- MIN(created_at) を高速に取るためのインデックス例
CREATE INDEX IX_UserLog_UserId_CreatedAt
ON dbo.UserLog(user_id, created_at);

「今日が属する週番号」を返すクエリ（週の区間も同時に取得）

境界の重複を避けるため、週の判定は 終端を「未満」にします（後述）。

DECLARE @UserId int = 123;

-- 起点（ユーザーの最初の作成日時）を先に確定
DECLARE @StartAt datetime2(0);
SELECT @StartAt = MIN(created_at)
FROM dbo.UserLog
WHERE user_id = @UserId;

-- レコードが無いユーザー対策
IF @StartAt IS NULL
BEGIN
  SELECT
    CAST(NULL AS int)          AS current_week_no,
    CAST(NULL AS datetime2(0)) AS start_of_week,
    CAST(NULL AS datetime2(0)) AS end_of_week_exclusive;
  RETURN;
END;

DECLARE @Now datetime2(0) = SYSDATETIME();  -- GETDATE()でもOK（精度が欲しいならSYSDATETIME）

;WITH Weeks AS
(
  -- 週1（アンカー）
  SELECT
    1 AS week_no,
    @StartAt AS start_of_week
  UNION ALL
  -- 週2以降（再帰部：集計なし）
  SELECT
    week_no + 1,
    DATEADD(day, 7, start_of_week)
  FROM Weeks
  WHERE DATEADD(day, 7, start_of_week) < DATEADD(year, 1, @StartAt)
)
SELECT TOP (1)
  week_no AS current_week_no,
  start_of_week,
  DATEADD(day, 7, start_of_week) AS end_of_week_exclusive
FROM Weeks
WHERE @Now >= start_of_week
  AND @Now <  DATEADD(day, 7, start_of_week)
ORDER BY week_no
OPTION (MAXRECURSION 100);

このクエリは次を満たします。

• 起点日を変数に退避するため、再帰部に集計が出てこない（エラー回避）
• 1年分（起点〜起点+1年未満）の週開始日を作る
• 今日が属する週を1行で返す（current_week_no）

「全週分の一覧」が欲しい場合

週一覧をレポートやデバッグで見たい場合は、最後の WHERE @Now ... を外して並べればOKです。

-- 上のCTE（Weeks）をそのまま使う前提
SELECT
  week_no,
  start_of_week,
  DATEADD(day, 6, start_of_week) AS end_of_week_inclusive,
  DATEADD(day, 7, start_of_week) AS end_of_week_exclusive
FROM Weeks
ORDER BY week_no
OPTION (MAXRECURSION 100);

「1年の終端を超えない」ように最終週の終端を丸めたい場合

要件によっては、最終週の終端が「起点+1年」を超えるのが気持ち悪いことがあります。そんなときは、end_exclusive を CASE で丸めます。

DECLARE @EndAt datetime2(0) = DATEADD(year, 1, @StartAt);

SELECT
  week_no,
  start_of_week,
  CASE
    WHEN DATEADD(day, 7, start_of_week) > @EndAt THEN @EndAt
    ELSE DATEADD(day, 7, start_of_week)
  END AS end_of_week_exclusive
FROM Weeks
ORDER BY week_no
OPTION (MAXRECURSION 100);

「週区切りを1年以内に完全に収めたい」「最後の週だけ短くしてよい」という要件なら、この丸めが綺麗にハマります。

解決策B：起点日CTEを作り、CROSS JOINで参照して単一SQLにまとめる

ストアドの中なら変数方式（解決策A）が読みやすい一方、ビューにしたい／アプリ側で単発SQLとして投げたい場合は、起点日をCTE化して1本にまとめると便利です。

ポイントは次の2つです。

• MinCreated というCTEで MIN(created_at) を1行に固定
• 再帰部では CROSS JOIN MinCreated でその値を参照するだけ（集計は書かない）

DECLARE @UserId int = 123;

;WITH MinCreated AS
(
  SELECT MIN(created_at) AS start_at
  FROM dbo.UserLog
  WHERE user_id = @UserId
),
Weeks AS
(
  -- 週1（アンカー）
  SELECT
    1 AS week_no,
    m.start_at AS start_of_week
  FROM MinCreated m
  WHERE m.start_at IS NOT NULL

  UNION ALL

  -- 週2以降（再帰部：MinCreatedを参照するだけ）
  SELECT
    w.week_no + 1,
    DATEADD(day, 7, w.start_of_week)
  FROM Weeks w
  CROSS JOIN MinCreated m
  WHERE DATEADD(day, 7, w.start_of_week) < DATEADD(year, 1, m.start_at)
)
SELECT TOP (1)
  w.week_no AS current_week_no,
  w.start_of_week,
  DATEADD(day, 7, w.start_of_week) AS end_of_week_exclusive
FROM Weeks w
WHERE SYSDATETIME() >= w.start_of_week
  AND SYSDATETIME() <  DATEADD(day, 7, w.start_of_week)
ORDER BY w.week_no
OPTION (MAXRECURSION 100);

この形なら、@StartAt 変数を使わずに「起点日→週生成→current week抽出」までを1本のSQLで完結できます。アプリケーションのORMや、ビュー／関数にまとめたい場合に便利です。

比較表：変数方式（解決策A）とCTE方式（解決策B）の使い分け

観点	解決策A：変数に退避	解決策B：起点日CTE + CROSS JOIN
読みやすさ	起点日が変数で明示され、追いやすい	CTEが増えるが、1本のSQLとして完結する
利用シーン	ストアド、バッチ、SQLジョブなど	ビュー化、単発SQL、アプリ側で組み込みたいとき
起点日の再利用	変数を使って複数箇所で参照しやすい	CTEを参照する形で再利用できる
落とし穴	NULLユーザーの扱いをIFで忘れがち	MinCreatedがNULLのときに0行になる点を理解しておく

実運用でハマりやすいポイント

週の判定は「終端を未満」にする（重複・抜けを防ぐ）

区間判定を BETWEEN（両端含む）で書くと、境界の日時が複数週にマッチする可能性が出ます。特に「日付だけ」でなく「日時」になるほど事故りやすいです。

おすすめは次の形です。

• start_of_week <= @Now
• @Now < end_of_week_exclusive

終端を「未満」にしておけば、同じ瞬間が2つの週に同時に属することがありません。

DATEに丸めるか、datetime2で保持するかを最初に決める

created_at が datetime / datetime2 の場合、起点を DATE にキャストすると時刻が切り捨てられます。これは「週は日単位で十分」という要件なら便利ですが、次のようなズレが起きます。

• 起点が 2025-01-10 23:50 でも、2025-01-10 00:00 扱いになる
• 「起点からちょうど7×n日後」という厳密な区切りが必要な場合、期待と違う結果になる

迷ったら、まずは datetime2で起点を保持し、必要に応じて表示だけ日付に整形するのが安全です。日単位で揃えたい場合は、起点を明示的に丸めます。

-- 例：起点を「その日の0:00」に揃える（要件が日単位のときだけ）
SELECT @StartAt = DATEFROMPARTS(YEAR(MIN(created_at)), MONTH(MIN(created_at)), DAY(MIN(created_at)))
FROM dbo.UserLog
WHERE user_id = @UserId;

UTCで保存しているなら比較もUTCに揃える

created_at がUTCで保存されている運用は多いです。その場合、GETDATE() や SYSDATETIME()（サーバーローカル時刻）で比較すると、時差分だけ週判定がずれます。UTC保存なら、比較も SYSUTCDATETIME() に揃えるのが基本です。

DECLARE @NowUtc datetime2(0) = SYSUTCDATETIME();

-- 以降の判定は @NowUtc を使う
WHERE @NowUtc >= start_of_week
  AND @NowUtc <  DATEADD(day, 7, start_of_week);

MAXRECURSION と「1年＝何週生成するか」

再帰CTEには既定の再帰上限（既定は100）があり、これを超えるとエラーになります。ただし1年を7日区切りで回す場合は最大でも53回程度なので、通常は既定値の範囲内です。

一方で「2年分」「N年分」「ユーザーごとに可変」などに拡張する可能性があるなら、意図を明確にするために OPTION (MAXRECURSION 100) のように上限を明示しておくと、運用時の読み解きが楽になります。

MIN(created_at) を速くするインデックスが効く

今回の要件は、突き詰めると「特定ユーザーの created_at の最小値」を取る処理がボトルネックになりがちです。ここはインデックスでほぼ解決します。

-- user_idで絞ってcreated_atの先頭（最小）を取りやすい並びにする
CREATE INDEX IX_UserLog_UserId_CreatedAt
ON dbo.UserLog(user_id, created_at);

このインデックスがあると、SQL Serverは該当ユーザー範囲の先頭行を読むだけでMINを求められるため、テーブル全体のスキャンを避けられます。

再帰を使わない別解：Numbers（Tally）で週を生成する

週は最大53程度と少ないため再帰CTEでも十分ですが、再帰そのものを避けたい、あるいは大量ユーザーに対して一括で週を生成したい場合は、Numbers（連番）を使ったセットベースの生成が強力です。

単一ユーザー向け（1年分の週一覧を作る）

DECLARE @UserId int = 123;

DECLARE @StartAt datetime2(0);
SELECT @StartAt = MIN(created_at)
FROM dbo.UserLog
WHERE user_id = @UserId;

IF @StartAt IS NULL
BEGIN
SELECT CAST(NULL AS int) AS week_no, CAST(NULL AS datetime2(0)) AS start_of_week;
RETURN;
END;

DECLARE @EndAt datetime2(0) = DATEADD(year, 1, @StartAt);

;WITH N AS
(
-- 0,1,2,... を作る（sys.all_objectsは行数が多いので連番の元として便利）
SELECT TOP (60) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n
FROM sys.all_objects
)
SELECT
n + 1 AS week_no,
DATEADD(day, 7 * n, @StartAt) AS start_of_week,
CASE
WHEN DATEADD(day, 7 * (n + 1), @StartAt) > @EndAt THEN @EndAt
ELSE DATEADD(day, 7 * (n + 1), @StartAt)
END AS end_of_week_exclusive
FROM N
WHERE DATEADD(day, 7 * n, @StartAt) < @EndAt
ORDER BY week_no;

この方法は再帰を使わないため、MAXRECURSION を気にしなくてよく、実行計画が読みやすい傾向があります。

複数ユーザー向け（全ユーザーの起点から一括で週を作る）

「ユーザーごとにMIN(created_at)を取り、その全員ぶん週を作る」という用途では、再帰CTEをユーザーごとに回すより、次のように「起点一覧 × 連番」で作るほうが素直です。

DECLARE @EndWeeks int = 60; -- 余裕を持って60（1年≒53週）

;WITH UserStart AS
(
  SELECT
    user_id,
    MIN(created_at) AS start_at
  FROM dbo.UserLog
  GROUP BY user_id
),
N AS
(
  SELECT TOP (@EndWeeks) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n
  FROM sys.all_objects
)
SELECT
  us.user_id,
  n + 1 AS week_no,
  DATEADD(day, 7 * n, us.start_at) AS start_of_week,
  DATEADD(day, 7 * (n + 1), us.start_at) AS end_of_week_exclusive
FROM UserStart us
CROSS JOIN N
WHERE us.start_at IS NOT NULL
  AND DATEADD(day, 7 * n, us.start_at) < DATEADD(year, 1, us.start_at)
ORDER BY us.user_id, week_no;

「コホート分析用の週マスタ」を作りたいときは、このセットベース方式が扱いやすいです。

今日の週番号だけが欲しい場合は、生成せずに計算式で求める手もある

「週一覧はいらない。current weekの週番号だけ分かれば良い」なら、実は再帰もNumbersも不要で、起点からの経過日数で直接出せます。

日単位の定義（起点日を含む）なら次のように計算できます。

DECLARE @UserId int = 123;

DECLARE @StartAt datetime2(0);
SELECT @StartAt = MIN(created_at)
FROM dbo.UserLog
WHERE user_id = @UserId;

SELECT
  CASE
    WHEN @StartAt IS NULL THEN NULL
    WHEN SYSDATETIME() < @StartAt THEN 0  -- 起点より前なら0（要件に合わせて調整）
    ELSE (DATEDIFF(day, @StartAt, SYSDATETIME()) / 7) + 1
  END AS current_week_no;

ただし、この方法は「週の境界が日単位で良い」場合に向きます。起点時刻まで厳密に7日×nで区切りたい場合は、秒差（または分差）で計算するか、前述のように区間生成して判定するほうが確実です。

まとめ：再帰CTEのエラーは「集計を再帰の外へ」出すのが正攻法

SQL Serverの再帰CTEでは、再帰部に集計（MINなど）を含められません。週区切りを再帰で作る場合は、起点日（MIN(created_at)）を再帰の外側で1回だけ求めるのが解決の基本です。

• まずは変数に退避（解決策A）で確実にエラーを消す
• 単一SQLにしたいなら起点日CTE（解決策B）
• よりセットベースに寄せるならNumbers（Tally）で再帰自体を避ける

最後に、週の判定は「終端を未満」にする、日時型やUTC/ローカルの扱いを揃える、といった運用面のコツを押さえると、後からのバグ修正コストを大きく減らせます。