SQL Server T-SQLで投薬オーダーの有効総投与量を区間集計する方法（Gaps and Islands）

投薬オーダーが開始・停止を繰り返し、同一薬剤のオーダーが期間重複するケースでは、単純なSUMやGROUP BYでは「有効な総投与量」を正しく出せません。SQL Server / T-SQLで、総量が同じ状態が続く日付レンジ単位に区間化して集計する実装パターンを、実務目線で解説します。

なぜ「期間の集計」は難しくなるのか（問題の本質）

投薬オーダーのデータは、一般に「開始日（s_date）～終了日（e_date）」という期間で登録されます。しかし現場データには次のような“クセ”が混ざるため、期間をそのまま集計すると破綻しがちです。

• 同一薬剤が、同時期に複数オーダーとして存在する（重複期間がある）
• 途中で追加・停止されるため、ある日付時点の有効な合計投与量が日々変化する
• 知りたいのは日次の羅列ではなく、「総投与量が同じ状態が続く日付レンジ」の一覧
• cadence（投与間隔）が違うものは別扱いにしたい
• 同一dose＋同一cadence（NULL含む）なら、重複・連続（±1日程度の揺れ）を“同じ連続オーダー”としてつなげたい
• 重複期間は（同時に有効な分を）合算したい

この手の要求は、SQLの定番である「Gaps and Islands（ギャップ＆アイランド）」として扱うのが最短距離です。ポイントは「日付を全部展開して集計する」のではなく、変化点（イベント）だけを取り出し、累積和で区間を作ることです。

期待する出力イメージ（“日々”ではなく“区間”）

日別に出すと行数が膨らみ、差分も追いにくくなります。欲しいのは次のような“区間”です。

patid	Generic_Name	cadence	区間開始	区間終了	有効総投与量（合算）	（任意）投与回数
1001	DrugA	1	2025-10-15	2025-10-15	37.5	1
1001	DrugA	1	2025-10-16	2025-11-03	25.0	19

この「区間開始～区間終了」は、総投与量が変化する日（開始/終了）だけを境界として切り出せます。つまり、変化点さえ正しく扱えれば、日付展開は不要です。

前提テーブル例とサンプルデータ

説明を具体化するため、最小限のカラムで例を作ります（実データでは剤形・用法・単位・処方量などが増えますが、基本ロジックは同じです）。

CREATE TABLE dbo.MedOrders (
    patid         int            NOT NULL,
    Generic_Name  nvarchar(100)  NOT NULL,
    s_date        date           NOT NULL,
    e_date        date           NULL,          -- NULL = 終了日未確定（継続中）を想定
    dose          decimal(10,2)  NOT NULL,
    cadence_days  int            NULL           -- 例: 1=毎日, 2=隔日（業務定義に合わせて）
);

INSERT INTO dbo.MedOrders(patid, Generic_Name, s_date, e_date, dose, cadence_days) VALUES
-- 10/15だけ 37.5、10/16以降は25 を作る例（同一cadence=1で重複/追加）
(1001, N'DrugA', '2025-10-15', '2025-11-03', 12.50, 1),
(1001, N'DrugA', '2025-10-15', '2025-11-03', 12.50, 1),
(1001, N'DrugA', '2025-10-15', '2025-10-15', 12.50, 1),

-- cadence違いは別扱い（同じDrugAでも別パーティション）
(1001, N'DrugA', '2025-10-20', '2025-10-30',  5.00, 2),

-- NULL cadence 例（業務上の意味は要定義）
(1001, N'DrugA', '2025-11-01', '2025-11-01', 10.00, NULL);

ここでの重要な前提は、e_dateはその日まで有効（inclusive）として扱うことです。SQLで区間を作るときは、inclusive/exclusiveの扱いを統一しないと1日ズレが発生します。

解決の全体像：イベント化＋累積和で「区間」を作る

実務で強いのは、期間を日付展開せずに処理できる「イベント化＋running sum」です。概念図にすると次の通りです。

処理段階	やること	狙い
正規化	同一dose＋同一cadenceの重複/連続（±1日）を1本の区間にまとめる	データ入力の揺れ・重複で区間が細切れになるのを防ぐ
イベント化	開始日を「+dose」、終了日の翌日を「-dose」として変換	変化点だけを扱い、行数を抑える
累積和	イベント日順にrunning sumを取って「その日からの総投与量」を得る	同時に有効な分を自然に合算できる
区間化	次のイベント日の前日までを1区間として出力	「総量が同じ状態が続くレンジ」になる

ステップ1：同一dose＋同一cadenceの「連続オーダー」を正規化する

まず、同一dose＋同一cadence（NULL含む）で、重複している／連続している区間を1本にまとめます。ここを先にやる理由は、データ入力の都合で「同じ内容なのに、開始・終了が細切れ」になっていると、後段の区間化が不要に複雑化するからです。

ここでは「前の区間の終了日＋1日以内に次が開始する（重複または連続）」なら同じ連続オーダーとしてまとめる実装にします。要件が「±1日」の揺れを許容したい場合も、このルールに寄せると運用しやすいことが多いです（必要なら+2日などに調整します）。

DECLARE @from date = '2025-10-01';
DECLARE @to   date = '2025-11-30';

;WITH base AS (
    SELECT
        patid,
        Generic_Name,
        dose,
        cadence_days,
        CAST(s_date AS date) AS s_date,
        CAST(e_date AS date) AS e_date
    FROM dbo.MedOrders
    WHERE s_date <= @to
      AND ISNULL(e_date, @to) >= @from
),
prep AS (
    SELECT
        patid,
        Generic_Name,
        dose,
        cadence_days,
        ISNULL(cadence_days, -1) AS cadence_key,     -- NULL cadence を同一扱いするキー
        s_date,
        ISNULL(e_date, @to) AS e_date_capped         -- 期間集計の上限でキャップ（無期限を安全に扱う）
    FROM base
),
ordered AS (
    SELECT
        *,
        MAX(e_date_capped) OVER (
            PARTITION BY patid, Generic_Name, dose, cadence_key
            ORDER BY s_date, e_date_capped
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ) AS prev_running_end
    FROM prep
),
marked AS (
    SELECT
        *,
        CASE
            WHEN prev_running_end IS NULL THEN 1
            WHEN s_date <= DATEADD(day, 1, prev_running_end) THEN 0  -- 重複/連続なら同一
            ELSE 1
        END AS is_new_island
    FROM ordered
),
islands AS (
    SELECT
        *,
        SUM(is_new_island) OVER (
            PARTITION BY patid, Generic_Name, dose, cadence_key
            ORDER BY s_date, e_date_capped
            ROWS UNBOUNDED PRECEDING
        ) AS island_id
    FROM marked
)
SELECT
    patid,
    Generic_Name,
    dose,
    NULLIF(cadence_key, -1) AS cadence_days,
    MIN(s_date) AS s_date,
    MAX(e_date_capped) AS e_date
FROM islands
GROUP BY patid, Generic_Name, dose, cadence_key, island_id
ORDER BY patid, Generic_Name, cadence_days, dose, s_date;

この結果で「同じdose・同じcadenceなのに細切れ」の区間が1本にまとまります。以降の処理は、この正規化済み区間を入力にします。

ステップ2：期間を「開始イベント＋終了翌日イベント」に変換する

次に、区間（s_date～e_date）をイベント（変化点）にします。

• 開始日：+dose
• 終了日の翌日：-dose（e_dateはinclusiveなので「翌日」に落とすのがコツ）

これだけで「その日から有効な総投与量」は、イベント日順に累積和を取るだけで表現できます。日次展開と違って、行数はオーダー数×2程度で済むため、期間が長いデータほど効きます。

ステップ3：running sum（累積和）で日付時点の「有効総投与量」を作る

イベントを作ったら、patid＋薬剤（Generic_Name）＋cadenceでパーティションし、イベント日でソートしてrunning sumを計算します。これで「その日からの総投与量」が得られます。

ステップ4：次のイベント日の前日までを1区間として畳み込む

最後に、LEAD()で「次のイベント日」を取り、次のイベント日の前日までを1つの区間として確定します。これが「総投与量が同じ状態が続く日付レンジ」です。

以下に、ステップ1～4をつないだ“区間出力”のテンプレを示します（そのまま実務に持ち込める形を意識してあります）。

DECLARE @from date = '2025-10-01';
DECLARE @to   date = '2025-11-30';

;WITH base AS (
    SELECT
        patid,
        Generic_Name,
        dose,
        cadence_days,
        CAST(s_date AS date) AS s_date,
        CAST(e_date AS date) AS e_date
    FROM dbo.MedOrders
    WHERE s_date <= @to
      AND ISNULL(e_date, @to) >= @from
),
prep AS (
    SELECT
        patid,
        Generic_Name,
        dose,
        cadence_days,
        ISNULL(cadence_days, -1) AS cadence_key,
        s_date,
        ISNULL(e_date, @to) AS e_date_capped
    FROM base
),
-- 1) 同一dose＋同一cadence の重複/連続を正規化（ギャップ＆アイランド）
ordered AS (
    SELECT
        *,
        MAX(e_date_capped) OVER (
            PARTITION BY patid, Generic_Name, dose, cadence_key
            ORDER BY s_date, e_date_capped
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ) AS prev_running_end
    FROM prep
),
marked AS (
    SELECT
        *,
        CASE
            WHEN prev_running_end IS NULL THEN 1
            WHEN s_date <= DATEADD(day, 1, prev_running_end) THEN 0
            ELSE 1
        END AS is_new_island
    FROM ordered
),
islands AS (
    SELECT
        *,
        SUM(is_new_island) OVER (
            PARTITION BY patid, Generic_Name, dose, cadence_key
            ORDER BY s_date, e_date_capped
            ROWS UNBOUNDED PRECEDING
        ) AS island_id
    FROM marked
),
normalized AS (
    SELECT
        patid,
        Generic_Name,
        dose,
        cadence_days,
        cadence_key,
        MIN(s_date) AS s_date,
        MAX(e_date_capped) AS e_date
    FROM islands
    GROUP BY patid, Generic_Name, dose, cadence_days, cadence_key, island_id
),
-- 2) 集計対象期間にクリップ（期間外の部分は切り落とす）
clipped AS (
    SELECT
        patid,
        Generic_Name,
        dose,
        cadence_days,
        cadence_key,
        CASE WHEN s_date < @from THEN @from ELSE s_date END AS s_date,
        CASE WHEN e_date > @to   THEN @to   ELSE e_date END AS e_date
    FROM normalized
    WHERE s_date <= @to AND e_date >= @from
),
-- 3) 期間→イベント（開始 +dose、終了翌日 -dose）
events_raw AS (
    SELECT patid, Generic_Name, cadence_days, cadence_key, s_date AS event_date, +dose AS delta_dose
    FROM clipped
    UNION ALL
    SELECT patid, Generic_Name, cadence_days, cadence_key, DATEADD(day, 1, e_date) AS event_date, -dose AS delta_dose
    FROM clipped
    UNION ALL
    -- 4) 最終区間を閉じるための番兵イベント（@to+1）
    SELECT d.patid, d.Generic_Name, d.cadence_days, d.cadence_key, DATEADD(day, 1, @to) AS event_date, 0 AS delta_dose
    FROM (SELECT DISTINCT patid, Generic_Name, cadence_days, cadence_key FROM clipped) AS d
),
-- 同日に複数イベントがあるときはまとめてから累積和
events AS (
    SELECT
        patid,
        Generic_Name,
        cadence_days,
        cadence_key,
        event_date,
        SUM(delta_dose) AS delta_dose
    FROM events_raw
    GROUP BY patid, Generic_Name, cadence_days, cadence_key, event_date
),
-- 5) running sum と次イベント日
running AS (
    SELECT
        *,
        SUM(delta_dose) OVER (
            PARTITION BY patid, Generic_Name, cadence_key
            ORDER BY event_date
            ROWS UNBOUNDED PRECEDING
        ) AS total_dose,
        LEAD(event_date) OVER (
            PARTITION BY patid, Generic_Name, cadence_key
            ORDER BY event_date
        ) AS next_event_date
    FROM events
),
-- 6) 区間化（次イベント日の前日まで）
segments AS (
    SELECT
        patid,
        Generic_Name,
        cadence_days,
        cadence_key,
        event_date AS s_date,
        DATEADD(day, -1, next_event_date) AS e_date,
        total_dose
    FROM running
    WHERE next_event_date IS NOT NULL
)
SELECT
    patid,
    Generic_Name,
    NULLIF(cadence_key, -1) AS cadence_days,
    s_date,
    e_date,
    total_dose,
    -- （任意）投与回数：cadence_days を「日数間隔」とみなす場合の例
    CASE
        WHEN total_dose = 0 THEN NULL
        WHEN NULLIF(cadence_key, -1) IS NULL THEN 1
        WHEN NULLIF(cadence_key, -1) <= 0 THEN NULL
        ELSE (DATEDIFF(day, s_date, e_date) / NULLIF(cadence_key, -1)) + 1
    END AS admin_count
FROM segments
WHERE s_date <= @to
  AND e_date >= @from
  AND total_dose <> 0
ORDER BY patid, Generic_Name, cadence_days, s_date;

このクエリの重要ポイントを、実務で詰まりやすい箇所に絞って補足します。

実務で差が出るポイント

inclusiveな終了日を「終了翌日イベント」で扱う理由

e_dateがその日まで有効（inclusive）なら、e_date当日はまだdoseが効いています。そこで減算はe_date当日ではなく、翌日に置きます。こうすると区間化したときに、区間終了日＝次イベント日の前日という一貫したルールで書けます。

cadence違いを別扱いにする設計

要件にある「cadenceが違えば別扱い」は、T-SQL的にはパーティションキーにcadenceを含めるだけです。上のクエリでは cadence_key を使い、NULL cadenceも同一の値として扱えるようにしています。

NULL cadence の意味を業務ルールで決める

NULL cadenceはデータ的には「未設定」ですが、業務上は以下のような意味を持つことがあります。

• 単回投与（投与回数は常に1）
• 頓用（回数計算ができない）
• 外部システム都合で欠損（本来はBIDなど別カラムで持つ）

投与回数の算出は、ここが曖昧なまま実装すると現場で揉めやすいので、出力に含めるなら「NULLの扱い」を先に決めてから式を確定してください。

投与回数を出したいときの考え方（cadence=日数間隔の例）

「有効総投与量（/投与回数）」という要件は、実装上は2段階に分けると安全です。

• まず区間（s_date～e_date）と、その区間の総投与量（合算）を確定する
• 次に、区間長とcadenceから投与回数を計算する（業務ルールを反映）

たとえばcadence_days=1（毎日）なら、区間の投与回数は「区間日数＋1」です。cadence_days=2（隔日）なら、日数差を2で割って＋1になります。

cadence_days	区間	計算例	投与回数
1	10/16～11/03	(DATEDIFF(day, 10/16, 11/03) / 1) + 1 = (18/1)+1	19
2	10/20～10/30	(DATEDIFF(day, 10/20, 10/30) / 2) + 1 = (10/2)+1	6

一方でcadenceが「BID」「TID」「q12h」のように“日数”で表現できない場合は、別途マスタを作って「cadence→間隔（分/時間/日）」へ正規化してから同様に計算するのが王道です。文字列のまま数式に突っ込むと、例外処理が増えてメンテ不能になります。

方法B：日次に展開してから再度区間化する（分かりやすいが重くなりやすい）

もう一つの考え方として、カレンダーテーブル（Numbersテーブル）で期間を日次に展開し、「その日に有効なオーダーを合算」してから、値が変わらない日付を再度Gaps and Islandsでまとめる方法もあります。

メリットは直感的で、途中の検算がしやすい点です。デメリットは、対象期間が長いほど行数が爆発する点です（患者数×薬剤数×日数）。短い期間に限定するレポートなら有効ですが、長期や全患者を対象にするならイベント方式が現実的です。

-- 例：日次展開（Numbers/Calendar がある前提のイメージ）
-- dbo.Calendar(date_value) が連続日付を持つとする

DECLARE @from date = '2025-10-01';
DECLARE @to   date = '2025-11-30';

WITH d AS (
    SELECT date_value
    FROM dbo.Calendar
    WHERE date_value BETWEEN @from AND @to
),
daily AS (
    SELECT
        o.patid,
        o.Generic_Name,
        o.cadence_days,
        d.date_value,
        SUM(o.dose) AS total_dose
    FROM d
    JOIN dbo.MedOrders o
      ON d.date_value BETWEEN o.s_date AND ISNULL(o.e_date, @to)
    GROUP BY o.patid, o.Generic_Name, o.cadence_days, d.date_value
),
islands AS (
    SELECT
        *,
        DATEADD(day, -ROW_NUMBER() OVER(
            PARTITION BY patid, Generic_Name, cadence_days, total_dose
            ORDER BY date_value
        ), date_value) AS grp
    FROM daily
)
SELECT
    patid,
    Generic_Name,
    cadence_days,
    MIN(date_value) AS s_date,
    MAX(date_value) AS e_date,
    total_dose
FROM islands
GROUP BY patid, Generic_Name, cadence_days, total_dose, grp
ORDER BY patid, Generic_Name, cadence_days, s_date;

この方法は“見える化”には便利ですが、日次行が増えるほどテンポラリ領域やI/Oに効いてきます。まずはイベント方式で組めるかを優先し、必要に応じて日次展開を検証用途に限定するのがおすすめです。

パフォーマンスを落とさないための実装メモ

• 日付型はdate/datetimeの混在を避ける：時刻が混ざると境界がズレます。集計粒度が“日”なら、最初にdateへ正規化するのが安全です。
• 対象期間で早めに絞る：WHERE s_date <= @to AND ISNULL(e_date, @to) >= @from のように、期間と交差する行だけを拾います。
• インデックス：よく使う並びは (patid, Generic_Name, cadence_days, s_date) を軸にし、e_date と dose をINCLUDEする形が効きやすいです（実データ分布で最適化してください）。
• イベントは同日集約してから累積和：同日に複数の開始・終了があると、running sumの行が増えます。GROUP BY event_date でdeltaをまとめてから計算すると安定します。
• 無期限（e_date=NULL）の扱い：集計期間の上限（@to）でキャップすると、オーバーフローや巨大日付の弊害を避けられます。

よくある落とし穴と対処

「同じ日付」の開始と終了があると結果がズレる

同日に開始・終了（あるいは終了翌日イベントが同日に重なる）が発生する場合、イベントの順序が重要になります。上のテンプレでは「同日イベントは合算」した上で累積和を取るため、順序依存を減らしています。もし「開始を先に効かせたい/終了を先に効かせたい」などの厳密なルールがあるなら、イベントに優先度列を追加し、ORDER BY event_date, priority で制御します。

doseの意味が「1回量」なのか「1日量」なのか

同じ“dose”でも、テーブル設計によって意味が変わります。

• 1回量なら、総投与量の合算に加えて「回数」を掛けて期間総量を出すケースがある
• 1日量なら、区間の日数で期間総量を算出するケースがある

この記事のテンプレは「その日からの合算dose（状態量）」を区間化するものです。最終的に「期間総投与量（累計）」が必要なら、区間の長さや投与回数を掛け合わせる層を別途用意してください（要件の誤解が起きやすい部分です）。

0（投与なし）の区間を出す/出さない

テンプレでは total_dose <> 0 で0区間を除外しています。治療ギャップを分析したい（休薬期間も見たい）なら、この条件を外し、0区間も含めて出力すると分析がやりやすくなります。

まとめ：Gaps and Islandsを「区間集計の標準テンプレ」にする

投薬オーダーのように「期間」「重複」「追加・停止」が混ざるデータは、日次展開で力技にするとスケールしません。SQL Server / T-SQLでは、

• 同一dose＋同一cadenceの重複/連続を正規化（ギャップ＆アイランド）
• 開始/終了をイベント化し、累積和で総量を作る
• 次イベント日の前日までを区間として出す

という流れに落とすと、読みやすく速いクエリになります。要件に出てくる「cadence別扱い」「±1日連続扱い」「重複の合算」をすべて自然に満たせるため、処方・オーダー系の集計を扱うなら、このテンプレを自分の引き出しに入れておくと強いです。