PostgreSQL中的移动平均线 [英] Moving average in postgresql

问题描述

我的Postgresql 9.1数据库中有下表:

I have the following table in my Postgresql 9.1 database:

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

我正在尝试将移动平均线与最近三个星期四的数据进行比较，而不包括当前星期四的数据.这是我的查询:

I am trying to get a moving average comparing data against last 3 Thursdays without including the current Thursday. Here's my query:

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

这是给出的结果

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

我希望

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 |

推荐答案

SQL提琴

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

OP查询的问题在于帧规范:

What is wrong with the OP's query is the frame specification:

ROWS BETWEEN 0 PRECEDING AND 2 FOLLOWING

除此之外，我的查询通过在应用昂贵的窗口函数之前过滤星期四来避免不必要的计算.

Other than that my query avoids unneeded computing by filtering Thursdays before applying the expensive window functions.

如果有必要按shop_id进行分区，则显然要将partition by shop_id添加到两个功能avg和row_number.

If it is necessary to partition by shop_id then obviously add the partition by shop_id to both functions, avg and row_number.

这篇关于PostgreSQL中的移动平均线的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！