GROUP BY DATE et autre critère

**joKED** · 01/04/2020, 14h23

Bonjour à tous,

J'ai besoin d'aide sur une gestion de regroupement par DATE (sans time).
Petit exemple :
J'ai une table contenant des coureurs qui font chacun leur jogging entre 0 et N fois par jour. A chaque séquence de jogging, j'enregistre une ligne dans ma table.

Le but du jeu est de trouver pour chaque JOUR et pour chaque coureur la distance parcourue. Pas difficile. Sauf que, j'ai besoin d'inclure les jours où mes coureurs n'ont pas couru (les fainéants).

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
 
DECLARE @TableCoureur TABLE(
	SomeId BIGINT IDENTITY(1,1) PRIMARY KEY NOT NULL,
	CoureurId SMALLINT NOT NULL,
	RunDate DATETIME NOT NULL,
	Distance BIGINT NOT NULL
)
 
INSERT INTO @TableCoureur(CoureurId, RunDate, Distance) VALUES (1,'01/03/2020 14:30:00', 10)
INSERT INTO @TableCoureur(CoureurId, RunDate, Distance) VALUES (1,'01/03/2020 15:30:00', 10)
INSERT INTO @TableCoureur(CoureurId, RunDate, Distance) VALUES (1,'02/03/2020 14:30:00', 10)
INSERT INTO @TableCoureur(CoureurId, RunDate, Distance) VALUES (1,'03/03/2020 14:30:00', 10)
INSERT INTO @TableCoureur(CoureurId, RunDate, Distance) VALUES (1,'03/03/2020 07:30:00', 10)
INSERT INTO @TableCoureur(CoureurId, RunDate, Distance) VALUES (1,'05/03/2020 14:30:00', 10)
INSERT INTO @TableCoureur(CoureurId, RunDate, Distance) VALUES (1,'06/03/2020 14:30:00', 10)
INSERT INTO @TableCoureur(CoureurId, RunDate, Distance) VALUES (1,'07/03/2020 14:30:00', 10)
INSERT INTO @TableCoureur(CoureurId, RunDate, Distance) VALUES (2,'01/03/2020 14:30:00', 10)
INSERT INTO @TableCoureur(CoureurId, RunDate, Distance) VALUES (2,'03/03/2020 14:30:00', 10)
INSERT INTO @TableCoureur(CoureurId, RunDate, Distance) VALUES (2,'04/03/2020 14:30:00', 10)
INSERT INTO @TableCoureur(CoureurId, RunDate, Distance) VALUES (2,'05/03/2020 14:30:00', 10)
INSERT INTO @TableCoureur(CoureurId, RunDate, Distance) VALUES (2,'07/03/2020 14:30:00', 10)
 
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
 
SET @StartDate = '01/03/2020' 
SET @EndDate = '10/03/2020' 
 
;WITH CTE_DATE AS (
SELECT CAST(@StartDate AS DATE) [zeDay]
UNION ALL
SELECT DATEADD(DAY, 1, [zeDay]) FROM CTE_DATE
WHERE [zeDay] < @EndDate
)
 
SELECT C.zeDay, SUM(TC.Distance)
FROM CTE_DATE C
LEFT JOIN @TableCoureur TC ON C.zeDay = CAST(TC.RunDate AS DATE)
GROUP BY C.zeDay

Jusqu'ici, tout va bien, j'ai donc une distance totale parcourue pour chaque jour.

Mais ce n'est pas ce que je veux ! J'ai besoin de la distance par jour ET par coureur.
J'ai donc tenté ceci :

Code :

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

1
2
3
4
5
 
SELECT C.zeDay, TC.CoureurId, ISNULL(SUM(TC.Distance),0)
FROM CTE_DATE C
LEFT JOIN @TableCoureur TC ON C.zeDay = CAST(TC.RunDate AS DATE)
GROUP BY C.zeDay, TC.CoureurId

Il me semblait logique d'essayer, mais je n'ai pas le résultat attendu. J'ai bien tous mes jours, mais pas tous les jours de tout mes coureurs.

Pour le coup, je sèche un peu.

Si quelqu'un a quelques minutes à perdre pour m'expliquer ce qui cloche et m'aider à me guider vers la solution, je lui en serais reconnaissant.

**escartefigue** · 01/04/2020, 15h19

Bonjour,

Essayez en insérant des dates ISO : 'CCAA-MM-JJ hh:mm:ss'

**joKED** · 01/04/2020, 15h40

Bonjour,

Je viens d'essayer avec dates ISO, le problème reste le même.

**escartefigue** · 01/04/2020, 15h45

Je ne comprends pas, car je viens de tester avec SQL server 2019 et ce jeu de test :

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
with tabj(ColJ) as 
    (select '2020-01-01' union all
     select '2020-01-02' union all
     select '2020-01-03' union all
     select '2020-01-04' union all
     select '2020-01-05' union all
     select '2020-01-06' union all
     select '2020-01-07' union all
     select '2020-01-08' union all
     select '2020-01-09' 
    )
   , tabc(ColC, ColJ, ColD) as
    (select 1, '2020-01-01 10:00:00', 10 union all
     select 1, '2020-01-01 14:30:00', 18 union all
     select 2, '2020-01-02 11:00:00', 20 union all
     select 1, '2020-01-04 08:30:00', 12 union all
     select 3, '2020-01-04 14:00:00', 08 union all
     select 3, '2020-01-04 18:00:00', 08 union all
     select 6, '2020-01-04 14:00:00', 08 union all
     select 6, '2020-01-07 14:30:00', 15
    )

Cette requête :

Code :

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

1
2
3
4
5
6
7
8
select J.ColJ                   as Jour
     , C.ColC                   as Coureur
     , coalesce(sum(C.ColD), 0) as Distance
from TABJ J
left join TABC C
  on cast(C.ColJ as date)=J.ColJ
group by J.ColJ
       , C.ColC

Donne les bons résultats :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
Jour 	Coureur 	Distance
2020-01-03 		0
2020-01-05 		0
2020-01-06 		0
2020-01-08 		0
2020-01-09 		0
2020-01-01 	1 	28
2020-01-04 	1 	12
2020-01-02 	2 	20
2020-01-04 	3 	16
2020-01-04 	6 	8
2020-01-07 	6 	15

**tatayo** · 01/04/2020, 15h52

Je pense que tu prends les tables dans le mauvais sens dans ta requête: tu prends toutes les dates, et les coureurs correspondants si ils existent.
Or il faut partir de la table des coureurs, et chercher les dates si elle existent.
Bref soit tu inverses les tables, soit tu remplace LEFT JOIN par RIGTH JOIN.

Perso je suis partisan de la première solution, je trouve que mixer les LEFT et RIGHT join rend les requêtes illisibles.

Tatayo.

**escartefigue** · 01/04/2020, 15h57

Non : il faut bien toutes les dates

Je cite :

le but du jeu est de trouver pour chaque JOUR et pour chaque coureur la distance parcourue. Pas difficile. Sauf que, j'ai besoin d'inclure les jours où mes coureurs n'ont pas couru (les fainéants).

La requête à xécuter est donc bien SELECT ... FROM TAB_DATE LEFT JOIN TAB_ACOURU.

Table ACOURU plutôt que COUREUR puisque chaque coureur y apparait autant de fois qu'il a couru

**joKED** · 01/04/2020, 16h05

Ok, j'arrive au même résultat que le votre, mais j'ai probablement mal exprimé mon besoin.

Mon but est d'obtenir une ligne de résultat contenant Jour, Coureur, Distance pour chaque jour et chaque coureur.
Dans l'absolu, si j'ai N coureurs, je dois avoir N Lignes pour chaque jour.

Tel que :

Jour 1 - Coureur 1 - 10Km
Jour 1 - Coureur 2 - 10Km
Jour 2 - Coureur 1 - 10Km
Jour 2 - Coureur 2 - NULL (il n'a pas couru jour 2)
Jour 3 - Coureur 1 - 10Km
Jour 3 - Coureur 2 - 20Km (il a couru 2 fois 10Km, soit 20Km au total)
Jour 4 - NULL - NULL (personne n'a couru, c'est un cas spécial, mais autant l'indiquer)
Jour 5 - Coureur 1 - 10Km
Jour 5 - Coureur 2 - 10Km
Etc...

**escartefigue** · 01/04/2020, 16h19

C'est exactement ce que fait ma requête !

Exemple : le coureur 1 a couru deux fois le 1er janvier, une fois 10km et une fois 18, la requête renvoie bien 28 km pour ce coureur à cette date

**joKED** · 01/04/2020, 16h30

Envoyé par escartefigue

C'est exactement ce que fait ma requête !

Exemple : le coureur 1 a couru deux fois le 1er janvier, une fois 10km et une fois 18, la requête renvoie bien 28 km pour ce coureur à cette date

Oui, mais ça ne répond pas totalement au besoin, je n'ai pas de ligne de résultat indiquant qu'il n'a pas couru le 02 Janvier par exemple

, et c'est ces lignes là que je veux aussi.

Je reprécise :
Hormis les jours où personne n'a couru, j'ai besoin d'une ligne contenant jour/coureur/distance (même si distance nulle) chaque jour où il y a eu au moins 1 coureur.

PI : Je retraite actuellement par code pour reconstruire les jours manquants, mais c'est moche, il doit bien avoir un moyen de retourner ça directement depuis la DB

**escartefigue** · 01/04/2020, 16h37

il faut donc d'abord faire une jointure sur la date sans tenir compte des coureurs, je regarde ça dès que j'ai un moment

**escartefigue** · 01/04/2020, 16h56

Une solution possible, il y a sans doute mieux (en général quand il y a un distinct, il y a un loup

)

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
select distinct
       Tabj.ColJ
     , Tabc.ColC
     , subq.Distance
From Tabj
cross Join Tabc
left join 
     (select J.ColJ                   as Jour
           , C.ColC                   as Coureur
           , coalesce(sum(C.ColD), 0) as Distance
      from TABJ J
      left join TABC C
        on cast(C.ColJ as date)=J.ColJ
      group by J.ColJ
             , C.ColC
     ) subq
  on subq.jour=Tabj.ColJ     
 and subq.Coureur=Tabc.ColC
order by Tabj.ColJ
       , Tabc.ColC

**vanagreg** · 01/04/2020, 18h07

Bonjour,

Je ne sais pas si le PARTITION OUTER JOIN existe sur SQL Server, mais en Oracle c'est qu'il suffit de faire:

Code :

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

1
2
3
4
5
6
7
8
9
select J.ColJ                   as Jour
     , C.ColC                   as Coureur
     , coalesce(sum(C.ColD), 0) as Distance
from TABC C
partition by (c.colC)
right outer join TabJ J on trunc(C.ColJ)=J.ColJ
group by J.ColJ
       , C.ColC    
order by 1;

**joKED** · 02/04/2020, 08h46

Envoyé par escartefigue

Une solution possible, il y a sans doute mieux (en général quand il y a un distinct, il y a un loup

)

Merci, ça a le mérite de fonctionner, je vais creuser un peu cette solution pour voir si on peut se passer du distinct.

Envoyé par vanagreg

Je ne sais pas si le PARTITION OUTER JOIN existe sur SQL Server

Non, à priori, ça n'existe pas.

Je laisse la discussion ouverte au cas où quelqu'un aurait une autre idée pour aborder ce problème.

**SQLpro** · 02/04/2020, 10h07

Envoyé par vanagreg

Bonjour,

Je ne sais pas si le PARTITION OUTER JOIN existe sur SQL Server, mais en Oracle c'est qu'il suffit de faire:

Code :

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

1
2
3
4
5
6
7
8
9
select J.ColJ                   as Jour
     , C.ColC                   as Coureur
     , coalesce(sum(C.ColD), 0) as Distance
from TABC C
partition by (c.colC)
right outer join TabJ J on trunc(C.ColJ)=J.ColJ
group by J.ColJ
       , C.ColC    
order by 1;

Sans doute avec un OUTER APPLY....

A +

**escartefigue** · 02/04/2020, 13h01

Bonjour,

Envoyé par vanagreg

Bonjour,

Je ne sais pas si le PARTITION OUTER JOIN existe sur SQL Server, mais en Oracle c'est qu'il suffit de faire:

Code :

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

1
2
3
4
5
6
7
8
9
select J.ColJ                   as Jour
     , C.ColC                   as Coureur
     , coalesce(sum(C.ColD), 0) as Distance
from TABC C
partition by (c.colC)
right outer join TabJ J on trunc(C.ColJ)=J.ColJ
group by J.ColJ
       , C.ColC    
order by 1;

Il doit manquer un bout de code là : ORA-00923: FROM keyword not found where expected

Quoi qu'il en soit, le problème c'est de ne pas avoir de table des coureurs, mais seulement une table associative coureurs-course.
On peut améliorer la requête que j'avais proposée plus haut en dédoublonnant les coureurs dès le départ plutôt que sur le résultat final.

Ce qui donne :

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
select 
       Tabj.ColJ
     , Tabc.ColC
     , subq.Distance
From Tabj
cross Join
     (select distinct Colc
      from Tabc) Tabc
left join
    (select J.ColJ                   as Jour
          , C.ColC                   as Coureur
          , coalesce(sum(C.ColD), 0) as Distance
     from TABJ J
     left join TABC C
       on cast(C.ColJ as date)=J.ColJ
     group by J.ColJ
            , C.ColC
    ) subq
  on subq.jour=Tabj.ColJ
 and subq.Coureur=Tabc.ColC
order by Tabj.ColJ
       , Tabc.ColC

**vanagreg** · 02/04/2020, 13h40

Si tu as eu une erreur c'est que SQL Server ne connait pas le partition outer join. La solution de contournement est en effet d'utiliser un cross join.

**escartefigue** · 02/04/2020, 13h53

Non : ORA-00923: FROM keyword not found where expected

Il s'agit d'un essai avec Oracle 18c

**vanagreg** · 02/04/2020, 14h09

ah oui ORA qqch, au temps pour moi

Pourtant ça fonctionne bien avec le jeu de données que tu as posté (sur Oracle Live SQL, en 19c):

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
with tabj(ColJ) as 
    (select to_date('2020-01-01', 'yyyy-mm-dd') from dual union all
     select to_date('2020-01-02', 'yyyy-mm-dd') from dual  union all
     select to_date('2020-01-03', 'yyyy-mm-dd') from dual  union all
     select to_date('2020-01-04', 'yyyy-mm-dd') from dual  union all
     select to_date('2020-01-05', 'yyyy-mm-dd') from dual  union all
     select to_date('2020-01-06', 'yyyy-mm-dd') from dual  union all
     select to_date('2020-01-07', 'yyyy-mm-dd') from dual  union all
     select to_date('2020-01-08', 'yyyy-mm-dd') from dual  union all
     select to_date('2020-01-09', 'yyyy-mm-dd') from dual  
    )
   , tabc(ColC, ColJ, ColD) as
    (select 1, to_date('2020-01-01 10:00:00', 'yyyy-mm-dd hh24:mi:ss'), 10 from dual union all
     select 1, to_date('2020-01-01 14:30:00', 'yyyy-mm-dd hh24:mi:ss'), 18 from dual union all
     select 2, to_date('2020-01-02 11:00:00', 'yyyy-mm-dd hh24:mi:ss'), 20 from dual union all
     select 1, to_date('2020-01-04 08:30:00', 'yyyy-mm-dd hh24:mi:ss'), 12 from dual union all
     select 3, to_date('2020-01-04 14:00:00', 'yyyy-mm-dd hh24:mi:ss'), 08 from dual union all
     select 3, to_date('2020-01-04 18:00:00', 'yyyy-mm-dd hh24:mi:ss'), 08 from dual union all
     select 6, to_date('2020-01-04 14:00:00', 'yyyy-mm-dd hh24:mi:ss'), 08 from dual union all
     select 6, to_date('2020-01-07 14:30:00', 'yyyy-mm-dd hh24:mi:ss'), 15 from dual
    )
select J.ColJ                   as Jour
     , C.ColC                   as Coureur
     , coalesce(sum(C.ColD), 0) as Distance
from TABC C
partition by (c.colC)
right outer join TabJ J on trunc(C.ColJ)=J.ColJ
group by J.ColJ
       , C.ColC    
order by 1;

**escartefigue** · 02/04/2020, 14h21

Envoyé par vanagreg

ah oui ORA qqch, au temps pour moi

Moi c'est pire : j'avais simplement oublié d'ajouter les "from dual" en basculant du code SQL server au code Oracle

Ça ma sauté aux yeux dès que j'ai vu la réponse

à ma décharge, je fais rarement du Oracle

**joKED** · 02/04/2020, 16h07

Envoyé par escartefigue

Quoi qu'il en soit, le problème c'est de ne pas avoir de table des coureurs, mais seulement une table associative coureurs-course.

J'ai bien une table des coureurs (Id, Nom, Prénom), je l'ai juste pas modélisée dans l'exemple. Mais j'ai du mal à voir en quoi ça influe. Tu peux développer ce point ?

GROUP BY DATE et autre critère [2014]

Développement SQL Server

Discussions similaires

Partager

Partager