作者热门文章
- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我的 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 |
最佳答案
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
除此之外,我的查询通过在应用昂贵的窗口函数之前过滤星期四来避免不必要的计算。
如果需要按 shop_id 分区,则显然将 partition by shop_id
添加到两个函数,avg
和 row_number
。
关于postgresql - postgresql中的移动平均线,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/14749450/
我是一名优秀的程序员,十分优秀!