BD IV: SQL III

Langage Manipulation de Données 3 : Aggrégation/Fenêtres/Partitions

Équipe BD

LPSM Université Paris-Cité

2025-10-03

Plan

• Fonctions d’agrégation
• Partitions, GROUP BY
• Sélection sur les groupes et tri des résultats
• Fonctions fenêtres (Window functions)
• Groupements avancés
• Résumé
• Références
• Fin

Fonctions d’agrégation

Limite de l’algèbre relationnelle pure

L’algèbre relationnelle pure (σ, π, ⋈) ne sait pas tout calculer.

Certains problèmes aisément solubles par un langage de programmation complet (Python, C, R, …) ne sont pas solubles dans l’algèbre relationnelle pure :

• compter. Exemple simple : calculer le nombre de lignes d’une table.

• dans une table décrivant une arborescence (une organisation hiérarchisée), déterminer tous les descendants d’un enregistrement.

Fonctions d’agrégation ?

Possibilité de compter, de faire des moyennes, de trouver un maximum, … en SQL (contrairement à l’algèbre relationnelle “classique”).

Extension possible de l’algèbre relationnelle

Idée

Introduire un opérateur \(Résume()\) qui admet comme arguments une table \(T\) et une suite d’expressions susceptibles de calculer des agrégats : moyenne, somme, comptage, etc.

Proposition de syntaxe :

\(Résume(T, expr_1, ..., expr_k)\) retourne une table \(S\) à une ligne et \(k\) colonnes.

La colonne \(i\) de \(S\) contient l’évaluation de \(expr_i\) sur la table \(T\).

Avertissement

Il n’est pas évident de définir ce que peut être une fonction d’agrégation et un agrégat.

Nous travaillerons à partir d’exemples.

Fonctions d’agrégation SUM et AVG

• Somme des valeurs prises par nomattribut

Résume(T, SOMME(nomattribut))

se traduit en

SELECT 
  SUM ([DISTINCT|ALL] <nomattribut>)  
FROM T

• Moyenne des valeurs prises par nomattribut

Résume(T, MOYENNE(nomattribut))

se traduit en

SELECT 
  AVG ([DISTINCT|ALL] <nom_attribut>) 
FROM T

Avertissement

Le type de l’attribut doit correspondre à un nombre (cf. spécifications de chaque fonction dans la documentation).

Options ALL et DISTINCT des fonctions d’agrégation

Ces options sont disponibles pour toutes les fonctions

mais sont surtout utiles pour COUNT :

• ALL, option par défaut : toutes les valeurs de la colonne sont utilisées,

• DISTINCT : les valeurs doublons ne sont utilisées qu’une fois.

Exemples avec SUM et AVG

• Somme des populations des villes du pays de code FRA (dans le schéma world).

SELECT SUM(population)  
FROM city 
WHERE countrycode = 'FRA';

Traduction de

R1 = σ(city, countrycode = 'FRA')
R = Résume(R1, SOMME(population))

• Moyenne des populations des villes du continent Europe et Afrique.

SELECT AVG(ci.population)  
FROM city ci join country co on ci.countrycode = co.countrycode
WHERE co.continent in ('Afrique','Europe');

Traduction de

R1 = ⋈(city, country, city.countrycode = country.countrycode)
R2 = σ(R1, continent in ('Afrique','Europe'))
R = Résume(R1, avg(population))

Fonctions de calcul MAX, MIN

• maximum des valeurs prises par <nomattribut> dans T :

SELECT 
  MAX (<nomattribut>)  
FROM T ;

• minimum des valeurs prises par <nomattribut> dans T :

SELECT 
  MIN (<nomattribut>)  
FROM T 

Exemple :

SELECT max(gnp)   
FROM country 
WHERE region = 'Caribbean';

Gestion des données manquantes (NULL)

• En statistique : données manquantes prises en compte dans les fonctions d’agrégation.

• En SQL : données manquantes ignorées dans les fonctions d’agrégation,

  à l’exception de COUNT(*).

SELECT name_country, gnpold, gnp
FROM country
WHERE continent = 'Europe' AND (gnp IS NULL OR gnpold IS NULL)

Résultat : 10 lignes.

Pourtant, ces données manquantes n’empèchent pas le calcul du maximum sur ces colonnes.

SELECT max(gnpold) AS max_gnpold, max(gnp) AS max_gnp   
FROM country 
WHERE continent  = 'Europe';

+-------------+------------+
|  max_gnpold |  max_gnp   |
+-------------+------------+
|  2102826.00 | 2133367.00 |
+-------------+------------+

Fonction d’agrégation sur des valeurs toutes NULL

Avertissement

Que se passe-t-il si, après une éventuelle sélection, toutes les valeurs de du calcul d’agrégat sont NULL ?

La valeur calculée est alors NULL, ce qui signifie “pas de valeur”.

Essayez SELECT MIN(gnpold) FROM country WHERE gnpold IS NULL; !

Fonction d’agrégation sur une table vide

Avertissement

Les fonctions d’agrégation renvoie un scalaire même sur une table vide.

Pour une table vide, la valeur calculée est NULL.

SELECT 
  SUM(population) AS urban_pop
FROM city  ci
WHERE ci.countrycode = 'zzz' ;

+-----------+
| urban_pop |
|-----------|
| <null>    |
+-----------+

Est-ce cohérent?

Le résultat signifie : “Pour le pays de code ‘zzz’, pas de données”

Fonctions de calcul : COUNT

On peut aussi compter le nombre de tuples d’une table :

SELECT 
  COUNT(* |[ALL | DISTINCT] <nomattribut>)  
FROM T
-- | pour indiquer une alternative, [ ] pour une option.

Note

Options de la fonction COUNT :

• * : compte le nombre de lignes dans la table ou la sous-requête, en incluant les valeurs NULL.
• DISTINCT : sans les doublons et sans les valeurs NULL,
• ALL : avec les doublons mais sans les valeurs NULL,

Si la table est vide, COUNT renvoie 0.

Nombre de pays dans la région Carribean

SELECT COUNT(*)  
FROM country
WHERE region = 'Caribbean'; 

Exemples d’utilisation des options de la fonction COUNT

SELECT COUNT(*) FROM country;

retourne 239, le nombre de lignes de la table country.

 

SELECT COUNT (indepyear) FROM country;
SELECT COUNT (ALL indepyear) FROM country;

retournent 192, le nombre de valeurs non NULL de la colonne indepyear.

 

SELECT COUNT (DISTINCT indepyear) FROM country;

retourne 88, le nombre de valeurs disctintes et non NULL de la colonne indepyear.

 

SELECT COUNT(*) FROM (SELECT indepyear FROM country) as T;

retourne 239 car T contient une seule colonne avec 239 lignes, les 47 lignes NULL sont comptées comme les autres.

Opérations sur des fonctions d’agrégation

PNB moyen par habitant du continent South America

SELECT 
  ROUND(SUM(1e6*gnp)/SUM(population_country),2) as pnb_avg
FROM 
  country 
WHERE 
  continent = 'South America';

Résultat : 4372,36

Note

\(10^6\) s’écrit 1e6. On a écrit 1e6*gnp car gnp est exprimé en millions de USD.

ROUND(x,s) effectue un arrondi de x avec s chiffres après la virgule. s négatif accepté.

Documentation des fonctions mathématiques

Fonctions d’agrégation sur des opérations

Note

Les fonctions d’agrégation peuvent s’appliquer à des opérations arithmétiques entre les différents attributs d’un même tuple…

Moyenne des PNB/habitant des pays du continent ‘South America’. A ne pas confondre avec la requête précédente !

SELECT 
  ROUND(AVG(1e6*gnp/population_country),2) as pnb_country_avg
FROM country 
WHERE 
  continent = 'South America' AND population_country > 0 ;
-- Le test population_country > 0 évite les divisions par 0

Résultat : 3176,44

Utilisation de CASE WHEN ...THEN ... [ELSE ...] END

Dans une fonction d’agrégation, une expression CASE WHEN teste des conditions sur le tuple courant et renvoie la valeur correspondant à la première condition vraie.

CASE
    WHEN condition1 THEN result1
    [WHEN condition2 THEN result2]
    [WHEN conditionN THEN resultN]
    [ELSE result]
END;

Avertissement

Si aucune condition n’est vraie et qu’il n’y pas de clause ELSE, CASE retourne NULL.

SELECT 
  SUM(
    CASE 
      WHEN countrycode='GBR' THEN population 
    END) AS urban_pop 
 FROM city ;

+-----------+
| urban_pop |
|-----------|
| 22436673  |
+-----------+

Gestion des valeurs manquantes avec CASE WHEN ...THEN ... [ELSE ...] END

SELECT 
  AVG(
    CASE 
      WHEN gnpold IS NOT NULL THEN gnpold 
      ELSE 0 
    END)::NUMERIC(8,2) AS avg_gnpold 
 FROM world.country 
 WHERE continent = 'Europe';

Retourne 203956,54

SELECT 
  AVG(gnpold)::NUMERIC(8,2) AS avg_gnpold 
 FROM world.country 
 WHERE continent = 'Europe';

Retourne 260611,14

Autres possibilités avec CASE WHEN ...THEN ... [ELSE ...] END

SELECT 
  SUM(CASE 
    WHEN governmentform LIKE '%Monarchy%' THEN population_COUNTRY 
    ELSE 0    -- résultat identique si on enlève cette ligne
  END) AS pop_monarch,  
  SUM(CASE 
    WHEN governmentform LIKE '%Republic%' THEN population_COUNTRY 
    ELSE 0   -- résultat identique si on enlève cette ligne
  END) AS pop_repu 
FROM country ;

+-------------+------------+
| pop_monarch | pop_repu   |
|-------------+------------|
| 519485000   | 5502453700 |
+-------------+------------+

Agrégats sur des données filtrées FILTER (WHERE condition)

Avec une clause FILTER, la fonction d’agrégation n’utilise que les tuples vérifiant la condition.

SELECT 
  SUM(population_country) FILTER  
    (WHERE governmentform LIKE '%Monarchy%') AS pop_monarch,  
  SUM(population_country) FILTER 
    (WHERE governmentform LIKE '%Republic%') AS pop_repu 
FROM country ;

+-------------+------------+
| pop_monarch | pop_repu   |
|-------------+------------|
| 519485000   | 5502453700 |
+-------------+------------+

Mise en garde

Il est plus conforme à l’algèbre relationnelle et au SQL d’organiser les données en lignes.

SELECT 'Monarchy' as governmentform, SUM(population_country) as population    
FROM world.country c
WHERE c.governmentform LIKE '%Monarchy%'
UNION
SELECT 'Republic' as governmentform, SUM(population_country) as population    
FROM world.country c
WHERE c.governmentform LIKE '%Republic%';

+----------------+------------+
| governmentform | population |
|----------------+------------|
| Monarchy       |  519485000 |
|----------------+------------|
| Republic       | 5502453700 |
+----------------+------------+

Problèmes d’efficacité

Nom des régions comportant plus de \(15\) pays.

La requête suivante est à éviter :

SELECT DISTINCT c.region 
FROM country c 
WHERE (
  SELECT COUNT (*)     
  FROM country co     
  WHERE co.region = c.region
) >= 15
ORDER BY c.region;  

5 lignes.

Mise en garde

Y-a-t-il des calculs effectués plusieurs fois sur les mêmes données ?

Efficacité

On peut faire plus efficace, gràce à la suite du cours :

SELECT region    
FROM country     
GROUP BY region
HAVING COUNT(*) >= 15
ORDER BY region;

Pour en savoir plus ?

Documentation PostgreSQL

Partitions, GROUP BY

Partition de résultats de requêtes

GROUP BY <nomattribut1>, ..., <nomattributn>

• GROUP BY permet de regrouper l’ensemble des résultats d’une requête selon la valeur de certains attributs,

• Forme des sous-relations auxquelles on peut appliquer des fonctions d’agrégation (SUM, MAX, …) renvoyant un résultat par sous-relation.

Avertissement

Gestion des valeurs manquantes : les valeurs NULLd’un attribut forment un groupe dans la partition selon celui-ci.

Utilisation GROUP BY

Calculer la population de chaque continent.

SELECT continent, SUM(population_country) as population_continent
FROM country 
GROUP BY continent;    

+--------------+----------------------+
| continent    | population_continent |
+--------------+----------------------+
|Asia          |          3705025700  |
|South America |           345780000  |
|North America |           482993000  |
|Oceania       |            30401150  |
|Antarctica    |                   0  |
|Africa        |           784475000  |
|Europe        |           730074600  |
+--------------+----------------------+

Important

On peut afficher continent pour chaque groupe parce qu’on a groupé selon cet attribut

Exemple avec GROUP BY

Quel est le gnp moyen, le gnp maximum et le gnp minimum par region sur le continent Europe en milliers de USD par habitant ?

SELECT region, 
     ROUND(1000 * SUM(gnp)/SUM(population_country),2) AS avg_gnp,  
     ROUND(MAX(1000* gnp/population_country),2) AS max_gnp, 
     ROUND(MIN(1000* gnp/population_country),2) AS min_gnp,
FROM country 
WHERE continent = 'Europe' 
GROUP BY region 
ORDER BY avg_gnp DESC;

+----------------+---------+---------+---------+
| region         | avg_gnp | max_gnp | min_gnp |
+----------------+---------+---------+---------+
|Nordic Countries|   28.00 |   32.66 |    0.00 |
|Western Europe  |   25.50 |   37.46 |   22.82 | 
|British Islands |   22.94 |   23.12 |   20.11 | 
|Southern Europe |   13.91 |   20.90 |    0.72 | 
|Baltic Countries|    2.96 |    3.70 |    2.64 |
|Eastern Europe  |    2.15 |    5.35 |    0.36 |
+----------------+---------+---------+---------+

Précautions avec GROUP BY

Pour chaque région, indiquer le continent, la région et la population du pays le plus peuplé.

La requête suivante n’est pas correcte

SELECT continent, region, MAX(population_country) 
FROM country 
GROUP BY region;

Important

Les attributs présents dans le SELECT doivent impérativement être présents dans le GROUP BY pour s’assurer qu’ils sont communs à tous les tuples de chaque groupe.

Comment afficher un attribut absent de GROUP BY ?

Pour chaque continent, indiquer le nom et la population du (des) pays le(s) plus peuplé(s).

Mise en garde

On ne peut pas utiliser une seule requête puisqu’on veut afficher les pays sans grouper selon les pays. Il faut donc faire les calculs d’agrégats dans une sous-requête.

Préférez les CTEs (ou éventuellement les requêtes imbriquées dans FROM), plus efficaces que les requêtes imbriquées dans WHERE qui sont ré-exécutées inutilement.

Solution

WITH pmc AS
  (SELECT continent, MAX(population_country) AS pop_max
  FROM country
  GROUP BY continent)
SELECT c.continent, c.name_country AS country_max_pop, c.population_country AS population
FROM country c JOIN pmc ON c.continent = pmc.continent
WHERE c.population_country = pmc.pop_max
ORDER BY pmc.pop_max DESC;

+---------------+----------------------------------------------+------------+
| continent     | country_max_pop                              | population |
+---------------+----------------------------------------------+------------+
| Asia          | China                                        | 1277558000 |
| North America | United States                                |  278357000 |
| South America | Brazil                                       |  170115000 |
| Europe        | Russian Federation                           |  146934000 |
| Africa        | Nigeria                                      |  111506000 |
| Oceania       | Australia                                    |   18886000 |
| Antarctica    | Antarctica                                   |          0 |
| Antarctica    | Bouvet Island                                |          0 |
| Antarctica    | South Georgia and the South Sandwich Islands |          0 |
| Antarctica    | Heard Island and McDonald Islands            |          0 |
| Antarctica    | French Southern territories                  |          0 |
+---------------+----------------------------------------------+------------+

Sous-partitionnements

Que fait cette requête ?

WITH 
  co_urb_rate AS
  (
    SELECT co.countrycode, co.name_country, co.continent, SUM(ci.population)/(co.population_country::numeric) AS urb_rate
    FROM world.country co JOIN world.city ci ON co.countrycode = ci.countrycode
    WHERE co.population_country > 0
    GROUP BY co.countrycode, co.name_country, co.continent
  ),
  cont_max_ur AS 
  (
    SELECT continent, MAX(urb_rate) AS max_ur
    FROM co_urb_rate
    GROUP BY continent
  )
SELECT 
  co.continent, 
  co.name_country, 
  round(co.urb_rate,2)
FROM co_urb_rate co JOIN cont_max_ur ct ON co.urb_rate = ct.max_ur;

Pour chaque continent, elle donne le pays le plus urbanisé avec son taux de population urbaine.

Important

La conversion du diviseur avec ::numeric est importante sinon le résultat est un entier (ici 0 ou 1) car la division entre entiers donne un entier par division euclidienne.

Note

Singapore, Gibraltar et les Cocos Islands ont un taux d’urbanisation supérieur à 1 !

Hypothèse : les origines des statistisques de population sur les villes et les pays ne sont pas les mêmes et les critères utilisés sont différents.

Aller plus loin que GROUP BY ?

Note

L’opération GROUP BY ne renvoie pas une table mais (implicitement) une collection de sous-tables indicées/étiquetées par les valeurs des attributs de groupement.

L’opération GROUP BY ne peut pas s’intégrer dans une algèbre relationnelle, même étendue.

Mise en garde

SQL n’offre pas de moyen d’utiliser le partitionnement effectué par GROUP BY autrement que pour effectuer des calculs d’agrégats et/ou une projection sur les attributs de groupement.

Cette requête n’est pas valide :

SELECT *  
FROM country 
GROUP BY continent ;

Cette requête est valide :

SELECT continent  
FROM country 
GROUP BY continent ;

Sélection sur les groupes et tri des résultats

Sélection sur les groupes HAVING

• La clause HAVING permet de poser une condition portant sur chacune des sous-relations générées par le GROUP BY,

• Les sous-relations ne vérifiant pas la condition sont écartées du résultat.

Liste des continents comportant au moins cinq régions.

SELECT continent 
FROM country 
GROUP BY continent    
HAVING COUNT(DISTINCT region) >= 5;  

Tri de résultats ORDER BY

La clause ORDER BY permet de trier le résultat de la requête, en fournissant la liste des attributs sur lesquels effectuer le tri et en spécifiant le sens du tri pour chacun d’eux (ASC ou DESC).

Mise en garde

L’ordre par défaut est ASC.

Liste des pays triés par ordre décroissant de population

SELECT * 
FROM country 
ORDER BY population_country DESC;  

Avertissement

Que se passe-t-il avec les valeurs nulles ?

Le comportement dépend du serveur. PostgreSQL et Oracle les classent comme plus grandes que toutes les autres. SQL Server (Microsoft), MySQL, SQLite font l’inverse.

Ordre des opérations WHERE, GROUP BY, HAVING, ORDER BY

Important

1. la sélection des tuples (WHERE),
2. puis le partitionnement (GROUP BY),
3. puis la sélection sur les groupes (HAVING),
4. enfin le tri des résultats (ORDER BY) .

Que fait cette requête ?

SELECT continent, COUNT(*) 
FROM country 
WHERE countrycode IN (
  SELECT countrycode 
  FROM countrylanguage 
  WHERE language='English' AND percentage > 10 
) 
GROUP BY continent 
HAVING AVG(1e6*gnpold/population_country) >= 1000 
ORDER BY continent;

Fonctions fenêtres (Window functions)

Principe des fonctions fenêtres

Une fonction fenêtre effectue une partition puis un calcul sur le groupe de lignes (“une fenêtre”) auquel la ligne courante appartient.

Documentation PostgreSQL

Note

C’est comparable au type de calcul effectué avec une fonction d’agrégation.

Mais les fonctions de fenêtre permettent de faire des calculs différents de ceux réalisés avec GROUP BY.

• Avec GROUP BY, le résultat rassemble les lignes d’un même groupe en une seule.

• Avec les fonctions fenêtres, chaque ligne de la table reste présente dans le résultat.

Fonctions fenêtres

Soit f est une fonction d’agrégation (SUM, AVG, MAX, MIN, COUNT, etc.)

f(...) OVER (PARTITION BY attribut) 

effectue le calcul sur le groupe de lignes auquel appartient la ligne courante après un partitionnement selon attribut.

Ce groupe de lignes est le cadre de la fonction fenêtre pour la ligne courante.

Exemple de fonctions fenêtres

Présenter pour chaque pays dont la région comprend le motif Countries, l’espérance de vie du pays et les espérance de vie maximale et minimale des pays de la région.

SELECT region, name_country, lifeexpectancy, 
     min(lifeexpectancy) OVER (PARTITION BY region),  
     max(lifeexpectancy) OVER (PARTITION BY region)   
FROM country 
WHERE region LIKE '%Countries'
ORDER BY region, lifeexpectancy  
LIMIT 6 ;

+------------------+-----------------+---------------+-----+-----+
| Region           | Name of country |Life expectancy| min | max |
+------------------+-----------------+---------------+-----+-----+
| Baltic Countries | Latvia          |          68.4 | 68.4| 69.5|
| Baltic Countries | Lithuania       |          69.1 | 68.4| 69.5|
| Baltic Countries | Estonia         |          69.5 | 68.4| 69.5|
| Nordic Countries | Denmark         |          76.5 | 76.5| 79.6|
| Nordic Countries | Finland         |          77.4 | 76.5| 79.6|
| Nordic Countries | Faroe Islands   |          78.4 | 76.5| 79.6|
+------------------+-----------------+---------------+-----+-----+

Note

Avec des fonctions d’agrégation sur un partitionnement par GROUP BY region, il n’était pas possible d’afficher une ligne par pays.

Sans utiliser OVER (...)

Pour calculer ce résultat sans fonctions fenêtres :

1. On effectue une partition selon la region, une aggrégation par groupe pour calculer min(lifeexpectancy) et max(lifeexpectancy). On obtient une table à trois colonnes.

2. On calcule une (équi)-jointure avec la table country originelle sur la colonne commune region.

3. On projette le résultat sur les cinq colonnes pertinentes.

WITH R AS (
  SELECT region, min(lifeexpectancy) AS minlex, max(lifeexpectancy) AS maxlex
  FROM country
  WHERE region LIKE '%Countries' AND lifeexpectancy IS NOT NULL 
  GROUP BY region
)

SELECT region, name_country, lifeexpectancy, minlex, maxlex
FROM country co NATURAL JOIN R

Fonctions fenêtres avec une clause ORDER BY

f(...) OVER (
    PARTITION BY attribut1
    ORDER BY attribut2 
  )

La clause ORDER BY attribut2 :

• ordonne les lignes de la fenêtre définie par PARTITION BY attribut1,
• restreint le cadre de la fenêtre, selon cet ordre, de la première ligne jusqu’aux lignes pour lesquelles attribut2 à la même valeur que dans la ligne courante.

Exemple de fonction fenêtre avec une clause ORDER BY

row_number() : fonction d’agrégation qui retourne le numéro de la ligne dans la fenêtre.

Pour les pays d’Asie, lister la région, le nom du pays, le rang du pays dans sa région pour l’espérance de vie et le rang du pays dans sa région pour le PNB par habitant.

SELECT region, name_country, 
  row_number() OVER (
    PARTITION BY region   
    ORDER BY lifeexpectancy DESC
  ) AS rk_lifeexpectancy,
  row_number() OVER (
    PARTITION BY region   
    ORDER BY gnp / population_country DESC
  ) AS rk_gnp 
FROM world.country 
WHERE continent LIKE 'Asi%' and lifeexpectancy IS NOT NULL and gnp IS NOT NULL
ORDER BY region, lifeexpectancy DESC;

Avertissement

Dans cette requête, il faut rejeter les valeurs NULL sinon le rang n’aurait pas de sens (ces lignes seraient en tête).

Y-a-t-il une correlation entre les deux rangs ?

|region                   |name_country        |rk_lifeexpectancy|rk_gnp|
|-------------------------|--------------------|-----------------|------|
|Eastern Asia             |Macao               |1                |3     |
|Eastern Asia             |Japan               |2                |1     |
|Eastern Asia             |Hong Kong           |3                |2     |
|Eastern Asia             |Taiwan              |4                |4     |
|Eastern Asia             |South Korea         |5                |5     |
|Eastern Asia             |China               |6                |6     |
|Eastern Asia             |North Korea         |7                |8     |
|Eastern Asia             |Mongolia            |8                |7     |
|Middle East              |Israel              |1                |2     |
|Middle East              |Jordan              |2                |12    |
|Middle East              |Cyprus              |3                |5     |
|Middle East              |Kuwait              |4                |4     |
|Middle East              |United Arab Emirates|5                |3     |
|Middle East              |Bahrain             |6                |6     |
|Middle East              |Qatar               |7                |1     |
|Middle East              |Oman                |8                |7     |
|Middle East              |Palestine           |9                |13    |
|Middle East              |Lebanon             |10               |9     |
|Middle East              |Turkey              |11               |11    |
|Middle East              |Syria               |12               |10    |
|Middle East              |Saudi Arabia        |13               |8     |
|Middle East              |Iraq                |14               |17    |
|Middle East              |Armenia             |15               |16    |
|Middle East              |Georgia             |16               |14    |
|Middle East              |Azerbaijan          |17               |15    |
|Middle East              |Yemen               |18               |18    |
|Southeast Asia           |Singapore           |1                |2     |
|Southeast Asia           |Brunei              |2                |1     |
|Southeast Asia           |Malaysia            |3                |4     |
|Southeast Asia           |Vietnam             |4                |9     |
|Southeast Asia           |Thailand            |5                |5     |
|Southeast Asia           |Indonesia           |6                |8     |
|Southeast Asia           |Philippines         |7                |6     |
|Southeast Asia           |Cambodia            |8                |7     |
|Southeast Asia           |Myanmar             |9                |3     |
|Southeast Asia           |Laos                |10               |10    |
|Southeast Asia           |East Timor          |11               |11    |
|Southern and Central Asia|Sri Lanka           |1                |4     |
|Southern and Central Asia|Iran                |2                |1     |
|Southern and Central Asia|Tajikistan          |3                |10    |
|Southern and Central Asia|Uzbekistan          |4                |6     |
|Southern and Central Asia|Kyrgyzstan          |5                |9     |
|Southern and Central Asia|Kazakstan           |6                |2     |
|Southern and Central Asia|India               |7                |7     |
|Southern and Central Asia|Maldives            |8                |5     |
|Southern and Central Asia|Pakistan            |9                |8     |
|Southern and Central Asia|Turkmenistan        |10               |3     |
|Southern and Central Asia|Bangladesh          |11               |12    |
|Southern and Central Asia|Nepal               |12               |13    |
|Southern and Central Asia|Bhutan              |13               |14    |
|Southern and Central Asia|Afghanistan         |14               |11    |

Sommes cumulées par groupes selon un ordre

Avec une clause ORDER BY, une fonction fenêtre SUM() effectue une somme cumulée selon l’ordre choisi.

Calculer, pour chaque pays d’Europe, le pourcentage des PNB supérieur ou égal à celui du pays parmi ceux de sa région.

SELECT 
  region, name_country,  
  ROUND(SUM(gnp) OVER (
    PARTITION BY region  
  ORDER BY gnp DESC) 
  / sum(gnp) OVER (
    PARTITION BY region), 4)*100  
  AS pc_sum_GNP    
FROM world.country c 
WHERE continent = 'Europe'
order by region, gnp DESC;

|region          |name_country                 |pc_sum_gnp|
|----------------|-----------------------------|----------|
|Baltic Countries|Lithuania                    |47,69     |
|Baltic Countries|Latvia                       |76,23     |
|Baltic Countries|Estonia                      |100       |
|British Islands |United Kingdom               |94,78     |
|British Islands |Ireland                      |100       |
|Eastern Europe  |Russian Federation           |41,91     |
|Eastern Europe  |Poland                       |64,9      |
|Eastern Europe  |Czech Republic               |73,23     |
|Eastern Europe  |Hungary                      |80,55     |
|Eastern Europe  |Ukraine                      |86,94     |
|Eastern Europe  |Romania                      |92,72     |
|Eastern Europe  |Slovakia                     |95,84     |
|Eastern Europe  |Belarus                      |97,92     |
|Eastern Europe  |Bulgaria                     |99,76     |
|Eastern Europe  |Moldova                      |100       |
|Nordic Countries|Sweden                       |33,47     |
|Nordic Countries|Denmark                      |59,2      |
|Nordic Countries|Norway                       |80,76     |
|Nordic Countries|Finland                      |98,78     |
|Nordic Countries|Iceland                      |100       |
|Nordic Countries|Svalbard and Jan Mayen       |100       |
|Nordic Countries|Faroe Islands                |100       |
|Southern Europe |Italy                        |57,73     |
|Southern Europe |Spain                        |85,23     |
|Southern Europe |Greece                       |91,23     |
|Southern Europe |Portugal                     |96,49     |
|Southern Europe |Croatia                      |97,49     |
|Southern Europe |Slovenia                     |98,48     |
|Southern Europe |Yugoslavia                   |99,32     |
|Southern Europe |Malta                        |99,5      |
|Southern Europe |Albania                      |99,66     |
|Southern Europe |Bosnia and Herzegovina       |99,8      |
|Southern Europe |Macedonia                    |99,88     |
|Southern Europe |Andorra                      |99,96     |
|Southern Europe |San Marino                   |99,99     |
|Southern Europe |Gibraltar                    |100       |
|Southern Europe |Holy See (Vatican City State)|100       |
|Western Europe  |Germany                      |45,65     |
|Western Europe  |France                       |76,13     |
|Western Europe  |Netherlands                  |84,07     |
|Western Europe  |Switzerland                  |89,73     |
|Western Europe  |Belgium                      |95,08     |
|Western Europe  |Austria                      |99,61     |
|Western Europe  |Luxembourg                   |99,96     |
|Western Europe  |Liechtenstein                |99,98     |
|Western Europe  |Monaco                       |100       |

Clause RANGE dans une fonction fenêtre

Il est possible d’ajouter une clause RANGE à une clause ORDER BY pour modifier le cadre de la fenêtre :

f(...) OVER (
    PARTITION BY attribut1
    ORDER BY attribut2 
    RANGE BETWEEN ... AND ...
  )

On utilise les mots clés UNBOUNDED, PRECEDING, FOLLOWING et CURRENT ROW pour spécifier les bornes de l’intervalle que doit respecter attribut2.

Note

• RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING : le cadre s’étend à tout le groupe défini par la partition.
• RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW : le cadre s’étend de la première ligne du groupe défini par la partition à la ligne courante.

Exemples de clause RANGE dans une fonction fenêtre

Calcul d’une moyenne mobile sur l’intervalle [GNP ligne courante - 1000 , GNP ligne courante + 1000]

SELECT 
  continent, name_country, 
  AVG(gnp) OVER 
  (
    PARTITION BY continent  
    ORDER BY gnp DESC
    RANGE BETWEEN 10000 PRECEDING AND 10000 FOLLOWING 
  ) mob_avg_gnp
FROM world.country c 
ORDER BY continent, gnp DESC;

Calcul d’une moyenne mobile sur l’intervalle [GNP ligne courante - 1000 , GNP ligne courante]

SELECT 
  continent, name_country, 
  AVG(gnp) OVER 
  (
    PARTITION BY continent  
    ORDER BY gnp DESC
    RANGE BETWEEN 10000 PRECEDING AND CURRENT ROW 
  ) mob_avg_gnp
FROM world.country c 
ORDER BY continent, gnp DESC;

Limites d’utilisation des fonctions fenêtres

Avertissement

Les fonctions de fenêtre ne sont autorisées que dans les clauses SELECT et ORDER BY de la requête.

Elles sont interdites ailleurs, par exemple dans les clauses GROUP BY, HAVING et WHERE.

En effet, elles s’exécutent logiquement après le traitement de ces clauses.

Mise en garde

De même, les fonctions de fenêtre s’exécutent après les fonctions d’agrégation habituelles.

Cela signifie qu’il est pertinent d’inclure un appel de fonction d’agrégation dans les arguments d’une fonction de fenêtre, mais pas l’inverse.

Groupements avancés

Introduction

Introduits dans SQL avec la mode du Data mining (Fouille de données) dans les années 1990.

Permet de réaliser de facon apparemment simultanée des aggrégations de régularités emboîtées.

Dans le monde des statistiques, lorsqu’on aggrège des comptages de grains différents, on parle de tables de contingences (contingency tables).

Documentation PostgreSQL

GROUPING SETS

Un GROUPING SETS permet d’effectuer simultanément des calculs d’agrégats pour différentes partitions de la table.

Il s’écrit dans la clause GROUP BY avec une liste d’ensemble d’attributs de la forme ((c1,c2), (c1), (c2)).

GROUP BY GROUPING SETS ((c1,c2), (c1), ())

signifie que l’on fera les calculs d’agrégats successivement pour les regroupements selon c1,c2, puis pour les regroupements selon c1, puis sans regroupement.

Dans le résultat, lorsque le partitionnement selon c1 est effectué, la colonne c2 a la valeur NULL (i.e. pas de valeur), ce qui cohérent avec le fonctionnement de GROUP BY.

On suppose que les colonnes c1 et c2 de la table t ne contiennent chacune que 2 valeurs distinctes.

La requête

SELECT c1, c2, aggregate_function( ... ) as f
FROM t
GROUP BY GROUPING SETS ((c1,c2), (c2), ());   

donne un résultat de la forme :

|c1    |c2    |f    |
|------|------|-----|
|x1    |y1    |z1   |
|x1    |y2    |z2   |
|x2    |y1    |z3   |
|x2    |y2    |z4   |
|      |y1    |z5   |
|      |y2    |z6   |
|      |      |z7   |

Exemple avec GROUPING SETS

SELECT continent, region, max(lifeexpectancy)
FROM country c 
WHERE continent LIKE '%America'
GROUP BY GROUPING SETS  ((continent, region), (continent), ())  
ORDER BY continent, region ;

|continent    |region         |max |
|-------------|---------------|----|
|North America|Caribbean      |78,9|
|North America|Central America|75,8|
|North America|North America  |79,4|
|North America|               |79,4|
|South America|South America  |76,1|
|South America|               |76,1|
|             |               |79,4|

ROLLUP

ROLLUP ( a, b, c, ... )

est un raccourci pour

GROUPING SETS (
    ( a, b, c, ... ),
    ...
    ( a, b ),
    ( a ),
    ( )
)

Exemple avec ROLLUP

SELECT continent, region, max(lifeexpectancy)
FROM country c 
WHERE continent LIKE '%America'
GROUP BY ROLLUP  (continent, region)  
ORDER BY continent, region ;

+-------------+---------------+----+
|Continent    |Region         |max |
+-------------+---------------+----+
|North America|Caribbean      |78.9|
|North America|Central America|75.8|
|North America|North America  |79.4|
|North America|               |79.4|
|South America|South America  |76.1|
|South America|               |76.1|
|             |               |79.4|

CUBE

CUBE (a, b, c) est un raccourci pour un GROUPING SETS contenant toutes les parties de (a, b, c), c.a.d

GROUPING SETS (
    (         )
    ( a, b, c ),
    ( a, b    ),
    ( a       ),
    (    b, c ),
    (    b    ),
    ( a,    c ),
    (       c ),
)

Exemple avec CUBE

SELECT continent, region, governmentform, 
       count(*) AS cnt, 
       max(lifeexpectancy)
FROM country c 
WHERE continent LIKE '%America'
GROUP BY CUBE (continent,region,governmentform)
HAVING COUNT(*) > 5;

|continent    |region         |governmentform               |cnt|max |
|-------------|---------------|-----------------------------|---|----|
|             |               |                             |51 |79,4|
|South America|South America  |Republic                     |9  |75,7|
|North America|Central America|Republic                     |6  |75,8|
|North America|Caribbean      |Constitutional Monarchy      |8  |75,2|
|North America|Caribbean      |                             |24 |78,9|
|South America|South America  |                             |14 |76,1|
|North America|Central America|                             |8  |75,8|
|South America|               |                             |14 |76,1|
|North America|               |                             |37 |79,4|
|             |Caribbean      |Constitutional Monarchy      |8  |75,2|
|             |South America  |Republic                     |9  |75,7|
|             |Central America|Republic                     |6  |75,8|
|             |South America  |                             |14 |76,1|
|             |Central America|                             |8  |75,8|
|             |Caribbean      |                             |24 |78,9|
|North America|               |Constitutional Monarchy      |9  |75,2|
|North America|               |Republic                     |10 |75,8|
|South America|               |Republic                     |9  |75,7|
|North America|               |Dependent Territory of the UK|6  |78,9|
|             |               |Republic                     |19 |75,8|
|             |               |Constitutional Monarchy      |9  |75,2|
|             |               |Dependent Territory of the UK|7  |78,9|

Résumé

Portrait robot d’une requête

SELECT <attributs>           -- les colonnes de la table résultat

FROM <relations>             -- la/les table/s qui émettent les données

[WHERE <condition> ]         -- filtre

[GROUP BY <attributs de partitionnement>   -- découpage en groupes

[HAVING <condition>]]      -- filtrage des groupes

[ORDER BY <critere>]       -- trier 

• SELECT : attributs du résultat (avec agrégations éventuelles)
• WHERE : condition de sélection indépendante du GROUP BY
• HAVING : condition de sélection portant sur les groupes

Ordre d’exécution des éléments d’une requête

1. Rassembler les données mentionnées dans la clause FROM

2. Filter les lignes selon la clause WHERE

3. Regrouper les lignes selon la clause GROUP BY

4. Filtrer les groupes selon la clause HAVING

5. Specifier les colonnes du résultat selon la clause SELECT

6. Trier le résultat final selon la clause ORDER BY

Références

Tutoriel SELECT de PostGreSQL

Tutoriel joins

Documentation requêtes

Documentation SELECT

GROUPING SETS, ROLLUP, CUBE

Fin

Requêtes SQL : Aggrégation/Fenêtres/Partition