用下面的“联合所有”怎么样-

Select store_id, 'product' as average_level_type,product as id, sum(quantity) as total_quantity,
Count(distinct customer_id) as unique_customer_count, sum(quantity)/count(distinct customer_id) as average
from transactions
where quantity > 0
group by store_id,product
Union all
Select store_id, 'category' as average_level_type, category as id, sum(quantity) as total_quantity,
Count(distinct customer_id) as unique_customer_count, sum(quantity)/count(distinct customer_id) as average
from transactions
where quantity > 0
group by store_id,category
Union all
Select store_id, 'department' as average_level_type,department as id, sum(quantity) as total_quantity,
Count(distinct customer_id) as unique_customer_count, sum(quantity)/count(distinct customer_id) as average
from transactions
where quantity > 0
group by store_id,department;

如果要避免在这种情况下使用union all，可以使用类似rollup（）或group by grouping sets（）的方法来实现相同的效果，但要以问题中显示的格式获得输出，查询会稍微复杂一些。
编辑：下面是如何使用分组集获得相同的输出-

Select store_id,
       case when G_ID = 3 then 'product' 
            when G_ID = 5 then 'category'
            when G_ID = 6 then 'department' end As average_level_type,
       case when G_ID = 3 then product 
            when G_ID = 5 then category
            when G_ID = 6 then department end As id,
       total_quantity,
       unique_customer_count,
       average
from            
    (select store_id, product, category, department, sum(quantity) as total_quantity, Count(distinct customer_id) as unique_customer_count, sum(quantity)/count(distinct customer_id) as average, GROUPING__ID As G_ID
    from transactions
    group by store_id,product,category,department
    grouping sets((store_id,product),(store_id,category),(store_id,department))
    ) Tab
order by 2    
;