Discussion :

[media]

Bonjour,

SGBDR : ORACLE v9.2.0.6.0 et V9.2.0.1.0 (même comportement)

Le problème qui se pose à moi est le suivant :

Le système gère des tâches qui peuvent être exécutées sur différentes machines.
Une tâche dure un certain temps.
Plusieurs tâches peuvent avoir lieu en même temps sur la même machine.

Pour simplifier le problème, je me ramène à une seule table :

CREATE TABLE Planning (IdMachine INTEGER, Debut DATE, Fin DATE);

IdMachine identifie la machine.
Debut est la date à laquelle commence la tâche
Fin est la date à laquelle se termine la tâche

(je n'inclus pas l'identification des tâches qui n'a aucune importance ici).

Voici un exemple de remplissage de cette table

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
INSERT INTO Planning VALUES (1, to_date('01/01/2005', 'DD/MM/YYYY'), to_date('01/03/2005', 'DD/MM/YYYY'));
INSERT INTO Planning VALUES (1, to_date('01/02/2005', 'DD/MM/YYYY'), to_date('01/05/2005', 'DD/MM/YYYY'));
INSERT INTO Planning VALUES (1, to_date('01/04/2005', 'DD/MM/YYYY'), to_date('01/08/2005', 'DD/MM/YYYY'));
INSERT INTO Planning VALUES (1, to_date('01/06/2005', 'DD/MM/YYYY'), to_date('01/09/2005', 'DD/MM/YYYY'));
INSERT INTO Planning VALUES (1, to_date('01/07/2005', 'DD/MM/YYYY'), to_date('01/10/2005', 'DD/MM/YYYY'));
INSERT INTO Planning VALUES (1, to_date('01/11/2005', 'DD/MM/YYYY'), to_date('01/03/2006', 'DD/MM/YYYY'));
INSERT INTO Planning VALUES (1, to_date('01/12/2005', 'DD/MM/YYYY'), to_date('01/01/2006', 'DD/MM/YYYY'));
INSERT INTO Planning VALUES (1, to_date('01/02/2006', 'DD/MM/YYYY'), to_date('01/04/2006', 'DD/MM/YYYY'));
 
INSERT INTO Planning VALUES (2, to_date('01/02/2005', 'DD/MM/YYYY'), to_date('01/02/2005', 'DD/MM/YYYY'));
INSERT INTO Planning VALUES (2, to_date('01/03/2005', 'DD/MM/YYYY'), to_date('01/07/2005', 'DD/MM/YYYY'));
INSERT INTO Planning VALUES (2, to_date('01/05/2005', 'DD/MM/YYYY'), to_date('01/08/2005', 'DD/MM/YYYY'));
INSERT INTO Planning VALUES (2, to_date('01/09/2005', 'DD/MM/YYYY'), to_date('01/10/2005', 'DD/MM/YYYY'));
INSERT INTO Planning VALUES (2, to_date('01/11/2005', 'DD/MM/YYYY'), to_date('01/01/2006', 'DD/MM/YYYY'));
INSERT INTO Planning VALUES (2, to_date('01/12/2005', 'DD/MM/YYYY'), to_date('01/03/2006', 'DD/MM/YYYY'));
INSERT INTO Planning VALUES (2, to_date('01/04/2006', 'DD/MM/YYYY'), to_date('01/05/2006', 'DD/MM/YYYY'));

cf. le fichier joint (en Jaune, Bleu et Orange les périodes de trois tâches ; en Violet les périodes libres).

Le question que je dois résoudre est de déterminer quelles sont les périodes pendant lesquelles les machines ne sont pas utilisées.
J'ai mis au point une solution qui devrait fonctionner, et que je vais expliquer en deux parties :



Requête N° 1

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
20
21
22
23
24
25
26
27
28
WITH ax AS (SELECT IdMachine, debut, fin
            FROM Planning
        UNION
            SELECT DISTINCT IdMachine, to_date('15/02/2005',    'DD/MM/YYYY'), to_date('15/02/2005',    'DD/MM/YYYY')
            FROM Planning
        UNION
            SELECT DISTINCT IdMachine, to_date('15/03/2006',    'DD/MM/YYYY'), to_date('15/03/2006',    'DD/MM/YYYY')
            FROM Planning),
     px AS (SELECT IdMachine, debut, fin, sys_connect_by_path(to_char(debut,    'DD/MM/YYYY'),    ' ') AS Chemin
            FROM ax 
            START WITH(IdMachine, debut) IN (SELECT IdMachine,  debut
                                      FROM ax b
                                      WHERE fin >= to_date('15/02/2005',    'DD/MM/YYYY')
                                        AND debut <= to_date('15/03/2006',    'DD/MM/YYYY')
                                        AND NOT EXISTS (SELECT NULL
                                                        FROM ax c
                                                        WHERE b.IdMachine = c.IdMachine
                                                          AND b.debut > c.debut
                                                          AND b.debut <= c.fin))
            CONNECT BY IdMachine = PRIOR IdMachine
                   AND debut BETWEEN PRIOR debut AND PRIOR fin
                   AND fin > PRIOR fin),
     bx AS (SELECT IdMachine, 
                   greatest(to_date(SUBSTR(chemin, 1, 11), 'DD/MM/YYYY'),    to_date('15/02/2005', 'DD/MM/YYYY')) AS  debut,
                   least(MAX(fin),    to_date('15/03/2006',    'DD/MM/YYYY')) AS fin
            FROM px
            GROUP BY IdMachine, SUBSTR(chemin, 1, 11))
SELECT * FROM bx;

La requête précédente me donne les périodes où les machines sont utilisées, à titre d'exemple, pendant l'intervalle de temps ['15/02/2005'; '15/03/2006'].

Cette requête fonctionne parfaitement et me donne le résultat attendu

Code :

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

1
2
3
4
5
6
7
8
IDMACHINE	DEBUT		FIN
1		15/02/05	01/10/05
1		01/11/05	15/03/06
2		15/02/05	15/02/05
2		01/03/05	01/08/05
2		01/09/05	01/10/05
2		01/11/05	01/03/06
2		15/03/06	15/03/06

Les lignes (2; 15/02/05; 15/02/05) et (2; 15/03/06; 15/03/06) permettent de calculer les périodes au début et à la fin de l'intervalle de recherche

Pour obtenir les périodes où les machines ne sont pas utilisées, je remplace simplement la dernière ligne de la requête précédente (SELECT * FROM bx) par :

Code :

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

1
2
3
4
5
6
7
8
9
SELECT a.IdMachine, a.fin, b.debut
FROM bx a INNER JOIN bx b 
                  ON a.IdMachine = b.IdMachine
                 AND b.debut = (SELECT MIN(debut)
                                FROM bx c
                                WHERE c.IdMachine = a.IdMachine
                                  AND c.debut > a.fin)
WHERE a.fin < to_date('15/03/2006',   'DD/MM/YYYY')
  AND a.debut >= to_date('15/02/2005',   'DD/MM/YYYY');

Mais là, au lieu d'obtenir le résultat attendu, j'obtiens :

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
IDMACHINE	FIN		DEBUT
1		01/01/06	01/02/06
1		15/02/05	01/04/05
1		01/10/05	01/11/05
1		01/10/05	01/11/05
1		01/10/05	01/11/05
1		01/10/05	01/11/05
1		01/10/05	01/11/05
2		01/02/05	15/02/05
2		15/02/05	01/03/05
2		01/08/05	01/09/05
2		01/08/05	01/09/05
2		01/10/05	01/11/05
2		01/02/05	15/02/05
2		01/03/06	15/03/06
2		01/03/06	15/03/06

Résultat où on peut voir des données qui n'existent pas dans la vue bx !

Si je crée une table à partir de la requête N° 1 :
Create table bx as
...
select * from bx

Puis que j'utilise le petit bout de requête suivant (le même que le précédent, bien sur)

Code :

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

1
2
3
4
5
6
7
8
9
SELECT a.IdMachine, a.fin, b.debut
FROM bx a INNER JOIN bx b 
                  ON a.IdMachine = b.IdMachine
                 AND b.debut = (SELECT MIN(debut)
                                FROM bx c
                                WHERE c.IdMachine = a.IdMachine
                                  AND c.debut > a.fin)
WHERE a.fin < to_date('15/03/2006',   'DD/MM/YYYY')
  AND a.debut >= to_date('15/02/2005',   'DD/MM/YYYY');

Alors j'obtiens bien le bon résultat :

Code :

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

1
2
3
4
5
6
IDMACHINE	FIN		DEBUT
1		01/10/05	01/11/05
2		15/02/05	01/03/05
2		01/08/05	01/09/05
2		01/10/05	01/11/05
2		01/03/06	15/03/06

Je trouve bizarre que mes deux requêtes celle avec la factoring_clause et celle avec une table ne donne pas le même résultat.
Me suis-je planté quelque part ?

Je voudrais ajouter deux remarques :

1) je ne me préoccupe pas de l'inclusion ou non des bornes (je verrai plus tard)
2) je ne cherche pas une solution alternative, mais à comprendre pourquoi la solution précédente avec factoring_clause ne fonctionne pas.

Merci d'être arrivé jusque là.