fonction d’aggrégation Oracle : premier, dernier et rang dans un bloc

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 :

Script pour créer WRK CLIENT

à partir de cette table, on souhaite obtenir les 3 informations suivantes :

1. ordre du séjour dans le temps pour le client
2. premier séjour
3. 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