Forums PostgreSQL.fr

damalaan

Membre

Calcul de moyenne sur 2 mois

Bonjour,

Il faut que je calcule une moyenne en faisant un regroupement sur un champ de type date mais la particularité est que le calcul doit se faire au bimestre (2mois, le 2ème mois étant toujours impair).
Par exemple, il me faut calculer la moyenne pour Décembre 2011 et Janvier 2012, puis Février et Mars 2012, Avril et Mai 2012, etc.
Vu  ce regroupement un peu particulier, je n’arrive à voir comment faire !

Merci d’avance

rjuju

Administrateur

Re : Calcul de moyenne sur 2 mois

Bonjour,

le plus simple serait de faire un group by du genre "(extract(month from champ_date)::integer)%12/2", et y ajouter l'année si vous en avez besoin.

---

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

jpargudo

Administrateur

Re : Calcul de moyenne sur 2 mois

Bonjour Damalaan,

Je me suis bien amusé avec celui là

J'ai créé une table de test comme suit:

create table test3 (t_date date, t_integer integer); 

...que j'ai ensuite remplie avec des dates aléatoires entre 2011 et 2012 (pour avoir une période "à cheval"), et un entier fixe (1).

J'ai ensuite créée la requête suivante:

select pairs.somme+impairs.somme as total, pairs.annee, pairs.mois, impairs.annee, impairs.mois

from

(select sum(t_integer) as somme, extract(year from t_date)::integer as annee, extract(month from t_date)::integer as mois
from test3 where extract(month from t_date)::integer%2=1
group by 2,3 order by 2,3) impairs,

(select sum(t_integer) as somme, extract(year from t_date)::integer as annee, extract(month from t_date)::integer as mois
from test3 where extract(month from t_date)::integer%2=0
group by 2,3 order by 2,3) pairs

where

   ((impairs.mois=pairs.mois+1
and impairs.annee=pairs.annee))

or (pairs.mois=12 and impairs.mois=1 and pairs.annee=impairs.annee-1)

order by pairs.annee, pairs.mois;

Ou je fais les sommes d'un côté des mois pairs et de l'autre les mois impairs.

Ensuite, je joins sur le mois / mois+1 de la même année, et avec le cas spécial (le OR) du mois de décembre et du mois de janvier, où pour eux l'année est l'année-1 de l'autre...

Je fais un order by pour que ça soit plus lisible, voici le résultat:

 total | annee | mois | annee | mois 
-------+-------+------+-------+------
    16 |  2011 |    2 |  2011 |    3
    24 |  2011 |    4 |  2011 |    5
    17 |  2011 |    6 |  2011 |    7
    21 |  2011 |    8 |  2011 |    9
    27 |  2011 |   10 |  2011 |   11
    17 |  2011 |   12 |  2012 |    1
    58 |  2012 |    2 |  2012 |    3
    52 |  2012 |    4 |  2012 |    5
    69 |  2012 |    6 |  2012 |    7
    63 |  2012 |    8 |  2012 |    9
    65 |  2012 |   10 |  2012 |   11
(11 rows)

Voilà, je pense que ça répond à votre énoncé ?

Merci de revenir vers nous de toute façon que vous arriviez ou non à transposer cet exemple dans votre cas d'utilisation.

Bonne journée,

---

Jean-Paul Argudo
https://www.postgresql.fr
https://www.crunchydata.com

damalaan

Membre

Re : Calcul de moyenne sur 2 mois

merci jpargudo pour cette démo!
je n'ai pas opté pour votre solution

Je crée dans une requête le mois de "référence" de cette manière, et après je l'utilise pour mon regroupement

Pardon pour la trituration de la date, mais elle est stockée d'une manière particulière qui m'oblige à faire des imbrications de fonction pour en retirer qqc (cf la plupart de mes posts sur ce forum!!)

	CASE extract(month from (to_timestamp((tbl_tournee_trn.trn_date_prel / 100)::text, 'YYMM'::text)))::integer%2
	WHEN 0 THEN to_timestamp((tbl_tournee_trn.trn_date_prel / 100)::text, 'YYMM'::text)+ '1 month'::interval
	WHEN 1 THEN to_timestamp((tbl_tournee_trn.trn_date_prel / 100)::text, 'YYMM'::text)
	END as mois_ref

j'extrais le mois, un modulo 2 pour voir s'il est pair ou impair;
si pair, j'ajoute 1 mois
si impair je garde le même mois

celà me semble plus simple!

fneurth

Membre

Re : Calcul de moyenne sur 2 mois

Bonsoir,

-- Je crée une 1er  table de données test avec des dates et un int pour la moyenne.
-- Je crée une 2eme table avec les extrémités des bimestres, début et fin.
-- Je join les deux tables avec un BETWEEN
-- Les extremités filtrent les données ...
-- Un GROUP BY permet la moyenne

-------- données test -------------------------------------
DROP TABLE IF EXISTS mytable ;
SELECT
    generate_series (
    current_date - interval '1 year',
    current_date + interval '1 year',
    '1 day') AS mydate,
    ( random()* 1000)::INT AS myctr

INTO public.mytable ;
-- SELECT * FROM public.mytable ;

-------- 1er jour de chaque bimestre -------------------------------------
DROP TABLE IF EXISTS bim_items;
SELECT
    generate_series (
        CASE     WHEN mod(extract('month' from current_date)::int , 2) = 0
                 THEN  date_trunc('month', CURRENT_DATE ) - interval '13 months'
                 ELSE  date_trunc('month', CURRENT_DATE ) - interval '12 months'
         END                   ,    -- début de la serie,
         current_date           ,    -- fin   de la serie,
          '2 month')                 -- intervalle de la serie
                      AS bim_deb,
        NULL::DATE     AS bim_fin
INTO bim_items
;
---------- Dernier jour de chaque bimestre-----------------------------------
UPDATE bim_items
SET bim_fin = bim_deb + (interval '2 months' - interval '1 day')
;
--------- Filtre by JOIN ... BETWEEN ------------------------------------
SELECT bim_deb , bim_fin , avg(myctr )::NUMERIC(6,2)
FROM bim_items
JOIN mytable ON mydate BETWEEN bim_deb AND bim_fin
GROUP BY bim_deb, bim_fin
ORDER BY bim_deb, bim_fin