SQL: Requête Complexe

Version imprimable

Bonjour,

Je voulais changer la requête initiale en enlevant le WITH mais cela me donne des valeurs erronées.

Merci de bien vouloir m'aider.

La requête initiale:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
WITH TRAFI AS (SELECT ID_DATE, SUM(COUT_INTR) AS COUT_TRAFIC  FROM TRAFIC
WHERE ID_DATE between '21/12/2007' and '27/12/2007'
AND TYP_TRFC = 'Voice' 
AND DIRC_APPL = 'OUT'
GROUP BY ID_DATE),
TRAFIC_OP AS (SELECT ID_DATE, SUM(COUT_INTR) AS COUT_TRAFIC_OPERATEUR FROM TRAFIC_OPERATEUR
WHERE ID_DATE between '21/12/2007' and '27/12/2007'
AND TYP_TRFC <> 'SMS'
AND DIRC_APPL <> 'IN'
GROUP BY ID_DATE),
TICKETS_INTER AS (SELECT ID_DATE, SUM(COUT_HT) AS COUT_TICKETS_INTERCO FROM TICKETS_INTERCO
WHERE ID_DATE  between '21/12/2007' and '27/12/2007'
AND CALL_DIRC = 'OUT'
AND CALL_TYP = 'Voice'
AND CALL_TYP2 NOT IN ('1','2','3')
GROUP BY ID_DATE)
SELECT TRAFI.ID_DATE, TRAFI.COUT_TRAFIC, TRAFIC_OP.COUT_TRAFIC_OPERATEUR, TICKETS_INTER.COUT_TICKETS_INTERCO
FROM TRAFI, TRAFIC_OP, TICKETS_INTER
WHERE TRAFI.ID_DATE = TRAFIC_OP.ID_DATE
AND TRAFI.ID_DATE = TICKETS_INTER.ID_DATE

Solution1:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
select T.id_date, sum(T.COUT_INTR), sum(TP.cout_intr), sum(TI.cout_ht)
from trafic T, 
( 
select cout_intr 
from trafic_operateur
WHERE ID_DATE between '21/12/2007' and '27/12/2007'
AND TYP_TRFC <> 'SMS'
AND DIRC_APPL <> 'IN'
) TP,
(
select cout_ht
from tickets_interco
WHERE ID_DATE  between '21/12/2007' and '27/12/2007'
AND CALL_DIRC = 'OUT'
AND CALL_TYP = 'Voice'
AND CALL_TYP2 NOT IN ('1','2','3')
) TI
WHERE ID_DATE between '21/12/2007' and '27/12/2007'
AND TYP_TRFC = 'Voice' 
AND DIRC_APPL = 'OUT'
group by T.id_date

Solution2:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
select T.id_date, sum(T.COUT_INTR), sum(TP.cout_intr), sum(TI.cout_ht)
from trafic T, trafic_operateur TP, tickets_interco TI
where T.id_date = TP.id_date
and  T.id_date = TI.id_date
AND T.TYP_TRFC = 'Voice' 
AND T.DIRC_APPL = 'OUT'
AND TI.CALL_DIRC = 'OUT'
AND TI.CALL_TYP = 'Voice'
AND TI.CALL_TYP2 NOT IN ('1','2','3')
AND TP.TYP_TRFC <> 'SMS'
AND TP.DIRC_APPL <> 'IN'
group by T.id_date

Solution3:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
select T.id_date, sum(T.COUT_INTR), sum(TP.cout_intr), sum(TI.cout_ht)
from trafic T, trafic_operateur TP, tickets_interco TI
where T.id_date = TP.id_date
and  T.id_date = TI.id_date
and T.ID_DATE between '21/12/2007' and '27/12/2007'
AND T.TYP_TRFC = 'Voice' 
AND T.DIRC_APPL = 'OUT'
AND TI.CALL_DIRC = 'OUT'
AND TI.CALL_TYP = 'Voice'
AND TI.CALL_TYP2 NOT IN ('1','2','3')
AND TP.TYP_TRFC <> 'SMS'
AND TP.DIRC_APPL <> 'IN'
group by T.id_date

Soyez sympa et commencez par bien indenter vos requêtes. Sans cela c'est illisible et vous même aurez du mal à vous dépatouiller !

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
WITH 
 
TRAFI AS 
(SELECT ID_DATE, SUM(COUT_INTR) AS COUT_TRAFIC  
 FROM   TRAFIC
 WHERE  ID_DATE BETWEEN '21/12/2007' AND '27/12/2007'
   AND  TYP_TRFC = 'Voice' 
   AND  DIRC_APPL = 'OUT'
 GROUP  BY ID_DATE),
 
TRAFIC_OP AS 
(SELECT ID_DATE, SUM(COUT_INTR) AS COUT_TRAFIC_OPERATEUR 
 FROM   TRAFIC_OPERATEUR
 WHERE  ID_DATE BETWEEN '21/12/2007' AND '27/12/2007'
   AND  TYP_TRFC <> 'SMS'
   AND  DIRC_APPL <> 'IN'
 GROUP BY ID_DATE),
 
TICKETS_INTER AS 
(SELECT ID_DATE, SUM(COUT_HT) AS COUT_TICKETS_INTERCO 
 FROM   TICKETS_INTERCO
 WHERE  ID_DATE  BETWEEN '21/12/2007' AND '27/12/2007'
   AND  CALL_DIRC = 'OUT'
   AND  CALL_TYP = 'Voice'
   AND  CALL_TYP2 NOT IN ('1','2','3')
 GROUP  BY ID_DATE)
 
SELECT TRAFI.ID_DATE, TRAFI.COUT_TRAFIC, TRAFIC_OP.COUT_TRAFIC_OPERATEUR, TICKETS_INTER.COUT_TICKETS_INTERCO
FROM   TRAFI
       INNER JOIN TRAFIC_OP
             ON TRAFI.ID_DATE = TRAFIC_OP.ID_DATE
       INNER JOIN TICKETS_INTER
             ON TRAFI.ID_DATE = TICKETS_INTER.ID_DATE

Voici une solution.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
SELECT TRAFI.ID_DATE, TRAFI.COUT_TRAFIC, TRAFIC_OP.COUT_TRAFIC_OPERATEUR, TICKETS_INTER.COUT_TICKETS_INTERCO
FROM   (SELECT ID_DATE, SUM(COUT_INTR) AS COUT_TRAFIC  
        FROM   TRAFIC
        WHERE  ID_DATE BETWEEN '21/12/2007' AND '27/12/2007'
          AND  TYP_TRFC = 'Voice' 
          AND  DIRC_APPL = 'OUT'
        GROUP  BY ID_DATE) AS TRAFI
       INNER JOIN (SELECT ID_DATE, SUM(COUT_INTR) AS COUT_TRAFIC_OPERATEUR 
                   FROM   TRAFIC_OPERATEUR
                   WHERE  ID_DATE BETWEEN '21/12/2007' AND '27/12/2007'
                     AND  TYP_TRFC <> 'SMS'
                     AND  DIRC_APPL <> 'IN'
                   GROUP BY ID_DATE) AS TRAFIC_OP
             ON TRAFI.ID_DATE = TRAFIC_OP.ID_DATE
       INNER JOIN (SELECT ID_DATE, SUM(COUT_HT) AS COUT_TICKETS_INTERCO 
                   FROM   TICKETS_INTERCO
                   WHERE  ID_DATE  BETWEEN '21/12/2007' AND '27/12/2007'
                     AND  CALL_DIRC = 'OUT'
                     AND  CALL_TYP = 'Voice'
                     AND  CALL_TYP2 NOT IN ('1','2','3')
                   GROUP  BY ID_DATE) AS TICKETS_INTER
             ON TRAFI.ID_DATE = TICKETS_INTER.ID_DATE

Néanmoins je préfère nettement la version CTE plus lisible.

A +

Je voulais comparer 3 tables. Je m'explique.

- 1ère table: id_date, cout1 et d'autres champs
- 2ème table: id_date, cout2 et d'autres champs
- 3ème table: id_date, cout3 et d'autres champs

La requête consiste à donner la somme de chacun des couts groupés par id_date

ex: id_date | sum(cout1) | sum(cout2) | sum(cout3)

La requête que j'ai pu réaliser est la suivante:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 with tb1 as (select id_date, sum(cout1) from table1 where condition1 and condition2 ... group by id_date), tb2 as (select id_date, sum(cout2) from table2 where condition1 and condition2 ... group by id_date), tb3 as (select id_date, sum(cout3) from table3 where condition1 and condition2 ... group by id_date), select tb1.id_date, tb1.cout1 , tb2.cout2, tb3.cout3 from tb1, tb2, tb3 where tb1.id_date = tb2.id_date and tb1.id_date = tb3.id_date
Le problème reste au niveau du temps d'exécution surtout par rapport à la volumétrie des données stockées.

J'ai utilisé les techniques d'optimisation des requêtes mais malgrés cela il y a toujours ce problème.

Si cela est possible j'aimerai avoir une autre façon d'écrire ma requête d'une manière plus optimale.

Merci d'avance pour votre aide.

cordialement.

Je vous remercie Sqlpro d’avoir pris le temps de lire ma demande et d’y répondre.
Je tiens à préciser que mon problème se pose sur le temps d’exécution de la requête, je cherche à ce qu'il soit optimal, la requête que vous m’aviez proposé prend autant de temps que la mienne.:?

Ma requête initiale:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
WITH 
tb1 AS (SELECT id_date, sum(cout1) as cout1 FROM table1
WHERE condition1 AND condition2 ...
GROUP BY id_date),
tb2 AS (SELECT id_date, sum(cout2) as cout2 FROM table2
WHERE condition1 AND condition2 ...
GROUP BY id_date),
tb3 AS (SELECT id_date, sum(cout3) as cout3 FROM table3
WHERE condition1 AND condition2 ...
GROUP BY id_date),
SELECT tb1.id_date, tb1.cout1 , tb2.cout2, tb3.cout3
FROM tb1, tb2, tb3
WHERE tb1.id_date = tb2.id_date
AND tb1.id_date = tb3.id_date

La votre:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Select tb1.id_date, tb1.cout1, tb2.cout2, tb3.cout3
From 
(Select id_date, sum (cout1) as cout1
From table1
Where condition1, condition2…
Group by id_date) as tb1
Inner join (Select id_date, sum (cout2) as cout2
From table2
Where condition1, condition2…
Group by id_date) as tb2
On tb1.id_date = tb2.id_date
Inner join (Select id_date, sum (cout3) as cout3
From table3
Where condition1, condition2…
Group by id_date) as tb3
On tb1.id_date = tb3.id_date

Merci de m'aider.

11/05/2008, 23h24
SQLpro

Il aurait été plus simple de poser la bonne question dès le départ !

Commencez dans ce cas par décrire l'intégralité de vos tables sous forme d'ordre DDL.

En cela vous respecterez la charte de postage : http://www.developpez.net/forums/ann....php?f=37&a=69

A +