Giới thiệu

Thống kê tóm tắt và Window Functions trong PostgreSQL

Michel Semaan

Data Scientist

Động lực

Tổng và tổng luỹ kế huy chương vàng Thế vận hội Mùa hè của USA từ 2004

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

Trạng thái đương kim vô địch ném đĩa

| 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             |

Đề cương học phần

Giới thiệu hàm cửa sổ
Lấy, xếp hạng, phân trang
Hàm tổng hợp cửa sổ và khung
Vượt xa hàm cửa sổ

Bộ dữ liệu Thế vận hội mùa hè

Mỗi hàng là một huy chương tại Thế vận hội Mùa hè

Cột

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

Hàm cửa sổ

Thực hiện phép tính trên một tập hàng có liên quan đến hàng hiện tại
Tương tự hàm tổng hợp GROUP BY, nhưng mọi hàng vẫn giữ trong kết quả

Ứng dụng

Lấy giá trị từ các hàng trước/sau (ví dụ: giá trị hàng trước)
- Xác định trạng thái đương kim vô địch
- Tính tăng trưởng theo thời gian
Gán thứ hạng (1st, 2nd, v.v.) cho hàng theo vị trí trong danh sách đã sắp xếp
Tổng luỹ kế, trung bình trượt

Số thứ tự hàng

Truy vấn

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

Kết quả

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

Giới thiệu ROW_NUMBER

Truy vấn

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

Kết quả

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

Cấu trúc của hàm cửa sổ

Truy vấn

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

Ayo berlatih!

Thống kê tóm tắt và Window Functions trong PostgreSQL