SQL:按多列汇总分组 [英] SQL: Group by Rollup with multiple columns
问题描述
我希望能够进行查询,从不同组中选择平均值,但也可以选择仅选择一组时的平均值.
I want to be able to do a query that selects average values from different groups, but also the average values when only one of the groups are selected.
下面是我使用 atm 的查询 + 简化表结构.
Below is the query + simplified table structure I am using atm.
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
上述查询的问题在于,当年份也是 Null 时,它只显示区域的 Null.而我想要的是区域为 Null 且年份为 2000 的新行.另一个区域为 Null 且年份为 2001 的行.
Issue with the above query is that it only shows Null for region when year is also Null. Whereas what I would want are new rows where region is Null and year is 2000. And another one where region is Null and year is 2001.
因此我们将所有类型的变化作为输出(而不仅仅是年份).它应该看起来像这样:
So we get all types of variations as output (and not just for year). It should look something like this:
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
推荐答案
不幸的是,MySQL 既不支持 Cube 也不支持分组集修饰符,而这些修饰符会使这成为一项简单的任务.由于 rollup 确实为超级聚合形成了层次结构,因此您需要联合结果以获得完整的聚合集.
Unfortunately MySQL doesn't support either the Cube nor the grouping sets modifiers that would have made this an easy task. Since rollup does form a hierarchy for the super aggregates, you will need to Union the results to get your full set of aggregates.
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
这篇关于SQL:按多列汇总分组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!