介绍

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

Passons à la pratique !

PostgreSQL 汇总统计与窗口函数