Finestre mobili

Manipolazione dei dati in SQL

Mona Khalil

Data Scientist, Greenhouse Software

Finestre mobili

Calcoli relativi alla riga attuale
Usato per totali, somme, medie, ecc.
Può essere partizionato per una o più colonne

Parole chiave finestre mobili

ROWS BETWEEN <start> AND <finish>

PRECEDING
FOLLOWING
UNBOUNDED PRECEDING
UNBOUNDED FOLLOWING
CURRENT ROW

Esempio di finestra mobile

-- Partite in casa del Manchester City 
SELECT 
  date,
  home_goal,
  away_goal,
  SUM(home_goal) 
     OVER(ORDER BY date ROWS BETWEEN 
          UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM match
WHERE hometeam_id = 8456 AND season = '2011/2012';

| date       | home_goal | away_goal | running_total |
|------------|-----------|-----------|---------------|
| 2011-08-15 | 4         | 0         | 4             |
| 2011-09-10 | 3         | 0         | 7             |
| 2011-09-24 | 2         | 0         | 9             |
| 2011-10-15 | 4         | 1         | 13            |

Frame della finestra mobile

-- Partite in casa del Manchester City 
SELECT date,
       home_goal,
       away_goal,
       SUM(home_goal) 
          OVER(ORDER BY date 
          ROWS BETWEEN 1 PRECEDING 
          AND CURRENT ROW) AS last2
FROM match
WHERE hometeam_id = 8456
      AND season = '2011/2012';

Facciamo pratica!

Manipolazione dei dati in SQL