はじめに

PostgreSQL 集計統計とウィンドウ関数

Michel Semaan

Data Scientist

学習の動機

2004年以降の米国夏季五輪金メダル数と累計

| Year | Medals | Medals_RT |
|------|--------|-----------|
| 2004 | 116    | 116       |
| 2008 | 125    | 241       |
| 2012 | 147    | 388       |

円盤投げの現チャンピオン状況

| Year | Champion | Last_Champion | Reigning_Champion |
|------|----------|---------------|-------------------|
| 1996 | GER      | null          | false             |
| 2000 | LTU      | GER           | false             |
| 2004 | LTU      | LTU           | true              |
| 2008 | EST      | LTU           | false             |
| 2012 | GER      | EST           | false             |

コースの概要

ウィンドウ関数の概要
フェッチ、ランキング、ページング
集計ウィンドウ関数とフレーム
ウィンドウ関数の応用

夏季五輪データセット

各行は夏季五輪で授与されたメダルを表します

列

Year, City
Sport, Discipline, Event
Athlete, Country, Gender
Medal

ウィンドウ関数

現在の行に関連する行集合に対して操作を実行する
GROUP BY の集計関数に似ているが、すべての行が出力に残る

用途

前後の行から値を取得する（例：直前の行の値を取得）
- 現チャンピオンの状態を判定する
- 時系列の成長を計算する
値の順位に基づいて行に順位（1位、2位など）を付与する
累計、移動平均の計算

行番号

クエリ

SELECT
  Year, Event, Country
FROM Summer_Medals
WHERE
  Medal = 'Gold';

結果

| Year | Event                      | Country |
|------|----------------------------|---------|
| 1896 | 100M Freestyle             | HUN     |
| 1896 | 100M Freestyle For Sailors | GRE     |
| 1896 | 1200M Freestyle            | HUN     |
| ...  | ...                        | ...     |

ROW_NUMBER の導入

クエリ

SELECT
  Year, Event, Country,
  ROW_NUMBER() OVER () AS Row_N
FROM Summer_Medals
WHERE
  Medal = 'Gold';

結果

| Year | Event                      | Country | Row_N |
|------|----------------------------|---------|-------|
| 1896 | 100M Freestyle             | HUN     | 1     |
| 1896 | 100M Freestyle For Sailors | GRE     | 2     |
| 1896 | 1200M Freestyle            | HUN     | 3     |
| ...  | ...                        | ...     | ...   |

ウィンドウ関数の構造

クエリ

SELECT
  Year, Event, Country,
  ROW_NUMBER() OVER () AS Row_N
FROM Summer_Medals
WHERE
  Medal = 'Gold';

FUNCTION_NAME() OVER (...)
- ORDER BY
- PARTITION BY
- ROWS/RANGE PRECEDING/FOLLOWING/UNBOUNDED

練習しましょう！

PostgreSQL 集計統計とウィンドウ関数