PostgreSQL中的移动平均值

Question

PostgreSQL中的移动平均值

postgresqlmathaveragepostgresql-9.1moving-average

32

我在我的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

我想要得到一个移动平均值，将数据与最近的3个星期四进行比较，但不包括本周四。以下是我的查询：

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 |

- Glicious

+1 好问题 - Pg 版本，样例数据，期望结果。谢谢！在此转换为 SQLFiddle：http://sqlfiddle.com/#!1/18891/1 - Craig Ringer

5

顺便说一句，“date”是一个可怕的列名，因为它是数据类型的名称。避免使用它。如果必须使用它，请始终使用表别名进行限定，并将其用双引号括起来，就像这里所示：http://sqlfiddle.com/#!1/18891/4 - Craig Ringer

谢谢Craig :) 这只是一个非常大的表格中的样本数据集。我只是想先正确地获取查询结果。 - Glicious

2个回答

13

我认为一个更好的答案可能是:

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)

我认为，在同一个窗口中同时检查窗口中的行数和取平均值更加清晰。(这也可以看作是在原回答中节省了两个窗口聚合操作)

关于之前的回答中声称“通过在应用昂贵的窗口函数之前过滤掉星期四来避免不必要的计算”的说法，这也适用于OP建议的查询和我的查询，可以通过将EXPLAIN附加到任一查询中查看。

- Ian Gow

这是一个很旧的帖子，但感谢您提供的解决方案。 :-) - Glicious

好的解决方案。SQL很清晰，但是对于喜欢引用的读者，这是来自Jul 05 '17（比本篇文章早）。该技术可以被命名为“移动窗口”。一个关于如何指定您的窗口的好教程。 - Peter Krauss

如果数据中有一个星期四被跳过了，会发生什么？滚动平均数会包括来自意外扩大的窗口的数据吗（因为缺少的星期四数据被隐式地视为0）？ - Joey Baruch

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Clodoaldo Neto · Accepted Answer

SQL Fiddle

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 的查询语句存在问题，原因在于框架规范的设置：

ROWS BETWEEN 0 PRECEDING AND 2 FOLLOWING

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

如果需要按店铺ID分区，那么显然需要将partition by shop_id添加到avg和row_number两个函数中。