Fonctions de fenêtre

Manipulation de données en SQL

Mona Khalil

Data Scientist, Greenhouse Software

Utilisation des valeurs agrégées

Nécessitent l'utilisation de GROUP BY avec toutes les colonnes non agrégées

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

Présentation des fonctions de fenêtre !

Effectuer des calculs sur un ensemble de résultats déjà généré (une fenêtre)
Calculs agrégés
- Semblable aux sous-requêtes dans SELECT
- Totaux cumulés, classements, moyennes mobiles

Qu'est-ce qu'une fonction de fenêtre ?

Combien de buts ont été marqués lors de chaque match en 2011/2012, et comment ce chiffre se compare-t-il à la moyenne ?

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'est-ce qu'une fonction de fenêtre ?

Combien de buts ont été marqués lors de chaque match en 2011/2012, et comment ce chiffre se compare-t-il à la moyenne ?

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           |

Générer un classement (RANK)

Quel est le classement des matchs en fonction du nombre de buts marqués ?

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     |

Générer un classement (RANK)

Quel est le classement des matchs en fonction du nombre de buts marqués ?

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          |

Générer un classement (RANK)

Quel est le classement des matchs en fonction du nombre de buts marqués ?

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          |

Principales différences

Traité après chaque partie de la requête, à l'exception de ORDER BY
- Utilise les informations contenues dans le jeu de résultats plutôt que celles de la base de données
Disponible dans PostgreSQL, Oracle, MySQL, SQL Server…
- … mais PAS dans SQLite

Passons à la pratique !

Manipulation de données en SQL