SQL:按多列汇总分组 [英] SQL: Group by Rollup with multiple columns

查看:122
本文介绍了SQL:按多列汇总分组的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我希望能够进行查询,从不同组中选择平均值,但也可以选择仅选择一组时的平均值.

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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆