trouver une un MAX dans une jointure

**cmako** · 02/02/2009, 12h06

Bonjour,

J'ai un souci pour trouver une bonne syntaxe SQL.
Je dois corriger une table qui a cette structure (très simplifié)

Code :

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

1
2
3
4
5
6
7
8
9
10
PR DEB FIN
1 2005 2006
1 2006 2008
1 2008 2009
2 1998 2002
2 2002 2005
2 2005 2009
3 1998 2005
3 2000 2002
3 2001 2008

Le problème est que je veux faire un UPDATE de cette table pour corriger la date de fin du produit n°3 qui a les dates qui se chevauchent.
Je veux obtenir ça :

Code :

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

1
2
3
4
PR DEB FIN
3 1998 2000
3 2000 2001
3 2001 2008

A priori c'est facile il suffit de faire un UPDATE avec un t1 INNER JOIN t2 sur la même table avec la date de fin t1 = date debut t2.
Le pb est que pour la première ligne j'aurais 2 possibilités et donc la BDD va d'abord mette la date de fin à 2000 puis le mettra à 2001. Il faudra alors reexecuter la même requête pour remettre la date de fin à 2001.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
premier passage:
PR DEB FIN
3 1998 2001
3 2000 2001
3 2001 2008
 
deuxième passage:
PR DEB FIN
3 1998 2000
3 2000 2001
3 2001 2008

Alors, comment trouver la bonne date de début en une seule requête?

**Waldar** · 02/02/2009, 12h15

Quel est votre SGBD ?

**SQLpro** · 02/02/2009, 12h41

Code :

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

CREATE TABLE X (PR INT, DEB INT, FIN INT)

Code :

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

1
2
3
4
5
6
7
8
9
INSERT INTO X VALUES (1, 2005, 2006)
INSERT INTO X VALUES (1, 2006, 2008)
INSERT INTO X VALUES (1, 2008, 2009)
INSERT INTO X VALUES (2, 1998, 2002)
INSERT INTO X VALUES (2, 2002, 2005)
INSERT INTO X VALUES (2, 2005, 2009)
INSERT INTO X VALUES (3, 1998, 2005)
INSERT INTO X VALUES (3, 2000, 2002)
INSERT INTO X VALUES (3, 2001, 2008)

La solution :

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
SELECT PR, DEB,
       CASE 
          WHEN DF0 < DF1 THEN DF0
          WHEN DF0 < DF2 THEN DF0
          WHEN DF1 < DF2 THEN DF1
          ELSE DF0
       END AS FIN
FROM   (SELECT PR, DEB, FIN AS DF0, 
               (SELECT MIN(DEB) 
                FROM   X AS Tin 
                WHERE  Tin.PR = Tout.PR 
                  AND  Tin.DEB > Tout.DEB) AS DF1,
               (SELECT MIN(FIN) 
                FROM   X AS Tin 
                WHERE  Tin.PR = Tout.PR 
                  AND  Tin.DEB > Tout.DEB) AS DF2
        FROM   X AS Tout ) AS T

Résultat :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
PR          DEB         FIN
----------- ----------- -----------
1           2005        2006
1           2006        2008
1           2008        2009
2           1998        2002
2           2002        2005
2           2005        2009
3           1998        2000
3           2000        2002
3           2001        2008

Cela dit c'est votre modèle de données qui est stupide. En effet il introduit une redondance puisque la fin d'un produit est le début de la suivante, ce qui se déduit par un calcul. or par principe on ne doit JAMAIS faire figurer une coilonne calculée dans une base de données.

A +

**Waldar** · 02/02/2009, 12h49

Une petite erreur dans le résultat :

Code :

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

1
2
3
4
PR          DEB         FIN
----------- ----------- -----------
3           2000        2002
3           2001        2008

**cmako** · 02/02/2009, 13h03

Envoyé par SQLpro

La solution :

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
SELECT PR, DEB,
       CASE 
          WHEN DF0 < DF1 THEN DF0
          WHEN DF0 < DF2 THEN DF0
          WHEN DF1 < DF2 THEN DF1
          ELSE DF0
       END AS FIN
FROM   (SELECT PR, DEB, FIN AS DF0, 
               (SELECT MIN(DEB) 
                FROM   X AS Tin 
                WHERE  Tin.PR = Tout.PR 
                  AND  Tin.DEB > Tout.DEB) AS DF1,
               (SELECT MIN(FIN) 
                FROM   X AS Tin 
                WHERE  Tin.PR = Tout.PR 
                  AND  Tin.DEB > Tout.DEB) AS DF2
        FROM   X AS Tout ) AS T

Cela dit c'est votre modèle de données qui est stupide. En effet il introduit une redondance puisque la fin d'un produit est le début de la suivante, ce qui se déduit par un calcul. or par principe on ne doit JAMAIS faire figurer une coilonne calculée dans une base de données.

A +

Une date de fin est très utile lorsqu'on veut rechercher un résultat BETWEEN deb AND fin sinon ça serait très lourd au niveau SQL de rechercher la date de fin.

A quoi sert l'alias DF2?

**cmako** · 02/02/2009, 13h15

Au début j'étais parti sur cette syntaxe:

Code :

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

1
2
3
4
5
6
7
8
--UPDATE x1
--set x1.fin = x2.fin
SELECT *
FROM X X1
INNER JOIN X X2 
	ON X1.PR= X2.PR
	AND X1.DEB < x2.DEB
	AND X1.FIN > x2.DEB

mais le seul pb est que pour la première ligne j'ai 2 résultats. Serait-il possible de garder la même structure?

**cmako** · 02/02/2009, 13h57

Voilà ce que j'ai trouvé

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
UPDATE xa
SET xa.FIN = xb.FIN
--select *
FROM X xa
INNER JOIN (
	SELECT x1.PR, x1.DEB, min(x2.deb) FIN
	FROM X x1
	INNER JOIN X x2 
		ON X1.PR= X2.PR
		AND X1.DEB < x2.DEB
		AND X1.FIN > x2.DEB
	GROUP BY x1.PR, x1.DEB
) xb
	ON xa.PR = xb.PR
	AND xa.DEB = xb.DEB

N'y aurait-il pas une syntaxe plus simple ?

**Waldar** · 02/02/2009, 13h59

Envoyé par cmako

Une date de fin est très utile lorsqu'on veut rechercher un résultat BETWEEN deb AND fin sinon ça serait très lourd au niveau SQL de rechercher la date de fin.

Ce que veux dire SQLPro c'est que cette date de fin vous pouvez la calculer dans une vue voire dans une colonne virtuelle si votre SGBD le supporte.

Et si vous effectuez le calcul à la volée, vous n'aurez jamais d'erreur de cohérence.
Il reste à évaluer le coût induit en fonction de la volumétrie bien entendu.

**SQLpro** · 02/02/2009, 14h04

Il reste à évaluer le coût induit en fonction de la volumétrie bien entendu.

Tout dépend du SGBDR. Certains permettent en plus des vues :
1) des colonnes calculées dans les tables
2) des colonnes calculées persistante dans les tables
3) des colonnes calculées persistante indexées dans les tables
3) des vues indexées.

A +

**pacmann** · 03/02/2009, 20h56

Salut !

Envoyé par Waldar

Il reste à évaluer le coût induit en fonction de la volumétrie bien entendu.

Ouais Waldar, sous Oracle, on a au moins les materialized views.
Par contre, la dernière fois que j'ai essayé de faire marcher le query rewrite, je me suis tapé la tête contre les murs (c'était la semaine dernière

)

**Waldar** · 04/02/2009, 11h00

Envoyé par pacmann

Sous Oracle, on a au moins les materialized views.

Il y a aussi depuis la 11g la plupart des solutions mentionnées par SQLPro.
On peut même partitionner sur une colonne calculée !

**cmako** · 04/02/2009, 12h55

Envoyé par SQLpro

Tout dépend du SGBDR. Certains permettent en plus des vues :
1) des colonnes calculées dans les tables
2) des colonnes calculées persistante dans les tables
3) des colonnes calculées persistante indexées dans les tables
3) des vues indexées.

A +

Est ce que les colonnes calculés sont calculés à chaque SELECT ou après un INSERT ou UPDATE?

**pacmann** · 05/02/2009, 20h34

Lorsque la colonne n'est pas persistente, c'est lors du SELECT.

Pour le reste, il se peut que ça dépende du SGBD.
Sous Oracle, pour la vue indexée (qu'on appelle chez nous une vue matérialisée), on peut définir à la carte le rafraîchissement.

Synchrone (lors du commit de modification insert, update)
Asynchrone, à la demande.
Asynchrone, à interval régulier.

trouver une un MAX dans une jointure

Langage SQL

Discussions similaires

Partager

Partager