I. Les fonctions OLAP et la clause WINDOW de fenêtrage▲
On peut symboliser cette vision des principes du fenêtrage par l'image suivante :
figure 1 : fenêtres de données
La partie en jaune figure l'ensemble des résultats d'une requête. Au sein de ces résultats, on a taillé deux fenêtres sur lesquelles portent certains calculs. La fenêtre verte incorpore elle-même des sous-fenêtres. La fenêtre orange prend en compte la totalité des données.
NOTA : certains auteurs les appellent « fonctions analytiques », ce qui est faux, car seule une partie de ces fonctions est réellement classable dans cette catégorie. D'autres les appellent fonctions OLAP, ce qui est aussi faux, car la norme considère une classe particulière de fonctions OLAP utilisant les agrégats avec une clause de groupage WITH CUBE ou WITH ROLLUP.
II. Catégories de fonctions fenêtrées▲
Il existe deux catégories de fonctions OLAP.
La première est appelée fonctions analytiques et concerne des fonctions bien connues de SQL, les fonctions d'agrégation telles que SUM, COUNT, MAX, MIN, AVG ou encore EVERY, ANY (ou SOME), STDDEV_POP, STDDEV_SAMP, VAR_POP, VAR_SAMP, PERCENTILE_CONT, PERCENTILE_DISC (voir annexe A).
Ces fonctions sont purement relationnelles, dans le sens où elles obéissent à une logique ensembliste. Elles ont besoin d'opérer des calculs directement sur les données de la requête.
La seconde est appelée fonctions de classement et permet d'enrichir un résultat par un classement reposant sur un ordre défini dans l'application de la fonction. Ce sont les fonctions RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST, ROW_NUMBER, NTILE, LEAD, LAG, FIRST_VALUE, LAST_VALUE, NTH_VALUE (voir annexe B).
Ces fonctions ne sont pas relationnelles, car elles supposent l'application d'un ordre dans leur traitement ce qui est contraire au principe ensembliste des bases de données relationnelles.
Ceci a deux conséquences : la difficulté de faire un traitement en parallèle et l'indéterminisme, c'est-à-dire la possibilité qu'une même exécution de la requête avec le même jeu de données donne des résultats différents…
Notez aussi que, contrairement aux fonctions analytiques, elles n'effectuent aucun calcul sur les données de la requête. Elles se contentent de comparer des données pour effectuer un classement.
III. Syntaxe des appels aux fonctions fenêtrées▲
Comme la syntaxe globale de ce type de fonction est assez complexe, nous allons la présenter en deux temps…
III-A. Syntaxe simplifiée d'un appel à une fonction fenêtrée▲
La syntaxe simplifiée d'un appel à une fonction fenêtrée est la suivante :
<fonction_fenêtrée> OVER <fenêtre_de_données>
<fenêtre_de_données> ::=
OVER ( [ PARTITION BY ( <liste_expression_groupage> ) ]
[ ORDER BY <liste_expression_ordonancement> ] )
PARTITION BY : permet d'organiser le jeu de résultats en groupes logiques, d'après les différentes valeurs de l'expression spécifiée. Cela correspond à peu de choses près à la clause GROUP BY lorsque vous utilisez des agrégats directement dans une requête.
En cas d'omission de cette clause, l'intégralité du jeu de résultats est considérée comme une partition.
ORDER BY : définit le tri des lignes dans chaque partition d'un jeu de résultats. Les mots clefs ASC et DESC sont utilisables pour définir le sens du tri, ainsi qu'une clause COLLATE dans le cas des littéraux. Enfin la précision NULL FIRT ou NULL LAST peut être apportée pour situer le classement des NULL en début ou en fin de résultat.
Pour certains cas d'utilisation des fonctions, cette clause doit être omise (en particulier pour les fonctions d'agrégation).
NOTA : le contenu de OVER peut être vide (par exemple pour avoir en ligne le maximum absolu).
Exemple 1 (voir l'annexe E pour les jeux d'essais) - agrégat simple
À partir des données de la table des films (T_FILM_FLM) nous aimerions, en sus de toutes les données, faire figurer le nombre de films que chaque metteur en scène a réalisés.
Pour cela nous devons rajouter un comptage avec comme niveau de rupture le nom du réalisateur. Ceci se fait à l'aide de la fonction COUNT(*) appliquée sur (OVER) la partition (PARTITION BY) FLM_REALISATEUR (colonne), comme ceci :
SELECT *,
COUNT(*) OVER(PARTITION BY FLM_REALISATEUR) AS NB_FILM_REAL
FROM T_FILM_FLM
ORDER BY FLM_REALISATEURFLM_ID FLM_TITRE FLM_REALISATEUR FLM_DUREE_MN FLM_ANNEE FLM_GENRE NB_FILM_REAL
------- ---------------------------------- ------------------ ------------ --------- ---------------- ------------
8 La mort aux trousses Alfred Hitchcock 136 1959 Policier 4
15 Fenêtre sur cour Alfred Hitchcock 112 1954 Policier 4
19 Les oiseaux Alfred Hitchcock 130 1963 Fantastique 4
23 Psychose Alfred Hitchcock 109 1960 Fantastique 4
24 Boulevard du crépuscule Billy Wilder 110 1950 Drame 5
21 Assurance sur la mort Billy Wilder 107 1944 Policier 5
27 Témoin à charge Billy Wilder 116 1958 Policier 5
30 La vie privée de sherlock holmes Billy Wilder 125 1970 Policier 5
37 Certains l'aiment chaud Billy Wilder 120 1959 Comédie 5
35 La panthère rose Blake Edwards 113 1963 Comédie 4
28 Diamants sur canapé Blake Edwards 115 1961 Comédie 4
14 Victor, Victoria Blake Edwards 132 1982 Comédie musicale 4
10 La party Blake Edwards 99 1968 Comédie musicale 4
2 L'arnaque George Roy Hill 129 1973 Policier 5
29 Bucth Cassidy et le kid George Roy Hill 110 1969 Western 5
32 Abattoir 5 George Roy Hill 104 1972 Science fiction 5
34 La kermesse des aigles George Roy Hill 107 1975 Historique 5
38 Le monde selon Garp George Roy Hill 136 1983 Science fiction 5
39 Eve Joseph Mankiewicz 130 1950 Drame 4
3 Le limier Joseph Mankiewicz 138 1972 Policier 4
6 Jules Cesar Joseph Mankiewicz 120 1953 Histoire 4
13 L'aventure de madame Muir Joseph Mankiewicz 104 1947 Fantastique 4
16 La soif du mal Orson Welles 108 1958 Policier 4
12 Le procès Orson Welles 130 1962 4
9 La dame de Shangai Orson Welles 90 1946 Policier 4
4 Citizen Kane Orson Welles 115 1941 Biographie 4
5 Le coup de l'escalier Robert Wise 95 1959 Policier 7
7 Le mystère androméde Robert Wise 115 1971 Science fiction 7
1 West Side Story Robert Wise 145 1961 Comédie musicale 7
11 Audrey Rose Robert Wise 113 1977 Fantastique 7
18 Star Trek Robert Wise 129 1979 Science fiction 7
25 La mélodie du bonheur Robert Wise 166 1965 Comédie musicale 7
36 La maison du diable Robert Wise 112 1963 Fantastique 7
40 Dr. Folamour Stanley Kubrick 93 1963 Science fiction 7
26 Shinnig Stanley Kubrick 120 1980 Fantastique 7
22 Orange mécanique Stanley Kubrick 136 1971 Drame 7
20 Barry Lyndon Stanley Kubrick 187 1975 Biographie 7
33 2001 l'odyssée de l'espace Stanley Kubrick 141 1968 Science fiction 7
31 Lolita Stanley Kubrick 153 1962 Drame 7
17 Les sentiers de la gloire Stanley Kubrick 88 1957 Histoire 7
Notez combien le résultat de cette colonne est répétitif : il figure autant de fois qu'il y a de lignes portant le nom du réalisateur.
Si nous devions représenter symboliquement le fonctionnement de cette requête, l'image serait alors la suivante :
figure 2 : fenêtres avec partition
Si nous n'avions pas de fonction fenêtrée, cette requête s'écrirait :
SELECT *,
(SELECT COUNT(*)
FROM T_FILM_FLM AS Tin
WHERE Tin.FLM_REALISATEUR
= Tout.FLM_REALISATEUR) AS NOMBRE_FILM_DU_REALISATEUR
FROM T_FILM_FLM AS Tout
ORDER BY FLM_REALISATEUR
À ce stade il est intéressant de constater que les plans de requêtes de ces deux requêtes montrent que l'avantage est à celle utilisant la fonction fenêtrée :
figure 3 : plan de requêtes dans Microsoft SQL Server
Cette dernière consommant 50 % de ressources supplémentaires !
Exemple 2 (voir l'annexe E pour les jeux d'essais) - agrégats simples
Nous voulons maintenant connaître l'année du plus vieux film et l'année du film le plus récent pour chaque réalisateur, ainsi que le classement chronologique des films pour le réalisateur et globalement (quels que soient les réalisateurs).
Pour connaître l'année du plus vieux film du réalisateur, il faut calculer le minimum (MIN) de l'année (colonne FLM_ANNEE) appliqué sur (OVER) la partition (PARTITION BY), c'est-à-dire par groupe sur le réalisateur (colonne FLM_REALISATEUR).
Pour l'année du film le plus récent par réalisateur, il suffit d'utiliser la fonction d'agrégation MAX avec les mêmes éléments.
Pour donner le classement absolu par ancienneté des films, il suffit d'invoquer l'une des fonctions de classement (RANK, DENSE_RANK, PERCENT_RANK) appliquée sur (OVER) le tri (ORDER BY) effectué sur la colonne FLM_ANNEE.
Pour donner le classement par ancienneté des films, relatif au réalisateur, il suffit d'invoquer l'une des fonctions de classement (RANK, DENSE_RANK, PERCENT_RANK) appliquée sur (OVER) la partition ou sous-groupe (PARTITION BY) constitué par la colonne FM_REALISATEUR et le tri (ORDER BY) effectué sur la colonne FLM_ANNEE.
Tout ceci se résume dans la requête suivante :
SELECT FLM_TITRE, FLM_REALISATEUR,
MIN(FLM_ANNEE) OVER(PARTITION BY FLM_REALISATEUR)
AS AN_PLUS_VIEUX_FILM_REAL,
MAX(FLM_ANNEE) OVER(PARTITION BY FLM_REALISATEUR)
AS AN_PLUS_RECENT_FILM_REAL,
RANK() OVER(ORDER BY FLM_ANNEE)
AS CHRONO_FILM,
RANK() OVER(PARTITION BY FLM_REALISATEUR ORDER BY FLM_ANNEE)
AS CHRONO_FILM_REAL
FROM T_FILM_FLM
ORDER BY FLM_REALISATEURFLM_TITRE FLM_REALISATEUR AN_PLUS_VIEUX AN_PLUS_RECENT CHRONO CHRONO
_FILM_REAL _FILM_REAL _FILM _FILM_REAL
---------------------------------- ------------------- -------------- --------------- ------- ----------
Fenêtre sur cour Alfred Hitchcock 1954 1963 8 1
La mort aux trousses Alfred Hitchcock 1954 1963 12 2
Psychose Alfred Hitchcock 1954 1963 15 3
Les oiseaux Alfred Hitchcock 1954 1963 20 4
Assurance sur la mort Billy Wilder 1944 1970 2 1
Boulevard du crépuscule Billy Wilder 1944 1970 5 2
Témoin à charge Billy Wilder 1944 1970 10 3
Certains l'aiment chaud Billy Wilder 1944 1970 12 4
La vie privée de sherlock holmes Billy Wilder 1944 1970 28 5
Diamants sur canapé Blake Edwards 1961 1982 16 1
La panthère rose Blake Edwards 1961 1982 20 2
La party Blake Edwards 1961 1982 25 3
Victor, Victoria Blake Edwards 1961 1982 39 4
Bucth Cassidy et le kid George Roy Hill 1969 1983 27 1
Abattoir 5 George Roy Hill 1969 1983 31 2
L'arnaque George Roy Hill 1969 1983 33 3
La kermesse des aigles George Roy Hill 1969 1983 34 4
Le monde selon Garp George Roy Hill 1969 1983 40 5
L'aventure de madame Muir Joseph Mankiewicz 1947 1972 4 1
Eve Joseph Mankiewicz 1947 1972 5 2
Jules Cesar Joseph Mankiewicz 1947 1972 7 3
Le limier Joseph Mankiewicz 1947 1972 31 4
Citizen Kane Orson Welles 1941 1962 1 1
La dame de Shangai Orson Welles 1941 1962 3 2
La soif du mal Orson Welles 1941 1962 10 3
Le procès Orson Welles 1941 1962 18 4
Le coup de l'escalier Robert Wise 1959 1979 12 1
West Side Story Robert Wise 1959 1979 16 2
La maison du diable Robert Wise 1959 1979 20 3
La mélodie du bonheur Robert Wise 1959 1979 24 4
Le mystère androméde Robert Wise 1959 1979 29 5
Audrey Rose Robert Wise 1959 1979 36 6
Star Trek Robert Wise 1959 1979 37 7
Les sentiers de la gloire Stanley Kubrick 1957 1980 9 1
Lolita Stanley Kubrick 1957 1980 18 2
Dr. Folamour Stanley Kubrick 1957 1980 20 3
2001 l'odyssée de l'espace Stanley Kubrick 1957 1980 25 4
Orange mécanique Stanley Kubrick 1957 1980 29 5
Barry Lyndon Stanley Kubrick 1957 1980 34 6
Shinnig Stanley Kubrick 1957 1980 38 7
Notez que dans les résultats de classement absolu par ancienneté des films, certains ayant été produits la même année sont ex aequo (par exemple « Les oiseaux », « La panthère rose », « La maison du diable », « Dr. Folamour » : classés 20e) ce qui fait qu'au rang suivant on passe à 24. Il n'y a donc, ni 21e, ni 22e, ni 23e film(s). On aurait pu éviter ces « trous » en utilisant la fonction DENSE_RANK.
Bien entendu il est possible de se passer des fonctions fenêtrées. Pour les fonctions d'agrégation, cela se fait en général par une sous-requête corrélée présente dans la clause SELECT. Pour les fonctions de classement, les choses sont plus complexes. Il faut en général faire appel à un comptage sur une inégalité.
SELECT FLM_TITRE, FLM_REALISATEUR,
(SELECT MIN(FLM_ANNEE)
FROM T_FILM_FLM AS Tin
WHERE Tout.FLM_REALISATEUR = Tin.FLM_REALISATEUR),
(SELECT MAX(FLM_ANNEE)
FROM T_FILM_FLM AS Tin
WHERE Tout.FLM_REALISATEUR = Tin.FLM_REALISATEUR),
(SELECT COUNT(*)
FROM T_FILM_FLM AS Tin
WHERE Tout.FLM_ANNEE > Tin.FLM_ANNEE) + 1,
(SELECT COUNT(*)
FROM T_FILM_FLM AS Tin
WHERE Tout.FLM_ANNEE > Tin.FLM_ANNEE
AND Tin.FLM_REALISATEUR = Tout.FLM_REALISATEUR) + 1
FROM T_FILM_FLM AS Tout
ORDER BY FLM_REALISATEURExemple 3 (voir l'annexe E pour les jeux d'essais) - agrégat d'agrégat
On peut aussi à l'aide des fonctions fenêtrées, réaliser plus facilement un classement d'agrégat, ou encore un agrégat d'agrégats. Par exemple nous pouvons obtenir par réalisateur la somme des durées de leurs films (durée totale des films), mais aussi faire figurer le maximum de cette somme, autrement dit la durée la plus longue si l'on visionnait les films d'un réalisateur mis bout à bout :
SELECT FLM_REALISATEUR, SUM(FLM_DUREE_MN) AS DUREE_TOTALE,
RANK() OVER(ORDER BY SUM(FLM_DUREE_MN)) AS RANG_DUREE_TOTALE,
MAX(SUM(FLM_DUREE_MN)) OVER() AS DUREE_TOTALE_MAX
FROM T_FILM_FLM
GROUP BY FLM_REALISATEUR
ORDER BY FLM_REALISATEURFLM_REALISATEUR DUREE_TOTALE RANG_DUREE_TOTALE DUREE_TOTALE_MAX
---------------------- ------------ -------------------- ----------------
Alfred Hitchcock 487 3 918
Billy Wilder 578 5 918
Blake Edwards 459 2 918
George Roy Hill 586 6 918
Joseph Mankiewicz 492 4 918
Orson Welles 443 1 918
Robert Wise 875 7 918
Stanley Kubrick 918 8 918
À nouveau la comparaison des plans de requêtes ne laisse aucun doute sur la requête la plus consommatrice :
comparaisons des plans de requêtes sous PostGreSQL :
figure 4 : plan de requête dans PostGreSQL
avec un cout de 126,75 pour les fonctions fenêtrées
figure 5 : plan de requête dans PostGreSQL
avec un cout de 40 212,54 sans les fonctions fenêtrées.
III-B. Filtrer le résultat d'une fonction fenêtrée▲
Vous ne pouvez pas filtrer directement le résultat d'une fonction fenêtrée.
En effet, le filtre WHERE ne porte que sur les données contenues dans les tables. Or le résultat d'une fonction fenêtrée qu'elle soit un classement ou une agrégation, n'est pas connu dans les données.
Le filtre HAVING ne peut non plus être utilisé, car ces fonctions opèrent après que le résultat de la requête a été produit. En effet, rien n'empêche de faire porter une fonction fenêtrée sur un agrégat.
Le seul moyen de filtrer le résultat d'une fonction fenêtrée est d'utiliser une table dérivée, soit directement dans la requête, soit par le biais d'une CTE ou encore à l'aide d'une vue.
Exemples 4 (voir l'annexe E pour les jeux d'essais) - filtrage d'un agrégat (pagination)
Vous souhaitez paginer les résultats d'une requête afin de présenter quelques lignes de chaque page d'un site web par exemple. Deux méthodes peuvent être envisagées :
- paginer en un nombre fixe de lignes par page, quel que soit le nombre de pages ;
- paginer en un nombre fixe de pages, quel que soit le nombre de lignes par page.
Dans le premier cas, vous devez utiliser la fonction de classement.
Pagination par ligne des films dans l'ordre chronologique du plus récent au plus ancien, avec 5 lignes par page, appel de la page 2 :
SELECT FLM_TITRE, FLM_REALISATEUR, FLM_ANNEE
FROM (SELECT FLM_TITRE, FLM_REALISATEUR, FLM_ANNEE,
ROW_NUMBER() OVER(ORDER BY FLM_ANNEE DESC) - 1 AS N
FROM T_FILM_FLM) AS T
WHERE (N / 5) + 1 = 2
Tout se joue dans le WHERE.
Ce genre de requête gagne à être écrit à l'aide d'une CTE pour la rendre plus lisible :
WITH
T AS (SELECT FLM_TITRE, FLM_REALISATEUR, FLM_ANNEE,
ROW_NUMBER() OVER(ORDER BY FLM_ANNEE DESC) - 1 AS N
FROM T_FILM_FLM)
SELECT FLM_TITRE, FLM_REALISATEUR, FLM_ANNEE
FROM T
WHERE (N / 5) + 1 = 2FLM_TITRE FLM_REALISATEUR FLM_ANNEE
--------------------------- ---------------------- ---------
Barry Lyndon Stanley Kubrick 1975
La kermesse des aigles George Roy Hill 1975
L'arnaque George Roy Hill 1973
Le limier Joseph Mankiewicz 1972
Abattoir 5 George Roy Hill 1972
Pagination par page des films dans l'ordre chronologique du plus récent au plus ancien, avec 5 pages, appel de la page 2 :
SELECT FLM_TITRE, FLM_REALISATEUR, FLM_ANNEE
FROM (SELECT FLM_TITRE, FLM_REALISATEUR, FLM_ANNEE,
NTILE(5) OVER(ORDER BY FLM_ANNEE DESC) AS N
FROM T_FILM_FLM) AS T
WHERE N = 2FLM_TITRE FLM_REALISATEUR FLM_ANNEE
----------------------------------- -------------------- ---------
Le limier Joseph Mankiewicz 1972
Abattoir 5 George Roy Hill 1972
Orange mécanique Stanley Kubrick 1971
Le mystère androméde Robert Wise 1971
La vie privée de sherlock holmes Billy Wilder 1970
Bucth Cassidy et le kid George Roy Hill 1969
2001 l'odyssée de l'espace Stanley Kubrick 1968
La party Blake Edwards 1968
La segmentation en 5 pages se fait dans l'appel de la fonction NTILE et la sélection de la bonne page dans la clause WHERE de la requête externe.
Exemple 5 (voir l'annexe E pour les jeux d'essais) - agrégat d'agrégat, recherche des n premiers (top n)
Voici maintenant comment sélectionner les trois metteurs en scène les plus prolifiques,
- en durée :
SELECT FLM_REALISATEUR, DUREE_TOTALE
FROM (SELECT FLM_REALISATEUR, SUM(FLM_DUREE_MN) AS DUREE_TOTALE,
RANK() OVER(ORDER BY SUM(FLM_DUREE_MN) DESC) AS RANG_DT
FROM T_FILM_FLM
GROUP BY FLM_REALISATEUR) AS T
WHERE RANG_DT <= 3FLM_REALISATEUR DUREE_TOTALE
-------------------------------- ------------
Stanley Kubrick 918
Robert Wise 875
George Roy Hill 586- comme en nombre de films :
SELECT FLM_REALISATEUR, NOMBRE_FILM
FROM (SELECT FLM_REALISATEUR, SUM(FLM_DUREE_MN) AS DUREE_TOTALE,
RANK() OVER(ORDER BY COUNT(*) DESC) AS NOMBRE_FILM
FROM T_FILM_FLM
GROUP BY FLM_REALISATEUR) AS T
WHERE NOMBRE_FILM <= 3FLM_REALISATEUR NOMBRE_FILM
-------------------------------- --------------------
Robert Wise 1
Stanley Kubrick 1
Billy Wilder 3
George Roy Hill 3
Remarquez que pour cette dernière requête, bien que nous attendions un nombre de lignes égal à 3 dans la réponse, la machine nous en renvoie 4, car elle prend en compte les ex aequo. Le résultat est même pire avec la fonction DENSE_RANK :
SELECT FLM_REALISATEUR, NOMBRE_FILM
FROM (SELECT FLM_REALISATEUR, SUM(FLM_DUREE_MN) AS DUREE_TOTALE,
DENSE_RANK() OVER(ORDER BY COUNT(*) DESC) AS NOMBRE_FILM
FROM T_FILM_FLM
GROUP BY FLM_REALISATEUR) AS T
WHERE NOMBRE_FILM <= 3FLM_REALISATEUR NOMBRE_FILM
-------------------------------- --------------------
Robert Wise 1
Stanley Kubrick 1
Billy Wilder 2
George Roy Hill 2
Joseph Mankiewicz 3
Orson Welles 3
Blake Edwards 3
Alfred Hitchcock 3
La tentation est alors grande de se servir de la fonction ROW_NUMBER qui numérote les lignes et nous donne par conséquent l'assurance de ne retourner jamais que trois lignes :
SELECT FLM_REALISATEUR, NOMBRE_FILM
FROM (SELECT FLM_REALISATEUR, SUM(FLM_DUREE_MN) AS DUREE_TOTALE,
ROW_NUMBER() OVER(ORDER BY COUNT(*) DESC) AS NOMBRE_FILM
FROM T_FILM_FLM
GROUP BY FLM_REALISATEUR) AS T
WHERE NOMBRE_FILM <= 3
Mais c'est là que la chose devient anti-relationnelle, car non déterministe. En effet, dans ce cas deux solutions sont possibles :
FLM_REALISATEUR NOMBRE_FILM
-------------------------------- --------------------
Robert Wise 1
Stanley Kubrick 1
Billy Wilder 3
FLM_REALISATEUR NOMBRE_FILM
-------------------------------- --------------------
Robert Wise 1
Stanley Kubrick 1
George Roy Hill 3
Et il est probable que le serveur vous renvoie tantôt l'une, tantôt l'autre. Une catastrophe serait de baser ensuite un traitement sur le résultat d'une telle requête !
Exemple 6 (voir l'annexe E pour les jeux d'essais) - trouver le CA jour le plus important et sa date
SELECT SNC_DATE, CA_JOUR
FROM (SELECT SNC_DATE, SUM(SNC_BILLET * SNC_TARIF) AS CA_JOUR,
RANK() OVER(ORDER BY SUM(SNC_BILLET * SNC_TARIF) DESC)
AS CLASSEMENT
FROM dbo.T_SEANCE_SNC
GROUP BY SNC_DATE) AS T
WHERE CLASSEMENT = 1SNC_DATE CA_JOUR
---------- ------------
2009-01-25 12220.00
Cette requête peut encore s'écrire à l'aide d'une CTE comme suit :
WITH
T AS (SELECT SNC_DATE, SUM(SNC_BILLET * SNC_TARIF) AS CA_JOUR,
RANK() OVER(ORDER BY SUM(SNC_BILLET * SNC_TARIF) DESC)
AS CLASSEMENT
FROM dbo.T_SEANCE_SNC
GROUP BY SNC_DATE)
SELECT SNC_DATE, CA_JOUR
FROM T
WHERE CLASSEMENT = 1
Sans de fonction fenêtrée cette requête s'écrit :
SELECT SNC_DATE, SUM(SNC_BILLET * SNC_TARIF) AS CA_JOUR
FROM dbo.T_SEANCE_SNC
GROUP BY SNC_DATE
HAVING SUM(SNC_BILLET * SNC_TARIF) =
(SELECT MAX(CA_JOUR)
FROM (SELECT SNC_DATE, SUM(SNC_BILLET * SNC_TARIF) AS CA_JOUR
FROM dbo.T_SEANCE_SNC
GROUP BY SNC_DATE) AS T)NOTA : jusqu'ici nous avons vu la syntaxe simplifiée des fonctions fenêtrées. Voyons maintenant une syntaxe étendue avec l'ajout de la clause de fenêtre proprement dite, que nous allons maintenant détailler…
III-C. Syntaxe avancée d'un appel à une fonction fenêtrée▲
Cette syntaxe étendue comporte la clause de fenêtrage proprement dite, introduite par le mot clef ROWS ou RANGE. Cette fenêtre permet de ne pas prendre en compte la totalité des données de la requête pour les fonctions fenêtrées, mais de naviguer dans différents sous-groupes, y compris recouvrant :
<fonction_fenêtrage> OVER <fenêtre_de_données>
<fenêtre_de_données> ::=
OVER ( [ PARTITION BY ( <liste_expression_groupage> ) ]
[ ORDER BY <liste_expression_ordonancement> ]
[ { ROWS| RANGE } <cadre_fenêtre> )
<cadre_fenêtre> ::=
{ BETWEEN <début_cadre> AND <fin_cadre>
| UNBOUNDED PRECEDING
| CURRENT ROW
| n PRECEDING }
[ <cadre_exclusion> ]
<début_cadre> ::=
| UNBOUNDED PRECEDING
| CURRENT ROW
| n { PRECEDING | FOLLOWING }
[ <cadre_exclusion> ]
<fin_cadre> ::=
| UNBOUNDED FOLLOWING
| CURRENT ROW
| n { PRECEDING | FOLLOWING }
<cadre_exclusion> ::=
EXCLUDE CURRENT ROW
| EXCLUDE GROUP
| EXCLUDE TIES
| EXCLUDE NO OTHERS
où n est un entier positif.
ROWS et RANGE permettent d'exprimer la taille de la fenêtre. Cette taille peut correspondre à une ou plusieurs lignes d'une partition, ou à toutes les lignes. Vous pouvez l'exprimer en intervalle de valeurs de données par rapport à la valeur de la ligne courante (RANGE), ou en nombre de lignes par rapport à la ligne courante (ROWS).
nPRECEDING : inclusion des n lignes précédentes.
UNBOUNDED PRECEDING : la première ligne de la fenêtre est la première de la partition.
BETWEEN : permet de définir la première et la dernière ligne de la fenêtre, la ligne courante servant comme élément de référence. Les première et dernière lignes sont exprimées en nombre de lignes précédant et suivant respectivement cette ligne courante.
n FOLLOWING : inclusion des n lignes suivantes.
CURRENT ROW : appelle la ligne courante.
UNBOUNDED FOLLOWING : la dernière ligne de la fenêtre est la dernière de la partition.
EXCLUDE CURRENT ROW : exclut la ligne courante de la fenêtre.
EXCLUDE GROUP : exclut la ligne courante et toutes les lignes identiques à la ligne courante de la fenêtre.
EXCLUDE TIES : exclut toutes les lignes à l'exception de la ligne courante et toutes les lignes identiques à la ligne courante de la fenêtre.
NOTA
- En cas d'omission de la clause PARTITION BY, c'est l'intégralité du jeu de résultats qui est pris en compte.
- Si vous utilisez la clause RANGE, vous devez également inclure une clause ORDER BY. En effet, le calcul permettant de générer la fenêtre exige le tri des valeurs. Par ailleurs, la clause ORDER BY ne peut contenir qu'une expression, celle-ci devant renvoyer une date ou une valeur numérique.
- Lorsque vous avez recours aux clauses ROWS ou RANGE, si vous n'indiquez qu'une ligne de départ, la ligne courante correspond à la dernière ligne de la fenêtre. Si vous ne spécifiez qu'une ligne de fin, la ligne courante sert de ligne de départ.
-
Si aucune clause ROW ou RANGE n'est spécifiée, la fenêtre est déterminée comme suit :
- elle commence à la première ligne de la partition (UNBOUNDED PRECEDING) ;
- elle se termine par la ligne courante (CURRENT ROW) si une clause ORDER BY est spécifiée, sinon par la dernière (UNBOUNDED FOLLOWING).
Voici une représentation sous la forme d'un diagramme de Backus Naur de la syntaxe de cette fenêtre :
figure 6 : diagramme de Backus Naur des mots clefs du fenêtrage
À ce diagramme, vous pouvez rajouter les exclusions, juste avant le AND et à la fin de l'expression de fenêtre.
Exemple 7 (voir l'annexe E pour les jeux d'essais) - somme cumulative avec niveau de rupture
Nous voudrions savoir quel est le nombre de places vendues par cumul au fil de la journée. De la même manière, nous voulons le chiffre d'affaires correspondant. Dans ce cas, il faut faire la somme des places en reprenant pour chaque jour, les places des séances précédentes…
Voici comment on écrit cela :
SELECT SNC_DATE, SNC_HEURE, FLM_TITRE, SNC_BILLET, SNC_TARIF,
SUM(SNC_BILLET)
OVER(PARTITION BY SNC_DATE ORDER BY SNC_HEURE
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS CUMUL_JOUR,
SUM(SNC_BILLET * SNC_TARIF)
OVER(PARTITION BY SNC_DATE ORDER BY SNC_HEURE
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS CA_CUMUL_JOUR
FROM T_SEANCE_SNC AS S
INNER JOIN T_FILM_FLM AS F
ON S.FLM_ID = F.FLM_ID
ORDER BY SNC_DATE, SNC_HEURESNC_DATE SNC_HEURE FLM_TITRE SNC_BILLET SNC_TARIF CUMUL_JOUR CA_CUMUL_JOUR
---------- ----------- ----------------- ---------- ----------- ----------- ---------------
2009-01-01 14:40:00 West Side Story 94 13.00 94 1222.00
2009-01-01 17:20:00 West Side Story 70 13.00 164 2132.00
2009-01-01 20:00:00 West Side Story 184 13.00 348 4524.00
2009-01-01 22:40:00 West Side Story 148 13.00 496 6448.00
2009-01-02 14:40:00 West Side Story 56 13.00 56 728.00
2009-01-02 17:20:00 West Side Story 216 13.00 272 3536.00
2009-01-02 20:00:00 West Side Story 133 13.00 405 5265.00
2009-01-02 22:40:00 West Side Story 48 13.00 453 5889.00
2009-01-03 14:40:00 West Side Story 104 13.00 104 1352.00
2009-01-03 17:20:00 West Side Story 168 13.00 272 3536.00
2009-01-03 20:00:00 West Side Story 250 13.00 522 6786.00
2009-01-03 22:40:00 West Side Story 73 13.00 595 7735.00
2009-01-04 14:40:00 West Side Story 233 13.00 233 3029.00
2009-01-04 17:20:00 West Side Story 152 13.00 385 5005.00
2009-01-04 20:00:00 West Side Story 201 13.00 586 7618.00
2009-01-04 22:40:00 West Side Story 250 13.00 836 10868.00
2009-01-05 15:12:00 Star Trek 218 12.00 218 2616.00
2009-01-05 17:36:00 Star Trek 93 12.00 311 3732.00
2009-01-05 20:00:00 Star Trek 110 12.00 421 5052.00
2009-01-05 22:24:00 Star Trek 34 12.00 455 5460.00
...
On peut symboliser graphiquement cet « emboîtage » de fenêtres comme ceci :
figure 7 : fenêtres emboîtes pour cumuls successifs
Bien entendu, il est possible de réaliser une telle requête sans ces opérateurs et utilisant des sous-requêtes dans la clause SELECT :
SELECT SNC_DATE, SNC_HEURE, FLM_TITRE, SNC_BILLET, SNC_TARIF,
(SELECT SUM(SNC_BILLET)
FROM T_SEANCE_SNC AS Si
WHERE Si.SNC_DATE = S.SNC_DATE
AND Si.SNC_HEURE <= S.SNC_HEURE)
AS CUMUL_JOUR,
(SELECT SUM(SNC_BILLET * SNC_TARIF)
FROM T_SEANCE_SNC AS Si
WHERE Si.SNC_DATE = S.SNC_DATE
AND Si.SNC_HEURE <= S.SNC_HEURE) AS CA_CUMUL_JOUR
FROM T_SEANCE_SNC AS S
INNER JOIN T_FILM_FLM AS F
ON S.FLM_ID = F.FLM_ID
ORDER BY SNC_DATE, SNC_HEUREÀ nouveau, si l'on regarde les plans de requêtes générés ainsi que leur cout, l'avantage revient aux fonctions fenêtrées :
Comparaison des plans de requête sous PostGreSQL
figure 8 : plan de requête PostGreSQL
Avec les fonctions fenêtrées, ce plan coute 179.73
figure 9 : plan de requête PostGreSQL
Sans les fonctions fenêtrées, ce plan coute 92 593.73
Une autre vision de la chose peut être apportée comme suit :
figure 10 : représentation de la notion de cumuls avec rupture
Nous n'avons fait figurer que le cumul du nombre de billets vendus par séance dans la journée pour simplifier notre démonstration.
Exemple 8 (voir l'annexe E pour les jeux d'essais) - cumul avec fenêtre glissante
Voyons maintenant comment on peut obtenir le chiffre d'affaires de notre cinéma par semaine glissante, c'est-à-dire en cumulant sur les 6 jours précédents avec le jour courant :
SELECT SNC_DATE, SNC_HEURE, FLM_TITRE, SNC_BILLET, SNC_TARIF,
SUM(SNC_BILLET * SNC_TARIF)
OVER (ORDER BY SNC_DATE
RANGE BETWEEN 6 PRECEDING
AND CURRENT ROW) AS CA_CUMUL_GLISSANT_SEMAINE
FROM T_SEANCE_SNC AS S
INNER JOIN T_FILM_FLM AS F
ON S.FLM_ID = F.FLM_ID
ORDER BY SNC_DATE, SNC_HEURE CA_CUMUL
_GLISSANT
SNC_DATE SNC_HEURE FLM_TITRE SNC_BILLET SNC_TARIF _SEMAINE
---------- ------------ ---------------------------- ---------- ------------ ----------
2009-01-01 14:40:00 West Side Story 94 13.00 6448.00
2009-01-01 17:20:00 West Side Story 70 13.00 6448.00
2009-01-01 20:00:00 West Side Story 184 13.00 6448.00
2009-01-01 22:40:00 West Side Story 148 13.00 6448.00
2009-01-02 14:40:00 West Side Story 56 13.00 12337.00
2009-01-02 17:20:00 West Side Story 216 13.00 12337.00
2009-01-02 20:00:00 West Side Story 133 13.00 12337.00
2009-01-02 22:40:00 West Side Story 48 13.00 12337.00
2009-01-03 14:40:00 West Side Story 104 13.00 20072.00
2009-01-03 17:20:00 West Side Story 168 13.00 20072.00
2009-01-03 20:00:00 West Side Story 250 13.00 20072.00
2009-01-03 22:40:00 West Side Story 73 13.00 20072.00
2009-01-04 14:40:00 West Side Story 233 13.00 30940.00
2009-01-04 17:20:00 West Side Story 152 13.00 30940.00
2009-01-04 20:00:00 West Side Story 201 13.00 30940.00
2009-01-04 22:40:00 West Side Story 250 13.00 30940.00
2009-01-05 15:12:00 Star Trek 218 12.00 36400.00
2009-01-05 17:36:00 Star Trek 93 12.00 36400.00
2009-01-05 20:00:00 Star Trek 110 12.00 36400.00
2009-01-05 22:24:00 Star Trek 34 12.00 36400.00
2009-01-06 15:12:00 Star Trek 129 12.00 42664.00
2009-01-06 17:36:00 Star Trek 188 12.00 42664.00
2009-01-06 20:00:00 Star Trek 192 12.00 42664.00
2009-01-06 22:24:00 Star Trek 13 12.00 42664.00
2009-01-07 15:12:00 Star Trek 245 12.00 48844.00
2009-01-07 17:36:00 Star Trek 70 12.00 48844.00
2009-01-07 20:00:00 Star Trek 12 12.00 48844.00
2009-01-07 22:24:00 Star Trek 188 12.00 48844.00
2009-01-08 15:12:00 Star Trek 138 12.00 50112.00
2009-01-08 17:36:00 Star Trek 244 12.00 50112.00
2009-01-08 20:00:00 Star Trek 62 12.00 50112.00
2009-01-08 22:24:00 Star Trek 199 12.00 50112.00
2009-01-09 15:12:00 Star Trek 7 12.00 50259.00
2009-01-09 17:36:00 Star Trek 250 12.00 50259.00
2009-01-09 20:00:00 Star Trek 184 12.00 50259.00
2009-01-09 22:24:00 Star Trek 62 12.00 50259.00
2009-01-10 15:12:00 Star Trek 222 12.00 52724.00
2009-01-10 17:36:00 Star Trek 192 12.00 52724.00
2009-01-10 20:00:00 Star Trek 205 12.00 52724.00
2009-01-10 22:24:00 Star Trek 231 12.00 52724.00
2009-01-11 15:12:00 Star Trek 184 12.00 50880.00
2009-01-11 17:36:00 Star Trek 114 12.00 50880.00
2009-01-11 20:00:00 Star Trek 239 12.00 50880.00
2009-01-11 22:24:00 Star Trek 215 12.00 50880.00
2009-01-12 14:48:00 2001 l'odyssée de l'espace 113 13.00 52830.00
2009-01-12 17:24:00 2001 l'odyssée de l'espace 152 13.00 52830.00
2009-01-12 20:00:00 2001 l'odyssée de l'espace 135 13.00 52830.00
2009-01-12 22:36:00 2001 l'odyssée de l'espace 170 13.00 52830.00
2009-01-13 14:48:00 2001 l'odyssée de l'espace 108 13.00 54249.00
2009-01-13 17:24:00 2001 l'odyssée de l'espace 189 13.00 54249.00
2009-01-13 20:00:00 2001 l'odyssée de l'espace 187 13.00 54249.00
2009-01-13 22:36:00 2001 l'odyssée de l'espace 107 13.00 54249.00
2009-01-14 14:48:00 2001 l'odyssée de l'espace 191 13.00 55856.00
2009-01-14 17:24:00 2001 l'odyssée de l'espace 147 13.00 55856.00
2009-01-14 20:00:00 2001 l'odyssée de l'espace 100 13.00 55856.00
2009-01-14 22:36:00 2001 l'odyssée de l'espace 161 13.00 55856.00
2009-01-15 14:48:00 2001 l'odyssée de l'espace 168 13.00 55095.00
2009-01-15 17:24:00 2001 l'odyssée de l'espace 7 13.00 55095.00
2009-01-15 20:00:00 2001 l'odyssée de l'espace 138 13.00 55095.00
2009-01-15 22:36:00 2001 l'odyssée de l'espace 222 13.00 55095.00
...
On peut visualiser ce concept de fenêtre glissante par l'image suivante :
figure 11 : fenêtre glissante
Sans les fonctions fenêtrées, cette requête s'écrit :
SELECT SNC_DATE, SNC_HEURE, FLM_TITRE, SNC_BILLET, SNC_TARIF,
(SELECT SUM(SNC_BILLET * SNC_TARIF)
FROM T_SEANCE_SNC AS Si
WHERE Si.SNC_DATE BETWEEN DATEADD(DAY, -6, S.SNC_DATE)
AND S.SNC_DATE) AS CA_CUMUL_GLISSANT_SEMAINE
FROM T_SEANCE_SNC AS S
INNER JOIN T_FILM_FLM AS F
ON S.FLM_ID = F.FLM_ID
ORDER BY SNC_DATE, SNC_HEURE
Sous Oracle, la différence dans les plans de requête est aussi spectaculaire :
Avec les fonctions fenêtrées :
figure 12 : plan de requête Oracle
Sans les fonctions fenêtrées :
figure 13 : plan de requête Oracle
Les éléments en rouge montrent qu'il manque un index pour que la seconde requête soit efficace, alors que la première n'en a pas besoin…
Exemple 9 (voir l'annexe E pour les jeux d'essais) - cumul avec fenêtre glissante de 7 jours avant, 7 jours centrés et 7 jours après la ligne courante
SELECT SNC_DATE, SNC_HEURE, SNC_BILLET, SNC_TARIF,
SUM(SNC_BILLET * SNC_TARIF)
OVER (ORDER BY SNC_DATE
RANGE BETWEEN 7 PRECEDING
AND CURRENT ROW EXCLUDE CURRENT ROW)
AS CA_CUMUL_GLISSANT_SEMAINE_AVANT_JOUR,
SUM(SNC_BILLET * SNC_TARIF)
OVER (ORDER BY SNC_DATE
RANGE BETWEEN 3 PRECEDING
AND 3 FOLLOWING)
AS CA_CUMUL_GLISSANT_SEMAINE_PENDANT_JOUR,
SUM(SNC_BILLET * SNC_TARIF)
OVER (ORDER BY SNC_DATE
RANGE BETWEEN CURRENT ROW EXCLUDE CURRENT ROW
AND 7 FOLLOWING)
AS CA_CUMUL_GLISSANT_SEMAINE_APRES_JOUR
FROM T_SEANCE_SNC AS S
INNER JOIN T_FILM_FLM AS F
ON S.FLM_ID = F.FLM_ID
ORDER BY SNC_DATE, SNC_HEUREOu encore :
SELECT SNC_DATE, SNC_HEURE, SNC_BILLET, SNC_TARIF,
SUM(SNC_BILLET * SNC_TARIF)
OVER (ORDER BY SNC_DATE
RANGE BETWEEN 7 PRECEDING
AND 1 PRECEDING)
AS CA_CUMUL_GLISSANT_SEMAINE_AVANT_JOUR,
SUM(SNC_BILLET * SNC_TARIF)
OVER (ORDER BY SNC_DATE
RANGE BETWEEN 3 PRECEDING
AND 3 FOLLOWING)
AS CA_CUMUL_GLISSANT_SEMAINE_PENDANT_JOUR,
SUM(SNC_BILLET * SNC_TARIF)
OVER (ORDER BY SNC_DATE
RANGE BETWEEN 1 FOLLOWING
AND 7 FOLLOWING)
AS CA_CUMUL_GLISSANT_SEMAINE_APRES_JOUR
FROM T_SEANCE_SNC AS S
INNER JOIN T_FILM_FLM AS F
ON S.FLM_ID = F.FLM_ID
ORDER BY SNC_DATE, SNC_HEURE CA_CUMUL_GLISSANT_SEMAINE_
SNC_DATE SNC_HEURE SNC_BILLET SNC_TARIF AVANT_JOUR PENDANT_JOUR APRES_JO
---------- ----------- ---------- ----------- ------------ -------------- --------
2009-01-01 14:40:00 94 13.00 NULL 30940.00 48844.00
2009-01-01 17:20:00 70 13.00 NULL 30940.00 48844.00
2009-01-01 20:00:00 184 13.00 NULL 30940.00 48844.00
2009-01-01 22:40:00 148 13.00 NULL 30940.00 48844.00
2009-01-02 14:40:00 56 13.00 6448.00 36400.00 50112.00
2009-01-02 17:20:00 216 13.00 6448.00 36400.00 50112.00
2009-01-02 20:00:00 133 13.00 6448.00 36400.00 50112.00
2009-01-02 22:40:00 48 13.00 6448.00 36400.00 50112.00
2009-01-03 14:40:00 104 13.00 12337.00 42664.00 50259.00
2009-01-03 17:20:00 168 13.00 12337.00 42664.00 50259.00
2009-01-03 20:00:00 250 13.00 12337.00 42664.00 50259.00
2009-01-03 22:40:00 73 13.00 12337.00 42664.00 50259.00
2009-01-04 14:40:00 233 13.00 20072.00 48844.00 52724.00
2009-01-04 17:20:00 152 13.00 20072.00 48844.00 52724.00
2009-01-04 20:00:00 201 13.00 20072.00 48844.00 52724.00
2009-01-04 22:40:00 250 13.00 20072.00 48844.00 52724.00
2009-01-05 15:12:00 218 12.00 30940.00 50112.00 50880.00
2009-01-05 17:36:00 93 12.00 30940.00 50112.00 50880.00
2009-01-05 20:00:00 110 12.00 30940.00 50112.00 50880.00
2009-01-05 22:24:00 34 12.00 30940.00 50112.00 50880.00
2009-01-06 15:12:00 129 12.00 36400.00 50259.00 52830.00
2009-01-06 17:36:00 188 12.00 36400.00 50259.00 52830.00
2009-01-06 20:00:00 192 12.00 36400.00 50259.00 52830.00
2009-01-06 22:24:00 13 12.00 36400.00 50259.00 52830.00
2009-01-07 15:12:00 245 12.00 42664.00 52724.00 54249.00
2009-01-07 17:36:00 70 12.00 42664.00 52724.00 54249.00
2009-01-07 20:00:00 12 12.00 42664.00 52724.00 54249.00
2009-01-07 22:24:00 188 12.00 42664.00 52724.00 54249.00
2009-01-08 15:12:00 138 12.00 48844.00 50880.00 55856.00
2009-01-08 17:36:00 244 12.00 48844.00 50880.00 55856.00
2009-01-08 20:00:00 62 12.00 48844.00 50880.00 55856.00
2009-01-08 22:24:00 199 12.00 48844.00 50880.00 55856.00
2009-01-09 15:12:00 7 12.00 50112.00 52830.00 55095.00
2009-01-09 17:36:00 250 12.00 50112.00 52830.00 55095.00
2009-01-09 20:00:00 184 12.00 50112.00 52830.00 55095.00
2009-01-09 22:24:00 62 12.00 50112.00 52830.00 55095.00
2009-01-10 15:12:00 222 12.00 50259.00 54249.00 59277.00
2009-01-10 17:36:00 192 12.00 50259.00 54249.00 59277.00
2009-01-10 20:00:00 205 12.00 50259.00 54249.00 59277.00
2009-01-10 22:24:00 231 12.00 50259.00 54249.00 59277.00
2009-01-11 15:12:00 184 12.00 52724.00 55856.00 55109.00
2009-01-11 17:36:00 114 12.00 52724.00 55856.00 55109.00
2009-01-11 20:00:00 239 12.00 52724.00 55856.00 55109.00
2009-01-11 22:24:00 215 12.00 52724.00 55856.00 55109.00
2009-01-12 14:48:00 113 13.00 50880.00 55095.00 54873.00
2009-01-12 17:24:00 152 13.00 50880.00 55095.00 54873.00
2009-01-12 20:00:00 135 13.00 50880.00 55095.00 54873.00
2009-01-12 22:36:00 170 13.00 50880.00 55095.00 54873.00
2009-01-13 14:48:00 108 13.00 52830.00 59277.00 50830.00
2009-01-13 17:24:00 189 13.00 52830.00 59277.00 50830.00
2009-01-13 20:00:00 187 13.00 52830.00 59277.00 50830.00
2009-01-13 22:36:00 107 13.00 52830.00 59277.00 50830.00
2009-01-14 14:48:00 191 13.00 54249.00 55109.00 47242.00
2009-01-14 17:24:00 147 13.00 54249.00 55109.00 47242.00
2009-01-14 20:00:00 100 13.00 54249.00 55109.00 47242.00
2009-01-14 22:36:00 161 13.00 54249.00 55109.00 47242.00
2009-01-15 14:48:00 168 13.00 55856.00 54873.00 45851.00
2009-01-15 17:24:00 7 13.00 55856.00 54873.00 45851.00
2009-01-15 20:00:00 138 13.00 55856.00 54873.00 45851.00
2009-01-15 22:36:00 222 13.00 55856.00 54873.00 45851.00
...
À nouveau cette requête est exprimable sans les fonctions fenêtrées :
SELECT SNC_DATE, SNC_HEURE, SNC_BILLET, SNC_TARIF,
(SELECT SUM(SNC_BILLET * SNC_TARIF)
FROM T_SEANCE_SNC AS Si
WHERE Si.SNC_DATE >= DATEADD(DAY, -7, S.SNC_DATE)
AND Si.SNC_DATE < S.SNC_DATE)
AS CA_CUMUL_GLISSANT_SEMAINE_AVANT_JOUR,
(SELECT SUM(SNC_BILLET * SNC_TARIF)
FROM T_SEANCE_SNC AS Si
WHERE Si.SNC_DATE BETWEEN DATEADD(DAY, -3, S.SNC_DATE)
AND DATEADD(DAY, +3, S.SNC_DATE) )
AS CA_CUMUL_GLISSANT_SEMAINE_PENDANT_JOUR,
(SELECT SUM(SNC_BILLET * SNC_TARIF)
FROM T_SEANCE_SNC AS Si
WHERE Si.SNC_DATE > S.SNC_DATE
AND Si.SNC_DATE <= DATEADD(DAY, 7, S.SNC_DATE) )
AS CA_CUMUL_GLISSANT_SEMAINE_APRES_JOUR
FROM T_SEANCE_SNC AS S
INNER JOIN T_FILM_FLM AS F
ON S.FLM_ID = F.FLM_ID
ORDER BY SNC_DATE, SNC_HEURE
Exemple 10 (voir l'annexe E pour les jeux d'essais) - cumuls autour d'une valeur
On cherche maintenant à afficher le CA du jour courant avec celui du jour d'avant et du jour d'après. On retiendra aussi le nombre de billets vendus dans le jour et le tarif moyen au cours du jour. On ne tient pas compte des horaires :
SELECT DISTINCT SNC_DATE,
SUM(SNC_BILLET) OVER(PARTITION BY SNC_DATE) AS TOTAL_BILLET,
AVG(SNC_TARIF) OVER(PARTITION BY SNC_DATE) AS TARIF_MOYEN,
SUM(SNC_BILLET * SNC_TARIF)
OVER(ORDER BY SNC_DATE
RANGE BETWEEN 1 PRECEDING
AND 1 PRECEDING) AS CA_JOUR_AVANT,
SUM(SNC_BILLET * SNC_TARIF)
OVER(PARTITION BY SNC_DATE) AS CA_JOUR,
SUM(SNC_BILLET * SNC_TARIF)
OVER(ORDER BY SNC_DATE
RANGE BETWEEN 1 FOLLOWING
AND 1 FOLLOWING) AS CA_JOUR_APRES
FROM T_SEANCE_SNC AS S
INNER JOIN T_FILM_FLM AS F
ON S.FLM_ID = F.FLM_ID
ORDER BY SNC_DATESNC_DATE TOTAL_BILLET TARIF_MOYEN CA_JOUR_AVANT CA_JOUR CA_JOUR_APRES
---------- ------------ -------------- -------------- ---------- ---------------
2009-01-01 496 13.000000 NULL 6448.00 5889.00
2009-01-02 453 13.000000 6448.00 5889.00 7735.00
2009-01-03 595 13.000000 5889.00 7735.00 10868.00
2009-01-04 836 13.000000 7735.00 10868.00 5460.00
2009-01-05 455 12.000000 10868.00 5460.00 6264.00
2009-01-06 522 12.000000 5460.00 6264.00 6180.00
2009-01-07 515 12.000000 6264.00 6180.00 7716.00
2009-01-08 643 12.000000 6180.00 7716.00 6036.00
2009-01-09 503 12.000000 7716.00 6036.00 10200.00
2009-01-10 850 12.000000 6036.00 10200.00 9024.00
2009-01-11 752 12.000000 10200.00 9024.00 7410.00
2009-01-12 570 13.000000 9024.00 7410.00 7683.00
2009-01-13 591 13.000000 7410.00 7683.00 7787.00
2009-01-14 599 13.000000 7683.00 7787.00 6955.00
2009-01-15 535 13.000000 7787.00 6955.00 10218.00
2009-01-16 786 13.000000 6955.00 10218.00 6032.00
2009-01-17 464 13.000000 10218.00 6032.00 8788.00
2009-01-18 676 13.000000 6032.00 8788.00 3367.00
2009-01-19 259 13.000000 8788.00 3367.00 4095.00
2009-01-20 315 13.000000 3367.00 4095.00 6396.00
2009-01-21 492 13.000000 4095.00 6396.00 5161.00
2009-01-22 397 13.000000 6396.00 5161.00 9607.00
2009-01-23 739 13.000000 5161.00 9607.00 5343.00
2009-01-24 411 13.000000 9607.00 5343.00 12220.00
2009-01-25 940 13.000000 5343.00 12220.00 7920.00
2009-01-26 720 11.000000 12220.00 7920.00 5885.00
2009-01-27 535 11.000000 7920.00 5885.00 6446.00
2009-01-28 586 11.000000 5885.00 6446.00 4873.00
2009-01-29 443 11.000000 6446.00 4873.00 3168.00
2009-01-30 288 11.000000 4873.00 3168.00 6501.00
2009-01-31 591 11.000000 3168.00 6501.00 5709.00
...
Une autre façon d'écrire cette requête est d'utiliser une imbrication des fonctions fenêtrées, par exemple dans une CTE et cela grâce aux fonctions LEAD et LAG qui permettent d'avancer ou de reculer dans les lignes :
WITH
T AS (SELECT DISTINCT SNC_DATE,
SUM(SNC_BILLET) OVER(PARTITION BY SNC_DATE) AS TOTAL_BILLET,
AVG(SNC_TARIF) OVER(PARTITION BY SNC_DATE) AS TARIF_MOYEN,
SUM(SNC_BILLET * SNC_TARIF)
OVER(PARTITION BY SNC_DATE) AS CA_JOUR
FROM T_SEANCE_SNC AS S
INNER JOIN T_FILM_FLM AS F
ON S.FLM_ID = F.FLM_ID)
SELECT SNC_DATE, TOTAL_BILLET, TARIF_MOYEN,
LAG(CA_JOUR, 1) OVER(ORDER BY SNC_DATE) AS CA_JOUR_AVANT,
CA_JOUR,
LEAD(CA_JOUR, 1) OVER(ORDER BY SNC_DATE) AS CA_JOUR_AVANT
FROM T
ORDER BY SNC_DATE
À nouveau une telle requête peut s'exprimer sans recourir aux fonctions fenêtrées :
SELECT DISTINCT SNC_DATE,
SUM(SNC_BILLET) AS TOTAL_BILLET,
AVG(SNC_TARIF) AS TARIF_MOYEN,
(SELECT SUM(SNC_BILLET * SNC_TARIF)
FROM T_SEANCE_SNC AS Si
WHERE Si.SNC_DATE = DATEADD(DAY, -1, S.SNC_DATE)) AS CA_JOUR_AVANT,
(SELECT SUM(SNC_BILLET * SNC_TARIF)
FROM T_SEANCE_SNC AS Si
WHERE Si.SNC_DATE = S.SNC_DATE) AS CA_JOUR,
(SELECT SUM(SNC_BILLET * SNC_TARIF)
FROM T_SEANCE_SNC AS Si
WHERE Si.SNC_DATE = DATEADD(DAY, 1, S.SNC_DATE)) AS CA_JOUR_APRES
FROM T_SEANCE_SNC AS S
INNER JOIN T_FILM_FLM AS F
ON S.FLM_ID = F.FLM_ID
GROUP BY SNC_DATE
ORDER BY SNC_DATE
Exemple 11 (voir l'annexe E pour les jeux d'essais) - calcul d'une médiane
Nous allons maintenant afficher le CA séance, le CA du jour, de la semaine et du mois, ainsi que le CA moyen et le CA médian pour les jours, semaines et mois. Rappelons que le calcul de la médiane n'est pas disponible dans SQL, mais qu'il s'agit de la valeur du « milieu » (ou la moyenne des valeurs du milieu si le nombre d'échantillons est pair) lorsque l'on ordonne toutes les valeurs.
WITH
T1 AS
(
SELECT SNC_DATE, SNC_HEURE, SNC_BILLET, SNC_TARIF, SNC_BILLET * SNC_TARIF AS CA_SEANCE,
SUM(SNC_BILLET * SNC_TARIF)
OVER(PARTITION BY SNC_DATE) AS CA_JOUR,
SUM(SNC_BILLET * SNC_TARIF)
OVER(PARTITION BY YEAR(SNC_DATE), DATEPART(isowk, SNC_DATE))
AS CA_SEMAINE,
SUM(SNC_BILLET * SNC_TARIF)
OVER(PARTITION BY YEAR(SNC_DATE), MONTH(SNC_DATE))
AS CA_MOIS
FROM T_SEANCE_SNC
),
T2 AS
(
SELECT SNC_DATE, SNC_HEURE, SNC_BILLET, SNC_TARIF, CA_SEANCE, CA_JOUR, CA_SEMAINE, CA_MOIS,
AVG(CA_JOUR) OVER() AS CA_MOYEN_JOUR,
AVG(CA_SEMAINE) OVER() AS CA_MOYEN_SEMAINE,
AVG(CA_MOIS) OVER() AS CA_MOYEN_MOIS,
ROW_NUMBER() OVER(ORDER BY CA_JOUR) AS CA_JOUR_ORDRE,
ROW_NUMBER() OVER(ORDER BY CA_SEMAINE) AS CA_SEMAINE_ORDRE,
ROW_NUMBER() OVER(ORDER BY CA_MOIS) AS CA_MOIS_ORDRE,
COUNT(*) OVER() AS N
FROM T1
)
SELECT SNC_DATE, SNC_HEURE, SNC_BILLET, SNC_TARIF, CA_SEANCE, CA_JOUR, CA_SEMAINE, CA_MOIS,
CA_MOYEN_JOUR, CA_MOYEN_SEMAINE, CA_MOYEN_MOIS,
(SELECT AVG(CA_JOUR)
FROM T2
WHERE CA_JOUR_ORDRE BETWEEN FLOOR((N -0.5) / 2) + 1
AND CEILING((N + 0.5) / 2))
AS CA_MEDIAN_JOUR,
(SELECT AVG(CA_SEMAINE)
FROM T2
WHERE CA_SEMAINE_ORDRE BETWEEN FLOOR((N -0.5) / 2) + 1
AND CEILING((N + 0.5) / 2))
AS CA_MEDIAN_SEMAINE,
(SELECT AVG(CA_MOIS)
FROM T2
WHERE CA_MOIS_ORDRE BETWEEN FLOOR((N -0.5) / 2) + 1
AND CEILING((N + 0.5) / 2))
AS CA_MEDIAN_MOIS
FROM T2
NOTA : cette requête est donnée sous MS SQL Server. Sur d'autres SGBDR, il faudra remplacer DATEPART(isowk, SNC_DATE) par WEEK(SNC_DATE) et pour d'autres, utiliser la fonction EXTRACT pour obtenir la partie souhaitable de la date.
Comme nous venons de le voir, cette requête est complexe et son plan de requête l'est aussi. Mais sans les fonctions fenêtrées, la complexité de mise au point de cette requête explose ! Et les temps de calcul deviennent gigantesques…
Nous allons voir maintenant un dernier « truc » qui va nous permettre de simplifier grandement cette requête… Voir paragraphe suivant.
IV. PERCENTILE… et WITHIN GROUP▲
L'option WITHIN GROUP, utilisable uniquement avec les fonctions PERCENTILE_CONT et PERCENTILE_DISC (classement par 100e), permet d'obtenir rapidement une valeur à un point précis d'un classement. CONT agit sur des valeurs continues (interpolation linéaire) et DISC sur des valeurs discrètes.
La syntaxe de WITHIN GROUP est la suivante :
WITHIN GROUP
(ORDER BY <expression1> [ DESC | ASC ] [ NULLS { FIRST | LAST } ]
[, <expression2> [ DESC | ASC ] [ NULLS { FIRST | LAST } ]
[, ... ] )
Exemple 12 (voir l'annexe E pour les jeux d'essais) - calcul d'une médiane (reprise de l'exemple 11)
WITH
T1 AS
(
SELECT SNC_DATE, SNC_HEURE, SNC_BILLET, SNC_TARIF, SNC_BILLET * SNC_TARIF AS CA_SEANCE,
SUM(SNC_BILLET * SNC_TARIF)
OVER(PARTITION BY SNC_DATE) AS CA_JOUR,
SUM(SNC_BILLET * SNC_TARIF)
OVER(PARTITION BY YEAR(SNC_DATE), WEEK(SNC_DATE))
AS CA_SEMAINE,
SUM(SNC_BILLET * SNC_TARIF)
OVER(PARTITION BY YEAR(SNC_DATE), MONTH(SNC_DATE))
AS CA_MOIS
FROM dbo.T_SEANCE_SNC
)
SELECT SNC_DATE, SNC_HEURE, SNC_BILLET, SNC_TARIF,
CA_SEANCE, CA_JOUR, CA_SEMAINE, CA_MOIS,
AVG(CA_JOUR) OVER() AS CA_MOYEN_JOUR,
AVG(CA_SEMAINE) OVER() AS CA_MOYEN_SEMAINE,
AVG(CA_MOIS) OVER() AS CA_MOYEN_MOIS,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CA_JOUR)
AS CA_MEDIAN_JOUR,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CA_SEMAINE)
AS CA_MEDIAN_SEMAINE,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CA_MOIS)
AS CA_MEDIAN_MOIS
FROM T1
GROUP BY SNC_DATE, SNC_HEURE, SNC_BILLET, SNC_TARIF,
CA_SEANCE, CA_JOUR, CA_SEMAINE, CA_MOIS
En effet la valeur 0,5 passée en argument de la fonction PERCENTILE_CONT permet de retrouver la valeur médiane !
Attention : les valeurs calculées peuvent différer sensiblement entre la requête pour laquelle on calcule les valeurs médianes par moyenne et la fonction PERCENTILE_CONT, plus juste, qui réalise une interpolation linéaire.
V. Utilisation de la clause WINDOW▲
Le but de la clause WINDOW qui se place dans le SELECT juste avant la clause de tri (ORDER BY), est de fournir des fenêtres de données prédéfinies aux différentes fonctions qui le nécessitent. Ces fonctions devront donc reprendre le nom de la fenêtre dans la partie PARTITION de l'appel de clause OVER.
Comprenons que la clause OVER est une fenêtre WINDOW particulière, mais que la clause WINDOW permet de globaliser la fenêtre.
Comprenons aussi qu'une fenêtre n'est en fait rien d'autre que la définition d'une table dont les lignes ont un comportement particulier.
Exemple 13 (voir l'annexe E pour les jeux d'essais) - utilisation de la clause WINDOW (reprise de l'exemple 2)
SELECT FLM_TITRE, FLM_REALISATEUR,
MIN(FLM_ANNEE) OVER W_P_REA
AS AN_PLUS_VIEUX_FILM_REAL,
MAX(FLM_ANNEE) OVER W_P_REA
AS AN_PLUS_RECENT_FILM_REAL,
RANK() OVER W_O_FAN
AS CHRONO_FILM,
RANK() OVER W_P_REA_O_FAN
AS CHRONO_FILM_REAL
FROM T_FILM_FLM
WINDOW W_P_REA AS (PARTITION BY FLM_REALISATEUR),
W_O_FAN AS (ORDER BY FLM_ANNEE),
W_P_REA_O_FAN AS (PARTITION BY FLM_REALISATEUR ORDER BY FLM_ANNEE)
ORDER BY FLM_REALISATEURVI. Position des NULL▲
Les mots clefs NULL FIRST et NULL LAST peuvent être utilisés dans la partie ORDER BY de la clause OVER afin de préciser si les valeurs absentes doivent être positionnées au début ou en fin de tri.
Exemple 14 (voir l'annexe E pour les jeux d'essais) - utilisation du mot clef NULL FIRST
SELECT FLM_TITRE, FLM_REALISATEUR,
RANK() OVER(PARTITION BY FLM_REALISATEUR
ORDER BY FLM_ANNEE NULL FIRST)
AS CHRONO_FILM_REAL
FROM T_FILM_FLM
ORDER BY FLM_REALISATEURVII. Notes techniques▲
La plupart de ces requêtes sont compatibles avec PostGreSQL, MS SQL Server et Oracle. Néanmoins la version d'Oracle Express sur laquelle nous avons testé ces requêtes (10g release 2) n'accepte pas des SELECT avec l'étoile ni de nommer les tables dérivées (AS T). En sus, Oracle effectue des transtypages illicites qui rendent certaines requêtes inopérables et interdit d'utiliser des noms d'objet de plus de 30 caractères.
Par exemple, la requête suivante (exemple 4) :
SELECT FLM_TITRE, FLM_REALISATEUR, FLM_ANNEE
FROM (SELECT FLM_TITRE, FLM_REALISATEUR, FLM_ANNEE,
ROW_NUMBER() OVER(ORDER BY FLM_ANNEE DESC) - 1 AS N
FROM T_FILM_FLM) AS T
WHERE (N / 5) + 1 = 2
…doit être réécrite ainsi pour Oracle :
SELECT FLM_TITRE, FLM_REALISATEUR, FLM_ANNEE
FROM (SELECT FLM_TITRE, FLM_REALISATEUR, FLM_ANNEE,
ROW_NUMBER() OVER(ORDER BY FLM_ANNEE DESC) - 1 AS N
FROM T_FILM_FLM) --> suppression de l'alias de table
WHERE FLOOR((N / 5) + 1) = 2 --> rajout de la fonction FLOOR
Enfin, la norme SQL indique qu'un nom d'objet est limité à 128 caractères, mais Oracle refuse plus d'une trentaine, tant et si bien que l'on doit, par exemple, renommer CA_CUMUL_GLISSANT_SEMAINE_AVANT_JOUR en CA_CGSAJ et autres colonnes dans la requête de l'exemple 9.
La fonction Oracle pour extraire le numéro ISO de la semaine est TO_NUMBER(TO_CHAR(SYSDATE, 'WI')).
Remerciement :
- à Richard Gaillard dit « Pomalaix » et Stéphane Faroult de RoughSea pour leurs conseils sur Oracle.
VIII. ANNEXE A - liste des fonctions d'agrégation▲
|
fonction |
description |
Type |
|
COUNT(*) |
compte le nombre de lignes |
tous |
|
COUNT(exp) |
compte le nombre d'occurrences |
tous |
|
MAX(exp) |
renvoie le maximum |
tous |
|
MIN(exp) |
renvoie le minimum |
tous |
|
SUM(exp) |
sommation |
numérique |
|
AVG(exp) |
calcule la moyenne |
numérique |
|
EVERY(exp) |
renvoie un booléen SQL qui vaut vrai si les occurrences sont toutes vraies |
booléen |
|
ANY(exp)SOME(exp) |
renvoie un booléen SQL qui vaut vrai si au moins une occurrence est vraie |
booléen |
|
STDDEV_POP(exp) |
(nouveauté SQL:2003) écart type d'une population |
numérique |
|
STDDEV_SAMP(exp) |
(nouveauté SQL:2003) écart type d'un échantillon |
numérique |
|
VAR_POP(exp) |
(nouveauté SQL:2003) variance d'une population |
numérique |
|
VAR_SAMP(exp) |
(nouveauté SQL:2003) variance d'un échantillon |
numérique |
|
PERCENTILE_CONT(exp) |
valeur interpolée d'après pourcentage |
numérique |
|
PERCENTILE_DIST(exp) |
plus petite distribution cumulée supérieure ou égale au pourcentage indiqué |
numérique |
où (exp) est une expression basée sur les colonnes de la table.
IX. ANNEXE B - liste des fonctions de classement▲
|
fonction |
définition |
|
RANK() |
classement absolu avec prise en compte des ex aequo par saut |
|
DENSE_RANK() |
classement absolu sans prise en compte des ex aequo par saut |
|
PERCENT_RANK() |
classement en pourcentage |
|
CUM_DIST() |
position relative de la valeur. Résultat dans l'intervalle ] 0, 1 ] |
|
ROW_NUMBER() |
numérotation des lignes |
|
NTILE(n) |
définition de n-tile (décile : n=10, centile : n=100…) |
Utilisable uniquement avec un fenêtrage explicite :
|
fonction |
définition |
|
LEAD (exp [, offset] [, defaut] ) |
valeur relative avant dans la fenêtre |
|
LAG (exp [, offset] [, defaut] ) |
valeur relative après dans la fenêtre |
|
FIRST_VALUE |
première valeur dans la fenêtre |
|
LAST_VALUE |
dernière valeur dans la fenêtre |
|
NTH_VALUE |
énième valeur dans la fenêtre |
X. ANNEXE C - position des éditeurs sur les fonctions fenêtrées▲
|
SGBDR |
IBM DB2 |
Oracle |
MS SQL Server |
Sybase AS A |
PostGreSQL |
FireBird |
MySQL |
Access |
|
Version |
9 |
11G |
2008 |
9.0.1 |
8.4 |
2.1 |
5.0 |
2007 |
|
Agrégats |
Oui |
Oui |
Oui |
Oui |
Oui |
Non |
Non |
Non |
|
Analytiques |
Oui |
Oui |
Oui |
Oui |
Oui |
Non |
Non |
Non |
|
Windows |
Non |
Non |
Non |
Oui |
Oui |
Non |
Non |
Non |
|
Range/Rows |
Oui |
Oui |
Non |
Oui |
Oui (1) |
Non |
Non |
Non |
|
Within Group |
Oui |
Oui |
Non |
Non |
Non |
Non |
Non |
Non |
(1) n'accepte pas les constructions avec n PRECEDING ou n FOLLOWING
Fonctions spécifiques aux éditeurs :
|
RATIO_TO_REPORT |
CORR |
FIRST/LAST |
COVAR… |
REGR… |
|
|
IBM DB2 9 |
Oui |
Oui |
Non |
Non |
Non |
|
Oracle 11G |
Oui |
Oui |
Oui |
Oui |
Oui |
|
SQL Server 2008 |
Non |
Non |
Non |
Non |
Non |
|
Sybase ASA |
Non |
Non |
Non |
Oui |
Oui |
|
PostGreSQL 8.4 |
Oui |
Oui |
Oui |
Oui |
Oui |
En matière de fonctions fenêtrées le SGBDR le plus pointu est actuellement Oracle malgré qu'il n'implémente pas la clause WINDOW.
IBM DB2, PostGreSQL et Sybase ASA arrivent en second avec quelques lacunes dont la principale pour PostGreSQL est de ne pas permettre de limiter la fenêtre à un nombre déterminé de lignes.
Il est dommage de constater que MS SQL Server est à la traîne derrière Oracle, PostGreSQL Sybase AS A et IBM DB2.
Les plus nuls au véritable sens du terme sont MySQL, FireBird et Acces qui ne connaissent pas du tout ces concepts !
XI. ANNEXE D - informations complémentaires▲
Livres :
- SQL - collections Synthèse de cours et exercices - F. Brouard, C. Soutou, R Bruchez - Pearson Education 2008
- SQL par l'exemple - Anthony Molinaro - O'Reilly 2007
- Advanced SQL:1999 - Jim Melton - Morgan Kaufmann 2003
Web :
|
comparaison |
http://www.postgresonline.com/journal/index.php?/archives/122-Window-Functions-Comparison-Between-PostgreSQL-8.4,-SQL-Server-2008,-Oracle,-IBM-DB2.html |
|
DB2 |
http://publib.boulder.ibm.com/infocenter/rbhelp/v6r3/index.jsp?topic=/com.ibm.redbrick.doc6.3/sqlrg/sqlrg35.htmhttp://www.redbooks.ibm.com/redbooks/pdfs/sg246546.pdf |
|
Oracle |
http://lalystar.developpez.com/fonctionsAnalytiques/http://www.isnetne.ch/isnet29/documentation/ISNet29_SQL_aggregation.pdf |
|
SQL Server |
http://www.solidq.com/insidetsql/over_clause_and_ordered_calculations.doc |
|
PostGreSQL |
http://wiki.postgresql.org/wiki/SQL2003_widowing_querieshttp://docs.postgresqlfr.org/8.4/sql-expressions.html#syntax-window-functions |
|
Sybase ASA |
http://www.ianywhere.com/developer/product_manuals/sqlanywhere/1000/en/html/dbugen10/ug-ug-olap-s-5122634.html |
|
MySQL |
http://www.oreillynet.com/pub/a/mysql/2007/03/29/emulating-analytic-aka-ranking-functions-with-mysql.html |