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, dteAvec 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 COTATIONOutre le fait que la seconde requête est beaucoup plus concise et explicite, comparons les performances avec TkProf :
-
requête « standard » :
Sélectionnezcall 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 :
Sélectionnezcall 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 COTATIONavg : 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, dteISIN 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 62832774Quand 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,05Seul 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 descDé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, dteISIN 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 descSOCIETE 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, dteISIN 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.isinSOCIETE 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 1LIBELLE 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 1LIBELLE 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, dteISIN 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, dteOn 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 1LIBELLE 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.