第6回（SQL③）JOINだけで“安全に”表をつなぐ

ここまで簡単なSQLを書いてきましたが、次は、Joinをきっちり学びましょう。

ねらい

• 粒度（1行＝何を表す？） を意識してJOINする

• 患者⇄入院⇄ICUの典型パターンを安全に合体させる

• fan-out（行の増殖）やキー取り違えを事前に回避する

粒度って何？まずは直感から

データを患者毎に扱うのか、入院毎に扱うのか、ICU入室毎に扱うのかを考えていきましょう。この扱いの単位を粒度といいます。
MIMIC では主にこの3つが違う粒度を持ちます：

• patients … 1行=1人の患者（subject_id）

• admissions … 1行=1回の入院（hadm_id）

• icustays … 1行=1回のICU滞在（stay_id）

つまり、
subject_id: Aの人が、
２回入院（hadm_id: A1, A2)し
１回目の入院でICUに１回、２回目の入院でICU２回入室（stay_id: S1, S2,S3）
のようなIDを持っています。

このようなデータを結合（JOIN）しようと思ったときに行の増殖（fan-out）が起きて問題になることがあります。

下の例で、なぜ行が増える（=fan-out）かを“見える化”します。

ミニ例で直観をつかむ

例のデータ

• patients（患者粒度）のデータ

このデータでは患者1人にユニークなIDが付与されデータとなります

subject_id
----------
101
102

• admissions（入院粒度）のデータ

• このデータでは入院1回にユニークなIDが付与されデータとなります。患者単位でみると重複しています。

hadm_id  subject_id
-------------------
A1       101
A2       101
B1       102

• icustays（ICU滞在粒度）のデータ

• このデータではICU入室1回にユニークなIDが付与されデータとなります。入院単位でみると重複しています。

stay_id  hadm_id
----------------
S1       A1
S2       A1
S3       B1

• 患者101は入院A1とA2の2回。

• A1の入院中にICU滞在が2回（S1,S2）。

• 患者102は入院B1、ICUは1回（S3）。

❌ よくある間違い1：要約せずに入院×ICUをそのまま結合（fan-out）

例えば今入院ベースのデータを操作してい、そこにICU入室情報を結合させたいとします。
以下の操作をすると入院IDが増殖します。

SELECT a.hadm_id, a.subject_id, i.stay_id
FROM `...admissions` a
JOIN `...icustays`  i
  ON a.hadm_id = i.hadm_id;

• SELECT a.hadm_id, a.subject_id, i.stay_id … 入院とICUのペアをそのまま出す。

• FROM ...admissions a … 入院（hadm）粒度を左に配置。

• JOIN ...icustays i ON a.hadm_id = i.hadm_id … 同じ入院IDで結ぶ。

結果のイメージ

hadm_id  subject_id  stay_id
----------------------------
A1       101         S1
A1       101         S2   ← A1が2行に“増殖”（ICUが2回あるため）
A2       101         NULL ← （この行は実際には出ない。JOIN条件次第で消える）
B1       102         S3

• A1が2行になった＝fan-out。

• ここで COUNT(*) などをすると二重に数える事故が起きます。

こんなかんじで粗い表（入院）に細かい表（ICU滞在）をそのままJOINすると、fan-out（行の増殖） で集計が壊れます。
やりたい目的ごとに安全な書き方を、コードの前後に短い解説を添えて整理します。

ケース1：ICUに入った“入院だけ”を取り出したい（フィルタ）

先に考えること

母集団は入院（hadm）です。行数を増やさずに「ICU経験あり」の入院だけ残したいので、セミジョイン（EXISTS） が最適です。

-- ICU経験のある入院だけを残す（行は増えない）
SELECT a.*
FROM `...admissions` AS a
WHERE EXISTS (
  SELECT 1
  FROM `...icustays` AS i
  WHERE i.hadm_id = a.hadm_id
);

ここがポイント

• JOINしてからDISTINCTで削るより、最初から増やさないほうが速くて安全。

• 粒度（入院）を崩していないので、件数の検証が簡単（前後で同じか、意図どおりに減るか）。

ケース2：入院粒度にICU情報（回数・最初/最後の時刻）を付けたい

先に考えること

右側（ICU）を hadm_id ごとに要約して、入院粒度（1 hadm = 1行）に揃えてからJOINします。

-- ICUを hadm_id 粒度に要約→LEFT JOIN
WITH icu_by_hadm AS (
  SELECT
    hadm_id,
    COUNT(*) AS n_icu_stays,
    MIN(intime) AS first_icu_in,
    MAX(outtime) AS last_icu_out
  FROM `...icustays`
  GROUP BY hadm_id
)
SELECT
  a.hadm_id,
  a.subject_id,
  IFNULL(i.n_icu_stays, 0) AS n_icu_stays,
  i.first_icu_in,
  i.last_icu_out
FROM `...admissions` AS a
LEFT JOIN icu_by_hadm AS i USING (hadm_id);

ここがポイント

• 「要約→JOIN」が鉄則。要約せずJOINするとfan-out。

• LEFT JOIN + IFNULLで、ICUなしも保持（欠測→0）。

• 検証は「JOIN後のCOUNT(*)が入院件数と一致するか」。

ケース3：ICU滞在を分析の単位にする（粒度をstay_idへ切替）

先に考えること

最終粒度をICU滞在（stay_id) にします。ここで入院情報を付けるのは1対多の安全な方向なのでOK。

-- 粒度を stay_id に切り替え、入院属性を付与
SELECT
  i.stay_id,
  i.hadm_id,
  i.subject_id,
  i.intime, i.outtime,
  a.admittime, a.dischtime, a.hospital_expire_flag
FROM `...icustays` AS i
JOIN `...admissions` AS a
  ON a.hadm_id = i.hadm_id;

ここがポイント

• 以降の集計はstay_id 粒度で実施（hadm単位の数え上げと混ぜない）。

• 「粒度切替」を明文化し、レポートにも粒度を明記すると事故が減る。

ケース4：最初のICU1件だけ入院に付けたい

先に考えること

ICU側で行を1件に絞ってからJOINします（ウィンドウ関数）。

-- hadm内の最初のICUだけを1件に絞る
WITH first_icu AS (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY hadm_id ORDER BY intime) AS rn
  FROM `...icustays`
)
SELECT
  a.*,
  i.intime  AS first_icu_in,
  i.outtime AS first_icu_out
FROM `...admissions` AS a
LEFT JOIN first_icu AS i
  ON a.hadm_id = i.hadm_id
WHERE i.rn = 1;

ここがポイント

• JOIN前に“1 hadm = 1行”へ縮約しておけば、行は増えない。

• BigQueryなら QUALIFY rn = 1 でもOK。

やってはいけない書き方と修正

NG1：要約せずJOIN（fan-out）

-- ❌ 入院×ICUをそのままJOIN → A1がICU回数ぶんに増殖
SELECT a.hadm_id, a.subject_id, i.stay_id
FROM `...admissions` a
JOIN `...icustays`  i ON a.hadm_id = i.hadm_id;

修正：ケース2の「要約→LEFT JOIN」へ。

NG2：キー取り違え（subject_idで結ぶ）

-- ❌ 入院にICUを付けたいのに subject_id でJOIN
SELECT a.hadm_id, i.stay_id
FROM `...admissions` a
JOIN `...icustays`  i ON a.subject_id = i.subject_id;

修正：hadm_idで結ぶ／または先に要約して粒度を合わせる。

NG3：INNER JOINで母集団を落とす

-- ❌ ICUなしの入院が全て消える
SELECT a.hadm_id, i.n_icu_stays
FROM `...admissions` a
JOIN (
  SELECT hadm_id, COUNT(*) AS n_icu_stays
  FROM `...icustays`
  GROUP BY hadm_id
) i ON a.hadm_id = i.hadm_id;

修正：LEFT JOIN + IFNULL に置換。

ダブルfan-outを避ける：イベント表は“先に縮約”

admissions → icustays で膨らんだ表に、さらに chartevents を素朴JOINすると二重に爆発します。
原則：イベントはstay_id/hadm_idで先に要約してからJOIN。

WITH vitals_by_stay AS (
  SELECT
    stay_id,
    AVG(valuenum) AS hr_mean
  FROM `...chartevents`
  WHERE itemid = 220045     -- 例：Heart Rate
  GROUP BY stay_id
)
SELECT i.stay_id, a.hadm_id, v.hr_mean
FROM `...icustays`  AS i
JOIN `...admissions` AS a ON a.hadm_id = i.hadm_id
LEFT JOIN vitals_by_stay AS v USING (stay_id);

ここがポイント

• 最下流で畳む→上流に付ける。この順番を固定観念化。

セルフチェック

結合したら以下を実行してミスがないか確認しましょう。
データ操作→確認が基本です。

-- 1) 要約の一意性（本当に 1 hadm = 1行？）
SELECT hadm_id
FROM icu_by_hadm
GROUP BY hadm_id
HAVING COUNT(*) > 1;     -- 行が出たら要約ミス

-- 2) JOIN前後の件数（母集団が想定どおり？）
SELECT COUNT(*) FROM `...admissions`;          -- N1
SELECT COUNT(*) FROM ( ...LEFT JOIN結果... );  -- N2（通常N1と一致）

-- 3) 重複検知（hadmの重複増殖はない？）
SELECT COUNT(DISTINCT hadm_id) FROM ( ...JOIN後... );

コピペOK：最小テンプレ集

A. 入院に「ICUあり/なし」フラグを付ける

WITH icu_flag AS (
  SELECT hadm_id, 1 AS has_icu
  FROM `...icustays`
  GROUP BY hadm_id
)
SELECT a.hadm_id, IFNULL(f.has_icu, 0) AS has_icu
FROM `...admissions` AS a
LEFT JOIN icu_flag AS f USING (hadm_id);

B. 患者レベルに「ICU歴あり」フラグを付ける

WITH icu_pt AS (
  SELECT subject_id, 1 AS ever_icu
  FROM `...icustays`
  GROUP BY subject_id
)
SELECT p.subject_id, p.anchor_age, IFNULL(i.ever_icu, 0) AS ever_icu
FROM `...patients` AS p
LEFT JOIN icu_pt AS i USING (subject_id);

C. 入院に患者属性を付ける（1対多の安全パターン）

SELECT
  a.subject_id, a.hadm_id, a.admittime, a.dischtime,
  p.anchor_age, p.anchor_year
FROM `...admissions` AS a
LEFT JOIN `...patients`  AS p
  ON a.subject_id = p.subject_id
LIMIT 100;

まとめ

• 粒度を先に固定（最終の1行が何か）。

• 要約→LEFT JOINが基本。キーは目的に合わせて（hadm / subject / stay）。

• 二重fan-outは“最下流で畳む” で回避。

• 最後に件数・一意性・重複の3点検証で安全運転。