Introducere

Statistici sumare și funcții de fereastră în PostgreSQL

Michel Semaan

Data Scientist

Motivație

Total și total cumulat al medaliilor de aur olimpice de vară ale SUA din 2004

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

Statutul de campion en-titre la aruncarea discului

| 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             |
Statistici sumare și funcții de fereastră în PostgreSQL

Structura cursului

  1. Introducere în funcțiile de fereastră
  2. Extragere, clasificare și paginare
  3. Funcții de fereastră agregate și cadre
  4. Dincolo de funcțiile de fereastră
Statistici sumare și funcții de fereastră în PostgreSQL

Setul de date olimpice de vară

  • Fiecare rând reprezintă o medalie acordată la Jocurile Olimpice de Vară

Coloane

  • Year, City
  • Sport, Discipline, Event
  • Athlete, Country, Gender
  • Medal
Statistici sumare și funcții de fereastră în PostgreSQL

Funcții de fereastră

  • Efectuează o operație pe un set de rânduri corelate cu rândul curent
  • Similar cu funcțiile agregate GROUP BY, dar toate rândurile rămân în rezultat

Utilizări

  • Extragerea valorilor din rânduri anterioare sau următoare (ex. valoarea rândului precedent)
    • Determinarea statutului de campion en-titre
    • Calcularea creșterii în timp
  • Atribuirea de ranguri ordinale (1, 2, etc.) rândurilor pe baza pozițiilor valorilor într-o listă sortată
  • Totaluri cumulate, medii mobile
Statistici sumare și funcții de fereastră în PostgreSQL

Numere de rând

Interogare

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

Rezultat

| Year | Event                      | Country |
|------|----------------------------|---------|
| 1896 | 100M Freestyle             | HUN     |
| 1896 | 100M Freestyle For Sailors | GRE     |
| 1896 | 1200M Freestyle            | HUN     |
| ...  | ...                        | ...     |
Statistici sumare și funcții de fereastră în PostgreSQL

Introducere în ROW_NUMBER

Interogare

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

Rezultat

| Year | Event                      | Country | Row_N |
|------|----------------------------|---------|-------|
| 1896 | 100M Freestyle             | HUN     | 1     |
| 1896 | 100M Freestyle For Sailors | GRE     | 2     |
| 1896 | 1200M Freestyle            | HUN     | 3     |
| ...  | ...                        | ...     | ...   |
Statistici sumare și funcții de fereastră în PostgreSQL

Anatomia unei funcții de fereastră

Interogare

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
Statistici sumare și funcții de fereastră în PostgreSQL

Să exersăm!

Statistici sumare și funcții de fereastră în PostgreSQL

Preparing Video For Download...