BD IV: SQL III

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

Équipe BD

2024-10-04

Fonctions d’agrégation en SQL

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 ( \(\sigma(), \pi(), \bowtie(), ...\) ) :

accessibilité dans un graphe non-orienté (qui puis-je contacter dans un réseau social?)
compter : quel est le nombre de lignes d’une table ?

Extension de l’algèbre relationnelle

Idée générale

Un opérateur Résume() qui admet une opérande T de type table et comme autres arguments une suite d’expressions susceptibles de calculer des agrégats

Résume(T, expr1, ..., exprk) retourne une table S à une ligne et k colonnes

La colonne i de S contient l’évaluation de expri sur la table T

Mise en garde

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 concrets

Fonctions d’agrégation en SQL

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

Tutorial Aggregation functions PostgreSQL

Usages

Analyse commerciale ou Buisiness Analytics,
OLAP
Statistique et Data Science

Dans la partie `SELECT`

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

Le type des attributs doit être un nombre ou un entier (sinon, impossible de faire des sommes, des moyennes…).

Fonctions d’agrégation : `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

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

GNP moyen par habitant (pas pondéré!) sur le continent South America

SELECT 
  AVG(1e6*gnp/population_country)::decimal(8,0) 
FROM 
  country 
WHERE 
  continent = 'South America'

Traduction de

σ(country, continent = 'South America') |>
  Résume(MOYENNE(1e6*gnp/population_country))

Fonctions MAX et MIN

Fonctions de calcul : `MAX`, `MIN`

SELECT 
  MAX (<nomattribut>)  
FROM T ;

maximum des valeurs prises par <nomattribut>

SELECT 
  MIN (<nomattribut>)  
FROM T

minimum des valeurs prises par <nomattribut> dans T

Mise en garde

Attention aux valeurs nulles !

NULL = MIN(NULL, 3, 100) ou 3 = MIN(NULL, 3, 100) ?

Essayez SELECT MIN(gnpold) FROM country; !

SELECT MAX (gnpold), max(gnp)   
FROM country 
WHERE region = 'Caribbean';
   max    |   max    
----------+----------
 32100.00 | 34100.00

Que se passe-t-il lorsqu’on calcule un agrégat sur une colonne contenant des données manquantes (NULL) ?

Quelles sont les procédés mis en œuvre en statistique ?

Quels sont les procédés mis en œuvre par PostgreSQL ?

SELECT name_country, gnpold, gnp
FROM country
WHERE continent = 'Europe' AND (gnp IS NULL OR gnpold IS NULL)
         name_country          | gnpold |   gnp    
-------------------------------+--------+----------
 Andorra                       |        |  1630.00
 Bosnia and Herzegovina        |        |  2841.00
 Faroe Islands                 |        |     0.00
 Gibraltar                     |        |   258.00
 Svalbard and Jan Mayen        |        |     0.00
 Yugoslavia                    |        | 17000.00
 Monaco                        |        |   776.00
 San Marino                    |        |   510.00
 Belarus                       |        | 13714.00
 Holy See (Vatican City State) |        |     9.00

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 COUNT

Fonctions de calcul : `COUNT`

On peut aussi compter le nombre de tuples dans le résultat d’une requête

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

Nombre de valeurs prises par le résultat

DISTINCT : sans les doublons
ALL: avec les doublons
* : y compris les valeurs nullles/manquantes

Nombre de territoires dans la région Carribean

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

Retour sur fonction `SUM`

Fonction de calcul `SUM()`

On peut sommer les valeurs contenues dans une colonne numérique

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

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

Sommation sur tableau vide

SELECT 
  SUM(population) AS urban_pop
FROM city  ci
WHERE ci.countrycode = 'zzz' ;
+-----------+
| urban_pop |
|-----------|
| <null>    |
+-----------+

Est-ce cohérent?

Autre approche avec `CASE WHEN ...THEN ... ELSE ...`

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

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

SELECT 
  SUM(
    CASE 
      WHEN countrycode='xxx' THEN population 
      ELSE 0 
    END) AS urban_pop 
 FROM city  ci ;
+-----------+
| urban_pop |
|-----------|
| 0         |
+-----------+

Calculer les populations vivant sous diﬀérents régimes

SELECT 
  SUM(CASE 
    WHEN governmentform LIKE '%Monarchy%' THEN population_COUNTRY 
    ELSE 0 
  END) AS pop_monarch,  
  SUM(CASE 
    WHEN governmentform LIKE '%Republic%' THEN population_COUNTRY 
    ELSE 0 
  END) AS pop_repu 
FROM country ;
+-------------+------------+
| pop_monarch | pop_repu   |
|-------------+------------|
| 519485000   | 5502453700 |
+-------------+------------+

Agrégats sur données filtrées `FILTER (WHERE 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 |
+-------------+------------+

Pour en savoir plus ?

Documentation PostgreSQL

Fonctions de calcul : exemples

Combinaison de quelques opérations et fonctions

Nom des régions comportant plus de \(10\) territoires.

--| eval: false
--| echo: false
--| code-line-numbers: "1-2|4-6|"
SELECT DISTINCT c.region 
FROM country c 
WHERE (
  SELECT COUNT (*)     
  FROM country co     
  WHERE co.region = c.region
) >= 15;

Musée des horreurs

Trouver les régions où au moins un pays possède une espérance de vie inférieure à 50

SELECT DISTINCT continent, region
FROM country c 
WHERE (
  SELECT MIN(d.lifeexpectancy)     
  FROM country d    
  WHERE c.region=d.region
  ) < 50    
ORDER BY continent, region;

Continent	Region
Africa	Central Africa
Africa	Eastern Africa
Africa	Northern Africa
Africa	Southern Africa
Africa	Western Africa
Asia	Southeast Asia
Asia	Southern and Central Asia
North America	Caribbean

On peut faire plus simple (et plus efficace) dans le case présent

SELECT 
  DISTINCT continent, region
FROM country c 
WHERE c.lifeexpectancy <50 
ORDER BY continent, region;

Continent	Region
Africa	Central Africa
Africa	Eastern Africa
Africa	Northern Africa
Africa	Southern Africa
Africa	Western Africa
Asia	Southeast Asia
Asia	Southern and Central Asia
North America	Caribbean

Mais si on veut lister les régions où tous les pays ont une espèrance de vie supérieure à 50 ?

Pourquoi pas ?

SELECT 
  DISTINCT continent, region
FROM country c 
WHERE (
  SELECT MIN(d.lifeexpectancy)     
  FROM country d    
  WHERE c.region=d.region
  ) > 50    
ORDER BY continent, region;

Fonctions de calcul : exemples (suite)

Utilisation d’opérateurs arithmétique entre les différents attributs d’un même tuple…

SELECT 
  AVG(1e6*gnp/population_country)::decimal(8,0) 
FROM country 
WHERE 
  continent LIKE 'South Am%' AND
  population_country > 0 ;

Partitions, `GROUP BY`

Partition de résultats de requêtes

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

GROUP BY permet de regrouper selon la valeur de certains attributs l’ensemble des résults d’une requête
Forme des sous-relations auxquelles on peut appliquer des opérateurs (SUM, MAX, …) renvoyant un résultat par sous-relation

Exemple d’utilisation : regrouper les livraisons par numéro de fournisseur et prendre la quantité maximum livrée par fournisseur.

Partition de résultats de requêtes

Schéma général

SELECT ... 
FROM country 
GROUP BY continent

+-------------+---------------------+---------------+
| countrycode | name_country        | continent     |
|-------------+---------------------+---------------|
| DZA         | Algeria             | Africa        |
| AGO         | Angola              | Africa        |
| ...         |                     |               |
| ATA         | Antarctica          | Antarctica    |
| BVT         | Bouvet Island       | Antarctica    |
| ...         |                     |               |
| AFG         | Afghanistan         | Asia          |
| ARM         | Armenia             | Asia          |
| ...         |                     |               |
| ALB         | Albania             | Europe        |
| AND         | Andorra             | Europe        |
| ...         |                     |               |
| AIA         | Anguilla            | North America |
| ATG         | Antigua and Barbuda | North America |
| ...         |                     |               |
| ASM         | American Samoa      | Oceania       |
| AUS         | Australia           | Oceania       |
| ...         |                     |               |
| ARG         | Argentina           | South America |
| BOL         | Bolivia             | South America |
+-------------+---------------------+---------------+

Partition de résultats de requêtes

Population maximale par continent

SELECT continent, max(population_country) 
FROM country 
GROUP BY continent ;

+---------------+------------+
| continent     | max        |
|---------------+------------|
| Asia          | 1277558000 |
| South America | 170115000  |
| North America | 278357000  |
| Oceania       | 18886000   |
| Antarctica    | 0          |
| Africa        | 111506000  |
| Europe        | 146934000  |
+---------------+------------+

Les opérateurs (MAX, SUM, etc) s’appliquent à chaque groupe de relations.

Exemple

Dans le schéma world, quel est le gnp moyen par region sur le continent Europe ?

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,
       MIN(lifeexpectancy) AS min_life_exp
FROM country 
WHERE continent = 'Europe' 
GROUP BY region 
ORDER BY avg_gnp DESC;

region	avg_gnp	max_gnp	min_gnp	min_life_exp
Nordic Countries	28.00	32.66	0.00	76.5
Western Europe	25.50	37.46	22.82	77.1
British Islands	22.94	23.12	20.11	76.8
Southern Europe	13.91	20.90	0.72	71.5
Baltic Countries	2.96	3.70	2.64	68.4
Eastern Europe	2.15	5.35	0.36	64.5

Partition de résultats de requêtes

Quel est le sens de cette requête ?

SELECT 
  continent, 
  COUNT(*) as nombre_pays, 
  SUM(population_country) as population_totale
FROM country 
GROUP BY continent;

La requête suivante requête n’est pas correcte

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

Important

Les attributs présents dans le SELECT sont forcémment présents dans le GROUP BY.

Partition de résultats de requêtes

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 comportent au moins cinq régions.

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

Operation `GROUP BY` et calcul sur les tables

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 de mémoriser le résultat d’un GROUP BY avant d’effectuer des agrégations ou une projection sur les attributs de groupement.

SELECT *  
FROM country 
GROUP BY continent  ;
column "country.countrycode" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT * FROM country GROUP BY continent

SELECT continent  
FROM country 
GROUP BY continent  ;
+---------------+
| continent     |
|---------------|
| Asia          |
| South America |
| North America |
| Oceania       |
| Antarctica    |
| Africa        |
| Europe        |
+---------------+

Tri des résultats

Présentation/tri de résultats

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 (ascendan ou descendant)

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

SELECT * 
FROM country 
ORDER BY population_country DESC;

Regroupement, tri, etc : exemple

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;

Retour au musée des horreurs

SELECT continent, region, MIN(c.lifeexpectancy)
FROM country c 
GROUP BY continent, region
HAVING MIN(c.lifeexpectancy) < 50  
ORDER BY continent, region ;

Continent	Region	max	min
Africa	Central Africa	65.3	38.3
Africa	Eastern Africa	72.7	37.2
Africa	Northern Africa	75.5	49.8
Africa	Southern Africa	51.1	39.3
Africa	Western Africa	76.8	41.3
Asia	Southeast Asia	80.1	46.0
Asia	Southern and Central Asia	71.8	45.9
North America	Caribbean	78.9	49.2

Fonctions fenêtres (Window functions)

Une fonction fenêtre effectue un calcul sur un (sous)-ensemble de lignes de la table qui sont liées à la ligne courante.

Note

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

Mais les fonctions de fenêtre ne regroupement pas les lignes en une seule ligne de sortie.

Au contraire, les lignes conservent leurs identités distinctes.

En coulisses, la fonction de fenêtre est capable d’accéder à plus que la ligne actuelle du résultat de la requête.

Exemple

Dans le schéma world, présenter pour chaque pays dont la région comprend le motif Countries, l’espérance de vie, l’espérance de vie maximale et minimale dans 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

Calculer une fonction fenêtre sans invoquer `OVER (...)`

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

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.
on calcule une (équi)-jointure avec la table country originelle sur la colonne commune region
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

Variations

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

Region	Name of country	life expectancy	rank
Baltic Countries	Estonia	69.5	1
Baltic Countries	Lithuania	69.1	2
Baltic Countries	Latvia	68.4	3
Nordic Countries	Svalbard and Jan Mayen		1
Nordic Countries	Sweden	79.6	2
Nordic Countries	Iceland	79.4	3

Sommes cumulées par groupes selon un ordre

SELECT 
    region, name_country, 
    sum(gnp) OVER (
    PARTITION BY region  
        ORDER BY gnp
)     
FROM country c 
WHERE region LIKE '%Europe' 
LIMIT 6;

Region	Name Country	Sum
Eastern Europe	Moldova	1579.00
Eastern Europe	Bulgaria	13757.00
Eastern Europe	Belarus	27471.00
Eastern Europe	Slovakia	48065.00
Eastern Europe	Romania	86223.00
Eastern Europe	Ukraine	128391.00

Mise en garde

Les fonctions de fenêtre ne sont autorisées que dans la liste SELECT et la clause 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.

Exemple

Pour chaque continent, afficher les trois pays ayant l’espérance de vie à la naissance la plus élevée

SELECT continent, 
  name_country, 
  lifeexpectancy, 
  pos
FROM
  (SELECT continent, 
    name_country, 
    lifeexpectancy,
    rank() OVER (
      PARTITION BY continent 
      ORDER BY lifeexpectancy DESC) AS pos
  FROM country
  WHERE lifeexpectancy IS NOT NULL
  ) AS ws
WHERE pos <= 3;

Avec `WITH ...` et les CTEs

WITH ws AS (
  SELECT continent, 
    name_country, 
    lifeexpectancy,
    rank() OVER (
      PARTITION BY continent 
      ORDER BY lifeexpectancy DESC) AS pos
  FROM country
  WHERE lifeexpectancy IS NOT NULL
) 
SELECT continent, 
  name_country, 
  lifeexpectancy, 
  pos
FROM ws   
WHERE pos <= 3;

Exemple

Afficher le pays le plus peuplé

SELECT S.name_country, 
  S.continent, 
  S.population_country
FROM (
  SELECT max(population_country) AS mpc 
  FROM country c
  ) AS R 
  JOIN LATERAL (    
      SELECT * 
      FROM country c2 
      WHERE c2.population_country >= R.mpc
    ) AS S 
  ON (TRUE)
) ;

SELECT c.name_country, 
       c.continent, 
       c.population_country
FROM (
    SELECT max(population_country) AS mpc 
    FROM country c2
  ) AS S
  JOIN country c 
  ON (c.population_country >= S.mpc)    
;

name_country|continent|population_country|
------------+---------+------------------+
China       |Asia     |        1277558000|

Exemple (suite)

Afficher le pays le plus peuplé

SELECT * 
FROM world.country c  
WHERE population_country >= ALL(
  SELECT cc.population_country  
  FROM world.country  cc
) ;

WITH S AS (        
    SELECT c.*, max(population_country) 
         OVER () AS mpc  
    FROM world.country c
)
SELECT * 
FROM S   
WHERE population_country >= mpc ;

name_country|continent|population_country|
------------+---------+------------------+
China       |Asia     |        1277558000|

Exemple

Afficher les 10 pays les plus peuplés

WITH S AS (        
    SELECT c.*, rank() 
    OVER (ORDER BY population_country DESC) AS rpc  
    FROM world.country c
)

SELECT name_country, continent, population_country
FROM S   
WHERE rpc <= 10 
ORDER BY rpc DESC;

name_country      |continent    |population_country|
------------------+-------------+------------------+
China             |Asia         |        1277558000|
India             |Asia         |        1013662000|
United States     |North America|         278357000|
Indonesia         |Asia         |         212107000|
Brazil            |South America|         170115000|
Pakistan          |Asia         |         156483000|
Russian Federation|Europe       |         146934000|
Bangladesh        |Asia         |         129155000|
Japan             |Asia         |         126714000|
Nigeria           |Africa       |         111506000|

Groupements avancés

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).

`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|

GROUPING SETS

SELECT c1, c2, aggregate_function(c3)
FROM table_name
GROUP BY GROUPING SETS ((c1, c2), (c1), (c2), ());

ROLLUP and CUBE are special cases of GROUPING SETS

`GROUPING SETS` (suite)

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|

`CUBE`

SELECT c1, c2, c3,
  aggregate (c4) 
FROM table_name
GROUP BY CUBE (c1, c2, c3);

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       |Governmentf   |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|

Compter les formes de gouvernement et les région dans les Amériques

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

Compter les formes de gouvernement et les région dans les Amériques

region         |governmentform                              |cnt|max |
---------------+--------------------------------------------+---+----+
Caribbean      |                                            | 24|78.9|
Caribbean      |Constitutional Monarchy                     |  8|75.2|
Caribbean      |Dependent Territory of the UK               |  5|78.9|
Caribbean      |Republic                                    |  4|73.4|
...            |                                            |   |    |
               |Republic                                    | 19|75.8|
               |Constitutional Monarchy                     |  9|75.2|
               |Dependent Territory of the UK               |  7|78.9|
               |Federal Republic                            |  5|77.1|
               |Overseas Department of France               |  3|78.3|
...

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égats éventuels)
WHERE : condition de sélection indépendante du GROUP BY
HAVING : condition de sélection portant sur les groupes

Avis

Toutes les requêtes SQL contiennent une combinaison de ces clauses. Retenez au moins cela!

SELECT        -- columns to display
FROM          -- table(s) to pull from
WHERE         -- filter
GROUP BY      -- split rows into groups
HAVING        -- filter within groups
ORDER BY      -- sort

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

Rassembler les données mentionnées dans la clause FROM
Filter les lignes selon la clause WHERE
Regrouper les lignes selon la clause GROUP BY
Filtrer les groupes selon la clause HAVING
Specifier les colonnes du résultat selon la clause SELECT
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

BD IV: SQL III

Fonctions d’agrégation en SQL

Limite de l’algèbre relationnelle pure

Extension de l’algèbre relationnelle

Fonctions d’agrégation en SQL

Usages

Dans la partie SELECT

Fonctions d’agrégation : SUM et AVG

Fonctions MAX et MIN

Fonctions de calcul : MAX, MIN

Fonction COUNT

Fonctions de calcul : COUNT

Retour sur fonction SUM

Fonction de calcul SUM()

Sommation sur tableau vide

Autre approche avec CASE WHEN ...THEN ... ELSE ...

Calculer les populations vivant sous diﬀérents régimes

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

Pour en savoir plus ?

Fonctions de calcul : exemples

Combinaison de quelques opérations et fonctions

Musée des horreurs

Fonctions de calcul : exemples (suite)

Partitions, GROUP BY

Partition de résultats de requêtes

Partition de résultats de requêtes

Partition de résultats de requêtes

Exemple

Partition de résultats de requêtes

Partition de résultats de requêtes

Operation GROUP BY et calcul sur les tables

Tri des résultats

Présentation/tri de résultats

Regroupement, tri, etc : exemple

Retour au musée des horreurs

Fonctions fenêtres (Window functions)

Exemple

Calculer une fonction fenêtre sans invoquer OVER (...)

Variations

Sommes cumulées par groupes selon un ordre

Exemple

Avec WITH ... et les CTEs

Exemple

Exemple (suite)

Exemple

Groupements avancés

ROLLUP

GROUPING SETS

GROUPING SETS (suite)

CUBE

Compter les formes de gouvernement et les région dans les Amériques

Compter les formes de gouvernement et les région dans les Amériques

Résumé

Portrait robot d’une requête

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

Références

Fin

Dans la partie `SELECT`

Fonctions d’agrégation : `SUM` et `AVG`

Fonctions de calcul : `MAX`, `MIN`

Fonctions de calcul : `COUNT`

Retour sur fonction `SUM`

Fonction de calcul `SUM()`

Autre approche avec `CASE WHEN ...THEN ... ELSE ...`

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

Partitions, `GROUP BY`

Operation `GROUP BY` et calcul sur les tables

Calculer une fonction fenêtre sans invoquer `OVER (...)`

Avec `WITH ...` et les CTEs

`ROLLUP`

`GROUPING SETS` (suite)

`CUBE`