Funciones de ventana

Manipulación de datos en SQL

Mona Khalil

Data Scientist, Greenhouse Software

Trabajar con valores agregados

Requiere que utilices GROUP BY con todas las columnas no agregadas

SELECT
  country_id,
  season,
  date,
  AVG(home_goal) AS avg_home
FROM match
GROUP BY country_id;

ERROR: column "match.season" must appear in the GROUP BY 
clause or be used in an aggregate function

¡Presentamos las funciones de ventana!

Realizar cálculos sobre un conjunto de resultados ya generado (una ventana)
Cálculos agregados
- Similar a las subconsultas en SELECT
- Totales acumulados, clasificaciones, medias móviles

¿Qué es una función de ventana?

¿Cuántos goles se marcaron en cada partido en la temporada 2011/2012 y cómo es ese número respecto a la media?

SELECT
  date,
  (home_goal + away_goal) AS goals,
  (SELECT AVG(home_goal + away_goal) 
     FROM match
     WHERE season = '2011/2012') AS overall_avg
FROM match
WHERE season = '2011/2012';

| date       | goals | overall_avg       |
|------------|-------|-------------------|
| 2011-07-29 | 3     | 2.71646           |
| 2011-07-30 | 2     | 2.71646           |
| 2011-07-30 | 4     | 2.71646           |
| 2011-07-30 | 1     | 2.71646           |

¿Qué es una función de ventana?

¿Cuántos goles se marcaron en cada partido en la temporada 2011/2012 y cómo es ese número respecto a la media?

SELECT
    date,
    (home_goal + away_goal) AS goals,
    AVG(home_goal + away_goal) OVER() AS overall_avg
FROM match
WHERE season = '2011/2012';

| date       | goals | overall_avg       |
|------------|-------|-------------------|
| 2011-07-29 | 3     | 2.71646           |
| 2011-07-30 | 2     | 2.71646           |
| 2011-07-30 | 4     | 2.71646           |
| 2011-07-30 | 1     | 2.71646           |

Generar una RANK

¿Cuál es la clasificación de los partidos según el número de goles marcados?

SELECT
    date,
    (home_goal + away_goal) AS goals
FROM match
WHERE season = '2011/2012';

| date       | goals |
|------------|-------|
| 2011-07-29 | 3     |
| 2011-07-30 | 2     |
| 2011-07-30 | 4     |
| 2011-07-30 | 1     |

Generar una RANK

¿Cuál es la clasificación de los partidos según el número de goles marcados?

SELECT
    date,
    (home_goal + away_goal) AS goals,
    RANK() OVER(ORDER BY home_goal + away_goal) AS goals_rank
FROM match
WHERE season = '2011/2012';

| date       | goals | goals_rank |
|------------|-------|------------|
| 2012-04-28 | 0     | 1          |
| 2011-12-26 | 0     | 1          |
| 2011-09-10 | 0     | 1          |
| 2011-08-27 | 0     | 1          |
| 2012-01-07 | 0     | 1          |

Generar una RANK

¿Cuál es la clasificación de los partidos según el número de goles marcados?

SELECT
    date,
    (home_goal + away_goal) AS goals,
    RANK() OVER(ORDER BY home_goal + away_goal DESC) AS goals_rank
FROM match
WHERE season = '2011/2012';

| date       | goals | goals_rank |
|------------|-------|------------|
| 2011-11-06 | 10    | 1          |
| 2011-08-28 | 10    | 1          |
| 2012-05-12 | 9     | 3          |
| 2012-02-12 | 9     | 3          |
| 2012-03-09 | 9     | 3          |

Diferencias clave

Procesado después de cada parte de la consulta, excepto ORDER BY
- Utiliza la información del conjunto de resultados en lugar de la base de datos
Disponible en PostgreSQL, Oracle, MySQL, SQL Server...
- ... pero NOT en SQLite

¡Vamos a practicar!

Manipulación de datos en SQL