- Mécanisme miroir de l'agrégation :
- Avec l'agrégation on produit des résultats calculés par groupes préétablis via
GROUP BY
- Avec le fenêtrage on produit des résultats pour chaque ligne avec des calculs réalisés sur des groupes contextuels
- Parmi les fonctions dédiées (fonctions analytiques), on retrouve les fonctions d'agrégations et bien d'autres :
SUM, AVG, COUNT, MIN, MAX, RANK, DENSE_RANK, ROW_NUMBER, LAG, LEAD, ...
- Le mécanisme est utilisé dans la clause
SELECT (comme une sélection de colonne)
- Syntaxe générale :
<fonction analytique> OVER ( [PARTITION BY <colonnes>] [ORDER BY <colonnes>] [ROWS | RANGE <fenêtre>] )
- La clause
PARTITION BY définit les sous-groupes considérés pour chaque ligne (comme un GROUP BY local)
- La clause
ORDER BY permet de trier l'ordre des lignes dans chaque partition produite
- La clause
ORDER BY affecte le fonctionnement de la fonction qui devient cumulative et pas globale
- La clause
ROWS/RANGE définit la fenêtre de lignes voisines considérées dans le calcul
- Exemple élémentaire : affichage pour chaque département de la population totale de sa région
SELECT col_nom, col_region,
SUM(col_population) OVER (PARTITION BY col_region)
FROM collectivite;
+--------------------+----------+-------+
|col_nom |col_region|sum |
+--------------------+----------+-------+
|Haut-Rhin |Alsace |1843053|
|Bas-Rhin |Alsace |1843053|
|Landes |Aquitaine |3206137|
|Pyrénées-Atlantiques|Aquitaine |3206137|
|Lot-et-Garonne |Aquitaine |3206137|
|Dordogne |Aquitaine |3206137|
|Gironde |Aquitaine |3206137|
|Cantal |Auvergne |1343964|
...
+--------------------+----------|-------+
- conserve autant de lignes que de départements
- La fonction analytique (ici
SUM) s'applique sur chaque ligne à la totalité de la partition
- Exemple avec tri sur les lignes de chaque partition (ici les noms de départements)
SELECT col_nom, col_region,
SUM(col_population) OVER (PARTITION BY col_region ORDER BY col_nom)
FROM collectivite;
+--------------------+----------+-------+
|col_nom |col_region|sum |
+--------------------+----------+-------+
|Bas-Rhin |Alsace |1094439|
|Haut-Rhin |Alsace |1843053|
|Dordogne |Aquitaine |412082 |
|Gironde |Aquitaine |1846743|
|Landes |Aquitaine |2226084|
|Lot-et-Garonne |Aquitaine |2555781|
|Pyrénées-Atlantiques|Aquitaine |3206137|
|Allier |Auvergne |343046 |
|Cantal |Auvergne |491426 |
...
+--------------------+----------|-------+
- Cette fois, la fonction analytique s'applique progressivement sur les lignes de la partition
- La fenêtre ciblée correspond à
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (détails diapo suivante)
- Exemple avec deux fenêtrages :
SELECT col_nom, col_region, col_superficie,
AVG(col.col_superficie) OVER (PARTITION BY col_region) AS supMoyByReg,
col_population,
AVG(col_population) OVER (PARTITION BY col_region) AS popMoyByReg,
(SELECT avg(col_population) FROM collectivite)
FROM collectivite col
WHERE col_region LIKE '%Normandie';
+--------------+---------------+--------------+-----------+--------------+-----------+---------+
|col_nom |col_region |col_superficie|supmoybyreg|col_population|popmoybyreg|avg |
+--------------+---------------+--------------+-----------+--------------+-----------+---------+
|Calvados |Basse-Normandie|5548 |5863 |680908 |490293.33 |626826.68|
|Manche |Basse-Normandie|5938 |5863 |497762 |490293.33 |626826.68|
|Orne |Basse-Normandie|6103 |5863 |292210 |490293.33 |626826.68|
|Eure |Haute-Normandie|6040 |6159 |582822 |877782.5 |626826.68|
|Seine-maritime|Haute-Normandie|6278 |6159 |1172743 |877782.5 |626826.68|
+--------------+---------------+--------------+-----------+--------------+-----------+---------+
