Pendahuluan

Ringkasan Statistik dan Window Functions di PostgreSQL

Michel Semaan

Data Scientist

Motivasi

Total dan total berjalan medali emas Olimpiade Musim Panas AS sejak 2004

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

Status juara bertahan lempar cakram

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

Garis besar kursus

Pengenalan fungsi jendela
Fetching, ranking, dan paging
Agregat fungsi jendela dan frame
Di luar fungsi jendela

Dataset Olimpiade Musim Panas

Tiap baris mewakili satu medali yang diberikan di Olimpiade Musim Panas

Kolom

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

Fungsi jendela

Melakukan operasi pada sekumpulan baris yang terkait dengan baris saat ini
Mirip fungsi agregat GROUP BY, tetapi semua baris tetap di output

Kegunaan

Mengambil nilai dari baris sebelumnya/berikutnya (mis. nilai baris sebelumnya)
- Menentukan status juara bertahan
- Menghitung pertumbuhan dari waktu ke waktu
Memberi peringkat ordinal (1, 2, dst.) pada baris berdasarkan posisi nilainya dalam daftar terurut
Total berjalan, rata-rata bergerak

Nomor baris

Kueri

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

Hasil

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

Masuk ROW_NUMBER

Kueri

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

Hasil

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

Anatomi fungsi jendela

Kueri

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!

Ringkasan Statistik dan Window Functions di PostgreSQL