[Debutant] [9iR2] Filtrage sur 2 dates

in · 13/08/2007, 11h39

Bonjour,

c'est ptet parce que c'est lundi, mais j'ai une requete que je n'arrive pas du tout à faire. Pourtant c'est un problème très commun je pense ...

En fait, j'ai deux colonnes de type date dans mes lignes de résultats. Je voudrais filtrer les lignes qui ont les 2 dates les plus récentes.

Par exemple, pour les données suivantes (en pièce jointe), je ne veux garder que la ligne marquée d'un point rouge:

En clair, la ligne qui a le couple de date les plus récentes.

J'ai esasyé de jouer avec des max, de convertir les dates en nombre et de faire la somme ... mais bon sans réel succès. Si vous avez une idée, voici ma requete ...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
with pmb as (
	   	SELECT orig_proj_id, add_date
		FROM [...]
	   ),  cur as (
	   	SELECT orig_proj_id, add_date
		FROM [...]
	   ), proj as (
	   	  	SELECT pw.proj_id id, wbs_short_name, wbs_name
			FROM privuser.projwbs pw JOIN privuser.project p ON pw.proj_id = p.proj_id
			WHERE proj_node_flag='Y' 
			AND orig_proj_id IS NULL 
			START WITH pw.wbs_short_name = 'P-01034' 
			CONNECT BY PRIOR wbs_id = parent_wbs_id
	   )
	   	SELECT proj.id, proj.wbs_short_name, proj.wbs_name, pmb.wbs_id pmb, pmb.add_date pmbDate, cur.wbs_id cur, cur.add_date curDate
		FROM proj LEFT JOIN pmb ON proj.id = pmb.orig_proj_id
			 LEFT JOIN cur ON proj.id = cur.orig_proj_id

PS : pour faire la "somme" des 2 dates, j'ai essayé de cette manière :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

nvl(to_number(to_char(pmb.add_date, 'RRRRMMDDHHMISS')),0) + nvl(to_number(to_char(cur.add_date, 'RRRRMMDDHHMISS')),0) sum_date

. Je pense que c'est une partie de la solution mais je ne sais comment filtrer ensuite. Je pensais à greatest mais pas de réussite non plus

**McM** · 13/08/2007, 11h53

Soit 2 sous-select
:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
where pk = (select pk from matable 
where rownum = 1 and (dt1, dt2) IN (select dte1, dte2
     FROM matable order by dte1 desc, dte2 desc)
)

Soit avec les fonctions analytiques

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
SELECT DISTINCT col1, FIRST_VALUE(dte1) OVER (PARTITION BY col1 ORDER BY dte1 DESC, dte2 DESC),
FIRST_VALUE(dte2) OVER (PARTITION BY col1 ORDER BY dte1 DESC, dte2 DESC)

**Garuda** · 13/08/2007, 11h55

Qu'entends-tu par couple de dates les plus récentes ?
Ex
Entre le couple
(28/07/2007,31/07/2007)
et le couple
(29/07/2007,30/07/2007)
Quel est le plus récent ?

En clair (

) , merci d'expliciter un peu plus le besoin !

in · 13/08/2007, 14h03

Envoyé par Garuda

Entre le couple
(28/07/2007,31/07/2007)
et le couple
(29/07/2007,30/07/2007)

Bonne question !

En fait les "tables temporaires" (je ne sais pas si c'est le bon terme) me renvoie des lignes avec 1 date.

Je joins ensuite ces 2 tables à une troisième. J'aimerais que le résultat me donne la date la plus récente de chacune des tables temporaires.

Je vais essayer d'être plus clair avec un exemple.

contenu de pmb :

orig_ID add_date idPMB
--------------------------------
1 01/01/2000 A
1 01/01/2007 B
2 01/01/1970 C
2 01/01/2000 D

contenu de cur :

orig_ID add_date idCUR
-------------------------------
1 01/01/2000 E
1 01/01/2007 F
2 01/01/1970 G
2 01/01/2000 H

contenu de proj :

ID
------
1
2

J'aimerais trouver la requete qui me donne

ID datePMB idPMB dateCUR idCUR
--------------------------------------------------
1 01/01/2007 B 01/01/2007 F
2 01/01/2000 D 01/01/2000 H

Voilà ...

Je vais jeter un coup d'oeil du côté des first_value ...

in · 13/08/2007, 16h25

Envoyé par McM

Soit 2 sous-select
:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
where pk = (select pk from matable 
where rownum = 1 and (dt1, dt2) IN (select dte1, dte2
     FROM matable order by dte1 desc, dte2 desc)
)

Désolé mais là je comprends pas vraiment le truc ....

Envoyé par McM

Soit avec les fonctions analytiques

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
SELECT DISTINCT col1, FIRST_VALUE(dte1) OVER (PARTITION BY col1 ORDER BY dte1 DESC, dte2 DESC),
FIRST_VALUE(dte2) OVER (PARTITION BY col1 ORDER BY dte1 DESC, dte2 DESC)

Alors, ça je découvre ... et je n'y arrive pas.

J'ai essayé ça (pour la fin de ma requete) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SELECT proj.id, proj.wbs_short_name, proj.wbs_name, pmb.wbs_id pmb
			   , last_value(pmb.add_date) OVER (PARTITION BY proj.id ORDER BY pmb.add_date DESC) datePMB
			   , cur.wbs_id cur
			   , last_value(cur.add_date) OVER (PARTITION BY proj.id ORDER BY cur.add_date DESC) dateCUR
		FROM proj LEFT JOIN pmb ON proj.id = pmb.orig_proj_id
			 	  LEFT JOIN cur ON proj.id = cur.orig_proj_id

mais bon c'est exactement pareil ...

j'ai également essayé avec

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
ORDER BY pmb.add_date 
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

dans la partie OVER, mais idem ...

Bref, je me sens un peu perdu. Et à vrai dire je capte pas trop trop l'histoire des fonctions analytiques ...

**McM** · 13/08/2007, 17h02

Tu t'es trompé, c'est pas last mais first qu'il faut utiliser avec order by date desc

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
WITH proj AS (SELECT 1 AS ID, 'un' AS short_name FROM dual
		UNION ALL SELECT 2, 'deux' FROM DUAL
		UNION ALL SELECT 3, 'trois' FROM DUAL),
pmb AS (SELECT 1 AS orig_proj_id, '01/01/2000' AS add_date, 'A' AS wbs_id FROM DUAL
UNION ALL SELECT 1, '01/01/2007', 'B' FROM dual
UNION ALL SELECT 2, '01/01/1970', 'C' FROM dual
UNION ALL SELECT 2, '01/01/2000', 'D' FROM dual),
cur AS (SELECT 1 AS orig_proj_id, '02/01/2000' AS add_date, 'E' AS wbs_id FROM DUAL
UNION ALL SELECT 1, '02/01/2007', 'F' FROM dual
UNION ALL SELECT 2, '02/01/1970', 'G' FROM dual
UNION ALL SELECT 2, '02/01/2000', 'H' FROM dual)
SELECT DISTINCT j.ID, j.short_name, 
	first_value(p.wbs_id) 	OVER (PARTITION BY p.orig_proj_id ORDER BY p.add_date DESC) PMB,
	first_value(p.add_date) 	OVER (PARTITION BY p.orig_proj_id ORDER BY p.add_date DESC) datePMB,
	first_value(c.wbs_id) 	OVER (PARTITION BY c.orig_proj_id ORDER BY c.add_date DESC) CUR,
	first_value(c.add_date)	OVER (PARTITION BY c.orig_proj_id ORDER BY c.add_date DESC) dateCUR
FROM proj j, pmb p, cur c
WHERE j.ID = p.orig_proj_id (+)
AND j.ID = c.orig_proj_id (+)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
ID	SHORT_NAME	PMB	DATEPMB	CUR	DATECUR
1	un	B	01/01/2007	F	02/01/2007
2	deux	D	01/01/2000	H	02/01/2000
3	trois

in · 13/08/2007, 17h14

Ha, je te remercie.

J'avais mal compris le truc du over. En tous cas ça fonctionne

Euh, juste une petite question bête de débutant : le distinct ne s'applique qu'à j.ID, j.short_name ? Ca me parait bizarre mais j'ai l'impression que c'est ça qui se passe ...

En tout cas merci encore !

A+

**McM** · 13/08/2007, 17h36

non, le distinct c'est sur tout.
enlève le et tu verras que les lignes sont identiques par id.

in · 14/08/2007, 09h36

Je me demandais aussi, au niveau perf, quelle solution parmis les 2 proposées est généralement la meilleure ? 2 sous-select ou la fonction analytique ?

enfin de manière générale, les fonctions analytiques sont elles performantes ?

**orafrance** · 14/08/2007, 09h42

oui, les fonctions analytiques sont meilleures parce qu'elle évite de ramener une collection de ligne plusieurs fois. La fonction est appliqué à l'ensemble des lignes ramenés par la requête soit sur une population de lignes bien moindre en général

[Debutant] [9iR2] Filtrage sur 2 dates

SQL Oracle

Discussions similaires

Partager

Partager