Ventanas deslizantes

Manipulación de datos en SQL

Mona Khalil

Data Scientist, Greenhouse Software

Ventanas deslizantes

Realizar cálculos relativos a la fila actual
Se puede utilizar para calcular totales acumulados, sumas, medias, etc.
Se puede particionar por una o más columnas

Palabras clave de ventana deslizante

ROWS BETWEEN <start> AND <finish>

PRECEDING
FOLLOWING
UNBOUNDED PRECEDING
UNBOUNDED FOLLOWING
CURRENT ROW

Ejemplo de ventana deslizante

-- Manchester City Home Games 
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            |

Marco de ventana deslizante

-- Manchester City Home Games 
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';

¡Vamos a practicar!

Manipulación de datos en SQL