Introduzione

Statistiche riepilogative e funzioni finestra in PostgreSQL

Michel Semaan

Data Scientist

Motivazione

USA: totale e cumulato delle medaglie d’oro estive dal 2004

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

Stato campione in carica lancio del disco

| 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             |
Statistiche riepilogative e funzioni finestra in PostgreSQL

Struttura del corso

  1. Introduzione alle funzioni finestra
  2. Recupero, ranking e paginazione
  3. Aggregazioni finestrate e frame
  4. Oltre le funzioni finestra
Statistiche riepilogative e funzioni finestra in PostgreSQL

Dataset Olimpiadi estive

  • Ogni riga rappresenta una medaglia assegnata ai Giochi Olimpici estivi

Colonne

  • Year, City
  • Sport, Discipline, Event
  • Athlete, Country, Gender
  • Medal
Statistiche riepilogative e funzioni finestra in PostgreSQL

Funzioni finestra

  • Esegue un’operazione su un insieme di righe correlate alla riga corrente
  • Simile alle aggregazioni con GROUP BY, ma tutte le righe restano in output

Utilizzi

  • Recuperare valori da righe precedenti o successive (es. il valore della riga precedente)
    • Determinare lo status di campione in carica
    • Calcolare la crescita nel tempo
  • Assegnare ranghi ordinali (1°, 2°, ecc.) in base alla posizione in un elenco ordinato
  • Totali cumulati, medie mobili
Statistiche riepilogative e funzioni finestra in PostgreSQL

Numeri di riga

Query

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

Risultato

| Year | Event                      | Country |
|------|----------------------------|---------|
| 1896 | 100M Freestyle             | HUN     |
| 1896 | 100M Freestyle For Sailors | GRE     |
| 1896 | 1200M Freestyle            | HUN     |
| ...  | ...                        | ...     |
Statistiche riepilogative e funzioni finestra in PostgreSQL

Introduzione a ROW_NUMBER

Query

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

Risultato

| Year | Event                      | Country | Row_N |
|------|----------------------------|---------|-------|
| 1896 | 100M Freestyle             | HUN     | 1     |
| 1896 | 100M Freestyle For Sailors | GRE     | 2     |
| 1896 | 1200M Freestyle            | HUN     | 3     |
| ...  | ...                        | ...     | ...   |
Statistiche riepilogative e funzioni finestra in PostgreSQL

Anatomia di una finestra

Query

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
Statistiche riepilogative e funzioni finestra in PostgreSQL

Passons à la pratique !

Statistiche riepilogative e funzioni finestra in PostgreSQL

Preparing Video For Download...