Allergie au SQL: Regrouper 2 tables par tranches horaires avec calculs

**Fredoun21** · 09/07/2024, 20h37

Bonjour à tous!

J'ai tout essayer depuis des jours pour résoudre mon problème, mais je n'y arrive pas. J'en ai conclu que j'étais allergique au SQL.
Mais je veux comprendre et résoudre ce problème évidemment.

Le voici:
2 tables sont remplies par un système de détection de personnes en entrée et en sortie d'un lieu donné.

Tous les 10 passages, la valeur 10, l'heure, la date, ID du compteur ainsi que le nombre total de personnes entrantes ou sortantes (calculée lors de la requête SQL d'enregistrement) sont ajoutés à l'une des bases, 't_counter_in' pour les entrées et 't_counter_out' pour les sorties.

t_counter_in:
Nom : 17194103502098_Capture d’écran 2024-06-26 155554.png
Affichages : 111
Taille : 24,4 Ko

Nom : 17194103502098_Capture d’écran 2024-06-26 155554.png
Affichages : 111
Taille : 24,4 Ko

t_counter_out:
Nom : 17194104525578_Capture d’écran 2024-06-26 160013.png
Affichages : 103
Taille : 23,2 Ko

L'objectif recherché, c'est d'obtenir un tableau de données exploité dans différents graphiques avec les info suivantes (les chiffres sont bidons):

t_resultat:
Nom : 17194109460688_Capture d’écran 2024-06-26 160732.png
Affichages : 103
Taille : 16,5 Ko

Nom : 17194109460688_Capture d’écran 2024-06-26 160732.png
Affichages : 103
Taille : 16,5 Ko

où

'Horaire' le regroupement par tranche horaire des entrants et des sortants

'Entrant' la somme 'CIN_COUNTER_VALUE' sur l'horaire donné

'Sortant' la somme 'COU_COUNTER_VALUE' sur l'horaire donné

'Sur site' la différence entre le MAX de CIN_COUNTER_TOTAL - COU_COUNTER_TOTAL sur l'horaire donné

'Disponible' la différence avec la jauge MAX du lieu - 'Sur site' (ici jauge = 6000)

Il est possible qu'aucun enregistrement ne soit disponible à un horaire donné en entrée, en sortie ou les 2.

J'ai 2 requêtes qui fonctionnent:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SELECT
	CONCAT (HOUR (i.CIN_TIME), "h") AS Horaire,
	MAX(i.CIN_COUNTER_TOTAL) AS "Entrant total",
	SUM(i.CIN_COUNTER_VALUE) AS "Entrant /h"
FROM
	t_counter_in AS i
WHERE
	i.CIN_DATE = "2023-08-05"
	AND i.CIN_COUNTER_ID = 7
	AND HOUR (i.CIN_TIME) BETWEEN "12" AND 18
GROUP BY
	HOUR (i.CIN_TIME);

qui donne

Nom : 17194121572512_Capture d’écran 2024-06-26 162554.png
Affichages : 102
Taille : 12,2 Ko

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SELECT
	CONCAT (HOUR (o.COU_TIME), "h") AS Horaire,
	MAX(o.COU_COUNTER_TOTAL) AS "Sortant total",
	SUM(o.COU_COUNTER_VALUE) AS "Sortant /h"
FROM
	t_counter_out AS o
WHERE
	o.COU_DATE = "2023-08-05"
	AND o.COU_COUNTER_ID = 7
	AND HOUR (o.COU_TIME) BETWEEN "13" AND "18"
GROUP BY
	HOUR (o.COU_TIME);

qui donne

Nom : 17194122762466_Capture d’écran 2024-06-26 162425.png
Affichages : 100
Taille : 11,7 Ko

la dernière requête qui devrait fonctionner mais qui ne fonctionne pas

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
 
SELECT
	CONCAT (HOUR (i.CIN_TIME), 'h') AS in_horaire,
	i.CIN_COUNTER_TOTAL AS present,
	SUM(i.CIN_COUNTER_VALUE) AS entrant,
	SUM(i.CIN_COUNTER_VALUE) - o.sortant AS 'Sur site',
	6000 - MAX(i.CIN_COUNTER_TOTAL - o.sortant) AS disponible,
	o.sortant AS sortant,
	COUNT(i.CIN_ID) AS Nbr_Compteur_in,
	COUNT(o.COU_ID) AS Nbr_Compteur_out
FROM
	t_counter_in i
	JOIN (
		SELECT
			CONCAT (HOUR (t_out.COU_TIME), 'h') AS out_horaire,
			t_out.COU_COUNTER_TOTAL AS out_total,
			SUM(t_out.COU_COUNTER_VALUE) AS sortant,
			COUNT(t_out.COU_ID) AS Nbr_Compteur,
			t_out.COU_COUNTER_ID,
			t_out.COU_ID,
			t_out.COU_TIME
		FROM
			t_counter_out t_out
		WHERE
			t_out.COU_DATE = "2023-08-05"
			AND t_out.COU_COUNTER_ID = 7
			AND HOUR (t_out.COU_TIME) BETWEEN '13' AND '18'
		GROUP BY
			HOUR (t_out.COU_TIME)
	) o ON i.CIN_COUNTER_ID = o.cou_counter_id
WHERE
	i.CIN_DATE = "2023-08-05"
	AND i.CIN_COUNTER_ID = 7
	AND HOUR (i.CIN_TIME) BETWEEN '13' AND '18'
GROUP BY
	HOUR (i.CIN_TIME);

C'est une de mes dernières tentatives, j'ai essayer des JOIN dans tous les sens, d'enlever ou d'ajouter des colonnes pour avoir plus de regroupement possible etc.. Le fait de ne pas pouvoir utilisé les ALIAS pour faire des calculs dans d'autres colonnes est frustrant (il y a les variables @, mais là j'ai dit non).
Le problème me parait simple, la solution devrait l'être, mais mon allergie prend le dessus, je pense.

Merci d'avance à toutes les bonnes volontés pour m'aider, et selon la formule, je reste à votre disposition pour toute information complémentaire.

Bonne journée

Fred W

**tatayo** · 10/07/2024, 07h51

Bonjour,
Vite fait, vu de loin dans le brouillard:

Code sql :

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
 
with stat1 as
(
SELECT
	CONCAT (HOUR (i.CIN_TIME), "h") AS Horaire,
	MAX(i.CIN_COUNTER_TOTAL) AS Entrant_total,
	SUM(i.CIN_COUNTER_VALUE) AS Entrant_h
FROM
	t_counter_in AS i
WHERE
	i.CIN_DATE = "2023-08-05"
	AND i.CIN_COUNTER_ID = 7
	AND HOUR (i.CIN_TIME) BETWEEN "12" AND 18
GROUP BY
	HOUR (i.CIN_TIME);
),
stat2 as
(
SELECT
	CONCAT (HOUR (o.COU_TIME), "h") AS Horaire,
	MAX(o.COU_COUNTER_TOTAL) AS Sortant_total,
	SUM(o.COU_COUNTER_VALUE) AS Sortant_h
FROM
	t_counter_out AS o
WHERE
	o.COU_DATE = "2023-08-05"
	AND o.COU_COUNTER_ID = 7
	AND HOUR (o.COU_TIME) BETWEEN "13" AND "18"
GROUP BY
	HOUR (o.COU_TIME);
)
select stat1.Horaire,stat2.horaire,
coalesce(stat1.Entrant_Total,0) as Entrant_Total, coalesce(stat2.Sortant_Total,0) as Sortant_Total, 
coalesce(Stat1.Entrant_H,0) as Entrant_H, coalesce(stat2.Sortant_H,0) as Sortant_H
from stat1
full outer join stat2
on stat1.horaire = stat2.horaire

Tatayo.

**LEGALLJJA** · 15/07/2024, 09h59

bonjour,

vous pouvez essayer de faire une requête UNION entre les entrées et les sorties
mais cous obtiendrez les entrées et sortie en ligne

vous me dite si la solution vous permet d'avancer merci

Code sql :

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
SELECT "Entrant" Typees,
	CONCAT (HOUR (i.CIN_TIME), "h") AS Horaire,
	MAX(i.CIN_COUNTER_TOTAL) AS "Entrant/sortie total",
	SUM(i.CIN_COUNTER_VALUE) AS "Entrant/sortie /h"
FROM
	t_counter_in AS i
WHERE
	i.CIN_DATE = "2023-08-05"
	AND i.CIN_COUNTER_ID = 7
	AND HOUR (i.CIN_TIME) BETWEEN "12" AND 18
GROUP BY
	HOUR (i.CIN_TIME)
 
UNION
SELECT "sortant" Typees,
	CONCAT (HOUR (i.CIN_TIME), "h") AS Horaire,
	MAX(i.CIN_COUNTER_TOTAL) AS "Entrant/sortie total",
	SUM(i.CIN_COUNTER_VALUE) AS "Entrant/sortie /h"
FROM
	t_counter_out AS i
WHERE
	i.CIN_DATE = "2023-08-05"
	AND i.CIN_COUNTER_ID = 7
	AND HOUR (i.CIN_TIME) BETWEEN "12" AND 18
GROUP BY
	HOUR (i.CIN_TIME);

**escartefigue** · 19/07/2024, 09h57

@LEGALLJJA : il ne faut pas confondre délimiteur de chaînes de caractères ou de dates et délimiteur de nom d'objet.

Dans le premier cas, on utilise la quote simple, par exemple where ma_colonne = 'TOTO'.
Dans le deuxième cas, on utilise les guillemets ou doubles quotes, par exemple where "user" = 'TOTO'.

Ce n'est pas neutre, si on délimite une chaîne avec des doubles quotes, et qu'il existe une colonne de même nom, le résultat sera faussé.

Exemple :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
-- création d'une table ayant (très mauvaise idée) des colonnes avec des noms réservés
create table T1
      (   IDENT    integer  primary key
       , "USER"    char(8)  not null
       , "DATE"    date     not null)
;
insert into T1 (IDENT, "USER", "DATE")  
values (001, 'MACHIN', '2024-01-06')
     , (002, 'USER',   '2024-01-15')
     , (003, 'BIDULE', '2024-01-28')
;

Cette requête utilisant les mauvais délimiteurs :

Code SQL :

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

1
2
3
4
5
6
select IDENT
     , "USER"
     , "DATE"
from T1
where "USER" = "USER"
;

Produit ce résultat (il y a évidemment toujours égalité !) :
Nom : Sans titre.png
Affichages : 46
Taille : 2,8 Ko

Alors que cette autre requête :

Code SQL :

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

1
2
3
4
5
6
select IDENT
     , "USER"
     , "DATE"
from T1
where "USER" = 'USER'
;

Produit ce résultat, qui lui est correct :
Nom : Sans titre.png
Affichages : 46
Taille : 1,4 Ko

De plus, la fonction HOUR() renvoie un entier, il ne faut donc pas utiliser de délimiteur

AND HOUR (i.CIN_TIME) BETWEEN "12" AND 18 est donc à remplacer par AND HOUR (i.CIN_TIME) BETWEEN 12 AND 18

EDIT : dans le cas où l'on utilise MySQL ou MariaDB, le délimiteur de nom d'objet est la quote inversée (Alt-GR + 7) et non pas le guillemet

**Séb.** · 19/07/2024, 13h15

EDIT : dans le cas où l'on utilise MySQL ou MariaDB, le délimiteur de nom d'objet est la quote inversée (Alt-GR + 7) et non pas le guillemet

Sauf si l'option ANSI_QUOTES est active

Dans ce cas MySQL accepte " et ` en tant que délimiteurs de noms d'objets.

**StringBuilder** · Hier, 09h32

J'aurais fait ça : (code MySQL)

Code sql :

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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
 
set @dte = cast('2023-08-05' as date);
set @capacity = 6000;
set @open_hour = 8;
set @close_hour = 18;
 
with hours (hour) /* CTE à remplacer par une table si possible */
as
(
  select 0
  union all
  select 1
  union all
  select 2
  union all
  select 3
  union all
  select 4
  union all
  select 5
  union all
  select 6
  union all
  select 7
  union all
  select 8
  union all
  select 9
  union all
  select 10
  union all
  select 11
  union all
  select 12
  union all
  select 13
  union all
  select 14
  union all
  select 15
  union all
  select 16
  union all
  select 17
  union all
  select 18
  union all
  select 19
  union all
  select 20
  union all
  select 21
  union all
  select 22
  union all
  select 23
),
t_counter_in (cin_id, cin_counter_value, cin_counter_total, cin_time, cin_date) /* CTE pour simuler la table réelle */
as
(
	select 40, 10, 340, cast('14:01:10' as time), cast('2023-08-05' as date)
  union all
	select 41, 10, 350, cast('14:05:39' as time), cast('2023-08-05' as date)
	union all
	select 42, 10, 360, cast('14:06:47' as time), cast('2023-08-05' as date)
	union all
	select 43, 10, 370, cast('14:11:14' as time), cast('2023-08-05' as date)
),
t_counter_out (cou_id, cou_counter_value, cou_counter_total, cou_time, cou_date) /* CTE pour simuler la table réelle */
as
(
	select 27, 10, 340, cast('14:11:45' as time), cast('2023-08-05' as date)
  union all
	select 28, 10, 350, cast('14:18:18' as time), cast('2023-08-05' as date)
	union all
	select 29, 10, 360, cast('14:24:58' as time), cast('2023-08-05' as date)
	union all
	select 30, 10, 370, cast('14:34:59' as time), cast('2023-08-05' as date)
),
sum_in (day, hour, nb_in, total_in) /* Il serait intéressant que hour() soit stocké et dans la table d'origine + index sur date et hour */
as
(
  select cin_date, hour(cin_time), sum(cin_counter_value), max(cin_counter_total)
  from t_counter_in
  where cin_date = @dte
  group by cin_date, hour(cin_time)
),
sum_out (day, hour, nb_out, total_out) /* Il serait intéressant que hour() soit stocké et dans la table d'origine + index sur date et hour */
as
(
  select cou_date, hour(cou_time), sum(cou_counter_value), max(cou_counter_total)
  from t_counter_out
  where cou_date = @dte
  group by cou_date, hour(cou_time)
)
select h.hour, i.nb_in entrants, o.nb_out, i.total_in - o.total_out, @capacity - (i.total_in - o.total_out)
from hours h
left outer join sum_in i on i.hour = h.hour
left outer join sum_out o on o.hour = h.hour
where h.hour between @open_hour and @close_hour;

**StringBuilder** · Aujourd'hui, 09h41

Version corrigée, car si des personnes sortes pendant que d'autres rentres, le total de personnes à l'intérieur n'est pas égal à la somme des personnes entrées, mais le MAX() du cumul progressif des entrée et sorties sur la période étudiée :

Code sql :

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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
 
set @dte = cast('2023-08-05' as date);
set @capacity = 6000;
set @open_hour = 8;
set @close_hour = 18;
 
with hours (hour)
as
(
  select 0
  union all
  select 1
  union all
  select 2
  union all
  select 3
  union all
  select 4
  union all
  select 5
  union all
  select 6
  union all
  select 7
  union all
  select 8
  union all
  select 9
  union all
  select 10
  union all
  select 11
  union all
  select 12
  union all
  select 13
  union all
  select 14
  union all
  select 15
  union all
  select 16
  union all
  select 17
  union all
  select 18
  union all
  select 19
  union all
  select 20
  union all
  select 21
  union all
  select 22
  union all
  select 23
),
t_counter_in (cin_id, cin_counter_value, cin_counter_total, cin_time, cin_date)
as
(
	select 40, 10, 340, cast('14:01:10' as time), cast('2023-08-05' as date)
  union all
	select 41, 10, 350, cast('14:05:39' as time), cast('2023-08-05' as date)
	union all
	select 42, 10, 360, cast('14:06:47' as time), cast('2023-08-05' as date)
	union all
	select 43, 10, 370, cast('14:11:14' as time), cast('2023-08-05' as date)
),
t_counter_out (cou_id, cou_counter_value, cou_counter_total, cou_time, cou_date)
as
(
	select 27, 10, 340, cast('14:04:45' as time), cast('2023-08-05' as date)
  union all
	select 28, 10, 350, cast('14:08:18' as time), cast('2023-08-05' as date)
	union all
	select 29, 10, 360, cast('14:24:58' as time), cast('2023-08-05' as date)
	union all
	select 30, 10, 370, cast('14:34:59' as time), cast('2023-08-05' as date)
),
t_counter (cou_date, cou_time, cou_value)
as
(
  select cin_date, cin_time, cin_counter_value
  from t_counter_in
  union all
	select cou_date, cou_time, -cou_counter_value
  from t_counter_out  
),
sum_cumul (cou_date, cou_time, nb_present)
as
(
  select cou_date, cou_time, sum(cou_value) over (partition by cou_date, hour(cou_time) order by cou_date, cou_time)
	from t_counter
),
sum_present (day, hour, nb_present)
as
(
  select cou_date, hour(cou_time), max(nb_present)
	from sum_cumul
  group by cou_date, hour(cou_time)
),
sum_in (day, hour, nb_in, total_in)
as
(
  select cin_date, hour(cin_time), sum(cin_counter_value), max(cin_counter_total)
  from t_counter_in
  group by cin_date, hour(cin_time)
),
sum_out (day, hour, nb_out, total_out)
as
(
  select cou_date, hour(cou_time), sum(cou_counter_value), max(cou_counter_total)
  from t_counter_out
  group by cou_date, hour(cou_time)
)
select h.hour, i.nb_in entrants, o.nb_out, p.nb_present, @capacity - p.nb_present
from hours h
left outer join sum_in i on i.day = @dte and i.hour = h.hour
left outer join sum_out o on o.day = @dte and o.hour = h.hour
left outer join sum_present p on p.day = @dte and o.hour = h.hour
where h.hour between @open_hour and @close_hour;

Au passage, je débarque de ma caverne, mais je suis très agréablement surpris de voir que MySQL supporte maintenant les CTE ainsi que les fonctions de fenêtrage...

Allergie au SQL: Regrouper 2 tables par tranches horaires avec calculs

Langage SQL

Discussions similaires

Partager

Partager