Discussion :

[Aurel3533]

Bonjour à vous,

Voici en photo à quoi ressemble les tables de départ, et la table résultat que je souhaite obtenir (en vérité je n'ai pas seulement 2 points mais 2000 d'où mon besoin...)

Je souhaiterais avoir pour chaque jour, le volume/jour en se référant à la tableB qui indique pour chaque plage de jours, le volume/jour associé :
du jour 1 au jour 2 -> 1,0
du jour 3 au jour 4 -> 4,5
du jour 5 au jour 10 -> 35,7
....

Il faut donc pour le point 1, dans la colonne nb_jours_cum de la TableB, sélectionner la valeur minimale avec pour condition qu'elle soit supérieure ou égale à la valeur de la colonne jour de la TableA, puis renseigner par la valeur associée qui figure dans la colonne volume_jour.

Autrement dit, je pense à ces bribes de formules :

SELECT TableB.volume_jour

WHERE min(TableB.nb_jours_cum) AND TableB.nb_jours_cum >= TableA.jour

la difficulté c'est d'une part obtenir le résultat pour chaque jour de la TableA, et puis pour chaque numéro de point.
En fait ça ferait comme une sorte de bouche, j'imagine.

Si jamais qqun aurait qqes pistes pour m'orienter, je suis preneur

Cordialement,
Aurélien.

[escartefigue]

C'est tordu de partir d'un cumul sur plusieurs jours (la table B) pour recalculer un volume journalier, ce serait bien plus simple de partir de la table de mesures quotidiennes qui existe forcément.
La requête serait à la fois plus simple et plus performante.

Cela étant dit, voici une solution possible :

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
create table TA
      (  TA_jour    smallint   primary key)
;
insert into TA(TA_jour)
values (01), (02), (03), (04), (05)  
     , (06), (07), (08), (09), (10)
     , (11), (12), (13), (14), (15)  
     , (16), (17), (18), (19), (20)  
;  
create table TB
      (  TB_point   smallint      not null
       , TB_nbj     smallint      not null
       , TB_cum     decimal(5,2)  not null
      )
;
insert into TB
      (TB_point, TB_nbj, TB_cum)
values
      (1, 02, 01.0)
    , (1, 04, 04.5)
    , (1, 10, 35.7)
    , (1, 18, 15.0)
    , (1, 20, 10.1)
    , (2, 02, 14.0)
    , (2, 08, 22.0)
    , (2, 10, 03.0)
    , (2, 18, 08.5)
    , (2, 20, 09.0)
;
with TX(C1, C2, C3, C4) as
    (select TB.TB_point         
          , TB.TB_nbj           
          , lag(TB.TB_nbj, 1, 0) 
            over(partition by TB.TB_point
                 order by TB.TB_nbj) 
          , TB_cum
     from TB
    )
select C1        as point
     , TA_jour   as jour
     , C4        as qte
from TA
left join TX
   on TA_jour <= C2
  and TA_jour  > C3
order by point, jour

[SQLpro]

En fait le problème que vous avez vient du fait que votre base de données est mal modélisée..... Ayant créé la base de données utilisée par tous les SPC (Service de Prévision de Crues) en France (qui recueille la pluviométrie, limnimétrie, débit sur tous les cours d'eau...), c'est en toute connaissance de cause que je vous indique la manière de modéliser correctement ceci...

Première chose il faut impérativement une colonne d’ordonnancement des valeurs insérées... En effet il n'existe aucun ordre des lignes dans une table, quelque soit le SGBDR. Ce n'est pas un tableur. Sans cette colonne vos cumul seront pris dans n'importe quel ordre et vos résultats aberrant...
Donc, j'y ajoute une colonne DATE...
Il vous faut aussi une clé sans cela ce n'est pas une table relationnelle !

Code :

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

1
2
3
4
5
6
CREATE TABLE T_MESURE_MSR
(MSR_ID                   INT IDENTITY PRIMARY KEY,
 MSR_DATE                 DATE NOT NULL,
 MSR_POINT                INT NOT NULL, -- REFERENCES...
 MSR_NB_JOUR_CUMUL        INT NOT NULL CHECK(MSR_NB_JOUR_CUMUL >= 0),
 MSR_VOLUME               FLOAT NOT NULL CHECK(MSR_VOLUME >= 0));

Et j'insère ces valeurs avec la date (un peu arbitraire....)

Code :

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

1
2
3
4
5
6
7
8
9
10
11
INSERT INTO T_MESURE_MSR VALUES
('2024-07-01', 1, 2, 1.0),
('2024-07-03', 1, 4, 4.5),
('2024-07-05', 1, 10, 35.7),
('2024-07-11', 1, 18, 15.0),
('2024-07-19', 1, 20, 10.1),
('2024-07-01', 2, 2, 14),
('2024-07-03', 2, 8, 22),
('2024-07-09', 2, 10, 3),
('2024-07-11', 2, 18, 8.5),
('2024-07-19', 2, 20, 9.0);

Si vous disposez de la fonction table GENERATE_SERIES (Microsoft SQL Server ou PostGreSQL) alors vous pouvez faire la requête suivante :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
WITH 
T AS
(
SELECT *, MSR_NB_JOUR_CUMUL - COALESCE(LAG(MSR_NB_JOUR_CUMUL) OVER(PARTITION BY MSR_POINT  ORDER BY MSR_DATE), 0)  AS N
FROM   T_MESURE_MSR
)
SELECT MSR_ID, MSR_DATE, MSR_POINT, MSR_NB_JOUR_CUMUL, MSR_VOLUME
FROM   T
       CROSS APPLY generate_series(1, N) 
ORDER BY MSR_POINT, MSR_DATE

Si vous êtes sous PostGreSQL, il vous faut utiliser le LATERAL JOIN. Postgresql étant toujours en retard de plusieurs années....

Qui donnera :

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
MSR_ID      MSR_DATE   MSR_POINT   MSR_NB_JOUR_CUMUL MSR_VOLUME     
----------- ---------- ----------- ----------------- ---------------
1           2024-07-01 1           2                 1              
1           2024-07-01 1           2                 1              
2           2024-07-03 1           4                 4,5            
2           2024-07-03 1           4                 4,5            
3           2024-07-05 1           10                35,7           
3           2024-07-05 1           10                35,7           
3           2024-07-05 1           10                35,7           
3           2024-07-05 1           10                35,7           
3           2024-07-05 1           10                35,7           
3           2024-07-05 1           10                35,7           
4           2024-07-11 1           18                15             
4           2024-07-11 1           18                15             
4           2024-07-11 1           18                15             
4           2024-07-11 1           18                15             
4           2024-07-11 1           18                15             
4           2024-07-11 1           18                15             
4           2024-07-11 1           18                15             
4           2024-07-11 1           18                15             
5           2024-07-19 1           20                10,1           
5           2024-07-19 1           20                10,1           
6           2024-07-01 2           2                 14             
6           2024-07-01 2           2                 14             
7           2024-07-03 2           8                 22             
7           2024-07-03 2           8                 22             
7           2024-07-03 2           8                 22             
7           2024-07-03 2           8                 22             
7           2024-07-03 2           8                 22             
7           2024-07-03 2           8                 22             
8           2024-07-09 2           10                3              
8           2024-07-09 2           10                3              
9           2024-07-11 2           18                8,5            
9           2024-07-11 2           18                8,5            
9           2024-07-11 2           18                8,5            
9           2024-07-11 2           18                8,5            
9           2024-07-11 2           18                8,5            
9           2024-07-11 2           18                8,5            
9           2024-07-11 2           18                8,5            
9           2024-07-11 2           18                8,5            
10          2024-07-19 2           20                9              
10          2024-07-19 2           20                9

Si vous ne disposez pas de cette fonction, vous pouvez la créer de toute pièces sous forme d'une UDF table (la syntaxe dépend de votre SGBDR)

Une autre solution est de rajouter à votre modèle de données une table des nombre de 1 à ... 100 000 par exemple, comme ceci :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE T_NUM (NUM INT PRIMARY KEY);
 
INSERT INTO T_NUM VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
 
INSERT INTO T_NUM 
SELECT T1.NUM + T2.NUM * 10 + T3.NUM * 100 + T4.NUM * 1000 + T5.NUM * 10000
FROM   T_NUM AS T1
       CROSS JOIN T_NUM AS T2
       CROSS JOIN T_NUM AS T3
       CROSS JOIN T_NUM AS T4
       CROSS JOIN T_NUM AS T5
WHERE  T1.NUM + T2.NUM * 10 + T3.NUM * 100 + T4.NUM * 1000 + T5.NUM * 10000 > 9;

La requête doit alors être récrite comme suit :

Code :

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

1
2
3
4
5
6
7
8
9
10
WITH 
T AS
(
SELECT *, MSR_NB_JOUR_CUMUL - COALESCE(LAG(MSR_NB_JOUR_CUMUL) OVER(PARTITION BY MSR_POINT  ORDER BY MSR_DATE), 0)  AS N
FROM   T_MESURE_MSR
)
SELECT MSR_ID, MSR_DATE, MSR_POINT, MSR_NB_JOUR_CUMUL, MSR_VOLUME
FROM   T
       JOIN T_NUM ON NUM-1 BETWEEN 1 AND N
ORDER BY MSR_POINT, MSR_DATE;

A +

PS pour un cours sur le langage SQL, suivez mes cours :


Le langage SQL – La synthèse

Ce cours se base sur la norme SQL ISO


Chapitre 1 : Les bases de données et le langage SQL

Chapitre 2 : définition des données : types, domaines, valeurs et NULL

Chapitre 3 – Création des objets :schémas, tables, vues, assertions

https://sqlpro.developpez.com/cours/sqlaz/select/

https://sqlpro.developpez.com/cours/sqlaz/jointures/

...

[Aurel3533]

C'est tordu de partir d'un cumul sur plusieurs jours (la table B) pour recalculer un volume journalier, ce serait bien plus simple de partir de la table de mesures quotidiennes qui existe forcément.
La requête serait à la fois plus simple et plus performante.

Cela étant dit, voici une solution possible [. . .]

Bonjour,

Merci bcp pour votre retour,
En fait la Table B ne correspond pas à un cumul sur plusieurs jours mais à des cumuls journaliers en fonction du numéro du jour.
Il n'y aura pas de calculs à faire, seulement une sélection.
C'est comme si c'était une répartition par classes:

du 1er au 2ieme jour : volume journalier = 1,0
du 3ieme au 4ieme jour: volume journalier = 4,5
du 5ieme au 10ieme jour : volume journalier = 35.7
...


Mais cette réorganisation de la TableB avec borne inférieure et borne supérieure en utilisant la fonction lag que je ne connaissais pas fonctionne parfaitement : MERCI BCP.

[Aurel3533]

En fait le problème que vous avez vient du fait que votre base de données est mal modélisée[...]

Merci à vous pour votre aide et ces conseils précieux

[SQLpro]

La solution normale (au sens de la modélisation des bases de données qui passent par les formes normales) consiste à créer une table de chronodatation au pas de temps voulu (par exemple chaque jour) et d'alimenter cette table tous les jours sans exception !

A +

[escartefigue]

En fait la Table B ne correspond pas à un cumul sur plusieurs jours mais à des cumuls journaliers en fonction du numéro du jour.
Il n'y aura pas de calculs à faire, seulement une sélection.
C'est comme si c'était une répartition par classes:

du 1er au 2ieme jour : volume journalier = 1,0
du 3ieme au 4ieme jour: volume journalier = 4,5
du 5ieme au 10ieme jour : volume journalier = 35.7
...

Ça revient au même : l'erreur est d'avoir une table dont la première ligne correspond à deux jours de relevés, la deuxième deux jours, la 3e six jours...
Ce que je dis c'est qu'il faut que chaque ligne corresponde à une même durée (heure, jour, semaine... selon le besoin), à partir de là, la requête devient très simple et les performances seront meilleures.
SQLPro et moi même disons la même chose : votre modèle ne rend pas la solution impossible, mais complexe, contre performante et peu évolutive.
Un modèle de données bien conçu éviterait ces écueils. Si vous en avez la possibilité, revoyez la modélisation

[Aurel3533]

En fait je traite une donnée reçue par un organisme qui correspond à une multitude de points de prélèvements d'eau, et chaque point a des données de volumes de prélèvement entre 2 dates mais il n'y a aucune correspondances entre les dates et les durées, chaque point est différent. Moi je cherche à tout globaliser pour faire des sommes par quinzaine et du coup dans un 1er temps je cherche à mettre toutes les données à la journée et en calculant le volume journalier au prorata.