Fonctions analytiques (méthode)

Bonjour,

Ce n'est pas forcément une explication des fonctions analytiques que je recherche mais c'est plutôt la méthode à utiliser pour arriver à mon résultat.


Les données :


Pièce jointe 307113

je souhaiterais obtenir les 2 lignes mises en verte.
J'ai utilisé dans mon code SQL (requête intermédiaire) les 2 fonctions : lag et lead. Pour obtenir la valeur précédente et suivante de l'indicateur.

Code:

---

1 2 3 4

SELECT NM_EC, DT_MAJ_EI, DT_HISTO_EI, ID_EVT, INDICATEUR , Lag(Nvl(INDICATEUR,'(NR)')) OVER (PARTITION BY NM_EC ORDER BY DT_HISTO_EI, DT_MAJ_EI) AS INDICATEUR_PREC , Lead(Nvl(INDICATEUR,'(NR)')) OVER (PARTITION BY NM_EC ORDER BY DT_HISTO_EI, DT_MAJ_EI) AS INDICATEUR_PREC FROM ...

---

Maintenant je voudrais avoir votre avis pour appliquer les règles de gestion ci-dessous et n'obtenir que les 2 lignes surlignées en verte :

Règles de gestion*:
Si on n'a pas de changement dans la valeur de l'indicateur pour un même numéro EC et une même date histo alors on garde l'ancien élément, donc le plus petit ID_EVT
En revanche Si on a une évolution de l'indicateur pour un même numéro EC et une même date histo c'est la ligne sur laquelle il y a eu le dernier changement (max de de l'ID_EVT)

Vous avez une solution à me proposer ?

Au cas où le fichier ne s'affiche pas :

Résultat à obtenir (lignes surlignées en verte)
Numéro EC Date Histo Date MAJ ID_EVT Indicateur
1016210035574 05/09/16 19/10/16 71010 010111
1016320050330 14/09/16 14/09/16 62441 111111

Étape intermédiaire (ma requête)

Numéro EC Date Histo Date MAJ ID_EVT Indicateur Indicateur PREC Indicateur SUIV
1016210035574 05/09/16 19/10/16 71008 11111 (null) 010111
1016210035574 05/09/16 19/10/16 71010 010111 11111 (null)
1016320050330 14/09/16 14/09/16 62286 111111 (null) 111111
1016320050330 14/09/16 14/09/16 62441 111111 111111 (null)

Le résultat que j'obtient et qui n'est pas correct est :

Numéro EC Date Histo Date MAJ ID_EVT Indicateur
1016210035574 19/10/16 05/09/16 71008 11111
1016210035574 19/10/16 05/09/16 71010 10111 -> Ici l'indicateur à changé donc c'est cette ligne uniquement qui doit apparaitre et non celle avec id_evt = 71008
1016320050330 14/09/16 14/09/16 62286 111111


Merci pour vos réponses et n'hésitez pas à me poser des questions

Cdlt,

Sakaupat

Dans l'ordre, je me dis que la requête va ressembler à :

Code:

---

1 2 3

Select ... from Ma_Table group by NM_EC

---

En effet, on veut une ligne (et une seule, d'où le GROUP BY) pour chaque valeur de NM_EC.
Et pas de clause Where, car on n'a pas de filtre sur les lignes.

LAG ou LEAD : ça permet de renvoyer la valeur de la ligne suivante ou précédente... ça ne convient pas ici. Si pour un NM_EC, tu as 10 enregistrements, tu ne veux pas le précédent ou le suivant, mais le dernier (ou le premier). Et en plus, LAG ou LEAD sont plus ou moins incompatibles avec un Group By.

Je verrais plus du Min() ou Max(), et probablement, du Min() Keep (Dense_Rank First order by )

Bonjour,

Merci tout d'abord pour tes réponses.

Est-ce que je pourrais t'envoyer à titre privée la requête qui me pose problème ?

Ca serait plus simple dans un premier temps pour comprendre cela.
je peux ensuite mettre les résultats intermédiaires dans la discussion avec un début de requête + ... + une fin de requête.

Tel :

Code:

---

1 2 3 4 5 6 7

SELECT Nvl(g.NM_EC, i.NM_EC) AS NM_EC_INIT , Nvl(g.DT_MAJ_EC, i.DT_MAJ_EC) AS DT_MAJ_EC_INIT , Nvl(g.DT_HISTO_EC, i.DT_HISTO_EC) AS DT_HISTO_EC_INIT .... FROM w_niveau g FULL OUTER JOIN w_ip_niveau i ON(g.NM_EC=i.NM_ECAND g.DT_HISTO_EC_INIT=i.DT_HISTO_EC_) WHERE Nvl(g.NM_EC, i.NM_EC )IN (<valeurs>)

---

Merci.

Ici, tu as une requête qui te renvoie tes 4 lignes (la 2ème partie du dessin). C'est peut-être une requête toute simple, c'est peut-être une requête compliquée, peu importe.
Tu peux utiliser le résultat de cette requête comme si c'était une simple table :

Code:

---

1 2 3

select NM_EC, ... ... from ( la requete qui renvoie le détail des 4 lignes ) group by NM_EC

---

Après, pour les colonnes à afficher, je suis à peu près convaincu que c'est du min( ... ) keep (dense_rank first order by ... )
Mais pour aller plus loin, il faudrait s'investir nettement plus. Regarde la documentation sur cette fonction oracle.

Bonjour,

J'ai réussi à m'en sortir avec la fonction DENSE_RANK :
,DENSE_RANK() over (partition by NM_EC, DT_HISTO ORDER BY ID_EVT DESC) AS RANG_NIV1
Et je choisi ensuite RANG=1

Seul hic c'est que j'ai besoin de faire un 2ème filtre, un RANG_NIV2

Et quand j'applique le premier filtre, l'enreg avec RANG_NIV2 que je souhaite est passé aux oubliettes

Exemple :
Pièce jointe 307699

Ici pour le NUM_EC 1016210034706 ma première sélection se porte sur le rang 1 surligné en jaune
Or je veux récupérer la ligne verte ...



Fonctionnellement il faut que je trie de façon descendante pour un même NM_EC / DT_HISTO_EC et on prend la date la plus récente (12/09/2016) lorsque l'indicateur n'a pas changé.
Or dans ce cas après un triplet, on a un duo avec le couple NM_EC / DT_HISTO_EC / DT_MAJ_EC et on doit prendre le premier ayant l'indicateur qui n'a pas évolué (111111), donc ID_EVT = 61495

Une idée ?

Merci pour votre aide