一个SQL实现不同聚合水平

将不同的聚合水平写在一个SQL中，主要是用union all合并不同的子查询。

举例：

with  sales as (
select 'v1' as vendor, 'a1' as asin ,10 as quantity
union all
select 'v1' as vendor, 'a2' as asin, 20 as quantity
union all
select 'v2' as vendor, 'a1' as asin, 100 as quantity
union all
select 'v2' as vendor, 'a2' as asin, 100 as quantity
union all
select 'v3' as vendor, 'a1' as asin, 200 as quantity

)
-- select * from sales;
select * from (
select vendor,asin, sum(quantity) as tot
from sales
group by 1,2
union all
select vendor, 'total_asin' as asin, sum(quantity) as tot
from sales
group by 1,2
union all
select 'total_vendor' vendor, asin, sum(quantity) as tot
from sales
group by 1,2
) t
order by 1,2

截图：