over partition by et dense_rank()
Voici un exemple simple et facile pour illustrer l’utilisation des puissantes fonctions d’aggrégation Oracle : over partition by et dense_rank()
Un table client, contient 3 champs : numéro client, identifiant du séjour et date d’entrée :
à partir de cette table, on souhaite obtenir les 3 informations suivantes :
- ordre du séjour dans le temps pour le client
- premier séjour
- dernier séjour
On va ainsi écrire la requête suivante :
select NUM_CLIENT, ID_SEJOUR, DAT_ENT, dense_rank() over(partition by NUM_CLIENT order by DAT_ENT) RANG, case when dense_rank() over(partition by NUM_CLIENT order by DAT_ENT)=1 THEN 1 ELSE 0 end PREM_SEJ, max(DAT_ENT) over(partition by NUM_CLIENT order by DAT_ENT desc) MAX_DAT, case when DAT_ENT = max(DAT_ENT) over(partition by NUM_CLIENT order by DAT_ENT desc) THEN 1 ELSE 0 end DER_SEJ from WRK_CLIENT;
Pour plus de lisibilité, on peut aussi écrire la requête ainsi :
WITH REQ_CLIENT as ( select NUM_CLIENT, ID_SEJOUR, DAT_ENT, dense_rank() over(partition by NUM_CLIENT order by DAT_ENT) RANG from WRK_CLIENT ) select NUM_CLIENT, ID_SEJOUR, DAT_ENT, RANG, case when RANG=1 THEN 1 ELSE 0 end PREM_SEJ, case when DAT_ENT = max(DAT_ENT) over(partition by NUM_CLIENT order by DAT_ENT desc) THEN 1 ELSE 0 end DER_SEJ from REQ_CLIENT order by DAT_ENT
Et on obtiendra le résultat suivant :
Voir aussi :
Oracle – un like multiple
TOAD : quelques raccourcis utiles et peu connus de l’éditeur SQL