SQL Serverで患者ごとにワクチン別の接種回数を条件付きCOUNTで集計したあと、「DTaPが4回以上かつIPVが3回以上」のような条件で患者を絞りたい場面は多いです。本記事ではWHEREで別名が使えない理由を整理し、HAVINGとCTEでしきい値フィルタを実装する定番パターンを具体例つきで解説します。
やりたいこと：患者（MRNumber）ごとにワクチン別回数を集計し、一定回数以上だけ抽出する
予防接種の記録テーブル（例：Vaccinations）に、患者ID（MRNumber）とワクチン種別（DTaP / IPV / MMR / Hib / HepB / VZW / PCV など）が入っているとします。まずは患者単位でワクチン別の接種回数を集計し、次に「DTaPは4回以上、IPVは3回以上」といったしきい値で患者を絞り込みます。
前提となるテーブル例
| 列名 | 型（例） | 意味 | 補足 |
|---|---|---|---|
| MRNumber | varchar(20) | 患者ID | 患者マスタとJOINするキー |
| vaccine | varchar(20) | ワクチン種別 | DTaP / IPV / MMR / Hib / HepB / VZW / PCV など |
| administered_at | datetime2 | 接種日時 | 重複判定・期間条件で使うことが多い |
なぜ「WHERE DTaP >= 4」がエラーになるのか
条件付きCOUNTで列別名（DTaPなど）を付けたあと、その別名をWHEREで参照すると、次のようなエラーになりがちです（例：「DTaP は無効な列名です」）。
SELECT
MRNumber,
COUNT(CASE WHEN vaccine = 'DTaP' THEN 1 END) AS DTaP,
COUNT(CASE WHEN vaccine = 'IPV' THEN 1 END) AS IPV
FROM Vaccinations
WHERE DTaP >= 4
AND IPV >= 3
GROUP BY MRNumber;理由は単純で、「WHEREの時点では、SELECTで定義した別名（DTaP）がまだ存在しない」ためです。SQLは書いた順番どおりに実行されるわけではなく、概ね次の“論理処理順”で評価されます。
| 論理処理順 | 句 | 役割 | この段階でできること（例） |
|---|---|---|---|
| 1 | FROM / JOIN | 対象行を作る | テーブル結合、対象データセットの構築 |
| 2 | WHERE | 行（レコード）を絞る | 期間条件、施設条件、対象ワクチンの事前フィルタ |
| 3 | GROUP BY | グループ化する | MRNumber単位にまとめる |
| 4 | HAVING | グループを絞る | 集計結果がしきい値以上の患者だけ残す |
| 5 | SELECT | 列を作る | AS DTaP のような別名付け、表示列の決定 |
| 6 | ORDER BY | 並び替える | 別名でソート（DTaP DESC など） |
ポイントは、WHEREは「行」を絞る場所であり、「集計後」の値（DTaPの件数など）を条件にする場所ではない、ということです。集計後の値で絞るなら、HAVINGか、集計をいったん別の問い合わせ（CTE/サブクエリ）にして外側でWHEREを使うのが基本解です。
解決策A：HAVINGで集計結果を絞り込む（王道）
最もシンプルで意図が伝わりやすいのがHAVINGです。GROUP BYで患者単位にまとめたあと、集計値がしきい値以上のグループだけ残します。
HAVINGで「DTaP >= 4 かつ IPV >= 3」を満たす患者だけ抽出
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 = 'MMR' THEN 1 END) AS MMR,
COUNT(CASE WHEN vaccine = 'Hib' THEN 1 END) AS Hib,
COUNT(CASE WHEN vaccine = 'HepB' THEN 1 END) AS HepB,
COUNT(CASE WHEN vaccine = 'VZW' THEN 1 END) AS VZW,
COUNT(CASE WHEN vaccine = 'PCV' THEN 1 END) AS PCV
FROM Vaccinations
GROUP BY MRNumber
HAVING COUNT(CASE WHEN vaccine = 'DTaP' THEN 1 END) >= 4
AND COUNT(CASE WHEN vaccine = 'IPV' THEN 1 END) >= 3
ORDER BY MRNumber;この書き方のメリットは、1本のSQLで完結し、意図も明快なことです。一方で、しきい値条件に同じ集計式を繰り返し書く必要があり、ワクチン種類や条件が増えるとSQLが長くなります。
COUNTよりSUMのほうが読みやすいケースもある
COUNT(CASE WHEN ... THEN 1 END)は「条件を満たしたときだけ1を返し、それ以外はNULL（未指定）なのでCOUNTされない」という仕組みです。同じ意味をSUMで書くこともできます。
| 書き方 | 例 | 考え方 | 注意点 |
|---|---|---|---|
| 条件付きCOUNT | COUNT(CASE WHEN vaccine='DTaP' THEN 1 END) | NULL以外を数える | ELSEを書かないのが一般的 |
| 条件付きSUM | SUM(CASE WHEN vaccine='DTaP' THEN 1 ELSE 0 END) | 1/0を足し上げる | ELSE 0を書き忘れるとNULLになり得る |
チーム内の可読性や好みによりますが、「足し上げて件数を作る」方が直感的という理由でSUMに寄せる現場も多いです。HAVINGでも同様に書けます。
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
FROM Vaccinations
GROUP BY MRNumber
HAVING SUM(CASE WHEN vaccine = 'DTaP' THEN 1 ELSE 0 END) >= 4
AND SUM(CASE WHEN vaccine = 'IPV' THEN 1 ELSE 0 END) >= 3;WHEREでできる“事前フィルタ”は先にやる
「集計結果のしきい値」はHAVINGで行いますが、集計対象の行を減らす条件はWHEREで先に掛けた方が効率的です。例えば「直近5年分だけ」「特定の施設だけ」「対象ワクチンだけ」といった条件は、グループ化前に絞っておくのが定石です。
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
FROM Vaccinations
WHERE administered_at >= DATEADD(YEAR, -5, SYSDATETIME())
AND vaccine IN ('DTaP','IPV')
GROUP BY MRNumber
HAVING SUM(CASE WHEN vaccine = 'DTaP' THEN 1 ELSE 0 END) >= 4
AND SUM(CASE WHEN vaccine = 'IPV' THEN 1 ELSE 0 END) >= 3;このように「行レベルの条件（期間・施設・患者属性など）」はWHERE、「集計後の条件（回数のしきい値）」はHAVING、と役割を分けると迷いが減ります。
解決策B：CTE（またはサブクエリ）で一度集計してから外側でWHERE
次におすすめなのが、集計パートと抽出条件パートを分離する書き方です。SQL ServerではCTE（共通テーブル式）を使うと読みやすくなります。外側のSELECTでは、すでに「DTaP」「IPV」といった列が実体として存在するので、WHERE DTaP >= 4が自然に書けます。
CTEで集計 → 外側WHEREでしきい値フィルタ
WITH Totals 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 = 'MMR' THEN 1 ELSE 0 END) AS MMR,
SUM(CASE WHEN vaccine = 'Hib' THEN 1 ELSE 0 END) AS Hib,
SUM(CASE WHEN vaccine = 'HepB' THEN 1 ELSE 0 END) AS HepB,
SUM(CASE WHEN vaccine = 'VZW' THEN 1 ELSE 0 END) AS VZW,
SUM(CASE WHEN vaccine = 'PCV' THEN 1 ELSE 0 END) AS PCV
FROM Vaccinations
-- ここは行レベルの絞り込み（任意）
-- WHERE administered_at >= DATEADD(YEAR, -5, SYSDATETIME())
GROUP BY MRNumber
)
SELECT
MRNumber, DTaP, IPV, MMR, Hib, HepB, VZW, PCV
FROM Totals
WHERE DTaP >= 4
AND IPV >= 3
ORDER BY MRNumber;この方式の利点は、条件式の見通しが良く、しきい値の調整が容易なことです。さらに外側のSELECT側で「DTaP/IPVのしきい値は必須、ほかのワクチンは参考表示」といった要件にも対応しやすくなります。
しきい値を変数化して運用しやすくする
運用で「基準回数が変わる」「画面やバッチから渡される」ケースでは、しきい値を変数化すると保守が楽になります。
DECLARE @MinDTaP int = 4;
DECLARE @MinIPV int = 3;
WITH Totals 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
FROM Vaccinations
GROUP BY MRNumber
)
SELECT *
FROM Totals
WHERE DTaP >= @MinDTaP
AND IPV >= @MinIPV;「HAVINGで同じ集計式が繰り返されるのがつらい」「条件だけ後から差し替えたい」なら、CTE/サブクエリ方式が特に効きます。
どちらを使うべき？HAVINGとCTE（サブクエリ）の選び方
| 観点 | HAVINGで直接絞る | CTE/サブクエリで集計→外側WHERE |
|---|---|---|
| 書きやすさ | 短い条件なら最短で書ける | 列別名で条件を書けるので読みやすい |
| 条件の変更 | 集計式を条件側でも修正が必要 | WHERE側だけ直せることが多い |
| ワクチン種類が多い | HAVINGが長くなりがち | “集計部”と“抽出部”が分かれて管理しやすい |
| パフォーマンス | 多くの場合同等（最終的に最適化される） | 多くの場合同等（実行計画で確認が確実） |
| 用途 | 集計結果で即フィルタして一覧を出す | 集計結果を使って追加処理（JOIN/再集計/出力）をしたい |
結論としては、単発の集計ならHAVING、再利用や拡張が見えているならCTEが選びやすいです。どちらが正しいというより、SQLの目的（読む人、変わりやすさ、拡張）で決めるのが実務的です。
応用：PIVOTでワクチン種別を列にし、外側で絞る
「ワクチンが行に並んでいるのを列にしたい」場合は、条件付きSUM/COUNTの代わりにPIVOTを使う方法もあります。CTEと相性がよく、列化したあとにWHEREで絞れます。
WITH Base AS (
SELECT MRNumber, vaccine
FROM Vaccinations
WHERE vaccine IN ('DTaP','IPV','MMR','Hib','HepB','VZW','PCV')
),
Pivoted AS (
SELECT
MRNumber,
ISNULL([DTaP], 0) AS DTaP,
ISNULL([IPV], 0) AS IPV,
ISNULL([MMR], 0) AS MMR,
ISNULL([Hib], 0) AS Hib,
ISNULL([HepB], 0) AS HepB,
ISNULL([VZW], 0) AS VZW,
ISNULL([PCV], 0) AS PCV
FROM Base
PIVOT (
COUNT(vaccine) FOR vaccine IN ([DTaP],[IPV],[MMR],[Hib],[HepB],[VZW],[PCV])
) p
)
SELECT *
FROM Pivoted
WHERE DTaP >= 4
AND IPV >= 3
ORDER BY MRNumber;PIVOTは「列の一覧を固定で書く」必要があるため、ワクチン種別が頻繁に追加される環境では更新作業が発生します。その代わり、クエリの意図が「行→列変換」として明確になり、BIツールやExcel連携でも扱いやすくなることがあります。
実務でよくある落とし穴と対策
JOINで行が増えて、件数が水増しされる
患者マスタや診療イベントなど別テーブルとJOINした結果、1件の接種記録が複数行に増えてしまい、COUNTが意図より大きくなることがあります。対策としては次のいずれかを検討します。
- 先に接種テーブルだけで集計してから、患者マスタにJOINする（CTE方式が向く）
- 接種記録の主キー（例：VaccinationId）で重複が起きないJOIN条件を見直す
- どうしても重複が避けられない場合は、
COUNT(DISTINCT ...)で“イベント単位”を数える
WITH Totals AS (
SELECT
v.MRNumber,
COUNT(DISTINCT CASE WHEN v.vaccine = 'DTaP' THEN CONVERT(date, v.administered_at) END) AS DTaP_Days,
COUNT(DISTINCT CASE WHEN v.vaccine = 'IPV' THEN CONVERT(date, v.administered_at) END) AS IPV_Days
FROM Vaccinations v
GROUP BY v.MRNumber
)
SELECT *
FROM Totals
WHERE DTaP_Days >= 4
AND IPV_Days >= 3;「同じ日に同じワクチンが二重登録され得る」「同一日の複数接種を1回とみなしたい」など、業務定義に合わせてDISTINCTの単位（日時・日付・ロット番号・オーダーIDなど）を決めるのが重要です。
COUNTの対象列を誤る（COUNT(*)との違い）
COUNT(*)は行数そのものを数えます。対してCOUNT(列)はNULLを除外します。条件付きCOUNTでよく使うのは「CASEで条件を満たすときだけ値を返し、満たさないときはNULLにする」パターンです。ここを理解しておくと、想定外の0/NULLに悩みにくくなります。
NULLと0の扱いを揃える
条件付きSUMは基本的に0を返しやすい一方、PIVOTは該当がない列がNULLになることがあります。表示や後続処理（アプリ側の判定）で混乱しないよう、ISNULLやCOALESCEで0に寄せるのが無難です。
パフォーマンスを落とさないための考え方
ワクチン種別の集計は、データ量が多いほどGROUP BYが重くなります。クエリ自体の書き方に加えて、次の観点を押さえると改善しやすいです。
まずWHEREで“行を減らす”
- 期間（例：直近N年）
- 施設・部門・データ区分
- 必要なワクチン種別（IN句）
これらは集計前に適用できるため、スキャン行数を減らして集計コストを下げます。
インデックスの方向性（一般論）
頻出パターンが「MRNumberでまとめ、vaccineで条件分岐する」なら、テーブル設計にもよりますが次のようなインデックスが候補になります。
(MRNumber, vaccine)の複合インデックス- 期間条件が多いなら
(administered_at, MRNumber)や(MRNumber, administered_at)も検討 - 参照列が多い場合はINCLUDEでカバリングを狙う
ただし実際の最適解はデータ分布や既存インデックス次第です。SQL Serverなら実行計画（推定/実績）で、スキャン行数・ハッシュ集計/ストリーム集計の選択・メモリ使用量などを確認すると原因が掴みやすくなります。
すぐ使えるテンプレート：条件付き集計＋しきい値抽出
最後に、実務でそのまま流用しやすい“型”をまとめます。ワクチンが増える場合も、まずはこの形に当てはめると迷いません。
テンプレート（CTE方式）
DECLARE @MinDTaP int = 4;
DECLARE @MinIPV int = 3;
WITH Totals 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 = 'MMR' THEN 1 ELSE 0 END) AS MMR,
SUM(CASE WHEN vaccine = 'Hib' THEN 1 ELSE 0 END) AS Hib,
SUM(CASE WHEN vaccine = 'HepB' THEN 1 ELSE 0 END) AS HepB,
SUM(CASE WHEN vaccine = 'VZW' THEN 1 ELSE 0 END) AS VZW,
SUM(CASE WHEN vaccine = 'PCV' THEN 1 ELSE 0 END) AS PCV
FROM Vaccinations
WHERE administered_at >= DATEADD(YEAR, -5, SYSDATETIME()) -- 必要に応じて
GROUP BY MRNumber
)
SELECT *
FROM Totals
WHERE DTaP >= @MinDTaP
AND IPV >= @MinIPV
ORDER BY MRNumber;テンプレート（HAVING方式）
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
FROM Vaccinations
WHERE administered_at >= DATEADD(YEAR, -5, SYSDATETIME())
GROUP BY MRNumber
HAVING SUM(CASE WHEN vaccine = 'DTaP' THEN 1 ELSE 0 END) >= 4
AND SUM(CASE WHEN vaccine = 'IPV' THEN 1 ELSE 0 END) >= 3;WHEREは行、HAVINGはグループという役割分担を押さえつつ、別名で素直に条件を書きたいときはCTE（またはサブクエリ）で集計→外側WHEREにする。この2パターンを手元に置いておけば、SQL Serverの「ワクチン別の件数集計（条件付きCOUNT）」は、しきい値条件が増えても安定して実装できます。
コメント