Demande aide sur une requête SQL

Version imprimable

Bonjour,

Actuellement je fais un traitement en Java après avoir fait une requête assez simple mais je me demande si ce traitement Java pourrait être remplacé par une requête plus complexe. Il me semble que ça serait plus rapide. Cependant je ne sais pas si une telle requête est faisable et c'est pourquoi je sollicite votre aide.

Donc pour résumer voici la structure de mes 2 tables concernées :

Citation:

TABLE1 "GAUCHE"
ID : NUMBER
IDVOITURE : NUMBER
DATESAISIE : DATE
DATEDEBUT : DATE
DATEFIN : DATE
PUISSANCE : NUMBER
TYPESAISIE : NUMBER

TABLE2 "DROITE"
ID : NUMBER (a la même valeur que l'ID de la TABLE1 "GAUCHE")
FK sur l'ID de la TABLE1

Actuellement je récupère tous les enregistrements de ma TABLE1 "GAUCHE" qui n'ont pas d'enregistrement correspondant dans la TABLE2 "DROITE".

J'ai donc une requête très simple de ce type :
Code:

1 2 3 SELECT T.* FROM TABLE1 T LEFT OUTER JOIN TABLE2 r ON T.id=R.id WHERE R.id IS NULL order by T.IDVOITURE asc, T.DATESAISIE desc, T.DATEDEBUT desc;
Cela me retourne une liste de valeur du genre :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 ID | IDVOITURE | DATESAISIE | DATEDEBUT | DATEFIN | PUISSANCE | TYPESAISIE 1 | 20 | 21/05/2013 10:12:05 | 21/05/2013 10:40 | 21/05/2013 10:45 | 540 |2 2 | 20 | 21/05/2013 10:12:05 | 21/05/2013 10:35 | 21/05/2013 10:40 | 530 |2 3 | 20 | 21/05/2013 10:12:05 | 21/05/2013 10:30 | 21/05/2013 10:35 | 520 |2 4 | 20 | 21/05/2013 10:12:05 |21/05/2013 10:25 | 21/05/2013 10:30 | 510 |2 5 | 20 | 21/05/2013 09:00:05 | 21/05/2013 12:00 | 21/05/2013 15:00 | 200 |3 6 | 20 | 21/05/2013 08:56:05 | 21/05/2013 21:45 | 21/05/2013 23:55 | 263 |4 7 | 21 | 21/05/2013 21:00:05 | 21/05/2013 21:25 | 21/05/2013 21:30 | 152 |6 8 | 21 | 21/05/2013 21:00:05 | 21/05/2013 21:20 | 21/05/2013 21:25 | 150 |6 9 | 21 | 21/05/2013 21:00:05 | 21/05/2013 21:15 | 21/05/2013 21:20 | 149 |6 10 | 22 | 21/05/2013 19:36:15 | 21/05/2013 20:00 | 21/05/2013 22:00 | 800 |4
Le traitement Java récupère cette liste et fait les traitements suivants :

Pour chaque IDVOITURE identique ayant un TYPESAISIE similaire on récupère la date de fin la plus haute, on prend la puissance de cette ligne et ensuite on cherche la date de début la plus basse.
Pour l'exemple donné au dessus on obtient donc le résultat Java suivant :
Code:

1 2 3 4 5 6 7 8 9 10 11 ID | IDVOITURE | DATESAISIE | DATEDEBUT | DATEFIN | PUISSANCE | TYPESAISIE 4 | 20 | 21/05/2013 10:12:05 | 21/05/2013 10:25 | 21/05/2013 10:45 | 540 |2 5 | 20 | 21/05/2013 09:00:05 | 21/05/2013 12:00 | 21/05/2013 15:00 | 200 |3 6 | 20 | 21/05/2013 08:56:05 | 21/05/2013 21:45 | 21/05/2013 23:55 | 263 |4 9 | 21 | 21/05/2013 21:00:05 | 21/05/2013 21:15 | 21/05/2013 21:30 | 152 |6 10 | 22 | 21/05/2013 19:36:15 | 21/05/2013 20:00 | 21/05/2013 22:00 | 800 |4
J'aimerais donc d'abord avoir votre avis quand à la faisabilité de la chose et si c'est le cas je veux bien un petit coup de main pour l'écrire car pour l'instant je patauge pas mal. Sachant aussi que je dois l'écrire en HQL.

Si ce n'est pas clair n'hésitez pas à me le dire et merci beaucoup à ceux qui m'aideront

En SQL normalisé, ça donnerait :

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
WITH    matable
    AS  (   SELECT  *
            FROM    table1  tb1
            WHERE   NOT EXISTS
                    (   SELECT  NULL
                        FROM    table2  tb2
                        WHERE   tb1.id  = tb2.id
                    )
        )
SELECT  deb.id
    ,   deb.idvoiture
    ,   deb.datesaisie
    ,   deb.datedebut
    ,   fin.datefin
    ,   fin.puissance
    ,   deb.typesaisie
FROM    matable deb
    INNER JOIN
        matable fin
        ON  deb.idvoiture   = fin.idvoiture
        AND deb.typesaisie  = fin.typesaisie
WHERE   EXISTS
        (   SELECT  NULL
            FROM    matable sel
            WHERE   deb.idvoiture   = sel.idvoiture
                AND deb.typesaisie  = sel.typesaisie
            HAVING  deb.datedebut   = MIN(sel.datedebut)
        )
    AND EXISTS
        (   SELECT  NULL
            FROM    matable sel
            WHERE   fin.idvoiture   = sel.idvoiture
                AND fin.typesaisie  = sel.typesaisie
            HAVING  fin.datefin     = MAX(sel.datefin)
        )
;

Tout d'abord merci beaucoup car ça fait presque exactement ce que je voudrais. 2 petites choses que je suis en train d'essayer de faire :

- Parfois les TYPESAISIE sont null et il faudrait quand même qu'une ligne s'affiche selon le même procédé

- Il est possible d'avoir des TYPESAISIE identiques mais des DATESAISIE différents et il faudrait donc que je réussisse à avoir 2 lignes distinctes suivant la DATESAISIE

Exemple

Code:

1
2
3
4
5
6
7
8
9
ID | IDVOITURE | DATESAISIE          | DATEDEBUT        | DATEFIN          | PUISSANCE | TYPESAISIE
1  | 20            | 21/05/2013 10:12:05 | 21/05/2013 10:40 | 21/05/2013 10:45 |   540  |2
2  | 20            | 21/05/2013 10:12:05 | 21/05/2013 10:35 | 21/05/2013 10:40 |   530  |2
 
3  | 20            | 21/05/2013 22:12:05 | 21/05/2013 00:30 | 21/05/2013 12:35 |   255  |2
 
4  | 20            | 21/05/2013 14:12:05 | 21/05/2013 10:25 | 21/05/2013 10:30 |   60    |2
 
5  | 20            | 21/05/2013 13:00:16 | 21/05/2013 09:15 | 21/05/2013 09:50 |   10    |NULL

Le résultat souhaité serait :

Code:

1
2
3
4
5
6
7
8
ID | IDVOITURE | DATESAISIE          | DATEDEBUT        | DATEFIN          | PUISSANCE | TYPESAISIE
2  | 20            | 21/05/2013 10:12:05 | 21/05/2013 10:35 | 21/05/2013 10:45 |   540  |2
 
3  | 20            | 21/05/2013 22:12:05 | 21/05/2013 00:30 | 21/05/2013 12:35 |   255  |2
 
4  | 20            | 21/05/2013 14:12:05 | 21/05/2013 10:25 | 21/05/2013 10:30 |   60    |2
 
5  | 20            | 21/05/2013 13:00:16 | 21/05/2013 09:15 | 21/05/2013 09:50 |   10    |NULL

J'ai fait quelques tentatives de mon côté mais pour le moment je n'obtiens rien de concluant.

Citation:

Envoyé par Igname

- Parfois les TYPESAISIE sont null et il faudrait quand même qu'une ligne s'affiche selon le même procédé

Modifier la condition de jointure pour prendre en compte ce cas :
Code:

1 2 3 4 5 6 7 8 WHERE EXISTS (... AND (deb.typesaisie = sel.typesaisie OR (deb.typesaisie IS NULL AND sel.typesaisie IS NULL) ) HAVING ...) AND EXISTS (... AND (fin.typesaisie = sel.typesaisie OR (fin.typesaisie IS NULL AND sel.typesaisie IS NULL) ) HAVING ...)
Citation:

Envoyé par Igname

- Il est possible d'avoir des TYPESAISIE identiques mais des DATESAISIE différents et il faudrait donc que je réussisse à avoir 2 lignes distinctes suivant la DATESAISIE

Ajouter la colonne datesaise dans les conditions de jointure :
Code:

1 2 3 4 5 6 WHERE EXISTS (... AND deb.datesaisie = sel.datesaisie HAVING ...) AND EXISTS (... AND fin.datesaisie = sel.datesaisie HAVING ...)

J'exclus la jointure externe du besoin :

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
41
42
With TABLE1  (ID, IDVOITURE, DATESAISIE, DATEDEBUT, DATEFIN, PUISSANCE, TYPESAISIE) as
(
select  1, 20, to_date('21/05/2013 10:12:05', 'dd/mm/yyyy hh24:mi:ss'), to_date('21/05/2013 10:40', 'dd/mm/yyyy hh24:mi'), to_date('21/05/2013 10:45', 'dd/mm/yyyy hh24:mi'), 540, 2    from dual union all
select  2, 20, to_date('21/05/2013 10:12:05', 'dd/mm/yyyy hh24:mi:ss'), to_date('21/05/2013 10:35', 'dd/mm/yyyy hh24:mi'), to_date('21/05/2013 10:40', 'dd/mm/yyyy hh24:mi'), 530, 2    from dual union all
select  3, 20, to_date('21/05/2013 10:12:05', 'dd/mm/yyyy hh24:mi:ss'), to_date('21/05/2013 10:30', 'dd/mm/yyyy hh24:mi'), to_date('21/05/2013 10:35', 'dd/mm/yyyy hh24:mi'), 520, 2    from dual union all
select  4, 20, to_date('21/05/2013 10:12:05', 'dd/mm/yyyy hh24:mi:ss'), to_date('21/05/2013 10:25', 'dd/mm/yyyy hh24:mi'), to_date('21/05/2013 10:30', 'dd/mm/yyyy hh24:mi'), 510, 2    from dual union all
select  5, 20, to_date('21/05/2013 09:00:05', 'dd/mm/yyyy hh24:mi:ss'), to_date('21/05/2013 12:00', 'dd/mm/yyyy hh24:mi'), to_date('21/05/2013 15:00', 'dd/mm/yyyy hh24:mi'), 200, 3    from dual union all
select  6, 20, to_date('21/05/2013 08:56:05', 'dd/mm/yyyy hh24:mi:ss'), to_date('21/05/2013 21:45', 'dd/mm/yyyy hh24:mi'), to_date('21/05/2013 23:55', 'dd/mm/yyyy hh24:mi'), 263, 4    from dual union all
select  7, 21, to_date('21/05/2013 21:00:05', 'dd/mm/yyyy hh24:mi:ss'), to_date('21/05/2013 21:25', 'dd/mm/yyyy hh24:mi'), to_date('21/05/2013 21:30', 'dd/mm/yyyy hh24:mi'), 152, 6    from dual union all
select  8, 21, to_date('21/05/2013 21:00:05', 'dd/mm/yyyy hh24:mi:ss'), to_date('21/05/2013 21:20', 'dd/mm/yyyy hh24:mi'), to_date('21/05/2013 21:25', 'dd/mm/yyyy hh24:mi'), 150, 6    from dual union all
select  9, 21, to_date('21/05/2013 21:00:05', 'dd/mm/yyyy hh24:mi:ss'), to_date('21/05/2013 21:15', 'dd/mm/yyyy hh24:mi'), to_date('21/05/2013 21:20', 'dd/mm/yyyy hh24:mi'), 149, 6    from dual union all
select 10, 22, to_date('21/05/2013 19:36:15', 'dd/mm/yyyy hh24:mi:ss'), to_date('21/05/2013 20:00', 'dd/mm/yyyy hh24:mi'), to_date('21/05/2013 22:00', 'dd/mm/yyyy hh24:mi'), 800, 4    from dual union all
select 11, 23, to_date('21/05/2013 10:12:05', 'dd/mm/yyyy hh24:mi:ss'), to_date('21/05/2013 10:40', 'dd/mm/yyyy hh24:mi'), to_date('21/05/2013 10:45', 'dd/mm/yyyy hh24:mi'), 540, 2    from dual union all
select 12, 23, to_date('21/05/2013 10:12:05', 'dd/mm/yyyy hh24:mi:ss'), to_date('21/05/2013 10:35', 'dd/mm/yyyy hh24:mi'), to_date('21/05/2013 10:40', 'dd/mm/yyyy hh24:mi'), 530, 2    from dual union all
select 13, 20, to_date('21/05/2013 22:12:05', 'dd/mm/yyyy hh24:mi:ss'), to_date('21/05/2013 00:30', 'dd/mm/yyyy hh24:mi'), to_date('21/05/2013 12:35', 'dd/mm/yyyy hh24:mi'), 255, 2    from dual union all
select 14, 20, to_date('21/05/2013 14:12:05', 'dd/mm/yyyy hh24:mi:ss'), to_date('21/05/2013 10:25', 'dd/mm/yyyy hh24:mi'), to_date('21/05/2013 10:30', 'dd/mm/yyyy hh24:mi'),  60, 2    from dual union all
select 15, 20, to_date('21/05/2013 13:00:16', 'dd/mm/yyyy hh24:mi:ss'), to_date('21/05/2013 09:15', 'dd/mm/yyyy hh24:mi'), to_date('21/05/2013 09:50', 'dd/mm/yyyy hh24:mi'),  10, null from dual
)
  select max(id)                                                     as id
       , idvoiture
       , datesaisie
       , min(datedebut)                                              as datedebut
       , max(datefin)                                                as datefin
       , max(puissance) keep(dense_rank first order by datefin desc) as puissance
       , typesaisie
    from Table1
group by idvoiture
       , datesaisie
       , typesaisie
order by 1 asc;
 
ID IDVOITURE DATESAISIE          DATEDEBUT        DATEFIN          PUISSANCE TYPESAISIE
-- --------- ------------------- ---------------- ---------------- --------- ----------
 4        20 2013-05-21 10:12:05 2013-05-21 10:25 2013-05-21 10:45        540         2
 5        20 2013-05-21 09:00:05 2013-05-21 12:00 2013-05-21 15:00        200         3
 6        20 2013-05-21 08:56:05 2013-05-21 21:45 2013-05-21 23:55        263         4
 9        21 2013-05-21 21:00:05 2013-05-21 21:15 2013-05-21 21:30        152         6
10        22 2013-05-21 19:36:15 2013-05-21 20:00 2013-05-21 22:00        800         4
12        23 2013-05-21 10:12:05 2013-05-21 10:35 2013-05-21 10:45        540         2
13        20 2013-05-21 22:12:05 2013-05-21 00:30 2013-05-21 12:35        255         2
14        20 2013-05-21 14:12:05 2013-05-21 10:25 2013-05-21 10:30         60         2
15        20 2013-05-21 13:00:16 2013-05-21 09:15 2013-05-21 09:50         10

25/09/2013, 13h50
Igname

Merci al1_24 j'essaie de suite.

@Waldar, j'avoue ne pas trop comprendre ta réponse. C'est comme si tu partais du principe que je connaissais déjà ce que j'avais en base ce qui n'est pas le cas. Pour le moment je vais essayer la solution d'al1 ;)
25/09/2013, 14h06
mnitu
Citation:

Envoyé par Igname

...@Waldar, j'avoue ne pas trop comprendre ta réponse. ...

La partie With c'est juste une méthode convenable pour préparer un jeu de test sans se soucier de la création des tables de l'insertion des enregistrements etc...
Ce qui devrait vous intéresser c'est la partie
Code:

1 2 max(puissance) keep(dense_rank first ORDER BY datefin DESC) AS puissance
pour laquelle on vous encourage activement d'aller lire la documentation SQL d'Oracle.
25/09/2013, 15h03
Igname

Autant pour moi Waldar, je ne connaissais pas du tout ces syntaxes la et je dois dire que je suis plutôt bluffé. J'ai regardé un peu la doc, je pense comprendre le principe mais de la à le refaire il y a encore un pas.....

Je me permet de compléter ma demande qui est en réalité un peu plus complexe que je vous l'ai expliqué. En fait suivant certains TYPESAISIE (3,4 et 5 par exemple) il faudrait utiliser ce système de DATEDEBUT DATEFIN et PUISSANCE et pour les autres TYPESAISIE il faudrait simplement ramener la ligne correspondante.
En gros c'est identique à ce que vous m'avez proposé mais avec un "filtre" sur TYPESAISIE mais la sans PL/SQL est ce que c'est possible ??

En tout cas, déjà merci pour tout
26/09/2013, 17h28
Igname

Impossible alors à votre avis ?

Sinon faire le traitement en Java et appeler la bonne requête, mais ça fait 2 accès base c'est ça qui est un peu plus gênant
26/09/2013, 17h36
Waldar

Si sur les autres types de saisies il n'y a qu'une ligne, la requête fonctionnera avec ou sans agrégat.

Sinon, en mettant bout à bout tes demandes, on arriverait à ça :

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
41
42
43
44
45
46
47
48
49
50
51
WITH    matable
    AS  (   SELECT  *
            FROM    table1  tb1
            WHERE   NOT EXISTS
                    (   SELECT  NULL
                        FROM    table2  tb2
                        WHERE   tb1.id  = tb2.id
                    )
        )
SELECT  deb.id
    ,   deb.idvoiture
    ,   deb.datesaisie
    ,   deb.datedebut
    ,   fin.datefin
    ,   fin.puissance
    ,   deb.typesaisie
FROM    matable deb
    INNER JOIN
        matable fin
        ON  deb.idvoiture   = fin.idvoiture
        AND deb.typesaisie  = fin.typesaisie
WHERE   (   deb.typesaisie  IN (3, 4, 5)
        AND EXISTS
            (   SELECT  NULL
                FROM    matable sel
                WHERE   deb.idvoiture   = sel.idvoiture
                    AND deb.datesaisie  = sel.datesaisie
                    AND (   deb.typesaisie  = sel.typesaisie
                        OR  (   deb.typesaisie IS NULL 
                            AND sel.typesaisie IS NULL
                            )
                        )
                HAVING  deb.datedebut   = MIN(sel.datedebut)
            )
        AND EXISTS
            (   SELECT  NULL
                FROM    matable sel
                WHERE   fin.idvoiture   = sel.idvoiture
                    AND fin.datesaisie  = sel.datesaisie
                    AND (   fin.typesaisie  = sel.typesaisie
                        OR  (   fin.typesaisie IS NULL 
                            AND sel.typesaisie IS NULL
                            )
                        )
                HAVING  fin.datefin     = MAX(sel.datefin)
            )
        )
    OR  (   deb.typesaisie  NOT IN (3, 4, 5)
        AND deb.id  = fin.id
        )
;

27/09/2013, 08h58
Igname

Citation:

Envoyé par Waldar

Si sur les autres types de saisies il n'y a qu'une ligne, la requête fonctionnera avec ou sans agrégat.

Malheureusement non pour les autres types de saisies il peut y avoir plusieurs lignes. C'est dommage sinon la requête était parfait :)

@al1_24
Merci je vais faire des tests avec ça.
27/09/2013, 11h29
Igname

@al1_24

Ma fois ça m'a l'air plutôt top :ccool:, je continue mes tests. En revanche je n'ai pas les lignes null. La condition "OR (deb.typesaisie IS NULL AND sel.typesaisie IS NULL) )" ne semble pas fonctionner.
Ou peut-être que plus haut dans la requête il y a une condition en trop.
Enfin pour le moment je n'arrive pas à trouver ce qui empêche le bon fonctionnement

Bonjour,

Je reviens à la charge :mouarf:

Après plusieurs tests je me suis rendu compte que c'était les lignes surlignées qui posaient problèmes et qui empêchaient le retour des lignes avec un typesaisie NULL

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
41
42
43
44
45
46
47
48
49
50
51
WITH    matable
    AS  (   SELECT  *
            FROM    table1  tb1
            WHERE   NOT EXISTS
                    (   SELECT  NULL
                        FROM    table2  tb2
                        WHERE   tb1.id  = tb2.id
                    )
        )
SELECT  deb.id
    ,   deb.idvoiture
    ,   deb.datesaisie
    ,   deb.datedebut
    ,   fin.datefin
    ,   fin.puissance
    ,   deb.typesaisie
FROM    matable deb
    INNER JOIN
        matable fin
        ON  deb.idvoiture   = fin.idvoiture
        AND deb.typesaisie  = fin.typesaisie
WHERE   (   deb.typesaisie  IN (3, 4, 5)
        AND EXISTS
            (   SELECT  NULL
                FROM    matable sel
                WHERE   deb.idvoiture   = sel.idvoiture
                    AND deb.datesaisie  = sel.datesaisie
                    AND (   deb.typesaisie  = sel.typesaisie
                        OR  (   deb.typesaisie IS NULL 
                            AND sel.typesaisie IS NULL
                            )
                        )
                HAVING  deb.datedebut   = MIN(sel.datedebut)
            )
        AND EXISTS
            (   SELECT  NULL
                FROM    matable sel
                WHERE   fin.idvoiture   = sel.idvoiture
                    AND fin.datesaisie  = sel.datesaisie
                    AND (   fin.typesaisie  = sel.typesaisie
                        OR  (   fin.typesaisie IS NULL 
                            AND sel.typesaisie IS NULL
                            )
                        )
                HAVING  fin.datefin     = MAX(sel.datefin)
            )
        )
    OR  (   deb.typesaisie  NOT IN (3, 4, 5)
        AND deb.id  = fin.id
        )
;

Mais si je les enlève les résultats ne sont plus bon du tout.

J'ai fait plusieurs tentatives mais la j'avoue que je bloque et ne vois pas comment faire.

Merci à vous

As-tu essayé en remplaçant

Code:

AND deb.typesaisie = fin.typesaisie

par

Code:

1
2
3
4
5
        AND (   deb.typesaisie  = fin.typesaisie
            OR  (   deb.typesaisie IS NULL 
                AND fin.typesaisie IS NULL
                )
            )

Actuellement je teste ça :

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
WITH    prerequete
    AS  (   SELECT  *
            FROM    modification_programme  mp
            AND   NOT EXISTS
                    (   SELECT  NULL
                        FROM    redeclaration  r
                        WHERE   mp.id  = r.id
                    )
        )
SELECT max(id)                                                     AS id
       , idvoiture
       , datesaisie
       , min(datedebut)                                              AS datedebut
       , max(datefin)                                                AS datefin
       , max(puissance) keep(dense_rank first ORDER BY datefin DESC) AS puissance
       , typesaisie
    FROM prerequete p
    where p.typesaisie in (1,3,4)
GROUP BY identite
       , datesaisie
       , typesaisie
UNION
SELECT id                                                  AS id
       , idvoiture
       , datesaisie
       , datedebut                                            AS datedebut
       , datefin                                                AS datefin
       , puissance   AS puissance
       , typesaisie
    FROM prerequete p
    where p.typesaisie not in (1,3,4)or p.typesaisie is null
ORDER BY dateSaisie DESC, dateDebut DESC;

Je suis en train de vérifier mes résultats mais dans l'esprit ça semble pouvoir faire ce que je désire je pense ?

04/10/2013, 00h14
Chtulus

Bonsoir,

Et la réponse de @al1_24 donne quoi ?

;)
08/10/2013, 10h08
Igname

Bonjour,

La dernière requête que j'ai posté fonctionne pour mon besoin. C'est super.

Merci à tous. Je passe en résolu

:ccool: