Moyenne mobile en postgresql
j'ai le tableau suivant dans ma base de données Postgresql 9.1:
select * from ro;
date | shop_id | amount
-----------+----------+--------
2013-02-07 | 1001 | 3
2013-01-31 | 1001 | 2
2013-01-24 | 1001 | 1
2013-01-17 | 1001 | 5
2013-02-10 | 1001 | 10
2013-02-03 | 1001 | 4
2012-12-27 | 1001 | 6
2012-12-20 | 1001 | 8
2012-12-13 | 1001 | 4
2012-12-06 | 1001 | 3
2012-10-29 | 1001 | 3
j'essaie d'obtenir une moyenne mobile comparant les données par rapport aux 3 derniers jeudis sans inclure le jeudi en cours. Voici ma requête:
select date, shop_id, amount, extract(dow from date),
avg(amount) OVER (PARTITION BY extract(dow from date) ORDER BY date DESC
ROWS BETWEEN 0 PRECEDING AND 2 FOLLOWING)
from ro
where extract(dow from date) = 4
C'est le résultat donné
date | shop_id | amount | date_part | avg
-----------+----------+--------+-----------+--------------------
2013-02-07 | 1001 | 3 | 4 | 2.0000000000000000
2013-01-31 | 1001 | 2 | 4 | 2.6666666666666667
2013-01-24 | 1001 | 1 | 4 | 4.0000000000000000
2013-01-17 | 1001 | 5 | 4 | 6.3333333333333333
2012-12-27 | 1001 | 6 | 4 | 6.0000000000000000
2012-12-20 | 1001 | 8 | 4 | 5.0000000000000000
2012-12-13 | 1001 | 4 | 4 | 3.5000000000000000
2012-12-06 | 1001 | 3 | 4 | 3.0000000000000000
j'attends
date | shop_id | amount | date_part | avg
-----------+----------+--------+-----------+--------------------
2013-02-07 | 1001 | 3 | 4 | 2.6666666666666667
2013-01-31 | 1001 | 2 | 4 | 4.0000000000000000
2013-01-24 | 1001 | 1 | 4 | 6.3333333333333333
2013-01-17 | 1001 | 5 | 4 | 6.0000000000000000
2012-12-27 | 1001 | 6 | 4 | 5.0000000000000000
2012-12-20 | 1001 | 8 | 4 |
2012-12-13 | 1001 | 4 | 4 |
2012-12-06 | 1001 | 3 | 4 |
2 réponses
select
"date",
shop_id,
amount,
extract(dow from date),
case when
row_number() over (order by date) > 3
then
avg(amount) OVER (
ORDER BY date DESC
ROWS BETWEEN 1 following AND 3 FOLLOWING
)
else null end
from (
select *
from ro
where extract(dow from date) = 4
) s
Quel est le problème avec les OP de la requête est le cadre de la spécification:
ROWS BETWEEN 0 PRECEDING AND 2 FOLLOWING
à part cela, ma requête évite le calcul inutile en filtrant les jeudis avant d'appliquer les coûteuses fonctions de fenêtre.
S'il est nécessaire de diviser par shop_id, ajoutez évidemment le partition by shop_id pour les deux fonctions, avg et row_number.
je pense qu'une meilleure réponse pourrait être:
SELECT date, shop_id, amount,
extract(dow from date) AS dow,
CASE WHEN count(amount) OVER w = 3
THEN avg(amount) OVER w END AS average_amt
FROM ro
WHERE extract(dow from date) = 4
WINDOW w AS (ORDER BY date DESC ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING)
je pense que c'est plus propre d'utiliser la même fenêtre pour vérifier le nombre de lignes dans la fenêtre et prenant la moyenne. (Ceci sauve aussi deux agrégations de fenêtres, comme on peut le voir dans la réponse originale.)
en ce qui concerne l'affirmation dans la réponse précédente que "ma requête évite l'informatique inutile en filtrant les jeudis avant d'appliquer les fonctions de fenêtre coûteuses", ceci s'applique également à la requête suggéré par L'OP et à ma requête, comme ajout EXPLAIN à l'affiche.