Forums PostgreSQL.fr

Thomas Williamson

Membre

[RESOLU] Afficher des mois non enregistrés

Bonjour,

J'ai une table avec la structure suivante :

CREATE TABLE operation (gid serial NOT NULL, date DATE, temps VARCHAR(5)) ;

Cette table comporte des dates (ex : 02/01/2013) et des périodes de temps (ex : 01:30). Je dais la somme des périodes de temps par mois avec la requête suivante :

SELECT 
 EXTRACT(MONTH FROM date)::SMALLINT AS num,
 to_char(date, 'TMMonth') AS mois,
 sum(temps::TIME) AS nb_heure 

FROM schema.operation 

GROUP BY EXTRACT(MONTH FROM date), to_char(date, 'TMMonth') 

ORDER BY num ; 

J'obtiens le tableau suivant :

num          mois               nb_heure
----------+-----------------+-------------
1         + Janvier         + 05:00:00
3         + Mars            + 02:30:00
4         + Avril           + 01:00:00
5         + Mai             + 01:00:00
6         + Juin            + 01:00:00
7         + Juillet         + 01:00:00
9         + Septembre       + 01:00:00
10        + Octobre         + 01:00:00
11        + Novembre        + 01:00:00
12        + Décembre        + 01:00:00

Ma question : Il manque certains mois pour lesquels aucune date ne fait référence dans la table operation... Comment pourrais-je constituer un tableau avec tous les mois de l'année par défaut (même si plusieurs sont à 00:00:00) ? J'ai pensé à faire un LEFT JOIN avec une table listant les mois mais je pense qu'il doit y avoir plus simple.

Merci d'avance pour vos avis sur ce sujet !

Thomas

Dernière modification par Thomas Williamson (21/01/2014 16:29:07)

rjuju

Administrateur

Re : [RESOLU] Afficher des mois non enregistrés

Il faut utiliser une requête générant tous les mois, par exemple :

SELECT num, to_char(to_timestamp(num::text,'MM'), 'TMMonth') as mois
FROM generate_series(1,12) num

et faire un left join de votre table.

---

Julien.
https://rjuju.github.io/

Thomas Williamson

Membre

Re : [RESOLU] Afficher des mois non enregistrés

Bonjour,

J'ai modifié ma requête comme ci-dessous mais j'ai toujours des mois manquants...

WITH mois AS (SELECT num, to_char(to_timestamp(num::TEXT,'MM'), 'TMMonth') AS mois FROM generate_series(1,12) num)

SELECT 
 EXTRACT(MONTH FROM b.date)::SMALLINT AS num,
 a.mois AS mois,
 sum(b.temps::TIME) AS nb_heure 

FROM mois a
  LEFT JOIN schema.operation b ON EXTRACT(MONTH FROM b.date) = a.num

GROUP BY EXTRACT(MONTH FROM b.date), a.mois 

ORDER BY num ;

Dernière modification par Thomas Williamson (21/01/2014 14:32:09)

rjuju

Administrateur

Re : [RESOLU] Afficher des mois non enregistrés

Cela marche bien sur mon poste :

create table operation (ladate date, temps time);
CREATE TABLE
insert into operation values (now(), '01:00:00');
INSERT 0 1
WITH mois AS (SELECT num, to_char(to_timestamp(num::TEXT,'MM'), 'TMMonth') AS mois FROM generate_series(1,12) num)

SELECT
EXTRACT(MONTH FROM b.ladate)::SMALLINT AS num,
a.mois AS mois,
sum(b.temps::TIME) AS nb_heure

FROM mois a
  LEFT JOIN operation b ON EXTRACT(MONTH FROM b.ladate) = a.num

GROUP BY EXTRACT(MONTH FROM b.ladate), a.mois

ORDER BY num ;
num  |   mois    | nb_heure
------+-----------+----------
    1 | Janvier   | 01:00:00
NULL | Août      | NULL
NULL | Avril     | NULL
NULL | Décembre  | NULL
NULL | Février   | NULL
NULL | Juillet   | NULL
NULL | Juin      | NULL
NULL | Mai       | NULL
NULL | Mars      | NULL
NULL | Novembre  | NULL
NULL | Octobre   | NULL
NULL | Septembre | NULL
(12 lignes)

et pour avoir le numéro du mois :

WITH mois AS (SELECT num, to_char(to_timestamp(num::TEXT,'MM'), 'TMMonth') AS mois FROM generate_series(1,12) num)

SELECT
a.num,
a.mois AS mois,
sum(b.temps::TIME) AS nb_heure

FROM mois a
  LEFT JOIN operation b ON EXTRACT(MONTH FROM b.ladate) = a.num

GROUP BY a.num, a.mois

ORDER BY num ;
num |   mois    | nb_heure
-----+-----------+----------
   1 | Janvier   | 01:00:00
   2 | Février   | NULL
   3 | Mars      | NULL
   4 | Avril     | NULL
   5 | Mai       | NULL
   6 | Juin      | NULL
   7 | Juillet   | NULL
   8 | Août      | NULL
   9 | Septembre | NULL
  10 | Octobre   | NULL
  11 | Novembre  | NULL
  12 | Décembre  | NULL
(12 lignes)

---

Julien.
https://rjuju.github.io/

Thomas Williamson

Membre

Re : [RESOLU] Afficher des mois non enregistrés

Re !

En fait, j'avais glissé une clause WHERE qui semble bloquer la sortie des 12 mois (je n'ai plus le problème lorsque je la retire). Je l'avais retiré de mon exemple pour ne pas trop le surcharger, pensant que ça n'aurait pas d'effet sur le résultat. Bon, en fait si (et je ne vois pas pourquoi : le LEFT JOIN est sensé reprendre toutes les occurences de la table mois, non ?). Ma table operation stocke des dates sur plusieurs années donc je veux pouvoir faire des sommes mensuelles pour une année donnée.

Je mets ma requête complète :

WITH mois AS (SELECT num, to_char(to_timestamp(num::TEXT,'MM'), 'TMMonth') AS mois FROM generate_series(1,12) num)

SELECT 
 EXTRACT(MONTH FROM b.date)::SMALLINT AS num,
 a.mois AS mois,
 sum(b.temps::TIME) AS nb_heure 

FROM mois a
  LEFT JOIN schema.operation b ON EXTRACT(MONTH FROM b.date) = a.num

WHERE EXTRACT(YEAR FROM b.date) = '2013'

GROUP BY EXTRACT(MONTH FROM b.date), a.mois 

ORDER BY num ;

rjuju

Administrateur

Re : [RESOLU] Afficher des mois non enregistrés

Le LEFT JOIN récupère tous les enregistrements, et le WHERE les filtre ensuite. Vous pouvez soit mettre la clause WHERE dans le LEFT JOIN (LEFT JOIN schema.operation b ON EXTRACT(MONTH FROM b.date) = a.num AND EXTRACT(YEAR FROM b.date) = '2013'), soit modifier le WHERE actuel (WHERE COALESCE(EXTRACT(YEAR FROM b.date),2013) = 2013 par exemple)

---

Julien.
https://rjuju.github.io/

Thomas Williamson

Membre

Re : [RESOLU] Afficher des mois non enregistrés

Super, merci beaucoup pour ton aide ! Ça fonctionne très bien à présent...

Thomas