样本数据：
product_type |segment_type |promotion_id |promotion_value
-----------------------------------------------------------
Beer |Regional |1 |20
Beer |National |1 |20
Beer |Regional |2 |20
Beer |National |2 |20
Beer |National |3 |30
Beer |Regional |4 |40
Soda |Regional |5 |50
Soda |National |5 |50
Soda |Regional |6 |50
Soda |National |6 |50
Soda |National |7 |15
Soda |Regional |8 |20
目标：获取按product_type和segment_type(多维数据集)分组的总促销价值,考虑不同的促销活动.请注意,单个促销可以到达一个或两个细分(区域和国家).
期望的结果：
product_type |segment_type |promotion_value
-------------------------------------------------
Beer | |110
Beer |Regional |80
Beer |National |70
Soda | |135
Soda |Regional |120
Soda |National |115
我目前的SQL如下：
SELECT product_Type,
segment_type,
sum(promotion_value)promotion_value
from sample_data
group by product_type,
cube(segment_type)
目前的结果是：
product_type |segment_type |promotion_value
-------------------------------------------------
Beer | |150
Beer |Regional |80
Beer |National |70
Soda | |235
Soda |Regional |120
Soda |National |115
SQLFiddle：link
有没有办法达到预期的效果？