Les fonctions analytiques

Laly KATTOOR

Les fonctions analytiques ont été introduites dans Oracle 8i. Elles permettent d'extraire des informations de manière beaucoup plus simple et performante qu'auparavant pour une classe assez large de problèmes.
Plus précisément, elles permettent d'appliquer une fonction (somme, moyenne…) à un ensemble de lignes définies par rapport à la ligne courante ce qui permet de calculer aisément des sommes cumulées, des moyennes mobiles et d'accéder aux valeurs de la ligne précédente…

Les fonctions analytiques opèrent donc sur un ensemble de lignes définies de manière relative; elles se distinguent des fonctions agrégats qui opèrent sur l'ensemble d'un groupe défini par le group by. En ce sens, elles sont d'une granularité plus fine.

Ce document présente les fonctions analytiques présentes dans Oracle 9i R2. Pour une présentation complète l'utilisateur pourra toujours se reporter à la documentation Oracle. Le but de ce document est d'introduire le lecteur aux fonctions analytiques en se basant sur des exemples concrets.

- Titre : Les fonctions analytiques

- Auteur : Laly KATTOOR

- Parution : 14 octobre 2004

- Licence : Copyright ® 2004 Laly KATTOOR. Aucune reproduction, même partielle, ne peut être faite de ce site et 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.

Sommaire

- I. Présentation

- II. Syntaxe

- III. Les fonctions analytiques

- III-A. AVG

- III-B. LAG et LEAD

- III-C. CORR

- III-D. MAX et MIN

- III-E. RATIO_TO_REPORT

- III-F. STDDEV et VARIANCE

- III-G. NTILE

- III-H. ROW_NUMBER, RANK, DENSE_RANK et COUNT

- III-I. FIRST et LAST

- III-J. FIRST_VALUE et LAST_VALUE

- III-K. PERCENTILE_CONT et PERCENTILE_DISC

- IV. Annexes

- V. Conclusion

I - Présentation

Nous allons d'abord voir un premier exemple d'utilisation des fonctions analytiques pour calculer une moyenne mobile.

Supposons qu'on ait une table de cotation boursière (les cotations ont lieu les jours ouvrés uniquement) :

create table COTATION (
isin    varchar2 (20),   -- identifiant de la société
dte     date,            -- date de cotation
ouv     number,          -- premier cours de la journée
max     number,          -- cours le plus haut de la journée
min     number,          -- cours le plus bas de la journée
clo     number,          -- dernier cours de la journée (clôture)
volume  number,          -- nb d'actions échangées
constraint pk_cotation primary key (isin, dte)
)

Remarque : le lecteur pourra se reporter à l'annexe afin de créer les deux tables utilisées dans cet article.

La moyenne mobile à 5 jours est la moyenne des clôtures du jour en cours ainsi que des 4 jours précédents.

La méthode traditionnelle est :

create table MMA_NORMAL as
select
a.isin,
a.dte,
avg(a.clo) mma_5
from
(
select   a.isin, a.dte, b.clo
from
(
select   isin, dte, rownum rnum
from
(
select   isin, dte
from     COTATION
order by isin, dte
)
) a,
(
select   isin, dte, clo, rownum rnum
from
(
select   isin, dte, clo
from     COTATION
order by isin, dte
)
) b
where
a.isin = b.isin and
b.rnum between a.rnum-4 and a.rnum
-- on a besoin d'utiliser les ROWNUM car
-- les dates ne sont pas forcément consécutives
) a
group by isin, dte

Avec les fonctions analytiques, la requête s'écrit simplement :

create table MMA_ANALYTIC as
select   isin, dte,
avg(clo) over(partition by isin order by dte rows 4 preceding) mma_5
from     COTATION

Outre le fait que la seconde requête est beaucoup plus concise et explicite, comparons les performances avec TkProf :

- requête « standard » :

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.02       0.02          0          0          0           0
Execute      4    244.86     255.88      31115       2486        382      148702
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        7    244.88     255.90      31115       2486        382      148702

- requête avec fonction analytique :

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          0          0           0
Execute      1      8.92      11.80       3799        938        158       74351
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      8.92      11.81       3799        938        158       74351

C'est le jour et la nuit ! 12s de temps total pour la requête « analytique » contre 4 min pour la requête « standard ». Au niveau des logical I/O : 33601 (31115 + 2486) pour la « standard » contre 4737 pour la requête « analytique » !

Décortiquons la requête « analytique » :

select   isin, dte,
avg(clo) over(partition by isin order by dte rows 4 preceding) mma_5
from     COTATION

avg : c'est la fonction analytique utilisée ici; elle a le même nom que la fonction de groupe AVG mais cette dernière nécessite un group by alors qu'ici on n'en a pas besoin.
over : mot-clé qui indique qu'on utilise une fonction analytique
partition by isin : partition by indique un découpage logique; la fonction analytique utilisée ne calcule qu'à l'intérieur d'une partition. C'est tout à fait similaire au group by dans une requête traditionnelle.
order by dte: à l'intérieur de chaque partition, les lignes sont ordonnées par date.
rows 4 preceding : on considère uniquement la ligne courante et les 4 lignes précédentes.

Donc on a exactement ce que l'on veut : pour une société donnée (partition by isin), on trie les données par dte (order by dte) et pour chaque ligne obtenue et les 4 lignes précédentes (rows 4 preceding) on prend la moyenne des cours de clôture (avg(clo)).

II - Syntaxe

La syntaxe est la suivante :

fonction (expression) over ([clause de partitionnement] [clause d'ordre] [clause de fenêtrage])

fonction est le nom de la fonction analytique.
over indique qu'on utilise une fonction analytique.

Les trois clauses de partitionnement, d'ordre et de fenêtrage sont facultatives en général mais souvent nécessaires suivant la fonction utilisée.

La clause de partitionnement est de la forme PARTITION BY … Elle définit un découpage des données suivant les valeurs des colonnes du PARTITION BY… Chaque valeur définit un groupe logique à l'intérieur duquel est appliquée la fonction analytique. C'est analogue au GROUP BY.

La clause d'ordre est de la forme ORDER BY … [NULLS FIRST|LAST] Elle indique comment les données sont triées à l'intérieur de chaque partition. L'emploi de NULLS FIRST/LAST est facultatif : NULLS FIRST indique que les valeurs nulles apparaissent d'abord tandis que NULL LAST indique que ces valeurs apparaissent à la fin.
Cette clause d'ordre est nécessaire pour certaines fonctions. Quand elle est utilisée elle modifie le comportement de la fonction. En effet, il faut savoir que si une clause ORDER BY est utilisée et qu'une clause de fenêtrage n'est pas spécifiée, une fenêtrage implicite est appliquée (UNBOUNDED PRECEDING). On va mieux comprendre sur un exemple :

select   isin, dte, volume,
sum(volume) over(partition by isin) volume_total,
sum(volume) over(partition by isin order by dte) volume_cumule
from     COTATION
where    isin = 'FR0000130007'
order by isin, dte

ISIN                 DTE          VOLUME VOLUME_TOTAL VOLUME_CUMULE
-------------------- -------- ---------- ------------ -------------
FR0000130007         02/01/02    4155397   9941158109       4155397
FR0000130007         03/01/02    9266945   9941158109      13422342
FR0000130007         04/01/02   10741659   9941158109      24164001
FR0000130007         07/01/02    8462352   9941158109      32626353
FR0000130007         08/01/02   11571103   9941158109      44197456
FR0000130007         09/01/02    9459922   9941158109      53657378
FR0000130007         10/01/02    9175396   9941158109      62832774

Quand on utilise sum(volume) over(partition by isin order by dte) il n'y a pas de clause de fenêtrage et toutes les lignes depuis la première jusqu'à celle en cours selon l'ordre induit par le order by sont passées à la fonction analytique qui calcule donc ici la somme cumulée. Pour sum(volume) over(partition by isin), comme on ne spécifié pas d'order by, toutes les lignes de la partition sont utilisées par la fonction analytique qui calcule donc la somme totale.

La clause de fenêtrage indique l'ensemble des lignes sur laquelle doit être appliquée la fonction. Si une clause de fenêtrage est spécifiée, une clause d'ordre doit obligatoirement l'être aussi.
La clause de fenêtrage est de la forme :

{ ROWS | RANGE }
{ BETWEEN
{ UNBOUNDED PRECEDING
| CURRENT ROW
| value_expr { PRECEDING | FOLLOWING }
}
AND
{ UNBOUNDED FOLLOWING
| CURRENT ROW
| value_expr { PRECEDING | FOLLOWING }
}
| { UNBOUNDED PRECEDING
| CURRENT ROW
| value_expr PRECEDING
}
}

ROWS indique qu'on définit une fenêtre en termes de ligne, par exemple. ROWS 5 PRECEDING signifie qu'on prend toutes les lignes comprises entre la ligne courante et celles qui précèdent jusqu'à la 5e incluse.
RANGE définit une fenêtre en terme de valeur : par exemple pour avoir les dates à moins de 100 jours d'une date de référence indiquée dans la clause order by on utiliserait RANGE 100 PRECEDING.
RANGE ne fonctionne qu'avec des colonnes de type numérique ou date; la colonne de référence est celle indiquée dans le order by, il ne peut y avoir qu'une unique colonne dans le order by.

Comme la clause de fenêtrage n'est pas très explicite, on va essayer de mieux la comprendre à partir de plusieurs exemples :

- ROWS 100 PRECEDING : on prend les lignes comprises entre la ligne courante et celles qui la précèdent jusqu'à la 100e incluse.

- CURRENT ROW : la fenêtre est réduite à la ligne courante

- ROWS UNBOUNDED PRECEDING : on prend toutes les lignes depuis le début de la partition jusqu'à la ligne courante incluse.

- ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW : c'est la même chose que l'exemple précédent

- ROWS BETWEEN CURRENT ROW AND 5 FOLLOWING : on prend toutes les lignes depuis la ligne courante ainsi que les suivantes jusqu'à la 5e incluse

III - Les fonctions analytiques

On va présenter ici les fonctions analytiques les plus courantes. Pour avoir la liste complète, reportez-vous à la documentation Oracle.

III-A - AVG

C'est la moyenne, on l'a déjà utilisée sur un exemple.

III-B - LAG et LEAD

LAG permet de « regarder » en arrière tandis que LEAD permet de « regarder » en avant. La requête suivante nous permet d'obtenir le dernier cours de la veille ainsi que le cours de clôture du lendemain :

select   isin, dte, clo,
lag(clo, 1) over(partition by isin order by dte) veille,
lead(clo, 1) over(partition by isin order by dte) lendemain
from     COTATION
where    isin = 'FR0000130007'

ISIN                 DTE             CLO     VEILLE  LENDEMAIN
-------------------- -------- ---------- ---------- ----------
FR0000130007         02/01/02      18,95                 20,67
FR0000130007         03/01/02      20,67      18,95      20,79
FR0000130007         04/01/02      20,79      20,67       20,9
FR0000130007         07/01/02       20,9      20,79      20,15
FR0000130007         08/01/02      20,15       20,9      20,05

Seul le premier paramètre de ces deux fonctions est obligatoire et est la valeur du passé ou du futur qu'on souhaite renvoyer; le second est la position de la ligne en avant ou en arrière qu'on souhaite interroger, s'il n'est pas renseigné sa valeur par défaut est 1. Ici on a utilisé 1 pour la veille, on aurait utilisé 2 pour l'avant-veille et ainsi de suite. Si la ligne référencée n'existe pas, un null est renvoyé. On peut également passer un troisième paramètre à ces fonctions indiquant la valeur à substituer dans ce cas.

Exemple :

select   dte, isin, clo,
lag(clo, 5, -1) over(partition by isin order by dte) cours_il_y_a_5j
from     COTATION
where    isin = 'FR0000130007'

DTE      ISIN                        CLO COURS_IL_Y_A_5J
-------- -------------------- ---------- ---------------
02/01/02 FR0000130007              18,95              -1
03/01/02 FR0000130007              20,67              -1
04/01/02 FR0000130007              20,79              -1
07/01/02 FR0000130007               20,9              -1
08/01/02 FR0000130007              20,15              -1
09/01/02 FR0000130007              20,05           18,95
10/01/02 FR0000130007              19,26           20,67
11/01/02 FR0000130007              19,34           20,79
14/01/02 FR0000130007               18,5            20,9
15/01/02 FR0000130007               19,1           20,15
...

La 4e colonne est le cours de clôture d'il y a 5 jours et si la ligne n'existe pas, -1 est retourné au lieu de NULL.

III-C - CORR

CORR renvoie le coefficient de corrélation entre deux variables. Ce coefficient est compris entre -1 (corrélation négative, les deux variables varient en sens inverse) et +1 (corrélation positive, les deux variables varie dans le même sens).

La requête suivante indique les sociétés dont les variations sont le plus corrélées avec Alcatel (isin = 'FR0000130007') :

select
lib_action.libelle,
correlation.corr
from
(
select distinct
action.isin,
corr(alcatel.variation, action.variation) over(partition by action.isin) corr
from
(
select   isin, dte, (clo-veille)/veille variation
from
(
select   isin, dte, clo,
lag(clo) over(partition by isin order by dte) veille
from     COTATION
where    isin = 'FR0000130007'
)
) alcatel,
(
select   isin, dte, (clo-veille)/veille variation
from
(
select   isin, dte, clo,
lag(clo) over(partition by isin order by dte) veille
from     COTATION
)
) action
where
action.dte = alcatel.dte
) correlation,
action lib_action
where
lib_action.isin = correlation.isin
order by 2 desc

Décortiquons cette requête.

La sous-requête « alcatel » calcule le pourcentage de variation par rapport à la veille pour Alcatel :

select   isin, dte, (clo-veille)/veille variation
from
(
select   isin, dte, clo,
lag(clo) over(partition by isin order by dte) veille
from     COTATION
where    isin = 'FR0000130007'
)

De même la sous-requête « action » calcule le pourcentage de variation par rapport à la veille pour toutes les sociétés disponibles. corr(alcatel.variation, action.variation) over(partition by action.isin) : calcule le coefficient de corrélation entre les variations d'alcatel et les variations des autres sociétés. Ce qui nous intéresse c'est un résultat au niveau société d'où le partition by action.isin. Voici les résultats par curiosité :

LIBELLE                    CORR
-------------------- ----------
ALCATEL A                     1
STMICROELEC.SICO.    ,685408305
CAP GEMINI           ,653326575
LVMH MOET VUITTON     ,58905202
DEUTSCHE BANK ()     ,588514958
PINAULT PRINTEMPS    ,574360425
...

La première ligne Alcatel ne doit pas nous étonner connaissant la définition du coefficient de corrélation, si on n'avait pas eu Alcatel parfaitement corrélée avec elle-même on aurait pu s'inquiéter des résultats de notre requête.

Alcatel est positivement corrélée avec STM et Cap Gemini, ce sont toutes les trois des valeurs technologiques…

III-D - MAX et MIN

Ces fonctions sont les mêmes que celles utilisées habituellement mais dans un contexte « analytique » :

select   isin, dte, clo,
max(clo) over(partition by isin order by dte rows 5 preceding) max_5,
min(clo) over(partition by isin order by dte rows 5 preceding) min_5
from     COTATION
where    isin = 'FR0000130007'
order by isin, dte

ISIN                 DTE             CLO      MAX_5      MIN_5
-------------------- -------- ---------- ---------- ----------
FR0000130007         02/01/02      18,95      18,95      18,95
FR0000130007         03/01/02      20,67      20,67      18,95
FR0000130007         04/01/02      20,79      20,79      18,95
FR0000130007         07/01/02       20,9       20,9      18,95
FR0000130007         08/01/02      20,15       20,9      18,95
FR0000130007         09/01/02      20,05       20,9      18,95
FR0000130007         10/01/02      19,26       20,9      19,26
FR0000130007         11/01/02      19,34       20,9      19,26
FR0000130007         14/01/02       18,5       20,9       18,5
...

Cette requête nous retourne le maximum et le minimum du cours de clôture sur les 6 derniers jours de bourse (ligne en cours et les 5 précédentes).

Remarques :

- comme ici on se restreint à un seul ISIN, il est inutile de mettre les partitions by isin mais c'est pour la complétude de l'exemple

- la clause order by isin, dte est inutile pour le calcul mais c'est pour mieux comprendre le résultat de la requête à l'affichage.

III-E - RATIO_TO_REPORT

Cette fonction prend un paramètre et renvoie la part que représente ce paramètre par rapport à la somme des paramètres des autres lignes telles que définies dans la clause where.

Ce sera sans doute plus clair sur un exemple : supposons que pour la date du 01/10/2004 on souhaite avoir les sociétés qui ont le plus contribué aux échanges sur la place boursière de Paris.
On estime le volume en euros de la journée par le produit entre le volume total de la journée par le derniers cours.

Plus précisément on souhaite avoir la part du volume de la société par rapport au volume total ; c'est ce que nous retourne la requête suivante :

select   b.libelle societe,
volume*clo volume_euro,
round(100*ratio_to_report(volume*clo) over (), 2) pct_volume_total
from     COTATION a, ACTION b
where    a.dte = '01/10/2004' and
a.isin = b.isin
order by 3 desc

SOCIETE                      VOLUME_EURO PCT_VOLUME_TOTAL
-------------------- ------------------- ----------------
TOTAL                     614.687.922,00            12,78
SANOFI-AVENTIS            522.629.474,40            10,86
FRANCE TELECOM            270.720.306,30             5,63
BNP PARIBAS               230.601.434,40             4,79
VIVENDI UNIVERSAL         206.701.003,74             4,30
CARREFOUR                 189.695.401,74             3,94
AXA                       188.202.814,96             3,91
...

On ne retrouve que des sociétés du CAC 40… et Total contribue à quasiment 13 % des échanges de la journée.
Comme on souhaitait avoir la part de chaque société par rapport au volume total, on n'a rien spécifié dans la clause over.

III-F - STDDEV et VARIANCE

VARIANCE donne la variance et STDDEV l'écart-type. Ce sont des mesures de la volatilité.

Calculons la volatilité quotidienne de l'action Alcatel :

select   isin, dte,
stddev((clo-veille)/veille) over(partition by isin order by dte) volatilite
from
(
select   isin, dte, clo,
lag(clo) over(partition by isin order by dte) veille
from     COTATION
where    isin = 'FR0000130007'
)
order by isin, dte

ISIN            DTE      VOLATILITE
--------------- -------- ----------
FR0000130007    02/01/02
FR0000130007    03/01/02        ,00
FR0000130007    04/01/02        ,06
FR0000130007    07/01/02        ,05
FR0000130007    08/01/02        ,05
FR0000130007    09/01/02        ,05
FR0000130007    10/01/02        ,05
FR0000130007    11/01/02        ,04
FR0000130007    14/01/02        ,04
FR0000130007    15/01/02        ,04
FR0000130007    16/01/02        ,04
...

Si on prend la volatilité calculé le 16/01/02, on trouve 4 %; cette volatilité est calculée comme l'écart-type des variations d'Alcatel sur toutes les lignes précédentes (selon la clause over).

III-G - NTILE

La fonction NTILE ordonne les lignes en N packets, N étant un entier passé en paramètre. La clause order by est donc obligatoire. NTILE renvoie pour chaque ligne le numéro du paquet auquel cette ligne appartient.

Si on considère la journée du 01/10/04 et qu'on veut classer les sociétés en 10 catégories selon la variation qu'elles ont subie, on peut faire :

select   action.libelle, variation,
ntile(10) over(order by variation) categorie
from
(
select   isin, dte,
(clo-veille)/veille variation
from
(
select   isin, dte, clo,
lag(clo) over(partition by isin order by dte) veille
from     COTATION
)
) variation,
action
where
variation.dte = '01/10/2004' and
variation.isin = action.isin

SOCIETE              VARIATION  CATEGORIE
-------------------- --------- ----------
ALTEN                     -,02          1
ALSTOM                    -,02          1
EURONEXT NV               -,02          1
GUYENNE GASCOGNE          -,02          1
SEB                       -,02          1
...
IPSOS                     -,00          2
FIMALAC                   -,00          2
HERMES INTL               -,00          2
RODRIGUEZ GROUP            ,00          2
...
MICHELIN                   ,03          9
DEUTSCHE BANK ()           ,03          9
PEUGEOT                    ,03          9
DASSAULT SYSTEMES          ,03          9
...
ALCATEL A                  ,05         10
VALTECH                    ,08         10
RHODIA                     ,23         10

Ça a été une très bonne journée puisque Rhodia a gagné 23 %

III-H - ROW_NUMBER, RANK, DENSE_RANK et COUNT

Ces fonctions permettent un peu comme ROWNUM d'affecter un indice à une ligne en fonction de son rang.
ROW_NUMBER donne un indice unique à une ligne.
RANK peut donner le même indice à plusieurs lignes mais renvoie des « trous » dans la plage d'indice renvoyée tandis que DENSE_RANK fonctionne pareil que RANK mais ne renvoie pas de « trous ».
COUNT fonctionne comme le count version agrégat mais en respectant la clause over.

On va voir ces différences sur un exemple pas très utile par ailleurs :

select   libelle, premiere_lettre,
row_number() over(order by premiere_lettre) RNUM,
rank() over(order by premiere_lettre) RANK,
dense_rank() over(order by premiere_lettre) DENSE_RNK,
count(*) over(order by premiere_lettre) CNT
from
(
select   libelle, substr(libelle, 1, 1) premiere_lettre
from     ACTION
)
order by 1

LIBELLE              P       RNUM       RANK  DENSE_RNK        CNT
-------------------- - ---------- ---------- ---------- ----------
A NOVO               A         10          1          1         16
ACCOR                A          3          1          1         16
AGF                  A          6          1          1         16
AIR FRANCE - KLM     A         15          1          1         16
AIR LIQUIDE          A          2          1          1         16
ALCAN                A          1          1          1         16
ALCATEL A            A          7          1          1         16
ALSTOM               A          5          1          1         16
ALTADIS              A         16          1          1         16
ALTEN                A         11          1          1         16
ALTRAN TECHN.        A          9          1          1         16
ARCELOR              A         13          1          1         16
ATOS ORIGIN          A         14          1          1         16
AUTOROUTES DU SUD DE A          8          1          1         16
AVENIR TELECOM       A         12          1          1         16
AXA                  A          4          1          1         16
BACOU - DALLOZ       B         18         17          2         24
BENETEAU             B         21         17          2         24
BIC                  B         20         17          2         24
BNP PARIBAS          B         22         17          2         24
BOLLORE              B         23         17          2         24
BONDUELLE            B         19         17          2         24
BOUYGUES             B         17         17          2         24
BUSINESS OBJECTS     B         24         17          2         24
CAC 40 MASTER UNIT   C         27         25          3         36
CANAL +              C         32         25          3         36
CAP GEMINI           C         33         25          3         36
CARBONE LORRAINE     C         28         25          3         36
CARREFOUR            C         25         25          3         36
...

On constate que :

- chaque ligne a un numéro unique avec row_number.

- plusieurs lignes peuvent partager le même indice obtenu avec rank et dense_rank mais avec dense_rank l'indice suivant est forcément consécutif.

III-I - FIRST et LAST

Ce sont des fonctions analytiques qui renvoient la valeur d'une expression pour le premier ou le dernier élément d'un ensemble de ligne.
En fait on va utiliser ici une fonction agrégat mais le lecteur pourra se reporter à la documentation Oracle pour plus d'explications sur la version analytique.

Comme d'habitude, on va voir sur un exemple comme elles fonctionnent : on cherche à savoir pour chacune des sociétés quelle est le minimum historique ainsi que la date à laquelle ce minimum a été atteint :

select   b.libelle, min(a.clo),
min(dte) keep (dense_rank first order by a.clo)
from     COTATION a, ACTION b
where    a.isin = b.isin
group by b.libelle
order by 1

LIBELLE              MIN(A.CLO) MIN(DTE)
-------------------- ---------- --------
A NOVO                      ,44 13/03/03
ACCOR                     25,32 31/03/03
AGF                       21,27 12/03/03
AIR FRANCE - KLM           7,12 03/10/02
AIR LIQUIDE               98,91 29/01/03
ALCAN                     30,96 17/05/04
ALCATEL A                   2,3 23/09/02
ALSTOM                      ,32 11/08/04
ALTADIS                   17,68 07/01/02
ALTEN                       3,6 24/09/02
ALTRAN TECHN.              2,13 10/03/03
...

min(dte) keep (dense_rank first order by a.clo) : cette expression ordonne les cours de clôture à l'intérieur pour une société donnée (group by b.libelle) par ordre croissant est renvoie pour le premier cours de clôture obtenue (dense_rank first) la date minimum à laquelle ce cours a été atteint.
On est obligé d'utiliser min(dte) car si le minimum historique a été atteint à plusieurs dates, on ne peut en retourner qu'une seule.

III-J - FIRST_VALUE et LAST_VALUE

Jusqu'à présent on a travaillé sur des clauses analytiques ROW et pas RANGE car on a plus souvent besoin d'accéder aux lignes précédentes. Ici on va utiliser RANGE pour présenter les fonctions FIRST_VALUE et LAST_VALUE qui renvoie la première et la dernière valeur d'une expression pour une fenêtre donnée :

En considérant uniquement la société Alcatel, on souhaite avoir pour une date donnée le minimum et le maximum du dernier cours dans un intervalle de deux jours avant ou après cette date (les dates de cotations ne sont pas forcément contigües) :

select   isin, dte, clo,
min(clo) over(partition by isin order by dte range between 2 preceding and 2 following) min_2j,
max(clo) over(partition by isin order by dte range between 2 preceding and 2 following) max_2j,
first_value(dte) over(partition by isin order by dte range between 2 preceding and 2 following) debut,
last_value(dte) over(partition by isin order by dte range between 2 preceding and 2 following) fin
from     COTATION
where    isin = 'FR0000130007'
order by isin, dte

ISIN                 DTE             CLO     MIN_2J     MAX_2J DEBUT    FIN
-------------------- -------- ---------- ---------- ---------- -------- --------
FR0000130007         02/01/02      18,95      18,95      20,79 02/01/02 04/01/02
FR0000130007         03/01/02      20,67      18,95      20,79 02/01/02 04/01/02
FR0000130007         04/01/02      20,79      18,95      20,79 02/01/02 04/01/02

FR0000130007         07/01/02       20,9      20,05       20,9 07/01/02 09/01/02
FR0000130007         08/01/02      20,15      19,26       20,9 07/01/02 10/01/02
FR0000130007         09/01/02      20,05      19,26       20,9 07/01/02 11/01/02
FR0000130007         10/01/02      19,26      19,26      20,15 08/01/02 11/01/02

FR0000130007         11/01/02      19,34      19,26      20,05 09/01/02 11/01/02
FR0000130007         14/01/02       18,5      18,01       19,1 14/01/02 16/01/02
FR0000130007         15/01/02       19,1      18,01       19,1 14/01/02 17/01/02
FR0000130007         16/01/02      18,01      17,78       19,1 14/01/02 18/01/02
FR0000130007         17/01/02      18,15      17,78       19,1 15/01/02 18/01/02
FR0000130007         18/01/02      17,78      17,78      18,15 16/01/02 18/01/02
FR0000130007         21/01/02         17       16,7      17,06 21/01/02 23/01/02
...

Si on se place le 08/01/02, on cherche à savoir le minimum et le maximum des clôtures entre le 06/01/02 et le 10/01/02. On voit que le 05/01 et le 06/01 il n'y a pas eu de cotation. Les fonctions first_value et last_value nous indique les dates de début et de fin de la fenêtre en cours et nous permettent donc de la visualiser.

Si on modifie légèrement cette requête :

select   isin, dte, clo,
first_value(dte) over(partition by isin order by dte range between 2 preceding and 2 following) date_debut,
first_value(clo) over(partition by isin order by dte range between 2 preceding and 2 following) clo_debut
from     COTATION
where    isin = 'FR0000130007'
order by isin, dte

On obtient :

ISIN                 DTE             CLO DATE_DEB  CLO_DEBUT
-------------------- -------- ---------- -------- ----------
FR0000130007         02/01/02      18,95 02/01/02      18,95
FR0000130007         03/01/02      20,67 02/01/02      18,95
FR0000130007         04/01/02      20,79 02/01/02      18,95
FR0000130007         07/01/02       20,9 07/01/02       20,9
FR0000130007         08/01/02      20,15 07/01/02       20,9
FR0000130007         09/01/02      20,05 07/01/02       20,9
FR0000130007         10/01/02      19,26 08/01/02      20,15
FR0000130007         11/01/02      19,34 09/01/02      20,05
...

Ici first_value(clo) over(partition by isin order by dte range between 2 preceding and 2 following) nous permet d'obtenir le cours de clôture du premier jour de la fenêtre, ce qui est très utile et évite une jointure supplémentaire pour obtenir cette information.

III-K - PERCENTILE_CONT et PERCENTILE_DISC

Ce sont des fonctions qui permettent le calcul de médianes, déciles, quartiles… PERCENTILE_CONT fait un calcul de manière continue (le plus utilisé) tandis que PERCENTILE_DISC le fait de manière discrète.
De la même manière que pour les fonctions FIRST et LAST présentées ci-dessus, on va utiliser ici l'équivalent agrégat de la fonction PERCENTILE_CONT, pour calculer le cours médian, le premier et le dernier décile des sociétés que nous suivons :

select   b.libelle,
percentile_cont(0.5) within group (order by clo) mediane,
percentile_cont(0.10) within group (order by clo) decile_1,
percentile_cont(0.90) within group (order by clo) decile_9
from     COTATION a, ACTION b
where    a.isin = b.isin
group by b.libelle
order by 1

LIBELLE                 MEDIANE   DECILE_1   DECILE_9
-------------------- ---------- ---------- ----------
A NOVO                     ,905        ,71     11,808
ACCOR                    34,055     28,895     42,393
AGF                      43,545     28,675     52,785
AIR FRANCE - KLM         13,285      9,635     18,174
AIR LIQUIDE              123,91    114,027     135,45
ALCAN                      35,3       32,5     37,647
ALCATEL A                 10,18      4,893     15,225
ALSTOM                     2,66       ,626     12,082
ALTADIS                  22,265     20,493     25,597
ALTEN                    11,025      6,253     15,927
...

Si on considère A Novo, 50% des clôtures sont au dessus de 0.905 et 50 % sont en dessous. Seul 10% des clôtures sont au dessous de 0.71 et les 10% de cotations les plus hautes sont au dessus de 11.80. Ah qu'il est loin le temps où A Novo était à 11.80

percentile_cont(0.5) within group (order by clo) : 0.50 indique le pourcentage (ici 0.50 correspond à la médiane). within group (order by clo) indique qu'à l'intérieur d'un groupe définit par le group by, on ordonne par clo croissant.

IV - Annexes

Le lecteur qui souhaite essayer les exemples fournis dans ce document pourra utiliser les scripts suivants pour créer les tables :

-- historique des cotations
create table COTATION (
isin    varchar2(20),
dte     date,
ouv     number,
max     number,
min     number,
clo     number,
volume  number,
constraint pk_cotation primary key (isin, dte)
);

-- table de nomenclature des sociétés
create table ACTION (
isin     varchar2(20),
libelle  varchar2(100),
constraint pk_action primary key (isin)
);

- Pour une alimentation rapide mais fictive de ces tables :

insert into ACTION
select   'FR' || rownum, 'SOCIETE ' || rownum
from     ALL_OBJECTS
where    rownum < 50

insert into COTATION
select
isin,
dte,
round(100*dbms_random.value, 2),
round(100*dbms_random.value, 2),
round(100*dbms_random.value, 2),
round(100*dbms_random.value, 2),
round(100000*dbms_random.value)
from
ACTION,
(
select   sysdate-rownum dte
from     ALL_OBJECTS
where    rownum < 200
)

Pour une alimentation réelle, le lecteur pourra utiliser les fichiers de contrôle SQL*Loader suivants :

- pour alimentation de la table COTATION

load data
append
into table COTATION
fields terminated by ";"
(
isin,
dte      date "dd/mm/yy",
ouv      "replace(:ouv, '.', ',')",
max      "replace(:max, '.', ',')",
min      "replace(:min, '.', ',')",
clo      "replace(:clo, '.', ',')",
volume   "replace(:volume, '.', ',')"
)

Il faut ensuite télécharger l'historique des cotations sur http://download.abcbourse.com/altistock_histo.aspx : cochez SRD, spécifiez la plage de dates (par tranche de 2 mois) puis cliquez sur le bouton « Télécharger ».
Il faut répéter l'opération plusieurs fois pour avoir un petit historique et pour chaque fichier télécharger lancer SQL*Loader.

- pour alimentation de la table ACTION

load data
truncate
into table ACTION
fields terminated by '|' trailing nullcols
(
isin,
libelle
)

Il faut ensuite télécharger les données sur http://www.boursorama.com/outils/telechargement/telechargement.phtml en cochant ISIN et SRD puis en cliquant sur le bouton « Télécharger ».
Il faut ensuite charger ces données avec SQL*Loader.

V - Conclusion

J'espère que ce document vous a permit de mieux comprendre ces fonctions exotiques et vous permettra de penser « analytique » lors de l'écriture de vos requêtes.

Je remercie SheikYerbouti de m'avoir encouragé à écrire cet article ainsi que pour l'aide qu'il m'a apportée.
Je remercie également et félicite l'équipe de Developpez.net pour la qualité de leur site et de leurs forums francophones.

Laly KATTOOR.