第７回　（SQL④）Window関数で「順序」「前後関係」「累積」を操る

2025年10月13日 00:27

前回の「安全なJOINの型」に続き、今回はMIMIC-IVでよくある分析シナリオに合わせて Window関数 を扱ってみます。

ねらい

Window関数の正体を1行で説明できる
GROUP BY と何が違うのかを手で追えるミニ例で理解
PARTITION BY / ORDER BY / フレームの役割と書き方がわかる
MIMIC-IVでよくあるシナリオ（ICU何回目？直前からの間隔？累積時間？上位1件の抽出？）を自分で書ける

1. 定義

Window関数とは？

ウィンドウ関数は、現在の行と何らかの関連性を持つテーブル行の集合に対して計算を実行します。これは集計関数で可能な計算の種類に相当します。しかし通常の集計関数とは異なり、ウィンドウ関数の使用によって行が単一の出力行にグループ化されることはありません。各行は個別のアイデンティティを保持します。内部的には、ウィンドウ関数はクエリ結果の現在の行だけでなく、それ以上の行にアクセスすることが可能です。

PostgresSQL公式ドキュメント

もういきなり分けわかめですね。

意訳すると、
行を消さずに、その行が属するグループ内の情報（順位・直前値・累積・割合など）を列として貼り付けるための集計機能。
集約関数は1行に集約するが、window関数を使った場合は対象の行はそのままになります。

どうでしょうか？

より簡単にいうと、Window関数を使うモチベーションは

集計したいけど、行は消したくないから

です。

例1：入院ごとの累積ICU時間を、各滞在行に表示したい
例2：直前のICU退室時刻と今回の入室時刻の差を、各行に出したい
例3：入院内でICU何回目かの番号を、各行に持たせたい

GROUP BY は行を潰してしまいます（1行に縮約）。
Window は行を残したまま、“文脈”を列として貼るための道具です。

2. まずは“差”だけ掴む：GROUP BY vs Window

データ

以下のデータが手元にあるとしましょう。

student | time | score
--------+------+------
A       | 1    | 50
A       | 2    | 70
A       | 3    | 80
B       | 1    | 60
B       | 2    | 40
C       | 1    | 90

GROUP BY：行をまとめて減らす（平均だけ欲しい）

StudentでGROUP BYを使って平均が欲しい場合、以下のように書くと

SELECT student, AVG(score) AS avg_score
FROM scores
GROUP BY student;

以下のような結果になります（行が減る）

student | avg_score
A       | 66.7
B       | 50.0
C       | 90.0

Window：行を消さず、列として貼る（各行に平均を付けたい）

Window関数を使ってみましょう。PARTITION BYでどの単位でグループに分けるか指定して集計させると

SELECT
  student, time, score,
  AVG(score) OVER (PARTITION BY student) AS avg_in_student
FROM scores
ORDER BY student, time;

各個人に集計結果が付与されます（行はそのまま、列が増える）

student | time | score | avg_in_student
A       | 1    | 50    | 66.7
A       | 2    | 70    | 66.7
A       | 3    | 80    | 66.7
B       | 1    | 60    | 50.0
B       | 2    | 40    | 50.0
C       | 1    | 90    | 90.0

要点：Windowは「各行が属するグループの文脈」を見て、情報を列として増やす。すなわち、集計の列を増やすのがモチベーションです。

3. PARTITION・ORDER・フレームの役割

以下の３つの関数を見ていきましょう

PARTITION BY：グループ分け（例：PARTITION BY hadm_id → 入院ごと）
ORDER BY：グループ内の順序（例：ORDER BY intime → 入室時刻順）
フレーム：集計範囲。累積なら
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

PARTITION BY はオプションです（省略すると「全行ひとまとめ」）。
ORDER BY は順位・直前値・累積を作るときの軸。
フレームは「累積」「移動平均」など時系列の窓で重要。

同じ入院 A1 に ICU滞在が3回あったとします。

hadm_id | intime | hours
--------+--------+------
A1      | 01:00  | 5
A1      | 03:00  | 2
A1      | 06:00  | 4

やりたいこと（モチベ）：

各行に、「この入院の文脈で計算した値」を貼りたい。
たとえば “累積ICU時間（先頭から現在行までの合計）”。

ステップ0（比較対象）：ただの集計＝行が消える

モチベ：合計は知りたい。でも行は残したい。

SELECT SUM(hours) FROM t;

→ 結果は 1行 11（5+2+4）。元の3行は消える。
これが Window ではない集計。

ステップ1：OVER() を付けてWindow化（最小の窓）

モチベ：合計を各行に貼る（行を消さない）。

SELECT
  hadm_id, intime, hours,
  SUM(hours) OVER () AS s
FROM t;

結果

hadm_id | intime | hours | s
A1      | 01:00  | 5     | 11
A1      | 03:00  | 2     | 11
A1      | 06:00  | 4     | 11

行はそのまま。列 s が追加され、全行に合計である 11 が貼られた。
いまの範囲（窓）は「テーブル全体」。

ポイント：… OVER() と書いた瞬間、“行を消さずに貼る集計” に切り替わる。

ステップ2：PARTITION BY で単位を決める

モチベ：テーブル全体じゃなく、入院ごとに合計したい。

SELECT
  hadm_id, intime, hours,
  SUM(hours) OVER (PARTITION BY hadm_id) AS s
FROM t;

今はすべて A1 なので結果は変わらず全行 11。
もし A2 も混ざっていれば、hadm_id ごとに別々の合計が貼られる。

ポイント：PARTITION BY = どの単位で計算するか。
省略すれば「全行ひとまとめ」。MIMIC では目的に応じて

入院内なら PARTITION BY hadm_id
患者内なら PARTITION BY subject_id

ステップ3：ORDER BY で順序を与える

モチベ：累積や直前など“時系列の文脈”を使いたい。

SELECT
  hadm_id, intime, hours,
  SUM(hours) OVER (
    PARTITION BY hadm_id
    ORDER BY intime   -- 時間順の“並び”を与える
  ) AS s
FROM t;

A1 | 01:00 | 5 | 5
A1 | 03:00 | 2 | 7
A1 | 06:00 | 4 | 11

ORDER BY が入ると、「前からここまで」という概念が生まれる。
ただし実装によっては挙動が曖昧になり得るため、次のフレーム指定を推奨。

ポイント：ORDER BY = グループ内の並び（時間軸）。これがないと、直前や累積の計算が安定しない。

ステップ4：フレームで「どこからどこまで」を明示

モチベ：累積や移動平均を、曖昧さなく正確に作りたい。

4-1 累積（先頭→現在行）

SUM(hours) OVER (
  PARTITION BY hadm_id
  ORDER BY intime
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS s_cum

→ 5, 7, 11

4-2 移動窓（直近2行＝“1行前＋現在行”）

SUM(hours) OVER (
  PARTITION BY hadm_id
  ORDER BY intime
  ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
) AS s_move2

→ 5, 7, 6（= 5+2, 2+4）

ポイント：

フレーム = 「どこからどこまで集計するか」
累積の定型句は
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
移動平均は n PRECEDING 〜 CURRENT ROW を使う

ここまでのまとめ

PARTITION BY：どの単位（患者/入院）で区切る？
ORDER BY：その中の順序（時系列/大小）をどう並べる？
フレーム：どこからどこまで数える？（累積/移動窓）

この3つを OVER(...) に組み合わせ、文脈を持った数値を各行に貼る。
これが Window の正体です。

4. 代表的な Window関数（よく使うものだけ）

前後参照

LAG(expr)：ひとつ前の行の値
LEAD(expr)：ひとつ後ろの行の値
FIRST_VALUE(expr) / LAST_VALUE(expr)：先頭/末尾の値（※フレーム指定に注意）

集計のウィンドウ化

SUM / AVG / MIN / MAX / COUNT に OVER(...) を付けると、行を潰さずグループ内集計や累積が作れる

分布

PERCENT_RANK()：パーセンタイル順位（0〜1）
CUME_DIST()：累積分布（その値以下の割合）

5. MIMIC-IVでの実践レシピ

粒度の確認：

patients: 1行＝患者（subject_id）
admissions: 1行＝入院（hadm_id）
icustays: 1行＝ICU滞在（stay_id）

5-1. 入院内で「ICU何回目？」（ROW_NUMBER）

動機：入院内でICU何回目かを付けたい
理由：時系列の順番を特徴量にしたい／1回目だけ抜きたい

SELECT
  i.hadm_id,
  i.stay_id,
  i.intime,
  i.outtime,
  ROW_NUMBER() OVER (
    PARTITION BY i.hadm_id
    ORDER BY i.intime
  ) AS icu_ordinal_in_hadm
FROM `physionet-data.mimiciv_3_1_icu.icustays` AS i
ORDER BY i.hadm_id, icu_ordinal_in_hadm;

ポイント

PARTITION BY hadm_id → 入院ごとに連番
時間順の1,2,3回目がひと目でわかる

5-2. 直前ICU退室からの経過時間（LAG + DATETIME_DIFF）

動機：直前退室からの経過時間を出したい
理由：再入室の早さを知りたい／連続ICUの判定に使いたい

WITH icu_ordered AS (
  SELECT
    hadm_id,
    stay_id,
    intime,
    outtime,
    LAG(outtime) OVER (
      PARTITION BY hadm_id
      ORDER BY intime
    ) AS prev_outtime
  FROM `physionet-data.mimiciv_3_1_icu.icustays`
)
SELECT
  hadm_id,
  stay_id,
  intime,
  outtime,
  DATETIME_DIFF(intime, prev_outtime, HOUR) AS hours_from_prev_discharge
FROM icu_ordered
ORDER BY hadm_id, intime;

ポイント

先頭行は prev_outtime が NULL（IFNULL で補うか、そのまま扱う）
単位は HOUR / MINUTE / DAY を適宜

5-3. 入院内の累積ICU時間（SUM OVER + フレーム）

動機：入院内の累積ICU時間を持たせたい
理由：リソース消費の蓄積・閾値判定・グラフ表示

WITH icu_durations AS (
  SELECT
    hadm_id,
    stay_id,
    intime,
    outtime,
    DATETIME_DIFF(outtime, intime, HOUR) AS icu_hours
  FROM `physionet-data.mimiciv_3_1_icu.icustays`
)
SELECT
  hadm_id,
  stay_id,
  intime,
  outtime,
  icu_hours,
  SUM(icu_hours) OVER (
    PARTITION BY hadm_id
    ORDER BY intime
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cum_icu_hours_in_hadm
FROM icu_durations
ORDER BY hadm_id, intime;

ポイント

フレームを明示（累積の定型句）
表の粒度はstay_idのまま、累積はhadm_id内で進む

5-4. 「各入院の最初のICUだけ」をスマートに抜く（QUALIFY）

動機：各入院の最初のICUだけを取り出したい
理由：最初のICUを起点にイベントを揃えたい

SELECT
  hadm_id,
  stay_id,
  intime,
  ROW_NUMBER() OVER (PARTITION BY hadm_id ORDER BY intime) AS rn
FROM `physionet-data.mimiciv_3_1_icu.icustays`
QUALIFY rn = 1;

ポイント

BigQueryの QUALIFY は Window列でのフィルタに便利
サブクエリ不要で読みやすい

5-5. 「患者ごとの最初の入院時刻」を各入院行に貼る（FIRST_VALUE）

WITH adm AS (
  SELECT
    a.subject_id,
    a.hadm_id,
    a.admittime,
    FIRST_VALUE(a.admittime) OVER (
      PARTITION BY a.subject_id
      ORDER BY a.admittime
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS first_admit_time
  FROM `physionet-data.mimiciv_3_1_hosp.admissions` AS a
)
SELECT * FROM adm
ORDER BY subject_id, admittime;

ポイント

FIRST_VALUE / LAST_VALUE はフレーム指定が超重要（デフォルトだと“現在行まで”になりやすい）

5-6. ICU入室直前24時間の平均HRを作る（最下流は先に要約）

-- 例：HR itemid = 220045
WITH hr_by_stay AS (
  SELECT
    ce.stay_id,
    AVG(ce.valuenum) AS hr_mean_24h
  FROM `physionet-data.mimiciv_3_1_icu.chartevents` AS ce
  JOIN `physionet-data.mimiciv_3_1_icu.icustays`  AS i
    ON ce.stay_id = i.stay_id
  WHERE ce.itemid = 220045
    AND ce.charttime BETWEEN DATETIME_SUB(i.intime, INTERVAL 24 HOUR) AND i.intime
  GROUP BY ce.stay_id
)
SELECT
  i.hadm_id, i.stay_id, i.intime,
  hr.hr_mean_24h
FROM `physionet-data.mimiciv_3_1_icu.icustays` AS i
LEFT JOIN hr_by_stay AS hr USING (stay_id)
ORDER BY i.hadm_id, i.intime;

ポイント

chartevents などの最下流イベントは“先に縮約→JOIN”（ダブルfan-out回避）
Window関数とJOINは粒度の整合が鍵

6. よくあるつまずきと対処

6-1. 「PARTITION BY が Window関数？」問題

いいえ。Window関数 = … OVER(...) を伴う関数の総称
PARTITION BY は その設定（どの単位で計算するか）

6-2. フレーム省略の罠

SUM(...) OVER (PARTITION BY ... ORDER BY ...) など、フレームを省略すると実装依存の挙動に
累積なら常に ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

6-3. 先頭行の LAG が NULL

正常です。必要なら IFNULL(LAG(...), 0) などで明示処理

6-4. 粒度の取り違え

「入院内」なら PARTITION BY hadm_id
「患者内」なら PARTITION BY subject_id
クエリの冒頭コメントに最終粒度を書いておくと事故が減る

7. Window関数を使う際の思考チェックリスト

最終粒度を言語化したか（患者？入院？ICU？）
PARTITION BY は粒度と一致しているか
ORDER BY は正しい時系列か（intime / admittime など）
累積・移動窓ならフレーム明示したか
結果件数や COUNT(DISTINCT <主キー>) が意図どおりか

8. 目的別ショートレシピ

A. 各入院で「最初のICUだけ」

SELECT hadm_id, stay_id, intime
FROM `physionet-data.mimiciv_3_1_icu.icustays`
QUALIFY ROW_NUMBER() OVER (PARTITION BY hadm_id ORDER BY intime) = 1;

B. 「直前ICUからの経過時間（時間）」

SELECT
  hadm_id, stay_id, intime, outtime,
  DATETIME_DIFF(
    intime,
    LAG(outtime) OVER (PARTITION BY hadm_id ORDER BY intime),
    HOUR
  ) AS hours_from_prev
FROM `physionet-data.mimiciv_3_1_icu.icustays`;

C. 「入院内の累積ICU時間」

WITH x AS (
  SELECT hadm_id, stay_id, intime, outtime,
         DATETIME_DIFF(outtime, intime, HOUR) AS icu_hours
  FROM `physionet-data.mimiciv_3_1_icu.icustays`
)
SELECT
  hadm_id, stay_id, intime, outtime, icu_hours,
  SUM(icu_hours) OVER (
    PARTITION BY hadm_id
    ORDER BY intime
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cum_icu_hours
FROM x;

9. ミニ演習

各入院（hadm_id）について、ICU滞在ごとの「前回退室からの間隔（時間）」を計算し、入院粒度に中央値を要約せよ。
各患者（subject_id）について、最初の入院から退院までの累積ICU時間を求め、patients の年齢（anchor_age）を付与して年齢帯×累積ICU時間の分布を作れ。
ICU入室直前6時間の平均 SpO₂（例：itemid=220277）を stay_id 粒度で作成し、各入院で滞在時間が最長のICU1件だけを QUALIFY で抽出せよ。

10. まとめ

Window関数は 「行を消さずに文脈を計算して列を増やす」 道具
鍵は PARTITION BY（単位） と ORDER BY（順序）、そしてフレーム
MIMIC-IVでは「入院内の順序」「直前値」「累積」「上位1件抽出」がとくに実用的
粒度の明文化とフレーム明示を習慣化すれば、ほとんどの事故は防げます

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

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

第７回 （SQL④）Window関数で「順序」「前後関係」「累積」を操る

ねらい

1. 定義

2. まずは“差”だけ掴む：GROUP BY vs Window

データ

GROUP BY：行をまとめて減らす（平均だけ欲しい）

Window：行を消さず、列として貼る（各行に平均を付けたい）

3. PARTITION・ORDER・フレームの役割

ステップ0（比較対象）：ただの集計＝行が消える

ステップ1：OVER() を付けてWindow化（最小の窓）

ステップ2：PARTITION BY で単位を決める

ステップ3：ORDER BY で順序を与える

ステップ4：フレームで「どこからどこまで」を明示

ここまでのまとめ

4. 代表的な Window関数（よく使うものだけ）

ランキング

前後参照

集計のウィンドウ化

分布

5. MIMIC-IVでの実践レシピ

5-1. 入院内で「ICU何回目？」（ROW_NUMBER）

5-2. 直前ICU退室からの経過時間（LAG + DATETIME_DIFF）

5-3. 入院内の累積ICU時間（SUM OVER + フレーム）

5-4. 「各入院の最初のICUだけ」をスマートに抜く（QUALIFY）

5-5. 「患者ごとの最初の入院時刻」を各入院行に貼る（FIRST_VALUE）

5-6. ICU入室直前24時間の平均HRを作る（最下流は先に要約）

6. よくあるつまずきと対処

6-1. 「PARTITION BY が Window関数？」問題

6-2. フレーム省略の罠

6-3. 先頭行の LAG が NULL

6-4. 粒度の取り違え

7. Window関数を使う際の思考チェックリスト

8. 目的別ショートレシピ

A. 各入院で「最初のICUだけ」

B. 「直前ICUからの経過時間（時間）」

C. 「入院内の累積ICU時間」

9. ミニ演習

10. まとめ

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

第７回　（SQL④）Window関数で「順序」「前後関係」「累積」を操る