IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)

Les fonctions OLAP et la clause WINDOW de fenêtrage

SQL:2003 a introduit la notion de fonctions OLAP (appelées couramment fonctions fenêtrées, en anglais windowing functions, débattues lors de la norme SQL:1999 et finalement adoptées dans la norme SQL:2003) et dans ce concept se regroupent deux types de fonctions bien distinctes opérant sur des ensembles de données spécifiques appelés « fenêtre de données », constitués par des sous-ensembles des données du résultat de la requête globale.

Article lu   fois.

L'auteur

Profil ProSite personnel

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

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 :

image

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 :

 
Sélectionnez
<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 :

 
Sélectionnez
SELECT *, 
       COUNT(*) OVER(PARTITION BY FLM_REALISATEUR) AS NB_FILM_REAL
FROM   T_FILM_FLM  
ORDER  BY FLM_REALISATEUR
 
Sélectionnez
FLM_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 :

image

figure 2 : fenêtres avec partition


Si nous n'avions pas de fonction fenêtrée, cette requête s'écrirait :

 
Sélectionnez
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 :

image

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 :

 
Sélectionnez
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_REALISATEUR
 
Sélectionnez
FLM_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é.

 
Sélectionnez
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_REALISATEUR

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

 
Sélectionnez
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_REALISATEUR
 
Sélectionnez
FLM_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 :

PGplan1

figure 4 : plan de requête dans PostGreSQL


avec un cout de 126,75 pour les fonctions fenêtrées

PGplan2

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 :

 
Sélectionnez
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 :

 
Sélectionnez
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 = 2
 
Sélectionnez
FLM_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 :

 
Sélectionnez
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 = 2
 
Sélectionnez
FLM_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 :
 
Sélectionnez
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 <= 3
 
Sélectionnez
FLM_REALISATEUR                  DUREE_TOTALE
-------------------------------- ------------
Stanley Kubrick                  918
Robert Wise                      875
George Roy Hill                  586
  • comme en nombre de films :
 
Sélectionnez
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 <= 3
 
Sélectionnez
FLM_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 :

 
Sélectionnez
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 <= 3
 
Sélectionnez
FLM_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 :

 
Sélectionnez
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 :

 
Sélectionnez
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

 
Sélectionnez
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 = 1
 
Sélectionnez
SNC_DATE   CA_JOUR
---------- ------------
2009-01-25 12220.00


Cette requête peut encore s'écrire à l'aide d'une CTE comme suit :

 
Sélectionnez
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 :

 
Sélectionnez
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 :

 
Sélectionnez
<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


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 :

image

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 :

 
Sélectionnez
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_HEURE
 
Sélectionnez
SNC_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 :

image

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 :

 
Sélectionnez
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

image

figure 8 : plan de requête PostGreSQL


Avec les fonctions fenêtrées, ce plan coute 179.73

image

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 :

image

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 :

 
Sélectionnez
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
 
Sélectionnez
                                                                             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 :

fenetre4

figure 11 : fenêtre glissante


Sans les fonctions fenêtrées, cette requête s'écrit :

 
Sélectionnez
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 :

image

figure 12 : plan de requête Oracle


Sans les fonctions fenêtrées :

image

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

 
Sélectionnez
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_HEURE

Ou encore :

 
Sélectionnez
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
 
Sélectionnez
                                                    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 :

 
Sélectionnez
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 :

 
Sélectionnez
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_DATE
 
Sélectionnez
SNC_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 :

 
Sélectionnez
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 :

 
Sélectionnez
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.

 
Sélectionnez
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 :

 
Sélectionnez
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)

 
Sélectionnez
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)

 
Sélectionnez
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_REALISATEUR

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

 
Sélectionnez
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_REALISATEUR

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

 
Sélectionnez
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 :

 
Sélectionnez
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

XII. ANNEXE E - fichier SQL pour les exemples

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

Copyright © 2009 Frédéric Brouard. Aucune reproduction, même partielle, ne peut être faite de ce site ni de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.