MENU

SQL Serverで予防接種の接種回数を患者ごとに集計する方法（条件付き集計・2歳まで判定）

Microsoft

予防接種履歴が「1接種＝1行」のテーブルだと、患者ごとの接種回数や「2歳の誕生日までに所定回数を満たした子の割合」を出すときに、アプリ側（MVCなど）で並べ替えてループ集計しがちです。この記事ではSQL Server（T-SQL）だけで集計・判定を完結させ、負荷と実装コストを下げる実務的なパターンを解説します。

目次

予防接種データの集計は「SQLでまとめて返す」が基本

予防接種の履歴テーブルは、ほぼ例外なく「1接種＝1行」で蓄積されます。この形は登録・更新には強い一方、画面表示やレポートのために「患者ごとに何回打ったか」をまとめようとすると、アプリ側でループして数えたくなります。

しかし件数が増えるほど、アプリ側ループは次の問題が出やすくなります。

  • データ転送量が増える（必要以上の行をアプリへ持ってくる）
  • 並べ替え・集計の二重実装になりやすい（SQLもアプリも複雑化）
  • 表示要件（列が増える、年齢条件が入る）が変わるたびに修正箇所が増える

SQL Serverには、GROUP BY（集計）と条件付き集計（擬似ピボット）という定番の解決策があり、ループは不要です。まずは全体像を整理します。

やりたいことよくある実装おすすめ（T-SQL）ポイント
患者×ワクチンの回数を出すアプリで患者ごとに行を走査GROUP BYで集計「必要最小限の集計結果」だけ返せる
ワクチン別に列で出したいアプリで辞書化して列展開条件付き集計 / PIVOT画面が欲しい形で返す（再集計しない）
2歳までに所定回数を満たす割合アプリで年齢計算して判定日付フィルタ＋判定＋割合算出集計・判定・率の算出までSQLで完結

前提となるテーブル例と、集計前に押さえたい注意点

ここでは説明を分かりやすくするために、予防接種の履歴テーブルを Vaccinations とします。最低限、次の情報があると「2歳の誕生日まで」の判定ができます。

列名（例）意味型の例備考
MRNumber患者ID（カルテ番号など）INT / NVARCHAR集計の主キーになる
Vaccineワクチン名またはコードNVARCHAR文字列なら表記ゆれ対策が重要
VaccinationDate接種日DATE / DATETIME2年齢条件に必須
DOB生年月日DATE本来は患者マスタに置くことが多い
Status（任意）実施/取消/予定などNVARCHAR「実施のみ集計」したいなら必須
VaccinationId（任意）接種イベントのIDBIGINT二重登録の除外などに役立つ

実務でよく起きる落とし穴として、次のようなデータ品質の問題があります。SQL集計は正確でも、元データが揺れていると結果も揺れます。

  • 表記ゆれ：DTaP / DTaP4 / DTaP（混合）などが混在
  • 取消や重複：同じ接種が二重登録されている、あるいは取消行が混ざる
  • ワクチンの同義語：コードと名称が混在している

この記事のSQLは、まず「数える対象（実施のみ）」と「ワクチンの定義（コード統一）」が固まっている前提で進めます。未整備の場合の対策も後半で触れます。

基本形：患者×ワクチンの接種回数をGROUP BYで集計する

「患者（MRNumber）ごとに、ワクチン（Vaccine）別の接種回数を出したい」だけなら、もっともシンプルな形はこれです。

SELECT
  MRNumber,
  Vaccine,
  COUNT(*) AS Cnt
FROM dbo.Vaccinations
GROUP BY
  MRNumber,
  Vaccine
ORDER BY
  MRNumber,
  Vaccine;

出力は次のようなイメージになります（例）。

MRNumberVaccineCnt
10001DTaP4
10001IPV3
10001RV2
10002DTaP3

この形なら、アプリ側で「患者ごとの辞書に足し込む」ようなループ集計は不要です。SQL Serverが最短ルートで集約してくれます。

列で欲しい場合：条件付き集計（擬似ピボット）でワクチン別カラムを作る

画面やCSVで「MRNumber, DTaP, IPV, RV, …」のようにワクチンごとに列を並べたいケースが多いはずです。この場合は、T-SQLの定番である条件付き集計を使います。

SELECT
  MRNumber,
  COUNT(CASE WHEN Vaccine = 'DTaP' THEN 1 END) AS DTaP,
  COUNT(CASE WHEN Vaccine = 'IPV'  THEN 1 END) AS IPV,
  COUNT(CASE WHEN Vaccine = 'RV'   THEN 1 END) AS RV
  -- 必要なワクチン分だけ同様に追加
FROM dbo.Vaccinations
GROUP BY
  MRNumber
ORDER BY
  MRNumber;

COUNT(CASE WHEN ... THEN 1 END) は、条件に一致した行だけが「1（非NULL）」になり、COUNTが非NULL件数を数えるため、結果として「条件に一致する回数」になります。

同じことは SUM でも書けます。NULLの扱いが分かりやすいので、チームでの可読性重視ならこちらを採用することもあります。

SELECT
  MRNumber,
  SUM(CASE WHEN Vaccine = 'DTaP' THEN 1 ELSE 0 END) AS DTaP,
  SUM(CASE WHEN Vaccine = 'IPV'  THEN 1 ELSE 0 END) AS IPV,
  SUM(CASE WHEN Vaccine = 'RV'   THEN 1 ELSE 0 END) AS RV
FROM dbo.Vaccinations
GROUP BY
  MRNumber;

条件付き集計は、SQL Serverの現場で最もよく使われる「ピボットの代替」です。理由は単純で、書きやすく、チューニングもしやすく、追加条件も入れやすいからです（年齢条件、ステータス条件、期間条件など）。

PIVOT句でも実現できるが、まずは条件付き集計が実務向き

SQL Serverには PIVOT 句もあります。例えば「DTaP, IPV, RV を列にする」静的ピボットは次のように書けます。

SELECT
  MRNumber,
  ISNULL([DTaP], 0) AS DTaP,
  ISNULL([IPV],  0) AS IPV,
  ISNULL([RV],   0) AS RV
FROM (
  SELECT MRNumber, Vaccine
  FROM dbo.Vaccinations
) AS src
PIVOT (
  COUNT(Vaccine) FOR Vaccine IN ([DTaP], [IPV], [RV])
) AS p
ORDER BY MRNumber;

ただし、PIVOT は「列の一覧（IN句）」が固定で、後から条件を追加したり、微妙な数え方（重複除外やステータス判定）を入れたりすると、結局読みにくくなりがちです。まずは条件付き集計を第一候補にすると失敗が減ります。

手法メリット注意点
条件付き集計（COUNT/SUM + CASE）条件を足しやすい、読みやすい、拡張しやすい列が多いとSQLが長くなる
PIVOTピボット目的が明確、構文として分かりやすい場合もある列が固定、動的対応は動的SQLが必要

ワクチン種類が増減するなら「動的ピボット」も選択肢

ワクチン名（Vaccine）の種類が施設や時期で変わり、列を固定できない場合は動的ピボット（動的SQL）が必要になることがあります。SQL Server 2017以降なら STRING_AGG を使うと列一覧を作りやすいです。

DECLARE @cols  nvarchar(max);
DECLARE @sql   nvarchar(max);

SELECT @cols =
  STRING_AGG(QUOTENAME(Vaccine), ',')
FROM (
  SELECT DISTINCT Vaccine
  FROM dbo.Vaccinations
) AS v;

SET @sql = N'
SELECT MRNumber, ' + @cols + N'
FROM (
  SELECT MRNumber, Vaccine
  FROM dbo.Vaccinations
) AS src
PIVOT (
  COUNT(Vaccine) FOR Vaccine IN (' + @cols + N')
) AS p
ORDER BY MRNumber;';

EXEC sys.sp_executesql @sql;

重要：動的SQLは便利ですが、外部入力をそのまま文字列結合するとSQLインジェクションの原因になります。上の例は「テーブル内の値から列を作る」だけなので比較的安全ですが、業務アプリのパラメータを組み込む場合は必ずパラメータ化（sp_executesqlの引数）を徹底してください。

本題：2歳の誕生日までの接種だけを対象にする（年齢条件の掛け方）

「2歳までに所定回数を満たしたか」を判定するには、接種日（VaccinationDate）と生年月日（DOB）が必須です。年齢条件は、よくある DATEDIFF ベースの判定より、日付同士の比較で書くのが安全です。

例えば「2歳の誕生日の前日まで（2歳の誕生日を含めない）」なら次の条件が分かりやすいです。

WHERE VaccinationDate < DATEADD(YEAR, 2, DOB)

一方で「2歳の誕生日当日まで（当日を含める）」にしたいなら、次のように境界を明示します。

WHERE VaccinationDate <= DATEADD(YEAR, 2, DOB)

どちらが正しいかは業務要件（「誕生日まで」の定義、データがDATEかDATETIMEか）で変わります。たとえば接種日が DATETIME2 で「誕生日当日の23:59:59まで含めたい」なら、< DATEADD(DAY, 1, DATEADD(YEAR, 2, DOB)) のように半開区間にしておくとトラブルが減ります。

DOBが患者マスタ（例：Patients）にある場合は、JOINして同様に書けます。

SELECT
  v.MRNumber,
  v.Vaccine,
  COUNT(*) AS Cnt
FROM dbo.Vaccinations v
INNER JOIN dbo.Patients p
  ON p.MRNumber = v.MRNumber
WHERE
  v.VaccinationDate < DATEADD(YEAR, 2, p.DOB)
GROUP BY
  v.MRNumber, v.Vaccine;

「所定回数を満たす」判定は、SQLで2通りに設計できる

2歳までの接種回数を数えたら、次は「所定回数を満たすか」の判定です。ここは要件と運用に応じて、次の2パターンがあります。

判定設計向いているケース特徴
SQLに条件を直書き（DTaP>=4 など）対象ワクチンが少なく、ルールが当面固定最短で作れるが、ルール変更のたびにSQL改修が必要
要件テーブル（必要回数）を持ち、データ駆動で判定ワクチンが増える／基準が変わる／複数レポートで使う拡張しやすい。運用で基準を変えたい場合に強い

医療・行政の基準は変更されることがあるため、レポートを継続運用するなら「要件テーブル方式」が実務ではおすすめです。

例：条件付き集計で「2歳までの接種回数」を作り、SQLに直書きで達成率を出す

まずは最短ルートとして「対象ワクチンが固定」なケースの例です。2歳までの接種だけを数えてから、達成条件を CASE で判定し、最後に割合を計算します。

WITH Vax2 AS (
  SELECT
    MRNumber,
    SUM(CASE WHEN Vaccine = 'DTaP' THEN 1 ELSE 0 END) AS DTaP,
    SUM(CASE WHEN Vaccine = 'IPV'  THEN 1 ELSE 0 END) AS IPV,
    SUM(CASE WHEN Vaccine = 'RV'   THEN 1 ELSE 0 END) AS RV
  FROM dbo.Vaccinations
  WHERE
    VaccinationDate < DATEADD(YEAR, 2, DOB)  -- 「2歳の誕生日の前日まで」の例
    -- AND Status = 'Done'                  -- 実施のみ数えるなら追加
  GROUP BY
    MRNumber
),
Judge AS (
  SELECT
    MRNumber,
    DTaP, IPV, RV,
    CASE
      WHEN DTaP >= 4
       AND IPV  >= 3
       AND RV   >= 2
      THEN 1 ELSE 0
    END AS IsCompleted
  FROM Vax2
)
SELECT
  SUM(CASE WHEN IsCompleted = 1 THEN 1 ELSE 0 END) AS CompletedChildren,
  COUNT(*) AS TotalChildren,
  CAST(100.0 * SUM(CASE WHEN IsCompleted = 1 THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0) AS decimal(5,2)) AS CompletionRatePercent
FROM Judge;

ポイントは次の通りです。

  • 年齢条件（2歳まで）を集計前にWHEREで掛ける
  • 集計結果（DTaP/IPV/RVの回数）を基に、CASEで達成フラグを作る
  • 達成フラグの合計 ÷ 総数 で割合を出す（NULLIFでゼロ除算対策）

この方法は分かりやすい一方、ワクチン種類や必要回数が変わるとSQLを直す必要があります。そこで次に「要件テーブル方式」を紹介します。

例：要件テーブルで「必要回数」を管理し、2歳までの達成率を汎用的に出す

基準をデータで管理できるように、要件テーブル（例：VaccineRequirements）を用意します。内容は「ワクチンごとの必要回数」だけで十分です。

VaccineRequiredCnt備考（例）
DTaP4必要回数は要件に合わせて設定
IPV3同上
RV2同上

次のSQLは、

  • 2歳までの接種回数を「患者×ワクチン」で集計
  • 要件テーブルと突き合わせて「満たしているか」を判定
  • 患者ごとに「全ワクチン条件を満たすか」をまとめ
  • 達成率（割合）を算出

までを一気通貫で行います。

WITH Vax2 AS (
  SELECT
    MRNumber,
    Vaccine,
    COUNT(*) AS Cnt
  FROM dbo.Vaccinations
  WHERE
    VaccinationDate < DATEADD(YEAR, 2, DOB)
    -- AND Status = 'Done'
  GROUP BY
    MRNumber, Vaccine
),
Patients AS (
  SELECT DISTINCT MRNumber
  FROM dbo.Vaccinations
),
CheckEach AS (
  SELECT
    p.MRNumber,
    r.Vaccine,
    r.RequiredCnt,
    ISNULL(v.Cnt, 0) AS ActualCnt,
    CASE WHEN ISNULL(v.Cnt, 0) >= r.RequiredCnt THEN 1 ELSE 0 END AS IsOk
  FROM Patients p
  CROSS JOIN dbo.VaccineRequirements r
  LEFT JOIN Vax2 v
    ON v.MRNumber = p.MRNumber
   AND v.Vaccine  = r.Vaccine
),
Judge AS (
  SELECT
    MRNumber,
    MIN(IsOk) AS IsCompleted  -- 1が全て揃えば1、どれか欠ければ0
  FROM CheckEach
  GROUP BY MRNumber
)
SELECT
  SUM(CASE WHEN IsCompleted = 1 THEN 1 ELSE 0 END) AS CompletedChildren,
  COUNT(*) AS TotalChildren,
  CAST(100.0 * SUM(CASE WHEN IsCompleted = 1 THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0) AS decimal(5,2)) AS CompletionRatePercent
FROM Judge;

この設計の強みは、ワクチンが増えても減っても、必要回数が変わっても、SQLのロジック自体は変えずに要件テーブルのデータを直すだけで済む点です。レポートが複数ある場合も、同じ要件テーブルを参照すれば整合性が取りやすくなります。

「要件を満たさない患者」の一覧も簡単に出せる

割合だけでなく、フォロー対象を抽出したい場合は、CheckEach から「不足分」を出すと現場で使いやすいです。

WITH Vax2 AS (
  SELECT MRNumber, Vaccine, COUNT(*) AS Cnt
  FROM dbo.Vaccinations
  WHERE VaccinationDate < DATEADD(YEAR, 2, DOB)
  GROUP BY MRNumber, Vaccine
),
Patients AS (
  SELECT DISTINCT MRNumber FROM dbo.Vaccinations
)
SELECT
  p.MRNumber,
  r.Vaccine,
  r.RequiredCnt,
  ISNULL(v.Cnt, 0) AS ActualCnt,
  r.RequiredCnt - ISNULL(v.Cnt, 0) AS MissingCnt
FROM Patients p
CROSS JOIN dbo.VaccineRequirements r
LEFT JOIN Vax2 v
  ON v.MRNumber = p.MRNumber
 AND v.Vaccine  = r.Vaccine
WHERE ISNULL(v.Cnt, 0) < r.RequiredCnt
ORDER BY p.MRNumber, r.Vaccine;

「誰がどのワクチンを何回不足しているか」がそのまま出るため、保健指導やリコール業務の裏取りにも使えます（運用・権限・個人情報の扱いは必ず組織ルールに従ってください）。

重複・取消・表記ゆれに強くするための実務ポイント

集計SQLが正しくても、データ側の揺れで「回数が多すぎる／少なすぎる」が起きます。次の表は、現場で頻出する論点とSQL側の対処例です。

論点よくある症状SQL側の対処例理想の設計
二重登録同じ日・同じワクチンが2行あり回数が増えるCOUNT(DISTINCT VaccinationId) / 一意キーがなければ業務ルールで除外接種イベントIDの採番と一意制約
取消/予定の混在実施していないのに数えてしまうWHERE Status = ‘Done’ 等で実施のみフィルタ状態管理の正規化（コード化）
表記ゆれDTaP / DTaP4 / DTaP(混合) が別物として数えられるマッピング表で正規化してから集計VaccineId（コード）で保存し参照整合性を取る
混合ワクチン1接種が複数ワクチン要件に効くかで解釈が分かれる「混合→構成要素」テーブルを用意して展開してから集計投与製剤と抗原（要件）を分離して管理

表記ゆれを吸収する「正規化ビュー」を挟むと運用が安定する

既存DBを簡単に変えられない場合でも、ビューやCTEで「正規化したVaccineCode」を作ってから集計すると、レポートが安定します。例えばマッピング表 VaccineMap(OriginalName, CanonicalName) があるなら次のようにします。

WITH Normalized AS (
  SELECT
    v.MRNumber,
    COALESCE(m.CanonicalName, v.Vaccine) AS VaccineNorm,
    v.VaccinationDate,
    v.DOB
  FROM dbo.Vaccinations v
  LEFT JOIN dbo.VaccineMap m
    ON m.OriginalName = v.Vaccine
)
SELECT
  MRNumber,
  VaccineNorm,
  COUNT(*) AS Cnt
FROM Normalized
WHERE VaccinationDate < DATEADD(YEAR, 2, DOB)
GROUP BY MRNumber, VaccineNorm;

この「正規化レイヤー」を作っておくと、以降の集計SQLは VaccineNorm を使うだけで済み、保守性が上がります。

パフォーマンスを落とさないためのSQLの書き方とインデックス

「ループをやめてSQLで集計」すると、次に気になるのが速度です。SQL Serverで効きやすい基本を押さえるだけで、集計はかなり安定します。

まず意識するのは「絞り込み → 集計」の順番

2歳まで、期間指定、ステータス指定などの条件がある場合、できるだけ早い段階でWHEREで絞り込んでからGROUP BYするのが基本です。

  • 良い例：WHERE ... で対象行を減らしてから GROUP BY
  • 避けたい例：全件集計してからアプリで再フィルタ

DATEDIFFで年齢計算するより、日付比較が安全で速いことが多い

年齢判定で DATEDIFF(YEAR, DOB, VaccinationDate) < 2 のように書くと、境界（誕生日当日）の扱いで意図しない結果になりやすいです。基本は VaccinationDate < DATEADD(YEAR,2,DOB) のような日付同士の比較をおすすめします。

インデックスの考え方（例）

データ量が増えると、MRNumber と VaccinationDate と Vaccine の組み合わせが効きやすくなります。典型例としては次のような非クラスタ化インデックスが候補になります（環境・更新頻度・既存キーによって最適解は変わります）。

-- 例：患者ごとに接種日とワクチンを追いやすいインデックス
CREATE INDEX IX_Vaccinations_MRDateVaccine
ON dbo.Vaccinations (MRNumber, VaccinationDate, Vaccine)
INCLUDE (DOB, Status);

ただしDOBが患者マスタにあるなら、VaccinationsにDOBをINCLUDEするより、Patients側に主キー/インデックスを整えたうえでJOINする方が自然です。変更できない場合は、ビューや集計用の中間テーブル（バッチ更新）を検討することもあります。

アプリ側（MVCなど）での扱いを軽くする返し方のコツ

SQLで集計して返すと、アプリ側は「表示するだけ」に寄せられます。特に次の2パターンは使い分けると良いです。

  • 画面が固定列：条件付き集計（ピボット形）で「MRNumberごとに1行」を返す
  • ワクチン種類が可変：患者×ワクチン（縦持ち）で返して、UI側は単純な繰り返し表示にする

「列が増えるたびにAPIや画面も改修」になりそうなら、無理にピボットせず縦持ちで返す方が保守が楽なことも多いです。逆に帳票やCSVの仕様が固定なら、SQLで列を作り切った方が高速でシンプルです。

まとめ：ループ集計は不要。SQL Serverの集計で要件を最後まで完結させる

予防接種テーブルのような「1接種＝1行」データは、SQL Server（T-SQL）のGROUP BYと条件付き集計（擬似ピボット）で、患者ごとの接種回数を効率よく集計できます。さらに、接種日と生年月日があれば「2歳の誕生日まで」のフィルタもSQLで素直に書けます。

最終要件である「2歳までに所定回数を満たした割合」は、(1)2歳までの接種だけを集計、(2)必要回数を満たすか判定、(3)割合を算出、をCTEでつなげれば、アプリ側で重いループや再集計をせずに完結できます。運用で基準が変わる可能性があるなら、必要回数を要件テーブルに切り出す方式を採用すると、保守性と再利用性が大きく上がります。

Microsoft
SQL Server GROUP BY PIVOT T-SQL 条件付き集計

この記事を書いた人

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

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

関連記事

コメント

コメントする

目次