見出し画像

第4回（SQL①）SQLは「テーブル→テーブル」変換の言語だと捉える

半日仮装

ここまでBigQueryにmimicivを接続して使えるようになるまでを説明しました。


今回はBigQueryで、SQLを使えるように、簡単にSQLの基礎を学んでいきましょう。全３回で、概要を掴むようにしていきます。きちんと学びたい場合は、成書を参考にしてください。

ねらい

  • SQLの“見方”を揃え、テーブルを別のテーブルに変換する手段として理解します。

  • MIMIC-IVを題材に、最小限の構文で最大限の結果を出すコツを掴みます。

1) テーブルとは（SQLが前提にしていること）

SQLを理解するには「テーブル」という概念が重要です。日本語で言う表です。表は便利なのですが、きちんとルールに従わないと、うまく扱えないため以下のような特徴を有します。

  • 二次元の表：行（レコード）と列（カラム）で構成されます。

  • 1セル＝1値：1つのセルに配列や複数値を詰め込まないことが基本です。

  • 順序に意味を置かない：行の並び順に意味を持たせたいときは、ORDER BY を毎回明示します。

  • 正規化の発想：重複を避け、同じ事実は同じ場所に。MIMICでは subject_id / hadm_id / stay_id をもとに分割・連結します。

実務TIP：可視化や集計の前に「このクエリはどんな形のテーブルを作っているか？」を必ず言語化します。

2) 関係代数とSQL操作の対応（ざっくり理解）

|考え方                         ｜SQLでよくやる操作|
|-------------------------------|----------------------------|
|部分集合を取り出す     | WHERE                   |
|列を選ぶ／付け替える | SELECT col AS 別名|
|集合を結合する            | JOIN                         |
|群ごとの要約               | GROUP BY + 集計関数|

「難しい公式」を覚えるより、集合（セット）に対する操作だと捉えるのがコツです。

3) SELECTの“基本体幹”

SELECT分は、テーブルからこの列を持って来い、とするために必要な昨日です。FROMでどこからもってくるかを指定します。
最初はこれだけで理解できれば十分です。

mimicivを例に見てみましょう。

-- 例：患者テーブルから最低限の列だけを取る
SELECT subject_id, anchor_age, anchor_year
FROM `physionet-data.mimiciv_3_1_hosp.patients`
WHERE anchor_age >= 65
ORDER BY subject_id
LIMIT 50;

行ごとの意味

  • SELECT subject_id, anchor_age, anchor_year
    出力する列を3つに絞り込みます。

    • subject_id: 患者の匿名化ID

    • anchor_age: 基準年（anchor_year）時点の年齢（匿名化のため一部は上限処理あり）

    • anchor_year: 各患者の時系列の“基準年”（匿名化の基準。実暦年とはずれる）

  • FROM \physionet-data.mimiciv_3_1_hosp.patients` BigQueryの完全修飾名　で、プロジェクトphysionet-data、データセット mimiciv_3_1_hosp、テーブル patientsを指定しています。 （環境によってはmimiciv_hosp` という名前のこともあります。あなたの左側のExplorerに表示されるデータセット名に合わせてください。）

  • WHERE anchor_age >= 65
    65歳以上の行だけをフィルタします（条件に合わない行は捨てる＝部分集合を取る）。

  • ORDER BY subject_id
    結果を subject_id の昇順で並べ替えます。
    （SQLは本来「集合」なので順序は保証されません。必要な並びは必ず ORDER BY で指定します。）

  • LIMIT 50
    上位 50行だけを返します。動作確認や課金・処理量の節約のための定番テクニックです。

実務メモ

BigQueryでは料金がかかります。
毎月1TB（テラバイト）までクエリの料金が無料であるという無料枠がありますがそれを超えるとお金がかかるので、なるべく超えないように操作します。超えても経験上データの切り出しだけであれば数千円以内に収まります。

  • 費用を抑えるコツ：今回のように列を明示指定＋WHERE＋LIMIT を基本形に。

  • エラー対策：anchor_age にNULLが混じる可能性を考えるなら、厳密には WHERE anchor_age IS NOT NULL AND anchor_age >= 65 としてもOK。

  • データセット名：v3.1では環境により mimiciv_3_1_hosp / mimiciv_hosp のいずれかが見えることがあります。あなたの環境に表示される名前を使ってください。

  • SELECT * はすべての変数を選びます。*がすべてを表します。便利ですが、費用と時間の面で非効率。列を絞るのが原則です。

  • WHERE は部分集合、ORDER BY は並び替え、LIMIT は確認用に行数を絞るために使います。

4) 列を増やす（派生列）

例えば、ICU滞在日数という変数を新たに作りたいとします。
los_daysを作るSQL例を示します。

  • AS 別名 で新しい列を定義できます。

-- 入院の在院日数（概算）を付ける例
SELECT
  subject_id, hadm_id,
  DATETIME_DIFF(dischtime, admittime, DAY) AS los_days
FROM `physionet-data.mimiciv_3_1_hosp.admissions`
WHERE dischtime IS NOT NULL
LIMIT 50;

各行の意味

  • SELECT subject_id, hadm_id,

    • 患者ID（subject_id）と入院ID（hadm_id）を出力します。

    • 1入院＝1行のつもりで見るとイメージしやすいです。

  • DATETIME_DIFF(dischtime, admittime, DAY) AS los_days

    • 退院日時 dischtime と入院日時 admittime の日数差を計算し、los_days（length of stay in days）という列名で出します。

    • BigQueryの DATETIME_DIFF は指定した「単位（ここでは DAY）」で整数の差を返します。つまり24時間未満の滞在は 0 になる点に注意してください（切り捨て）。

    • ※もしスキーマ上の型が TIMESTAMP なら TIMESTAMP_DIFF を使います。DATETIME/TIMESTAMP のどちらかは、BigQueryのスキーマビューで確認してください。

  • FROM \physionet-data.mimiciv_3_1_hosp.admissions``

    • プロジェクトphysionet-data、データセットmimiciv_3_1_hosp、テーブルadmissions を参照しています。

    • 環境によってはデータセット名が mimiciv_hosp になっている場合もあります。左側のExplorerに見える名前を使いましょう。

  • WHERE dischtime IS NOT NULL

    • まだ退院していない（退院日時が空）行を除外します。

    • 在院日数は退院日時が無いと計算できないためです。

    • NULL を含む演算は結果が NULL になることに注意

      • BigQueryやSQLでは、値が存在しないことを意味するNULLと他の値（例えば数字）を足したり掛けたりする演算を行うと、結果もNULL（不明）になってしまいます。

      • この「NULL伝播」が原因で、意図した計算ができず、分析結果がおかしくなることがあるため、注意が必要です。

      • 対策として、COALESCEやIFNULL関数を使って、計算前にNULLを「0」や「空文字」などの意味のある値に置き換える必要があります。

  • LIMIT 50

    • 結果を50行に制限して、動作確認や費用節約をします。

5) 重複の扱い

MIMICデータでは、１人が複数回ICUに入室したり、何回も入院したりすることがありえます。例えば、Aさんが、ICUに入室２回している場合は、同じsubject_idの記録が２つ存在します。

こういった場合に、人を単位に何人の人がICUに入室したか知りたい場合は、ユニーク（一意や唯一という意味）なIDが何個あるか調べる必要があります。

DISTINCTはこれを可能にします。

  • DISTINCT は集合の重複を落とす操作。ただし、必要なときだけ使い、むやみに多用しません。

-- ICUに入った患者IDのユニーク集合
SELECT DISTINCT subject_id
FROM `physionet-data.mimiciv_icu.icustays`
LIMIT 50;

行ごとの意味

  • SELECT DISTINCT subject_id
    icustays テーブルには 同一患者が複数回ICUに入った記録が複数行ある可能性があります。DISTINCT を付けることで、重複を取り除いた患者IDの集合を返します（ユニーク化）。

    • FROM \physionet-data.mimiciv_icu.icustays` ICU滞在ごとの記録を持つテーブルを参照します。1行=1回のICU滞在（stay_id`）が基本です。

    • LIMIT 50
      先頭50行のみを表示します。動作確認や処理量の節約のための定番テクです。

実務メモ

  • 人数（件数）だけ知りたいなら、一覧ではなく集計を使うという方法もある。


ここまで簡単にいくつかのSQL関数を説明しました。
実際に以下の宿題を自身のBigQueryでやってみましょう。

宿題

  1. mimiciv_hosp.patients から 80歳以上の患者だけ subject_id, anchor_age を抽出し、LIMIT 20 で確認する。

  2. mimiciv_hosp.admissions で入院期間（日）を los_days として付け、los_days の大きい順に 20件見る。

次回で解説します。

次の「SQL②」では、集計（COUNT, AVG など）、グループごとの要約、段階的に形を整える WITH（CTE） を扱います。

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

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