Discussion :

[EnRadeSurEclipse]

Bonsoir
j'arrive à rien je vois plus rien.
Voilà j'ai des relations entre conurbations, de différents modes de transport (codés par 'dtype') et je veux sortir les relations ayant une réciproque, càd si j'ai du A-B en train et du B-A aussi en train, je prends sir la réciproque est en un autre mode, avion, ou n'existe pas je veux ne pas sélectionner cette relation.

voici ma requête:

Code :

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

1
2
3
4
5
insert into `rt_air_rail_5_23_reciproques`  SELECT r1.`dNomOri`, r1.`dNomDes`, r1.`dNomConurbOri` , r1.`dNomConurbDes`, r1.`dHdep`, r1.`dHArr`, r1.`dTemps`, r1.`dTVeh`, r1.`dTMap`, r1.`dTAtt`, r1.`dTAtt1`, r1.`dPole`, r1.`dTexte`, r1.`dType`, r1.`tpsDispo`, r1.`aType`, r1.`aHdep`, r1.`aHArr`, r1.`aTemps`, r1.`aTVeh`, r1.`aTMap`, r1.`aTAtt`, r1.`atatt1`, r1.`aPole`, r1.`aTexte`, r1.`dId`, r1.`aId`, r1.`dOri`, r1.`dDes`, r1.`aOri`, r1.`aDes`, r1.`dConurbOri` , r1.`dConurbDes`, r1.dModeOri, r1.dModeDes, r1.`aNomOri`, r1.`aNomDes`, r1.orix, r1.oriy, r1.desx, r1.desy
FROM `rt_air_rail_5_23_valides_best_by_mode` r1
INNER JOIN `rt_air_rail_5_23_valides_best_by_mode` r2 ON 
r1.dtype= r2.dtype and 
r1.`dConurbOri`=r2.`dConurbDes` and r1.`dConurbDes`=r2.`dConurbOri`;

j'ai 5090 lignes dans ma table et cette requête, censée m'en sortir moins m'en crache 7000 et des brouettes. Donc il y a un problème mais je ne le vois pas.

[Maljuna Kris]

Saluton,

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
INSERT INTO `rt_air_rail_5_23_reciproques`  
    SELECT r1.`dNomOri`, r1.`dNomDes`, r1.`dNomConurbOri` , r1.`dNomConurbDes`, 
           r1.`dHdep`, r1.`dHArr`, r1.`dTemps`, r1.`dTVeh`, r1.`dTMap`, r1.`dTAtt`,
           r1.`dTAtt1`, r1.`dPole`, r1.`dTexte`, r1.`dType`, r1.`tpsDispo`, r1.`aType`,
           r1.`aHdep`, r1.`aHArr`, r1.`aTemps`, r1.`aTVeh`, r1.`aTMap`, r1.`aTAtt`,
           r1.`atatt1`, r1.`aPole`, r1.`aTexte`, r1.`dId`, r1.`aId`, r1.`dOri`,
           r1.`dDes`, r1.`aOri`, r1.`aDes`, r1.`dConurbOri` , r1.`dConurbDes`,
           r1.dModeOri, r1.dModeDes, r1.`aNomOri`, r1.`aNomDes`, r1.orix, r1.oriy,
           r1.desx, r1.desy
    FROM `rt_air_rail_5_23_valides_best_by_mode` r1
    INNER JOIN `rt_air_rail_5_23_valides_best_by_mode` r2 
            ON r1.dtype= r2.dtype 
            AND r1.`dConurbOri`=r2.`dConurbDes` 
            AND r1.`dConurbDes`=r2.`dConurbOri`;

Je ne vois aucun filtre WHERE sur le mode de transport.

[EnRadeSurEclipse]

Euh, pourquoi saluton? et pour quoi pas bonjour?

Bon passons aux choses sérieuses; le mode de transport est codé dans la variable appelée "dType". Je sais on a vu plus explicite, mais le mode peut être assez compliqué, c'est même plutôt une chaine modale en fait, donc pour faire court ça devient un type.

Si je lis bien ton message j'ai 'impression que tu n'as fait que réécrire ma requête sans la modifier. Me trompé-je?

Sinon j'ai avancé sur le chemin. J'ai rajouté un group by sur les dConurbOri et dConurbDes, qui me sort moins de choses mais j'ai constaté qu'il a fonctionné sur certains cas mais pas sur d'autres, donc toujours pas la panacée.

[Maljuna Kris]

Euh, pourquoi saluton? et pour quoi pas bonjour?

Parce que je suis espérantiste

Bon passons aux choses sérieuses; le mode de transport est codé dans la variable appelée "dType". Je sais on a vu plus explicite, mais le mode peut être assez compliqué, c'est même plutôt une chaine modale en fait, donc pour faire court ça devient un type.

Si je lis bien ton message j'ai 'impression que tu n'as fait que réécrire ma requête sans la modifier. Me trompé-je?

J'ai tenté de la rendre plus lisible à l'écran.
Il n'en demeure pas moins qu'elle ne comporte aucun filtre sur le mode de transport

Sinon j'ai avancé sur le chemin. J'ai rajouté un group by sur les dConurbOri et dConurbDes, qui me sort moins de choses mais j'ai constaté qu'il a fonctionné sur certains cas mais pas sur d'autres, donc toujours pas la panacée.

Utiliser GROUP BY en dehors de sa finalité à être couplé avec des fonctions statistiques s'est s'exposer à recevoir un résultat sans grande signification. Surtout avec MySQL qui se montre très laxiste sur ce point.

[EnRadeSurEclipse]

Saluton, donc!

j'ai beaucoup évolué sur cette requête, pour ne pas dire progressé.
D'abord sur les modes de transport, j'ai relâché la pression. Mais pour répondre à ta question pour moi, au moment ou j'ai écrit la requête, la contrainte sur les modes etait matérialisée dans le ON, avec la condition:

Code :

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

ON r1.dtype= r2.dtype

En effet tu propose une explication théorique à ce que j'avais constaté empiriquement: une grande fantaisie des résultats en adjoignant une clause GROUP BY.

Du coup j'ai totalement décomposé mon problème. Je me dis maintenant avec le recul que j'ai voulu une formule magique qui me donne tout d'un coup, et c'était peut être illusoire.

Bon, je peux fournir ici les instructions qui m'ont permis de résoudre mon pb mais je ne suis pas certain que cela peut intéresser les lecteurs du forum. Parce que j'ai produit une tétrachiée de tables, presque toutes en dur car j'ai eu trop de déboires avec les tables temporaires.

Dans le doute, et pour les accro à la ligne de code:

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
#Les valides
 
insert into `rt_air_rail_5_23_valides`  SELECT  r1.`dNomOri`, r1.`dNomDes`, r1.`dNomConurbOri` , r1.`dNomConurbDes`, r1.`dHdep`, r1.`dHArr`, r1.`dTemps`, r1.`dTVeh`, r1.`dTMap`, r1.`dTAtt`, r1.`dTAtt1`, r1.`dPole`, r1.`dTexte`, r1.`dType`, r1.`tpsDispo`, r1.`aType`, r1.`aHdep`, r1.`aHArr`, r1.`aTemps`, r1.`aTVeh`, r1.`aTMap`, r1.`aTAtt`, r1.`atatt1`, r1.`aPole`, r1.`aTexte`, r1.`dId`, r1.`aId`, r1.`dOri`, r1.`dDes`, r1.`aOri`, r1.`aDes`, r1.`dConurbOri` , r1.`dConurbDes`, r1.dModeOri, r1.dModeDes, r1.`aNomOri`, r1.`aNomDes`, r1.orix, r1.oriy, r1.desx, r1.desy
FROM `rt_air_rail_5_23` r1
WHERE mid(r1.dtype, 3, 1) =1 and   #rail
(r1.tpsdispo > "07:29:00") 
   or mid(r1.dtype, 3, 1) =2 and    #air
(r1.tpsdispo > "08:29:00" );
 
# les valides les plus rapides par mode
CREATE ALGORITHM = UNDEFINED VIEW `v_best_by_mode_rt_5_23` AS select MAX(`tpsDispo`) as maxTpsDispo, `dconurbOri` ,`dconurbDes`, dtype, dnomconurbori, dnomconurbdes, dNomOri, dNomDes  from `rt_air_rail_5_23_valides` group by `dconurbOri` ,`dconurbDes`, dtype;
 
insert into `rt_air_rail_5_23_valides_best_by_mode`  SELECT  r1.`dNomOri`, r1.`dNomDes`, r1.`dNomConurbOri` , r1.`dNomConurbDes`, r1.`dHdep`, r1.`dHArr`, r1.`dTemps`, r1.`dTVeh`, r1.`dTMap`, r1.`dTAtt`, r1.`dTAtt1`, r1.`dPole`, r1.`dTexte`, r1.`dType`, r1.`tpsDispo`, r1.`aType`, r1.`aHdep`, r1.`aHArr`, r1.`aTemps`, r1.`aTVeh`, r1.`aTMap`, r1.`aTAtt`, r1.`atatt1`, r1.`aPole`, r1.`aTexte`, r1.`dId`, r1.`aId`, r1.`dOri`, r1.`dDes`, r1.`aOri`, r1.`aDes`, r1.`dConurbOri` , r1.`dConurbDes`, r1.dModeOri, r1.dModeDes, r1.`aNomOri`, r1.`aNomDes`, r1.orix, r1.oriy, r1.desx, r1.desy
FROM `rt_air_rail_5_23_valides` r1
right join `v_best_by_mode_rt_5_23` r2 on
r1.`dconurbOri` = r2.`dconurbOri` AND r1.`dconurbDes` = r2.`dconurbDes`
and r1.tpsdispo=r2.maxTpsDispo
group by r1.`dConurbOri`, r1.`dConurbDes`, r1.dtype;

C'est pas fini :

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
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
# isole les AR doubles meilleurs par mode
 
insert into `rt_air_rail_5_23_reciproques`  SELECT r1.`dNomOri`, r1.`dNomDes`, r1.`dNomConurbOri` , r1.`dNomConurbDes`, r1.`dHdep`, r1.`dHArr`, r1.`dTemps`, r1.`dTVeh`, r1.`dTMap`, r1.`dTAtt`, r1.`dTAtt1`, r1.`dPole`, r1.`dTexte`, r1.`dType`, r1.`tpsDispo`, r1.`aType`, r1.`aHdep`, r1.`aHArr`, r1.`aTemps`, r1.`aTVeh`, r1.`aTMap`, r1.`aTAtt`, r1.`atatt1`, r1.`aPole`, r1.`aTexte`, r1.`dId`, r1.`aId`, r1.`dOri`, r1.`dDes`, r1.`aOri`, r1.`aDes`, r1.`dConurbOri` , r1.`dConurbDes`, r1.dModeOri, r1.dModeDes, r1.`aNomOri`, r1.`aNomDes`, r1.orix, r1.oriy, r1.desx, r1.desy
FROM `rt_air_rail_5_23_valides_best_by_mode` r1
INNER JOIN `rt_air_rail_5_23_valides_best_by_mode` r2 ON 
r1.`dConurbOri`=r2.`dConurbDes` and r1.`dConurbDes`=r2.`dConurbOri`; 
#a ce stade tout est permis, y c un a-r fer et un ar réciproque air-air
 
 
# ne sortir que la meilleure combinaison modale sur un couple
# d'abord créer une table des meilleurs valeurs par couples
CREATE ALGORITHM = UNDEFINED VIEW `v_best_rt_5_23` AS select MAX(`tpsDispo`) as maxTpsDispo, `dconurbOri` ,`dconurbDes`, dnomconurbori, dnomconurbdes, dtype, dNomOri, dNomDes from `rt_air_rail_5_23_valides` group by `dconurbOri` ,`dconurbDes`;
 
 
# la table des meilleures combinaisons modales parmi les AR réciproques (inner pour seulement les concordances complètes)
# attention risque que les modes soient différents entre a-b et B-A
insert into `rt_air_rail_5_23_best_reciproques`  SELECT  r1.`dNomOri`, r1.`dNomDes`, r1.`dNomConurbOri` , r1.`dNomConurbDes`, r1.`dHdep`, r1.`dHArr`, r1.`dTemps`, r1.`dTVeh`, r1.`dTMap`, r1.`dTAtt`, r1.`dTAtt1`, r1.`dPole`, r1.`dTexte`, r1.`dType`, r1.`tpsDispo`, r1.`aType`, r1.`aHdep`, r1.`aHArr`, r1.`aTemps`, r1.`aTVeh`, r1.`aTMap`, r1.`aTAtt`, r1.`atatt1`, r1.`aPole`, r1.`aTexte`, r1.`dId`, r1.`aId`, r1.`dOri`, r1.`dDes`, r1.`aOri`, r1.`aDes`, r1.`dConurbOri` , r1.`dConurbDes`, r1.dModeOri, r1.dModeDes, r1.`aNomOri`, r1.`aNomDes`, r1.orix, r1.oriy, r1.desx, r1.desy
FROM `rt_air_rail_5_23_reciproques` r1
INNER JOIN `v_best_rt_5_23`  # la plage horaire est maximale
r2 ON 
r1.`dconurbOri` = r2.`dconurbOri` AND r1.`dconurbDes` = r2.`dconurbDes`
and r1.tpsdispo=r2.maxTpsDispo;
 
#une étape ultime pour enlever des doublons
insert into `rt_air_rail_5_23_best_reciproques_sd`  
select distinct 
`dNomOri`, `dNomDes`, `dNomConurbOri`, `dNomConurbDes`, `dHDep`, `dHArr`, `dTemps`, `dTVeh`, `dTMap`, `dTAtt`, `dTAtt1`, `dPole`, `dTexte`, `dType`, `tpsDispo`, `aType`, `aHdep`, `aHArr`, `aTemps`, `aTVeh`, `aTMap`, `aTAtt`, `aTAtt1`, `aPole`, `aTexte`, `dId`, `aId`, `dOri`, `dDes`, `aOri`, `aDes`, `dConurbOri`, `dConurbDes`, `dModeOri`, `dModeDes`, `aNomOri`, `aNomDes`, `orix`, `oriy`, `desx`, `desy`
from `rt_air_rail_5_23_best_reciproques`;
 
# les simples
insert into `rt_air_rail_5_23_simples`  SELECT r1.`dNomOri`, r1.`dNomDes`, r1.`dNomConurbOri` , r1.`dNomConurbDes`, r1.`dHdep`, r1.`dHArr`, r1.`dTemps`, r1.`dTVeh`, r1.`dTMap`, r1.`dTAtt`, r1.`dTAtt1`, r1.`dPole`, r1.`dTexte`, r1.`dType`, r1.`tpsDispo`, r1.`aType`, r1.`aHdep`, r1.`aHArr`, r1.`aTemps`, r1.`aTVeh`, r1.`aTMap`, r1.`aTAtt`, r1.`atatt1`, r1.`aPole`, r1.`aTexte`, r1.`dId`, r1.`aId`, r1.`dOri`, r1.`dDes`, r1.`aOri`, r1.`aDes`, r1.`dConurbOri` , r1.`dConurbDes`, r1.dModeOri, r1.dModeDes, r1.`aNomOri`, r1.`aNomDes`, r1.orix, r1.oriy, r1.desx, r1.desy
FROM `rt_air_rail_5_23_valides_best_by_mode` r1
left JOIN `rt_air_rail_5_23_valides_best_by_mode` r2 ON 
r1.`dConurbOri`=r2.`dConurbDes` and r1.`dConurbDes`=r2.`dConurbOri`
where r2.`dOri` is NULL; 
#un AR simple n'a rien en réciproque, quel que soit le mode considéré, il n'y a rien de rien en face
 
insert into `rt_air_rail_5_23_best_simples`  SELECT  r1.`dNomOri`, r1.`dNomDes`, r1.`dNomConurbOri` , r1.`dNomConurbDes`, r1.`dHdep`, r1.`dHArr`, r1.`dTemps`, r1.`dTVeh`, r1.`dTMap`, r1.`dTAtt`, r1.`dTAtt1`, r1.`dPole`, r1.`dTexte`, r1.`dType`, r1.`tpsDispo`, r1.`aType`, r1.`aHdep`, r1.`aHArr`, r1.`aTemps`, r1.`aTVeh`, r1.`aTMap`, r1.`aTAtt`, r1.`atatt1`, r1.`aPole`, r1.`aTexte`, r1.`dId`, r1.`aId`, r1.`dOri`, r1.`dDes`, r1.`aOri`, r1.`aDes`, r1.`dConurbOri` , r1.`dConurbDes`, r1.dModeOri, r1.dModeDes, r1.`aNomOri`, r1.`aNomDes`, r1.orix, r1.oriy, r1.desx, r1.desy
FROM `rt_air_rail_5_23_simples` r1
INNER JOIN `v_best_rt_5_23`  # la plage horaire est maximale
r2 ON 
r1.`dconurbOri` = r2.`dconurbOri` AND r1.`dconurbDes` = r2.`dconurbDes`
and r1.tpsdispo=r2.maxTpsDispo;
 
#une étape ultime pour enlever des doublons
insert into `rt_air_rail_5_23_best_simples_sd`  
select distinct 
`dNomOri`, `dNomDes`, `dNomConurbOri`, `dNomConurbDes`, `dHDep`, `dHArr`, `dTemps`, `dTVeh`, `dTMap`, `dTAtt`, `dTAtt1`, `dPole`, `dTexte`, `dType`, `tpsDispo`, `aType`, `aHdep`, `aHArr`, `aTemps`, `aTVeh`, `aTMap`, `aTAtt`, `aTAtt1`, `aPole`, `aTexte`, `dId`, `aId`, `dOri`, `dDes`, `aOri`, `aDes`, `dConurbOri`, `dConurbDes`, `dModeOri`, `dModeDes`, `aNomOri`, `aNomDes`, `orix`, `oriy`, `desx`, `desy`
from rt_air_rail_5_23_best_simples;
#ne s'est pas avérée nécessaire celle ci !!

Et voilà le travail.

ps merci Maljuna.