Forum Programmation.SQL calcul de moyennes dans postgreSQL

Posté par  . Licence CC By‑SA.
Étiquettes :
0
14
fév.
2020

Bonjour,

Dans une base pg, j'ai une table "data" qui ressemble à celle ci (ma vrai table a bien plus de "code" et d'années) :

code date_debut valeur
1 2015-01-01 6.5
1 2016-01-01 8.1
1 2017-01-01 2.1
1 2018-01-01 9.0
1 2019-01-01 10.0
2 2015-01-01 3.0
2 2016-01-01 1.7
2 2017-01-01 4.0
2 2018-01-01 1.2
2 2019-01-01 8.3
3 2015-01-01 5.3
3 2016-01-01 9.9
3 2017-01-01 3.7
3 2018-01-01 8.1
3 2019-01-01 8.6

j'aimerais calculer pour chaque "code", les moyennes glissantes sur 3 ans des "valeur" (c'est à dire les moyennes 2015-2017, 2016-2018 et 2017-2019)

j'ai essayè ceci :

SELECT code,AVG(valeur) over w       
FROM data 
GROUP BY code
WINDOW w AS (ORDER BY date_debut DESC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) ;

mais j'obtiens l'erreur

la colonne « valeur » doit apparaître dans la clause GROUP BY ou doit être utilisé dans une fonction d'agrégat
LINE 1: SELECT code,AVG(valeur) over w

et là, je sèche, toute aide sera la bienvenue

Merci d'avance

  • # group by valeur?

    Posté par  . Évalué à 2.

    Je vais surement dire une connerie, mais c'est dredi!

    Tu as essayé GROUP BY valeur, et un ORDER BY code?

  • # partition by code

    Posté par  . Évalué à 4.

    Salut,

    Tu n'en étais pas loin du tout!

    Il ne te manquait que le 'partition by code' dans ta fenêtre.

    Décomposer le problème aide pas mal, je l'ai fait en rajoutant des colonnes supplémentaires pour mieux situer les résultats de la fenêtre.

    J'ai ajouté des dates pour les 2 années précédentes, avec les mèmes valeurs que pour 2015-01-01, pour la démo, mais ça n'est pas fondamentalement nécessaire.

    create table data
(
    code       int,
    date_debut date,
    valeur     float
);
insert into data (code, date_debut, valeur)
values (1, '2015-01-01', 6.5),
       (1, '2016-01-01', 8.1),
       (1, '2017-01-01', 2.1),
       (1, '2018-01-01', 9),
       (1, '2019-01-01', 10),
       (2, '2015-01-01', 3),
       (2, '2016-01-01', 1.7),
       (2, '2017-01-01', 4),
       (2, '2018-01-01', 1.2),
       (2, '2019-01-01', 8.3),
       (3, '2015-01-01', 5.3),
       (3, '2016-01-01', 9.9),
       (3, '2017-01-01', 3.7),
       (3, '2018-01-01', 8.1),
       (3, '2019-01-01', 8.6);

insert into data (code, date_debut, valeur)
values (1, '2013-01-01', 6.5),
       (2, '2013-01-01', 3),
       (3, '2013-01-01', 5.3),
       (1, '2014-01-01', 6.5),
       (2, '2014-01-01', 3),
       (3, '2014-01-01', 5.3),
       (1, '2015-01-01', 6.5),
       (2, '2015-01-01', 3),
       (3, '2015-01-01', 5.3);

SELECT code,
       count(*) over w,
       avg(valeur) over w,
       min(date_debut) over w,
       min(valeur) over w,
       max(date_debut) over w,
       max(valeur) over w
FROM data
window w as (partition by code ORDER BY date_debut DESC ROWS BETWEEN 2 PRECEDING and CURRENT ROW )
order by code, date_debut;

    Note qu'il faudra exclure par la suite les résultats dont la moyenne est calculée sur moins de 3 ans.

    Merci pour l'exercice, ça décrasse!

Suivre le flux des commentaires

Note : les commentaires appartiennent à celles et ceux qui les ont postés. Nous n’en sommes pas responsables.