SQL一个查询中的不同分组依据级别
SQL一个查询中的不同分组依据级别
提问于 2019-07-25 18:57:15
我尝试获取一个维度按另一个维度分组的百分比。所以说我有
table1 =
country, product, sale
US, TV, 50
US, TV, 150
MEX, TV, 50
US, PC, 800我想在国家/地区按产品分组，并获得：
Select... =
Country, Product, Country_Total, Product_Per_Country, Percentage
US, TV, 1000, 200, 20%
US, PC, 1000, 800, 80%
MEX, TV, 50, 50, 100%我只想从table1中选择一次，因为这是一个扩展的select。你能帮上忙吗？
致以问候，彼得
回答 3
Stack Overflow用户
回答已采纳
发布于 2019-07-25 18:58:47
使用窗口函数：
select country, product, sum(sale) as sales,
sum(sale) / sum(sum(sale)) over (partition by country) as country_ratio
from table1 t1
group by country, product;Stack Overflow用户
发布于 2019-07-25 19:01:02
尝试像下面这样使用窗口函数
select country, product,sum(sum(sale)) over( partition by country) Country_Total,
sum(sum(sale))over( partition by country,product) Product_Per_Country,
(sum(sum(sale))over( partition by country,product) /sum(sum(sale)) over( partition by country))*100 as Percentage
from cte group by country, product
COUNTRY PRODUCT COUNTRY_TOTAL PRODUCT_PER_COUNTRY PERCENTAGE
MEX TV 50 50 100
US PC 1000 800 80
US TV 1000 200 20Stack Overflow用户
发布于 2019-07-25 19:00:32
查询类似于
SELECT country, product, sum(sale) as sales,
sum(sale) / sum(sum(sale)) over (partition by country) as country_ratio
FROM table1
GROUP BY country, product;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/57200349
复制相关文章
相似问题
腾讯云开发者
