SQL:具有多列的Group by Rollup

Question

我希望能够执行一个查询，从不同的组中选择平均值，但也可以在仅选择其中一个组的情况下选择平均值。

下面是我使用的自动柜员机的查询+简化表结构。

create table income_region (year int,region varchar(40),income float)

insert into income_region (income,region,year) values (2000,'North America', 2000)
insert into income_region (income,region,year) values(2200,'Europe', 2000)
insert into income_region (income,region,year) values(2101,'North America', 2001)
insert into income_region (income,region,year) values(2001,'Europe', 2001)
insert into income_region (income,region,year) values(2400,'North America', 2000)

select avg(income) as avg_income ,region,year as year
from income_region group by region,year with rollup 

上述查询的问题是，当year也为Null时，它仅显示region为Null。而我想要的是region为Null，year为2000的新行。另一个是region为Null，year为2001。

因此，我们得到了所有类型的变化作为输出(而不仅仅是年份)。它应该看起来像这样：

avg_income region        year

2200      Europe         2000   
2001      Europe         2001    
2100.5    Europe         Null
2200      North America  2000     
2101      North America  2001   
2167      North America  Null    
2140.4    Null           Null 
2200      Null           2000
2050.5    Null           2001

Answer 1

不幸的是，MySQL既不支持多维数据集，也不支持分组集修饰符，这使得这项任务变得很容易。由于rollup确实形成了超级聚合的层次结构，因此您将需要联合结果以获得完整的聚合集。

select avg(income) as avg_income ,region,year as year
from income_region group by region,year with rollup
Union all 
Select avg(income) as avg_income ,null as region,year as year
from income_region group by year

HTH