介紹

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 名，等等）
累計總和、移動平均

列號（Row numbers）

查詢

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 統計摘要與視窗函式