sql - 2列的移动平均值

Question

你好，我有一个问题。我知道如何使用 oracle 分析函数计算过去 3 个月的移动平均线......但我的情况有点不同

月份-----ProductType-----Sales------------平均(必须找到这个)

1--------A------------10

1--------B----------------12

1--------C----------------17

2--------A------------21

3--------C------------2

3--------B----------------21

4--------B----------------23

5

6

7

8

9

所以我们有每个月和每种产品类型的销售额...我需要计算过去 3 个月和特定产品的移动平均值。

例子:

对于第 4 个月和产品 B，它将是 (21+0+12)/3

有什么想法吗？

Answer 1

另一种选择是使用解析函数的窗口子句

with my_data as (
  select 1 as month, 'A' as product, 10 as sales from dual union all
  select 1 as month, 'B' as product, 12 as sales from dual union all
  select 1 as month, 'C' as product, 17 as sales from dual union all
  select 2 as month, 'A' as product, 21 as sales from dual union all
  select 3 as month, 'C' as product, 2 as sales from dual union all
  select 3 as month, 'B' as product, 21 as sales from dual union all
  select 4 as month, 'B' as product, 23 as sales from dual 
)
select 
  month, 
  product, 
  sales,
  nvl(sum(sales) 
    over (partition by product order by month 
          range between 3 preceding and 1 preceding),0)/3 as average_sales
from my_data
order by month, product