mysql - 如何聚合不同粒度级别的加权平均值字段？

Question

我正在将字段的加权公式计算为 sum(revenue)/Sum(qty)，这按照以下查询进行。现在我将创建一个 View 来存储这些结果，如下面的代码所示。

我的问题是，如果我从 View 中选择此 w_revenue 并希望每年查看，我将如何汇总以每年显示它？查看所需的输出。

select month,item, sum(revenue)/ Sum(qty) as w_revenue,
year
from my_revenue_table
group by month, year,item;

create view xyz as 
    select month,item, sum(revenue)/ Sum(qty) as w_revenue,
    year
    from my_revenue_table
    group by month, year,item;

select w_revenue 
from xyz.

我该如何做才能每年汇总？

Year    Month   Item    Revenue Qty Sum(revenue)/Sum(Qty)
2019    Mar      A       10      2    5
2019    Mar      B       30      3    10
2019    Feb      C       50      1    50
2019    Feb      D       20      2    10

每年的预期值(value):

Year    Sum(revenue)/Sum(Qty)
2019    13.75                         (10+30+50+20)/(2+3+1+2)

Answer 1

按年分组，然后创建另一个 View

create view abc as
select sum(revenue)/ Sum(qty) as w_revenue, year
  from my_revenue_table
 group by year;

并调用

select * from xyz union all
select null, null, a.* from abc a

如果我理解正确的话，将它们结合起来。

或每年的收入在每一行重复为

select x.*,
       (select w_revenue
         from abc
        where year = x.year) as w_revenue_year      
  from xyz x