BD IV: SQL III

---
name: layout-general
layout: true
class: left, middle

.remark-slide-number .progress-bar-container {
  position: absolute;
  bottom: 0;
  height: 4px;
  display: block;
  left: 0;
  right: 0;
}

.remark-slide-number .progress-bar {
  height: 100%;
  background-color: red;
}
</style>

<div>
<style type="text/css">.xaringan-extra-logo {
width: 110px;
height: 128px;
z-index: 0;
background-image: url(./img/UniversiteParisCite_logo_horizontal_couleur_RVB.jpeg);
background-size: contain;
background-repeat: no-repeat;
position: absolute;
top:1em;right:1em;
}
</style>
<script>(function () {
  let tries = 0
  function addLogo () {
    if (typeof slideshow === 'undefined') {
      tries += 1
      if (tries < 10) {
        setTimeout(addLogo, 100)
      }
    } else {
      document.querySelectorAll('.remark-slide-content:not(.hide_logo)')
        .forEach(function (slide) {
          const logo = document.createElement('a')
          logo.classList = 'xaringan-extra-logo'
          logo.href = 'http://master.math.univ-paris-diderot.fr/annee/m1-mi/'
          slide.appendChild(logo)
        })
    }
  }
  document.addEventListener('DOMContentLoaded', addLogo)
})()</script>
</div>

---

# Bases de Données : SQL (III), Aggrégation, partition

### 2023-09-29

#### [Licence MIASHS et Mathématiques]()

#### [Bases de Données](http://stephane-v-boucheron.fr/courses/bdd/)

#### [Équipe BD](http://stephane-v-boucheron.fr)

---
template: inter-slide

### Plan  <svg aria-hidden="true" role="img" viewBox="0 0 576 512" style="height:1em;width:1.12em;vertical-align:-0.125em;margin-left:auto;margin-right:auto;font-size:inherit;fill:currentColor;overflow:visible;position:relative;"><path d="M565.6 36.2C572.1 40.7 576 48.1 576 56V392c0 10-6.2 18.9-15.5 22.4l-168 64c-5.2 2-10.9 2.1-16.1 .3L192.5 417.5l-160 61c-7.4 2.8-15.7 1.8-22.2-2.7S0 463.9 0 456V120c0-10 6.1-18.9 15.5-22.4l168-64c5.2-2 10.9-2.1 16.1-.3L383.5 94.5l160-61c7.4-2.8 15.7-1.8 22.2 2.7zM48 136.5V421.2l120-45.7V90.8L48 136.5zM360 422.7V137.3l-144-48V374.7l144 48zm48-1.5l120-45.7V90.8L408 136.5V421.2z"/></svg>

- [Fonctions d'agrégation en SQL](#fonctionsdagregation)

- [Partitions,  `GROUP BY`](#partitionsgroupby)

- [Tri des résultats](#tridesresultats)

- [Fonctions fenêtres](#windowfunctions)

- [Résumé](#resume)

---
name: fonctionsdagregation
template: inter-slide

## Fonctions d'agrégation en SQL

---

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 ?

---

### <svg aria-hidden="true" role="img" viewBox="0 0 512 512" style="height:1em;width:1em;vertical-align:-0.125em;margin-left:auto;margin-right:auto;font-size:inherit;fill:currentColor;overflow:visible;position:relative;"><path d="M128 32h32c17.7 0 32 14.3 32 32V96H96V64c0-17.7 14.3-32 32-32zm64 96V448c0 17.7-14.3 32-32 32H32c-17.7 0-32-14.3-32-32V388.9c0-34.6 9.4-68.6 27.2-98.3C40.9 267.8 49.7 242.4 53 216L60.5 156c2-16 15.6-28 31.8-28H192zm227.8 0c16.1 0 29.8 12 31.8 28L459 216c3.3 26.4 12.1 51.8 25.8 74.6c17.8 29.7 27.2 63.7 27.2 98.3V448c0 17.7-14.3 32-32 32H352c-17.7 0-32-14.3-32-32V128h99.8zM320 64c0-17.7 14.3-32 32-32h32c17.7 0 32 14.3 32 32V96H320V64zm-32 64V288H224V128h64z"/></svg> 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")

- Possibilité de *partitionner* les données

### Usages

- [Analyse commerciale ou Buisiness Analytics](https://fr.wikipedia.org/wiki/Business_analytics),

- Statistique et Data Science

---

### Dans la partie `SELECT`

- Somme des valeurs prises par `nomattribut`

```psql
SUM (DISTINCT|ALL <nomattribut>)  
```

--
 
- Moyenne des valeurs prises par `nomattribut`

```psql
AVG (DISTINCT|ALL <nom_attribut>) 
```

---

### Fonctions d'agrégation  : `SUM` et `AVG`
  
- Le nombre de pièces 'x21' livrées.

```psql
SELECT SUM (ALL Quantite)  
FROM Livraison 
WHERE NomP = 'x21';
```

- Prix moyen des pièces livrées par la socié  `FastDelivery`

```psql
SELECT AVG (ALL Prix)    
FROM Livraison L, Piece P 
WHERE L.NomP=P.NomP AND L.NomF = 'FastDelivery';
```

]

---
name: fonctionsmaxmin
template: inter-slide

## Fonctions MAX et MIN

---

### Fonctions de calcul  : `MAX`, `MIN`

```psql
MAX (<nomattribut>)  
```

maximum des valeurs prises par `<nomattribut>`

```psql
MIN (<nomattribut>)  
```

minimum des valeurs prises par  `<nomattribut>`

???

Attention aux valeurs nulles !

Quantité maximale livrée de pièce 'x21' lors d'une commande.

```psql
SELECT MAX (Quantite)   
FROM Livraison 
WHERE NomP = 'x21';
```

]
---
name: fonctioncount
template: inter-slide

## Fonction COUNT

---

### Fonctions de calcul : `COUNT`

On peut aussi compter le nombre de tuples dans un résultat

```psql
COUNT(* |[ ALL | DISTINCT <nomattribut>])  
```

Nombre de valeurs prises par le résultat

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

Nombre de livraisons de la pièce 'x21

```psql
SELECT COUNT(*)  
FROM Livraison
WHERE NomP = 'x21'; 
```

]
---

### Fonctions de calcul  : exemples

Exemple de combinaison de quelques opérations et fonctions

Nom des fournisseurs ayant effectué plus de `\(10\)` livraisons.

```psql
SELECT F.NomF 
FROM Fournisseur F 
WHERE (SELECT Count (*)     
          FROM Livraison L     
          WHERE F.NomF = L.NomF) >= 10;  
```

]
---

### Musée des horreurs

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

]

.f6.fr[

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

]

---

### Fonctions de calcul  : exemples

Utilisation d'opérateur arithmétique entre les différents attributs d'un même tuple...

Prix de revient des commandes livrées par le fournisseur `FD`

```psql
SELECT L.NomP, P.Prix*L.Quantite 
FROM Livraison F, Piece P 
WHERE P.NomP=L.NomP AND L.NomF='FD';
```

---
name: partitionsgroupby
template: inter-slide

## Partitions,  `GROUP BY`

---

### Partition de résultats de requêtes

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

```psql
SELECT .....  
FROM Livraison 
GROUP BY NomF    
```

| NomF | NomP | NumLiv |  Quantite |  DateLiv |
|:-----|:-----|:------:|----------:|:--------:|
| <td colspan="5"> Sous-relation 1 </td>  |
|    1 | x21  | 1      |  20       | 15/02/10 |
|  1   | a32  | 2      | 3         | 16/02/10 | 
|   1 | x21   | 3      | 5         | 15/01/10 | 
| <td colspan="5"> Sous-relation 2 </td>  |
| 2 | b27 | 4 | 8 | 01/02/12|
|2 | c31 | 5 | 9 | 17/09/11 |
| <td colspan="5"> Sous-relation 3 </td>  |
|3 |x 21 |6 |17 |15/03/10 |

---

### Partition de résultats de requêtes

Quantité maximale livrée par chaque fournisseur

```psql
SELECT NomF, MAX(Quantite) 
FROM Livraison 
GROUP BY NomF;
```

Livraison

|NomF |  NomP | NumL | Quantite | DateLiv |
|:-----|:-----|:--------:|-----:|:----------:|
|1     | x21 | 1 | 20 | 15/02/10  |
|1-2   | c31 | 5 | 9   | 17/09/11 | 
|1     | x21 | 6 | 17 | 15/03/10  |

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

```psql
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;
```
]

.f6.fr[

|**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 ?

```psql
SELECT NomP, COUNT(*), SUM(Quantite) 
FROM Livraison 
GROUP BY NomP;
```

---

Cette requête n'est pas correcte <svg aria-hidden="true" role="img" viewBox="0 0 512 512" style="height:1em;width:1em;vertical-align:-0.125em;margin-left:auto;margin-right:auto;font-size:inherit;fill:currentColor;overflow:visible;position:relative;"><path d="M416 398.9c58.5-41.1 96-104.1 96-174.9C512 100.3 397.4 0 256 0S0 100.3 0 224c0 70.7 37.5 133.8 96 174.9c0 .4 0 .7 0 1.1v64c0 26.5 21.5 48 48 48h48V464c0-8.8 7.2-16 16-16s16 7.2 16 16v48h64V464c0-8.8 7.2-16 16-16s16 7.2 16 16v48h48c26.5 0 48-21.5 48-48V400c0-.4 0-.7 0-1.1zM96 256a64 64 0 1 1 128 0A64 64 0 1 1 96 256zm256-64a64 64 0 1 1 0 128 64 64 0 1 1 0-128z"/></svg>

```psql
SELECT NomF, NomP, MAX(Quantite) 
FROM Livraison 
GROUP BY NomF;
```

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 fournisseurs qui ont effectué plus de 3 livraisons.

```psql
SELECT NomF 
FROM Livraison 
GROUP BY NomF    
HAVING COUNT(*) >= 3;  
```

---
template: inter-slide
name: tridesresultats

## 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 salariés triée par ordre décroissant salaire

```psql
SELECT * 
FROM Employe 
ORDER BY Salaire DESC;  
```

---

### Regroupement, tri, etc : exemple

Que fait cette requête ?

```psql
SELECT Fonction, COUNT(*) 
FROM Employe 
WHERE NumDep IN (
  SELECT NumDep 
  FROM Departement 
  WHERE NomDept='Financier') 
GROUP BY Fonction 
HAVING AVG(Salaire) >= 10000 
ORDER BY Fonction;
```

Sur les relations d'une base `employé` (tables `Departement`,`Employe`, ...)

---

### Retour au musée des horreurs

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

]

.f6.fr[

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

]

---

### Exemple d'aggrégation

Dans le schéma `world`,

---
template: inter-slide
name: windowfunctions

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

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.

```psql
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 ;
```
]

.fr.f6[

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

.f6[
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 `region, min, max`

Enfin, 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. 
]

.fr.f6[

]

---

### Variations

```psql
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 ;
```
]

.f6.fr[

|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

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

.f6.fr[

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

]

???

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.

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

Cela signifie qu'il est valable 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

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

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

.fl.w-50.pa2.f6[

```psql
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);
```
]

.fl.w-50.pa2.f6[

```psql
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)    
;
```
]

---

### Exemple  : Afficher le pays le plus peuplé (suite)

.fl.w-50.pa2.f6[

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

```
]

.fl.w-50.pa2.f6[

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

---

### Exemple  : Afficher les 10 pays les plus peuplés

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

---
template: inter-slide

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

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

.f6.fr[

|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

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

???

---

### `GROUPING SETS`

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

.f6.fr[

]

???

---

### `CUBE`

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

---
exclude: true

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

.f6.fr[
```
|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

```psql
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;
```
]

.f6[
```  
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|
...
```
]

---
name: resume
template: inter-slide

## Résumé

---

### Portrait robot d'une requête

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

???

“All SQL queries will contain some combination of these clauses. If you remember nothing else, remember this!

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

Note: the -- is a comment in SQL, meaning the text after it is just for documentation sake

Note: the SELECT is required and all other clauses are optional”

---

### 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](https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-select/)

[Tutoriel `joins`](https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-joins/)

[Documentation requêtes](https://www.postgresql.org/docs/current/queries.html)

[Documentation `SELECT`](https://www.postgresql.org/docs/14/sql-select.html)

[GROUPING SETS, ROLLUP, CUBE](https://www.postgresql.org/docs/14/queries-table-expressions.html#QUERIES-GROUPING-SETS)

---

background-image: url('./img/pexels-cottonbro-3171837.jpg')
background-size: cover

# The End