我有一张这样的桌子:
Week prod Value
1 A 2.3
1 B 4.1
1 C 6.3
2 A 0
2 B 3.4
2 C 1.4
3 A 4.2
3 B 2.5
3 C 6.7现在我有很多周的相似数据,我想汇总3周1,2,3周,然后取prods(A,B,C)的平均值,就像这样,下一次汇总2,3,4周,然后取prods的平均值,一直到上周。
发布于 2014-09-27 19:17:04
许多数据库支持ANSI标准窗口函数:
with weeks as (
select week, sum(value) as value
from table t
group by week
)
select week, value,
avg(value) over (order by week range between 2 preceding and current row) as avg_3week
from weeks;编辑:
您可以使用子查询来编写以下代码:
select week, value,
avg(value) over (order by week range between 2 preceding and current row) as avg_3week
from (select week, sum(value) as value
from table t
group by week
) weeks;您可能需要在SAS中将其设置为直通查询。proc sql不做窗口函数。
发布于 2014-09-27 19:32:16
试试这个:
select id, avg(avg(val)) over(order by id ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)
from t
group by id如果不支持滚动窗口,可以使用:
with tt(id, aval) as (
select id, avg(val)
from t
group by id
)
select id, (select avg(aval) from tt tt1 where tt1.id between tt2.id and tt2.id + 2)
from tt tt2不使用CTE:
select distinct id, (select avg(aval)
from (select id, avg(val) aval
from t
group by id
) tt1
where tt1.id between tt2.id and tt2.id + 2)
from t tt2https://stackoverflow.com/questions/26073862
复制相似问题