plusieurs COUNT sur une seule table avec conditions différentes

Version imprimable

Bonjour,

j'ai la table basique suivante :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE TABLE tmptable ( 
	id NUMBER(10) NOT NULL,
	lieu CHAR(10) NOT NULL,
	moment DATE DEFAULT SYSDATE NOT NULL,
) 
;
 
INSERT INTO tmptable( id , lieu , moment)
VALUES              ( 1 , 1 , SYSDATE-3);
INSERT INTO tmptable( id , lieu , moment)
VALUES              ( 2 , 2 , SYSDATE-2);
INSERT INTO tmptable( id , lieu , moment)
VALUES              ( 3 , 3 , SYSDATE-2);
INSERT INTO tmptable( id , lieu , moment)
VALUES              ( 4 , 1 , SYSDATE);
INSERT INTO tmptable( id , lieu , moment)
VALUES              ( 5 , 3 , SYSDATE);
INSERT INTO tmptable( id , lieu , moment)
VALUES              ( 6 , 3 , SYSDATE);
INSERT INTO tmptable( id , lieu , moment)
VALUES              ( 7 , 2 , SYSDATE);
INSERT INTO tmptable( id , lieu , moment)
VALUES              ( 8 , 2 , SYSDATE-1);

Je dois récupérer les informations comme suit : le lieu, le nombre d'enregistrement pour le jour-même, le nombre d'enregistrement pour la veille et le nombre d'enregistrement pour l'avant-veille.

J'ai pensé utiliser une requête complexe comme celle-ci :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT lieu,
    COUNT(DISTINCT aj.*) AS aujourdhui,
    COUNT(DISTINCT hi.*) AS hier,
    COUNT(DISTINCT au.*) AS auparavant
FROM
   tmptable aj,
   tmptable hi,
   tmptable au
WHERE
      (aj.lieu = hi.lieu)
    AND (aj.lieu = au.lieu)
    AND (TRUNC(aj.moment) = TRUNC(SYSDATE))
    AND (TRUNC(hi.moment) = TRUNC(SYSDATE-1))
    AND (TRUNC(au.moment) < TRUNC(SYSDATE-1))
GROUP BY lieu;

Malheureusement, comme vous pouvez vous en douter, cela ne fonctionne pas. Comment modifier ma requête pour obtenir le résultat suivant (avec le test donné) ?

Code:

1
2
3
4
5
 
lieu aujourdhui hier auparavant
   1          1    0          1
   2          1    1          1
   3          2    0          1

Cette requête répond à ta demande :

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
 
select lieu,sum(aujourdhui) as aujourdhui,sum(hier) as hier,sum(auparavant) as auparavant
from(
select lieu,COUNT( moment) AS aujourdhui,
    COUNT( moment)-COUNT( moment) AS hier,
    COUNT( moment)-COUNT( moment) AS auparavant 
from tmptable
where TRUNC(moment) = TRUNC(SYSDATE)
group by lieu
union all
select lieu,COUNT(moment)-COUNT(moment) AS aujourdhui,
    COUNT( moment) AS hier,
    COUNT( moment)-COUNT( moment) AS auparavant 
from tmptable
where TRUNC(moment) = TRUNC(SYSDATE-1)
group by lieu,moment
union all
select lieu,COUNT( moment)-COUNT( moment) AS aujourdhui,
    COUNT( moment)-COUNT( moment) AS hier,
    COUNT( moment) AS auparavant 
from tmptable
where TRUNC(moment) < TRUNC(SYSDATE-1)
group by lieu,moment)
group by lieu

Ceci dit, je ne suis pas certain quelle soit très performante sur des tables ayant une grosse volumétrie.

Bon courage...

Code:

1
2
3
4
5
6
7
8
9
10
11
12
SELECT lieu,
SUM(CASE WHEN TRUNC(aj.moment) = TRUNC(SYSDATE) THEN 1 ELSE 0 END) AS aujourdhui,
SUM(CASE WHEN TRUNC(aj.moment) = TRUNC(SYSDATE-1) THEN 1 ELSE 0 END) AS hier,
SUM(CASE WHEN TRUNC(aj.moment) < TRUNC(SYSDATE-1) THEN 1 ELSE 0 END) AS auparavant
FROM
   tmptable aj,
   tmptable hi,
   tmptable au
WHERE
      (aj.lieu = hi.lieu)
    AND (aj.lieu = au.lieu)
GROUP BY lieu;

18/12/2006, 15h07
dingoth

Merci beaucoup à vous deux :)

Je dois avouer que vos deux réponses me bluffent car elles furent rapides et le résultat en découlant est exactement ce que je souhaitais. Celle de nuke_y me bluffe d'autant plus qu'elle prend moins de 10 lignes : on peut encore supprimer deux tmptable pour n'en garder plus qu'une, ainsi que toute la clause where. Ce qui fait que la requête instancie une seule fois tmptable, contre trois avec ce que je m'attendais et ce que djeant m'a donné.
18/12/2006, 15h47
nuke_y

Fait quand même gaffe à un truc : ma requête ne contient pas de distinct, donc si c'était important, ça peut fausser les résultats. Si tu utilisais Distinct pour des histoires de doublons provoqués par tes tables satellites, il vaudrait mieux utiliser le EXISTS plutôt qu'une jointure + distinct.
18/12/2006, 16h20
dingoth

Non, la requête se fait bien sur une et une seule table.

J'avais mis les Distinct parce qu'avec, le résultat se rapprochait de celui attendu, vu que j'utilisais trois fois la même table (une par groupe de dates) et que sans, j'obtenais des résultats incompréhensibles.

J'avais bien entendu simplifié la table utilisée, mais une fois la requête retraduit pour coller à mon environnement, tout va très bien.

Encore merci ;)

Citation:

Envoyé par nuke_y

Code:

1
2
3
4
5
6
7
8
9
10
11
12
SELECT lieu,
SUM(CASE WHEN TRUNC(aj.moment) = TRUNC(SYSDATE) THEN 1 ELSE 0 END) AS aujourdhui,
SUM(CASE WHEN TRUNC(aj.moment) = TRUNC(SYSDATE-1) THEN 1 ELSE 0 END) AS hier,
SUM(CASE WHEN TRUNC(aj.moment) < TRUNC(SYSDATE-1) THEN 1 ELSE 0 END) AS auparavant
FROM
   tmptable aj,
   tmptable hi,
   tmptable au
WHERE
      (aj.lieu = hi.lieu)
    AND (aj.lieu = au.lieu)
GROUP BY lieu;

8O alors là, faut qu'on m'explique à quoi servent les tables hi et au...

pourquoi pas faire simplement:

Code:

1
2
3
4
5
6
7
SELECT lieu,
SUM(CASE WHEN TRUNC(aj.moment) = TRUNC(SYSDATE) THEN 1 ELSE 0 END) AS aujourdhui,
SUM(CASE WHEN TRUNC(aj.moment) = TRUNC(SYSDATE-1) THEN 1 ELSE 0 END) AS hier,
SUM(CASE WHEN TRUNC(aj.moment) < TRUNC(SYSDATE-1) THEN 1 ELSE 0 END) AS auparavant
FROM
   tmptable aj
GROUP BY aj.lieu;

19/12/2006, 22h20
nuke_y

J'en sais rien moi, à filtrer sur les lieux qui existent dans les tables ? Oui oui je sais, faudrait utiliser EXISTS, mais moi je rend service c'est tout ;)
19/12/2006, 23h08
remi4444

ben, vu qu'il s'agit de la meme table je vois pas trop ce que ça va filtrer... ça va juste filtrer les colonne ou "lieu" est nul mais bon, y'a "is not null" pour ça...

PS: j'ai fait un effort, j'ai passé toute une journée sans parler de EXISTS, mais si tu remets ça sur le tapis.... ;)
20/12/2006, 10h13
nuke_y

looooooooooooool. J'avais pas vu qu'il s'agissait de la même table en effet ! Ah ben oui dans ce cas là :lol:

Je pense qu'il s'agissait de résidus d'artifice pour essayer de faire la requête qu'il voulait. Il a du réduire à une seule table dans sa requête finale.