Script SQL selon algorithme

Version imprimable

16/04/2008, 14h39
aurelie83

2 pièce(s) jointe(s)

Script SQL selon algorithme
Bonjour,
J'ai requête à faire selon un algo...
En fait, je dois ajouter une colonne "test" dans ma table selon les resultats de la requete.
J'ai une table qui historise les données de cette façon:
cf pièce jointe : test.jpg

Mon objectif est de créer une colonne "test" qui marque :
- "1" si pour le MAX(update_ID), j'ai TYPE_O = "A"
- "2" si pour le MAX(update_ID), j'ai TYPE_O = "B"
Sinon il faut regarder les lignes au-dessus (l'historique de l'ID) jusqu'a ce que l'on obtienne:
- Type_O = "A" donc "test"=3
- Type_O = "B" donc "test"=4

Je ne sais pas si je suis claire...
J'obtiens correctement test = 1 ou 2 avec cette requete:
Code:

1 2 3 select distinct ID, MAX(UPDATE_ID), TYPE_O, DATE_O, decode(TYPE_O,'A',1, 'C',2) test from historique group by ID, TYPE_O, DATE_O, decode(TYPE_O,'A',1, 'B',2)
mais je ne sais pas trop comment faire lire l'historique pour les 2 autres cas...

voici ce que je veux obtenir: cf piece jointe: test_resultat.jpg

qqn peut me donner des pistes?
Je ne sais pas si avec les fonctions analytiques, ca peut faire avancer des choses?

Merci d'avance!
Ps: j'ai oublié d'indiquer que je suis en Oracle 9i
16/04/2008, 16h15
aurelie83

Personne n'a d'idée?
17/04/2008, 14h35
aurelie83

Tout le monde est en vac :(

Moi je dirais un truc du style.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
select   t1.id, t1.update_id, t1.date_o, t1.update_o,
         decode (t1_update_id,t3.update_id,decode(type_o,'A',1,2),decode(type_o,'A',3,4))
 
from     MATABLE t1,
         (select   id, max(update_id) update_id
          from     MATABLE
          where    type_o in ('A','B')
          group by id) t2,
         MATABLE t3
 
where    t1.id=t3.id
and      t3.id=t2.id
and      t3.update_id=t2.update_id
 
order by t1.id, t1.update_id

17/04/2008, 16h07
aurelie83

Ah oui, je vais tenter ça...je vous tiens au courant.
Merci d'avoir répondu!

Ca marche à peu pres si je fais ça:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 SELECT t1.id, t1.update_id, t1.date_o, t1.type_o, decode (t1.type_o,'A',1,'B',2,decode(t2.type_o,'A',3,'A5',2)) test FROM MATABLE t1, (SELECT id, max(update_id) update_id,type_o FROM historique WHERE type_o IN ('A','B') GROUP BY id, type_o) t2, historique t3 WHERE t1.id=t3.id AND t3.id=t2.id AND t3.update_id=t2.update_id ORDER BY t1.id, t1.update_id
Par contre pour un cas, j'ai une erreur.
Dans le cas où j'ai, dans ma table d'historique, 'A' et 'B' à suivre (ou vice-versa).
Par exemple :
Id-----update_id-----date_o-----type_o
1-----45-----01/01/2007-----A
1-----46-----02/01/2007-----A
1-----47-----03/01/2007-----B
1-----48-----04/01/2007-----XXX

Et bien cette requête, du coup m'affiche, 2 lignes pour cet ID, alors que j'en veux qu'une: le 'B'.
En fait, je veux le type_o pour chaque id selon le max(update_id).

J'avais un peu de temps libre au boulot,

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
WITH t AS (SELECT 933986296 AS ID, 0 AS update_id, TRUNC(SYSDATE) AS date_o, 'B' AS type_o FROM dual
	UNION ALL
	SELECT 933986296 AS ID, 1 AS update_id, TRUNC(SYSDATE) AS date_o, 'B' AS type_o FROM dual
	UNION ALL
	SELECT 1012731924 AS ID, 0 AS update_id, TRUNC(SYSDATE) AS date_o, 'A' AS type_o FROM dual
	UNION ALL
	SELECT 1012731924 AS ID, 1 AS update_id, TRUNC(SYSDATE) AS date_o, 'XXX' AS type_o FROM dual
	UNION ALL
	SELECT 1012731924 AS ID, 2 AS update_id, TRUNC(SYSDATE) AS date_o, 'XXX' AS type_o FROM dual
	UNION ALL
	SELECT 812732074 AS ID, 11 AS update_id, TRUNC(SYSDATE) AS date_o, 'A' AS type_o FROM dual
	UNION ALL
	SELECT 812732074 AS ID, 12 AS update_id, TRUNC(SYSDATE) AS date_o, 'B' AS type_o FROM dual
	UNION ALL
	SELECT 812732074 AS ID, 18 AS update_id, TRUNC(SYSDATE) AS date_o, 'XXX' AS type_o FROM dual)
SELECT ID, update_id, date_o, type_o, 
		(CASE WHEN last_type > 0
		THEN last_type
		WHEN MAX(upd_A) OVER (PARTITION BY ID) > NVL(MAX(upd_B) OVER (PARTITION BY ID),-1)
		THEN 3
		WHEN MAX(upd_B) OVER (PARTITION BY ID) > NVL(MAX(upd_A) OVER (PARTITION BY ID), -1)
		THEN 4
		ELSE -1 END) TEST
FROM (
	SELECT ID, update_id, date_o, type_o,
			DECODE(FIRST_VALUE(type_o) OVER (PARTITION BY ID ORDER BY update_id DESC), 'A', 1, 'B', 2, 0) AS last_type,
			DECODE(type_o, 'A', update_id) AS upd_A, DECODE(type_o, 'B', update_id) AS upd_B
	FROM T
	)
ORDER BY 1, 2
 
ID	UPDATE_ID	DATE_O	TYPE_O	TEST
812732074	11	29/04/2008	A	4
812732074	12	29/04/2008	B	4
812732074	18	29/04/2008	XXX	4
933986296	0	29/04/2008	B	2
933986296	1	29/04/2008	B	2
1012731924	0	29/04/2008	A	3
1012731924	1	29/04/2008	XXX	3
1012731924	2	29/04/2008	XXX	3

29/04/2008, 17h54
aurelie83

Merci MCM pour ton post rapide!

Par contre, ta requete va chercher les données que dans 1 table, alors qu'en fait, je dois faire une premiere recherche dans "matable" (qui contient le derniere état) puis si je n'ai pas 'A' ou 'B' dans cette table, je vais faire une recherche dans MATABLE_HIST.
(désolée je n'ai pas été claire).

voici un exemple du contenu de MATABLE:

id----update_id----date_o-----type_o
1----49----05/01/2007-----XXX

Et un exemple du contenu de MATABLE_HIST:
Id-----update_id-----date_o-----type_o
1-----45-----01/01/2007-----A
1-----46-----02/01/2007-----A
1-----47-----03/01/2007-----B
1-----48-----04/01/2007-----XXX

Donc pour cet ID, je dois récupérer 'B' soit test=4.

C'est plus simple alors, un truc dans le genre

Code:

1
2
3
4
5
6
7
8
9
SELECT   t1.ID, t1.update_id, t1.date_o, t1.type_o,
         DECODE (t1.type_o,'A',1,'B',2,
			 DECODE((SELECT MAX(type_o) KEEP (DENSE_RANK LAST ORDER BY update_id)
				FROM historique
				WHERE ID = t1.ID 
				AND type_o IN ('A','B')
			), 'A', 3, 'B', 4)
				) TEST
FROM     MATABLE t1

J'ai réussi à obtenir ce que je voulais avec cette requête:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT   t1.id, t1.update_id, t1.date_o, t1.type_o, t2.type_o, max(t2.update_id) as maxi, decode (t1.type_o, 'A', 1, 'B',2, decode (t2.type_o, 'A',3,'B',4))test
 FROM     matable t1, (SELECT   id, max(update_id) update_id, type_o
  FROM     matable_hist H
WHERE    type_o IN ('A','B')
 AND date_o >='01/01/2007'
having max(update_id)=(select max(update_id) from matable_hist where type_o IN ('A','B') and id=H.id)
GROUP BY  id,type_o) t2
WHERE    t1.id=t2.id
AND t1.date_o >='01/01/2007'
group by  t1.id, t1.update_id, t1.date_o, t1.type_o, t2.type_o
ORDER BY t1.id, t1.update_id

Par contre, elle est assez longue à s'exécuter....

La solution de MCM fonctionne trés bien.

Sinon essaie ça

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
SELECT m.*,
       CASE
          WHEN m.type_o = 'A'
             THEN 1
          WHEN m.type_o = 'B'
             THEN 2
          WHEN mh.type_o = 'A'
             THEN 3
          WHEN mh.type_o = 'B'
             THEN 4
       END TEST
  FROM (SELECT hist.*,
               ROW_NUMBER () OVER (PARTITION BY ID ORDER BY hist.update_id DESC)
                                                                           rn
          FROM matable_hist hist
         WHERE type_o IN ('A', 'B')) mh,
       matable m
 WHERE mh.ID = m.ID AND mh.rn = 1

02/05/2008, 10h47
aurelie83

Super. Ca marche super bien!
Merci à tous!
07/05/2008, 16h04
aurelie83

Je reviens vers vs car la demande a changée....:-( et je n'arrive pas a voir une requete optimisée...

voilà en fait, je dois sélectionner ds l'historique la première ligne qui ne contient pas reg='XXXXX'
Si dans cette ligne, type_o = 'A' alors test=1, si type_o='B' alors test=2.
Sinon si type_o est différent de A ou B, il faut lire les lignes au dessus et récupérer toute la ligne qui contient le prochain 'A' ou 'B'.
si le prochain type_o est 'A' alors test=3 sinon si type_o='B' alors test=4
Ex :
Id-----update_id-----date_o-----type_o-----reg
1------45--------01/01/2007------A-----'XXXXX'
1------46--------02/01/2007------A-----'XXXXX'
1------47--------03/01/2007------B-----'XXXXX'
1------48--------04/01/2007-----XXX-----'FGRTH'

Dans ce cas là :
On aura :

Id-----update_id-----date_o-----type_o-----reg-----test
1------47--------03/01/2007------B-----'XXXXX'-----4

A votre avis, c'est possible de le faire en 1 requete?
13/05/2008, 09h34
aurelie83

Pas de solution possible à votre avis?