x en fonction <= a la date

**reimic5** · 11/01/2018, 17h00

Bonjour,

Merci beaucoup pour votre aide.
J'ai deux table :
La table table1 avec 3 colonnes user, date, budget.

-- Table1 --
U | date | Budget
1 | 01/01/17 | 100
2 | 01/01/17 | 110
3 | 01/01/17 | 101
2 | 15/02/17 | 111
2 | 03/03/17 | 109
3 | 07/06/17 | 102

La table2 avec trois colonnes user, date depense

-- Table2 --
U | date | depense
1 | 04/01/17 | 100
2 | 06/01/17 | 150
1 | 24/02/17 | 130
2 | 20/03/17 | 110
1 | 14/07/17 | 170
3 | 16/08/17 | 180

je souhaiterai avoir la table 2 avec une colonne du budget aloue (< ou = a la date de depense) en fonction des users.

resultat souhaite:

-- resultat --
U | date | depense | budget
1 | 01/17 | 100 | 100
2 | 01/17 | 150 | 110
1 | 02/17 | 130 | 100
2 | 03/17 | 110 | 109
1 | 07/17 | 170 | 100
3 | 08/17 | 180 | 102

je n'y arrive pas, ma formule 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
SELECT 
  to_char((table2.date) ::timestamp with time zone, 'MM/YYYY' ::text) AS date,
  table2.depense,
  table2.user,
  table1.budget
FROM
  table1
  INNER JOIN table2 ON (public.table1.user = table2.user)
GROUP BY
  to_char((table2.date) ::timestamp with time zone, 'MM/YYYY' ::text),
  table2.user,
  table1.budget

**vttman** · 11/01/2018, 17h38

Bon je ne vais pas discuter sur les tables et leurs relations ...
Mais
1) tout champ figurant hors "group by" doit figurer dans une fonction de regroupement ...
2) Il manque à priori la jointure sur partie de date (MM/YYYY) entre table1 et table2
3) Si la requête ci-dessous fonctionne, je tenterai de remplacer le to_char ... par des combinaisons
de EXTRACT(MONTH FROM table2.date) et EXTRACT(year FROM date), plus propre à mon sens ...
car caster un timestamp en chaine puis en date ça me parait d'un lourd

Ainsi tester ceci

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SELECT
to_char((table2.date) ::timestamp with time zone, 'MM/YYYY' ::text) AS date,
table2.user,
min(table2.depense),
min(table1.budget)
FROM
table1
INNER JOIN 
table2 ON public.table1.user = table2.user
and to_char((table2.date) ::timestamp with time zone, 'MM/YYYY' ::text) = to_char((public.table1.date) ::timestamp with time zone, 'MM/YYYY' ::text) 
GROUP BY
to_char((table2.date) ::timestamp with time zone, 'MM/YYYY' ::text),
table2.user

puis

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT
EXTRACT(MONTH FROM table2.date) ,
EXTRACT(YEAR,FROM table2.date),
 table2.user,
min(table2.depense),
min(table1.budget)
...
GROUP BY
EXTRACT(MONTH FROM table2.date) ,
EXTRACT(YEAR,FROM table2.date),
table2.user

**escartefigue** · 12/01/2018, 08h51

Bonjour,

N'y a-t- il pas une erreur dans le résultat attendu, je ne comprends pas la ligne
2 | 03/17 | 110 | 111

qui à mon sens devrait être
2 | 03/17 | 110 | 109

Puisque dans la 1ère table, vous avez un budget de 109 au 03-03-2017 soit la date la plus proche antérieure à la dépense du 20-03-2017

Si c'est bien le cas, voici une solution possible :

Création de mon jeu d'essai conforme au votre, avec 2 CTE (la 1ère pour les dépenses, la deuxième pour les budgets)

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
 
   with cte1 (xuser, xdate, xdepe) as                          
       (select 1, '2017-01-04', 100     
        union all
        select 1, '2017-02-24', 130     
        union all                                               
        select 1, '2017-07-14', 170     
        union all                                              
        select 2, '2017-01-06', 150     
        union all                                              
        select 2, '2017-03-20', 110     
        union all                                              
        select 3, '2017-08-16', 180     
       )                                                       
      , cte2 (yuser, ydate, ybudg) as                         
       (select 1 , '2017-01-01', 100     
        union all                                             
        select 2 , '2017-01-01', 110     
        union all                                             
        select 2 , '2017-02-15', 111     
        union all                                             
        select 2 , '2017-03-03', 109     
        union all                                             
        select 3 , '2017-01-01', 101     
        union all                                             
        select 3 , '2017-06-07', 102     
       )

Puis exécution de la requête (à adapter pour la conversion de date en char à mettre à la syntaxe PG)

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
   select xuser                         
        , substr(char(xdate), 01, 07)   
        , sum(xdepe)                    
        , ybudg                         
   from CTE1 T1                         
   inner join CTE2 T2                   
      on T2.yuser =T1.xuser             
     and T2.ydate<=T1.xdate             
   where T2.ydate=                      
        (select max(ydate)              
         from CTE2 S2                   
         where S2.yuser =T2.yuser       
           and S2.ydate<=T1.xdate)      
   group by xuser                       
          , substr(char(xdate), 01, 07) 
          , ybudg                       
   order by 2, 1

Résultat :

Code :

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

1
2
3
4
5
6
  1 2017-01         100         100
  2 2017-01         150         110
  1 2017-02         130         100
  2 2017-03         110         109
  1 2017-07         170         100
  3 2017-08         180         102

**reimic5** · 12/01/2018, 16h36

Envoyé par escartefigue

Bonjour,

N'y a-t- il pas une erreur dans le résultat attendu, je ne comprends pas la ligne
2 | 03/17 | 110 | 111

qui à mon sens devrait être
2 | 03/17 | 110 | 109

Puisque dans la 1ère table, vous avez un budget de 109 au 03-03-2017 soit la date la plus proche antérieure à la dépense du 20-03-2017

Si c'est bien le cas, voici une solution possible :

Création de mon jeu d'essai conforme au votre, avec 2 CTE (la 1ère pour les dépenses, la deuxième pour les budgets)

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
 
   with cte1 (xuser, xdate, xdepe) as                          
       (select 1, '2017-01-04', 100     
        union all
        select 1, '2017-02-24', 130     
        union all                                               
        select 1, '2017-07-14', 170     
        union all                                              
        select 2, '2017-01-06', 150     
        union all                                              
        select 2, '2017-03-20', 110     
        union all                                              
        select 3, '2017-08-16', 180     
       )                                                       
      , cte2 (yuser, ydate, ybudg) as                         
       (select 1 , '2017-01-01', 100     
        union all                                             
        select 2 , '2017-01-01', 110     
        union all                                             
        select 2 , '2017-02-15', 111     
        union all                                             
        select 2 , '2017-03-03', 109     
        union all                                             
        select 3 , '2017-01-01', 101     
        union all                                             
        select 3 , '2017-06-07', 102     
       )

Puis exécution de la requête (à adapter pour la conversion de date en char à mettre à la syntaxe PG)

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
   select xuser                         
        , substr(char(xdate), 01, 07)   
        , sum(xdepe)                    
        , ybudg                         
   from CTE1 T1                         
   inner join CTE2 T2                   
      on T2.yuser =T1.xuser             
     and T2.ydate<=T1.xdate             
   where T2.ydate=                      
        (select max(ydate)              
         from CTE2 S2                   
         where S2.yuser =T2.yuser       
           and S2.ydate<=T1.xdate)      
   group by xuser                       
          , substr(char(xdate), 01, 07) 
          , ybudg                       
   order by 2, 1

Résultat :

Code :

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

1
2
3
4
5
6
  1 2017-01         100         100
  2 2017-01         150         110
  1 2017-02         130         100
  2 2017-03         110         109
  1 2017-07         170         100
  3 2017-08         180         102

exact c'est 109 je vais le rectifier
punaise ... Merci grandement ;-)

**reimic5** · 16/01/2018, 16h58

Envoyé par escartefigue

Bonjour,

N'y a-t- il pas une erreur dans le résultat attendu, je ne comprends pas la ligne
2 | 03/17 | 110 | 111

qui à mon sens devrait être
2 | 03/17 | 110 | 109

Puisque dans la 1ère table, vous avez un budget de 109 au 03-03-2017 soit la date la plus proche antérieure à la dépense du 20-03-2017

Si c'est bien le cas, voici une solution possible :

Création de mon jeu d'essai conforme au votre, avec 2 CTE (la 1ère pour les dépenses, la deuxième pour les budgets)

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
 
   with cte1 (xuser, xdate, xdepe) as                          
       (select 1, '2017-01-04', 100     
        union all
        select 1, '2017-02-24', 130     
        union all                                               
        select 1, '2017-07-14', 170     
        union all                                              
        select 2, '2017-01-06', 150     
        union all                                              
        select 2, '2017-03-20', 110     
        union all                                              
        select 3, '2017-08-16', 180     
       )                                                       
      , cte2 (yuser, ydate, ybudg) as                         
       (select 1 , '2017-01-01', 100     
        union all                                             
        select 2 , '2017-01-01', 110     
        union all                                             
        select 2 , '2017-02-15', 111     
        union all                                             
        select 2 , '2017-03-03', 109     
        union all                                             
        select 3 , '2017-01-01', 101     
        union all                                             
        select 3 , '2017-06-07', 102     
       )

Puis exécution de la requête (à adapter pour la conversion de date en char à mettre à la syntaxe PG)

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
   select xuser                         
        , substr(char(xdate), 01, 07)   
        , sum(xdepe)                    
        , ybudg                         
   from CTE1 T1                         
   inner join CTE2 T2                   
      on T2.yuser =T1.xuser             
     and T2.ydate<=T1.xdate             
   where T2.ydate=                      
        (select max(ydate)              
         from CTE2 S2                   
         where S2.yuser =T2.yuser       
           and S2.ydate<=T1.xdate)      
   group by xuser                       
          , substr(char(xdate), 01, 07) 
          , ybudg                       
   order by 2, 1

Résultat :

Code :

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

1
2
3
4
5
6
  1 2017-01         100         100
  2 2017-01         150         110
  1 2017-02         130         100
  2 2017-03         110         109
  1 2017-07         170         100
  3 2017-08         180         102

-----
Bonjour escartefigue,
ca ne fonctionne pas pour moi :-(
---
voici mon code:

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
SELECT
	relevecarburant.usager,
	substr( CHAR ( relevecarburant.DATE ), 01, 07 ),
	SUM ( relevecarburant.montant ),
	carmanagement.budgetessence 
FROM
	relevecarburant T1
	INNER JOIN carmanagement T2 ON T2.usager = T1.usager 
	AND T2.DATE <= T1.DATE 
WHERE
	T2.DATE = (
SELECT MAX
	( carmanagement.DATE ) 
FROM
	carmanagement S2 
WHERE
	S2.usager = T2.usager 
	AND S2.DATE <= T1.DATE 
	) 
GROUP BY
	relevecarburant.usager,
	substr( CHAR ( relevecarburant.DATE ), 01, 07 ),
	carmanagement.budgetessence 
ORDER BY
	2, 1

---

**reimic5** · 17/01/2018, 11h30

merci beaucoup ca fonctionne

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT to_char((t1.date)::timestamp with time zone, 'MM/YYYY'::text) AS date,
    t1.usager,
    t2.budgetessence AS budget_essence,
    sum(t1.montant) AS somme_carburant,
    (t2.budgetessence - sum(t1.montant)) AS delta
   FROM (relevecarburant t1
     JOIN carmanagement t2 ON (((t2.usager = t1.usager) AND (t2.date <= t1.date))))
  WHERE (t2.date = ( SELECT max(s2.date) AS max
           FROM carmanagement s2
          WHERE ((s2.usager = t2.usager) AND (s2.date <= t1.date))))
  GROUP BY t1.usager, (to_char((t1.date)::timestamp with time zone, 'MM/YYYY'::text)), t2.budgetessence
  ORDER BY (to_char((t1.date)::timestamp with time zone, 'MM/YYYY'::text)), t1.usager

**escartefigue** · 17/01/2018, 12h23

Oui j'avais bien précisé qu'il fallait adapter à la sauce PG, les fonctions de date sont assez différentes d'un SGBD à l'autre et je n'ai pas de PG sous la main

**reimic5** · 12/01/2018, 16h29

Envoyé par vttman

Bon je ne vais pas discuter sur les tables et leurs relations ...
Mais
1) tout champ figurant hors "group by" doit figurer dans une fonction de regroupement ...
2) Il manque à priori la jointure sur partie de date (MM/YYYY) entre table1 et table2
3) Si la requête ci-dessous fonctionne, je tenterai de remplacer le to_char ... par des combinaisons
de EXTRACT(MONTH FROM table2.date) et EXTRACT(year FROM date), plus propre à mon sens ...
car caster un timestamp en chaine puis en date ça me parait d'un lourd

Ainsi tester ceci

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SELECT
to_char((table2.date) ::timestamp with time zone, 'MM/YYYY' ::text) AS date,
table2.user,
min(table2.depense),
min(table1.budget)
FROM
table1
INNER JOIN 
table2 ON public.table1.user = table2.user
and to_char((table2.date) ::timestamp with time zone, 'MM/YYYY' ::text) = to_char((public.table1.date) ::timestamp with time zone, 'MM/YYYY' ::text) 
GROUP BY
to_char((table2.date) ::timestamp with time zone, 'MM/YYYY' ::text),
table2.user

puis

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT
EXTRACT(MONTH FROM table2.date) ,
EXTRACT(YEAR,FROM table2.date),
 table2.user,
min(table2.depense),
min(table1.budget)
...
GROUP BY
EXTRACT(MONTH FROM table2.date) ,
EXTRACT(YEAR,FROM table2.date),
table2.user

un tres tres grand MERCI.... ca fonctionne
je suis a la moselle aussi de Thionville ;-)

**vttman** · 12/01/2018, 17h45

Envoyé par reimic5

un tres tres grand MERCI.... ca fonctionne
je suis a la moselle aussi de Thionville ;-)

Thionville j'y passe tous les matins vers 6H ... quand je bosse bien sûr.

**escartefigue** · 12/01/2018, 19h00

Je me déplaçais il y a fort longtemps sur Florange pour le boulot, c'était à l'époque des dinosaures, c'est à dire les bases hiérarchiques et réseau, que les bases relationnelles n'avaient pas encore supplantées

**vttman** · 12/01/2018, 19h08

Florange aussi, je suis passé par là et maintenant je me souviens avoir travaillé à cette période sur du DL1, il y a un an il y a un siècle il y a une éternité

x en fonction <= a la date

Requêtes PostgreSQL

Vue hybride

Discussions similaires

Partager

Partager