第5回（SQL②）集計・グループ化・CTE

2025年10月8日 22:54

前回の宿題の答え（SQL①）

問1）80歳以上の患者の subject_id と anchor_age を20件だけ

SELECT
  subject_id,
  anchor_age
FROM `physionet-data.mimiciv_3_1_hosp.patients`
WHERE anchor_age >= 80
ORDER BY subject_id
LIMIT 20;

ポイント：列は必要最小限に、LIMITで費用と時間を節約。
SELECT … 返す列を絞る（subject_id と anchor_age）。
FROM … 患者テーブル（v3.1の mimiciv_3_1_hosp.patients）を参照。
WHERE … 80歳以上だけにフィルタ。
ORDER BY … 結果を subject_id 昇順に整列。
LIMIT … 動作確認と費用節約のため20行に制限。

問2）入院の在院日数（日）を計算し、長い順で20件

SELECT
  subject_id,
  hadm_id,
  TIMESTAMP_DIFF(dischtime, admittime, SECOND) / 86400.0 AS los_days
FROM `physionet-data.mimiciv_3_1_hosp.admissions`
WHERE dischtime IS NOT NULL
ORDER BY los_days DESC
LIMIT 20;

ポイント：TIMESTAMP_DIFF(..., SECOND) / 86400.0 で小数日を算出（24h未満も反映）。
DATETIME型なら DATETIME_DIFF に読み替え。
SELECT subject_id, hadm_id … 入院を識別する基本キーを出力。
TIMESTAMP_DIFF(..., SECOND) / 86400.0 AS los_days … 秒差→日差（小数）に変換し los_days と命名。
FROM … 入院テーブル admissions を参照。
WHERE dischtime IS NOT NULL … 退院日時がある入院だけに限定。
ORDER BY los_days DESC … 在院日数の長い順に並べ替え。
LIMIT 20 … 上位20件だけを表示。

では第５回に進みましょう。
今回は、SQLでの集計を学習していきます。

ねらい

宿題で使った患者（年齢）と在院日数の計算を“素材”に、
① 全体集計 → ② グループ別集計 → ③ CTEで段階整形まで一気通貫で身につけます。
以後の結合・派生（SQL③）にそのまま繋がる読みやすいクエリ設計を体得します。

WITH（CTE）とは？

私自身がR userなのでSELECTとかFROMとかWHEREは非常にわかりやすかったのですが、WITHの感覚を掴むのがややトリッキーでした。

目的：長いクエリを段階に分けて書くための“中間テーブルの別名”。
効果：読みやすく、デバッグしやすく、同じ中間結果をその場で再利用できる。
スコープ：そのクエリ1本の中だけで有効（他のクエリには残らない）。
実行：BigQueryでは多くの場合、サブクエリとして最適化される（物理的に保存されるわけではない。Rだと物理的に保存することが多い）。重い中間結果を何度も使うなら一時テーブルや永続テーブル化も検討。

最小の書式

WITH 別名 AS (
  SELECT ...
)
SELECT ...
FROM 別名;

WITH 別名 AS (...) … 中間結果に名前をつける。
FROM 別名 … 直後のクエリで“テーブルのように”参照。

例1：前処理→集計の二段構え（最小例）

WITH los AS (
  SELECT
    hadm_id,
    TIMESTAMP_DIFF(dischtime, admittime, SECOND) / 86400.0 AS los_days
  FROM `physionet-data.mimiciv_3_1_hosp.admissions`
  WHERE dischtime IS NOT NULL
)
SELECT
  AVG(los_days) AS mean_los,
  APPROX_QUANTILES(los_days, 100)[OFFSET(50)] AS median_los
FROM los;

WITH los AS ( … **CTE（中間表）**を宣言。以降の集計で使う “一時テーブル” に名前を付ける。
SELECT … CTEに入れる列の定義を開始。
hadm_id, … 入院ID（1入院=1行） を出力。後段での識別子。
TIMESTAMP_DIFF(dischtime, admittime, SECOND) / 86400.0 AS los_days …
退院−入院の秒差を計算し、**86400（秒/日）で割って日数（小数）**に変換。列名は los_days。
※dischtime/admittime が DATETIME 型なら DATETIME_DIFF を使う。
FROM \physionet-data.mimiciv_3_1_hosp.admissions`` … 入院テーブルを参照。
WHERE dischtime IS NOT NULL … 退院が確定している入院だけに限定（退院時刻が無いと差が計算できないため）。
) … CTE los の定義終了。ここまでで「hadm_id と los_days だけの軽い表」ができる。
SELECT … ここから要約集計。
AVG(los_days) AS mean_los, … 在院日数の平均を算出。列名は mean_los。
APPROX_QUANTILES(los_days, 100)[OFFSET(50)] AS median_los …
近似分位を用いて中央値を取得。APPROX_QUANTILES(x, 100) は 0〜100分位の配列を返し、OFFSET(50) が50%分位（中央値）。
※厳密な中央値が必要なら別途方法を検討するが、BigQueryでは通常この近似が実務十分・高速。
FROM los; … 先ほどのCTE（中間表）を集計対象として参照。

補足（実務のコツ）

コスト最適化：CTE内で列を絞る＆WHEREで先に絞る（今回その形）。SELECT * は避ける。
小数日が欲しいから / 86400.0 と浮動小数点割りを使う（.0が重要）。
型の確認：スキーマが DATETIME の場合は DATETIME_DIFF、TIMESTAMP なら TIMESTAMP_DIFF。
分布の把握：中央値に加えて四分位も欲しければ APPROX_QUANTILES(los_days, 4) の要素を参照すると良い。

例2：複数CTEで“段取り”を明示

WITH
pt AS (  -- 患者の年齢帯を付与
  SELECT
    subject_id,
    CASE
      WHEN anchor_age < 40 THEN '<40'
      WHEN anchor_age BETWEEN 40 AND 64 THEN '40-64'
      WHEN anchor_age BETWEEN 65 AND 79 THEN '65-79'
      ELSE '80+'
    END AS age_band
  FROM `physionet-data.mimiciv_3_1_hosp.patients`
),
adm AS ( -- 在院日数を計算
  SELECT
    subject_id,
    hadm_id,
    TIMESTAMP_DIFF(dischtime, admittime, SECOND) / 86400.0 AS los_days
  FROM `physionet-data.mimiciv_3_1_hosp.admissions`
  WHERE dischtime IS NOT NULL
)
SELECT
  p.age_band,
  COUNT(*) AS n_admissions,
  AVG(a.los_days) AS mean_los
FROM adm AS a
JOIN pt  AS p USING (subject_id)
GROUP BY p.age_band
ORDER BY
  CASE p.age_band
    WHEN '<40' THEN 1
    WHEN '40-64' THEN 2
    WHEN '65-79' THEN 3
    ELSE 4
  END;

WITH … 以降で使う**中間表（CTE）**を定義するブロックの開始。
pt AS ( … 患者テーブルを素材に、患者IDごとの年齢帯を作る中間表に名前を付ける。
SELECT … pt に入れる列の定義開始。
subject_id, … 患者の匿名ID（後で入院と結合する主キー）。
CASE ... END AS age_band … anchor_age を4区分にカテゴリ化して age_band と命名。
- <40 / 40–64 / 65–79 / 80+ の4帯。
FROM \...patients`` … 年齢情報のある患者テーブルを参照。
), … pt の定義終了。
adm AS ( … 入院テーブルを素材に、入院ごとの在院日数を計算する中間表を定義。
SELECT … adm に入れる列の定義開始。
subject_id, … 後で pt と結合するために患者IDを出力。
hadm_id, … 入院ID（1入院=1行の識別子）。
TIMESTAMP_DIFF(dischtime, admittime, SECOND) / 86400.0 AS los_days … 退院−入院の**秒差を日（小数）**に換算して los_days と命名。
※スキーマが DATETIME 型なら DATETIME_DIFF を使用。
FROM \...admissions`` … 入院テーブルを参照。
WHERE dischtime IS NOT NULL … 退院日時がある入院に限定（差が計算できるものだけ）。
) … adm の定義終了（ここまでで pt と adm の2つの中間表が完成）。
SELECT … 最終結果として返す列の定義。
p.age_band, … 年齢帯を出す（グルーピング軸）。
COUNT(*) AS n_admissions, … その年齢帯に属する入院件数をカウント。
AVG(a.los_days) AS mean_los … その年齢帯に属する入院の平均在院日数を計算。
FROM adm AS a … 入院ベースの中間表を左側（基準）に。
JOIN pt AS p USING (subject_id) … 患者IDで内部結合して、各入院に患者の年齢帯を付与。
- USING(subject_id) は ON a.subject_id = p.subject_id の省略形で、結合後の列名は1本（subject_id）にまとまる。
GROUP BY p.age_band … 年齢帯ごとに集計。
ORDER BY CASE p.age_band ... … ラベルを人間が読みやすい順（<40→40–64→65–79→80+）に並べ替え。文字列の辞書順ではなく明示順にするためのテクニック。

実務メモ

粒度の整理：pt は患者（subject）粒度、adm は入院（hadm）粒度。JOIN USING(subject_id) により「患者→入院」で多対1の付与になり、重複が増えません。
コスト最適化：CTEの中で列を絞る・条件で先にフィルタしておくとスキャン量が減ります。
中央値が必要なら：AVG に加え APPROX_QUANTILES(a.los_days, 100)[OFFSET(50)] を出力列に足すのが定番です。
期間を制限したい：adm CTEに WHERE admittime >= '2200-01-01' のように期間条件を追加するとさらに軽量化できます。

例3：CTEで“重い条件”を先に当ててコスト削減

WITH recent_adm AS (  -- 期間で先に絞る（スキャン量を抑える）
  SELECT subject_id, hadm_id, admittime, dischtime
  FROM `physionet-data.mimiciv_3_1_hosp.admissions`
  WHERE admittime >= '2200-01-01'  -- 例：期間条件（環境に合わせて調整）
        AND dischtime IS NOT NULL
),
los AS (  -- 小数日でLOSを計算
  SELECT
    subject_id, hadm_id,
    TIMESTAMP_DIFF(dischtime, admittime, SECOND) / 86400.0 AS los_days
  FROM recent_adm
)
SELECT
  COUNT(*) AS n_adm,
  AVG(los_days) AS mean_los
FROM los;

WITH recent_adm AS ( … CTE① を開始。以降の計算対象を期間で先に絞って軽量化するための中間表を定義。
SELECT subject_id, hadm_id, admittime, dischtime … 後段で必要になる最小限の列だけを残す（スキャン量節約）。
FROM \...admissions` … 入院テーブルを参照（1行=1入院＝hadm_id`）。
WHERE admittime >= '2200-01-01' … 入院日時で期間フィルタ。例として“2200年以降”を指定（実際のコホート期間に合わせて変更）。
AND dischtime IS NOT NULL … 退院が確定している入院のみ残す（LOS計算に必要）。
), … CTE① recent_adm の定義終了。ここまでで期間内の入院だけの軽い集合ができる。
los AS ( … CTE② を開始。CTE①の結果から在院日数（LOS）を小数日で算出する中間表。
SELECT subject_id, hadm_id, … 識別子をそのまま引き継ぐ。
TIMESTAMP_DIFF(dischtime, admittime, SECOND) / 86400.0 AS los_days … 退院−入院の秒差を日数（小数）に換算し、列名を los_days とする（/ 86400.0 の .0 で浮動小数点除算に）。
※スキーマが DATETIME の場合は DATETIME_DIFF を使用。
FROM recent_adm … 直前のCTE①を入力として利用。
) … CTE② los の定義終了。ここまでで**hadm_id と los_days だけの中間表**が完成。
SELECT … ここから要約集計の本体。
COUNT(*) AS n_adm, … 期間内・条件内の入院件数を数える。
AVG(los_days) AS mean_los … 平均在院日数（日）を計算。
FROM los; … CTE②を集計対象として参照。

よくある疑問

Q. CTEとサブクエリとの違いは？
A. 機能的には近いですが、CTEの方が読みやすく再利用しやすい（同じ中間結果を複数回参照する/論理ブロックとして区切るのに向く）。
Q. CTEだと速くなる？
A. 速さはケース次第。BigQueryはクエリ最適化でCTEをインライン化することが多いです。可読性と保守性のために使うのが基本。重い再利用が前提なら一時/永続テーブル化を検討。
Q. CTEは何個まで作れる？
A. 複数OK。短く・意味のある名前を付け、段取りを見せる目的で使うのがコツ。

WITHの説明がながくなってしまいました。

今回の目的は集計です。
では、WITHを使いながら集計をしていきましょう。

1) 全体集計

1-1. ICU入室患者の人数（ユニーク件数）

SELECT COUNT(DISTINCT subject_id) AS n_patients_icu
FROM `physionet-data.mimiciv_3_1_icu.icustays`;

SELECT COUNT(DISTINCT subject_id) AS n_patients_icu … ICU経験のある患者のユニーク人数をカウント。
FROM icustays … ICU滞在（1行=1滞在）テーブルを対象。

1-2. 在院日数の要約（平均・中央値・標準偏差）

WITH los AS (
  SELECT
    TIMESTAMP_DIFF(dischtime, admittime, SECOND) / 86400.0 AS los_days
  FROM `physionet-data.mimiciv_3_1_hosp.admissions`
  WHERE dischtime IS NOT NULL
)
SELECT
  AVG(los_days) AS mean_los,
  APPROX_QUANTILES(los_days, 100)[OFFSET(50)] AS median_los,
  STDDEV(los_days) AS sd_los
FROM los;

WITH los AS (... ) … 中間表 los を作る（在院日数を1列に計算）。
TIMESTAMP_DIFF(...)/86400.0 AS los_days … 秒差→日差（小数）。
WHERE dischtime IS NOT NULL … 退院済みのみ。
SELECT AVG(los_days) … 平均在院日数。
APPROX_QUANTILES(...)[OFFSET(50)] … 近似中央値。
STDDEV(los_days) … 標準偏差。
FROM los … 上で作成した中間表を集計。

2) グループ別の要約（年齢帯 × 件数/LOS）

2-1. 年齢帯別の患者数

WITH pt AS (
  SELECT
    subject_id,
    CASE
      WHEN anchor_age < 40 THEN '<40'
      WHEN anchor_age BETWEEN 40 AND 64 THEN '40-64'
      WHEN anchor_age BETWEEN 65 AND 79 THEN '65-79'
      ELSE '80+'
    END AS age_band
  FROM `physionet-data.mimiciv_3_1_hosp.patients`
)
SELECT
  age_band,
  COUNT(*) AS n_patients
FROM pt
GROUP BY age_band
ORDER BY
  CASE age_band
    WHEN '<40' THEN 1
    WHEN '40-64' THEN 2
    WHEN '65-79' THEN 3
    ELSE 4
  END;

WITH pt AS (...) … 患者を年齢帯に分類した中間表を作成。
CASE ... END AS age_band … 4カテゴリにバンド分け。
SELECT age_band, COUNT(*) … バンドごとの人数を集計。
GROUP BY age_band … 年齢帯でグループ化。
ORDER BY CASE ... … 意味のある順序で並べ替え。

2-2. 年齢帯×入院の件数とLOS要約（患者と入院を結合）

WITH
pt AS (
  SELECT
    subject_id,
    CASE
      WHEN anchor_age < 40 THEN '<40'
      WHEN anchor_age BETWEEN 40 AND 64 THEN '40-64'
      WHEN anchor_age BETWEEN 65 AND 79 THEN '65-79'
      ELSE '80+'
    END AS age_band
  FROM `physionet-data.mimiciv_3_1_hosp.patients`
),
adm AS (
  SELECT
    subject_id,
    hadm_id,
    TIMESTAMP_DIFF(dischtime, admittime, SECOND) / 86400.0 AS los_days
  FROM `physionet-data.mimiciv_3_1_hosp.admissions`
  WHERE dischtime IS NOT NULL
)
SELECT
  p.age_band,
  COUNT(*) AS n_admissions,
  AVG(a.los_days) AS mean_los,
  APPROX_QUANTILES(a.los_days, 100)[OFFSET(50)] AS median_los
FROM adm AS a
JOIN pt  AS p
  USING (subject_id)
GROUP BY p.age_band
ORDER BY
  CASE p.age_band
    WHEN '<40' THEN 1
    WHEN '40-64' THEN 2
    WHEN '65-79' THEN 3
    ELSE 4
  END;

WITH pt AS (...) … 年齢帯の付与（患者側）。
WITH adm AS (...) … 在院日数を計算（入院側）。
FROM adm AS a JOIN pt AS p USING(subject_id) … 患者IDで結合。
COUNT(*) AS n_admissions … 年齢帯ごとの入院件数。
AVG/APPROX_QUANTILES … 年齢帯ごとの平均LOS/中央値。
GROUP BY p.age_band … 年齢帯での集計。
ORDER BY CASE ... … バンド順で整列。

3) 条件付きカウント・比率（COUNTIF と SAFE_DIVIDE）

3-1. 病院IDごとの緊急入院割合

WITH base AS (
  SELECT
    hospital_id,
    admission_type
  FROM `physionet-data.mimiciv_3_1_hosp.admissions`
)
SELECT
  hospital_id,
  COUNT(*) AS n_adm,
  COUNTIF(admission_type = 'EMERGENCY') AS n_emerg,
  SAFE_DIVIDE(COUNTIF(admission_type = 'EMERGENCY'), COUNT(*)) AS prop_emerg
FROM base
GROUP BY hospital_id
ORDER BY prop_emerg DESC
LIMIT 50;

WITH base AS (...) … 必要列だけに絞った中間表。
COUNT(*) AS n_adm … 総入院件数。
COUNTIF(...) AS n_emerg … 緊急入院数（条件カウント）。
SAFE_DIVIDE(..., COUNT(*)) … 0割を回避しつつ比率を算出。
GROUP BY hospital_id … 病院IDごとに集計。
ORDER BY prop_emerg DESC … 緊急割合の高い順。
LIMIT 50 … 上位50病院のみ確認。

4) CTE（WITH句）で段取りを見える化

4-1. 高齢患者（80+）の入院だけ切り出してLOSを要約

WITH
elderly AS (
  SELECT subject_id
  FROM `physionet-data.mimiciv_3_1_hosp.patients`
  WHERE anchor_age >= 80
),
adm AS (
  SELECT
    subject_id,
    hadm_id,
    TIMESTAMP_DIFF(dischtime, admittime, SECOND) / 86400.0 AS los_days
  FROM `physionet-data.mimiciv_3_1_hosp.admissions`
  WHERE dischtime IS NOT NULL
)
SELECT
  COUNT(*) AS n_adm_elderly,
  AVG(los_days) AS mean_los_elderly,
  APPROX_QUANTILES(los_days, 100)[OFFSET(50)] AS median_los_elderly
FROM adm
JOIN elderly USING(subject_id);

elderly … 80歳以上の患者IDだけを抽出（軽量化）。
adm … 在院日数を計算した入院表。
FROM adm JOIN elderly USING(subject_id) … 高齢患者の入院に限定。
COUNT/AVG/APPROX_QUANTILES … 件数・平均・中央値を要約。

実務TIP（費用×可読性）

列を明示してスキャン量を抑制（SELECT *は避ける）。
WHEREで先に絞る（CTEの中で制限を当てる）。
LIMITで試作→本番の順に規模を上げる。
実行前にEstimated bytes processedをチェック、重ければ列・期間・母集団を削る。

まとめ（SQL②）

本回では、SQLの核となる集計（COUNT/AVG/APPROX_QUANTILES）とグループ化（GROUP BY）、そして読みやすさと再利用性を高めるCTE（WITH句）を使った“段取り設計”を身につけました。まずは必要列だけ選ぶ→WHEREで母集団を絞る→CTEで中間表を作る→最終集計という流れを徹底することで、BigQueryのスキャン量とコストを最小化できます。比率計算ではCOUNTIF＋SAFE_DIVIDEを用いて、ゼロ割を避けつつ指標を安定して算出できる点も実務では重要です。年齢帯のようなカテゴリ化（CASE）は、後続の集計・可視化を一気に分かりやすくします。以降の分析でも、「このクエリはテーブルをどう変換しているか」を意識し、CTEで段階を明確にしてください。次回は結合・ウィンドウ関数・PIVOTで「欲しい形」に整える方法を概説します

お願い

円安進行にともない、ジリ貧の生活をしています。
もしよかったら！珈琲一杯のチップをください！！！

いいなと思ったら応援しよう！

現在アメリカ留学中で、コーヒーも飲めないギリギリの生活を送っています。ぜひコーヒー１杯おごってください。