获取同一列的评级百分比

Question

我被这个问题困了好几个小时了，所以我需要一些帮助。表将被命名为table:

评论

考虑到您已经使用条件聚合，您的查询实际上非常接近您所需要的。您所缺少的只是将Good和Bad划分为Good和Bad评论的总数:

select restaurant
,count(rating) as Ratings
,sum(case when rating = 'Good'
then 1.0
else 0.0
end 
) / count(rating)  as good_percent
,sum(case when rating = 'Poor' 
then 1.0
else 0.0
end
) / count(rating) as bad_percent
from test 
where rating in('Good','Poor')
group by restaurant;

您可以使用条件聚合，这意味着case表达式是聚合函数的参数:

select restaurant, count(*) as num_ratings,
sum(case when rating = 'Good' then 1 else 0 end) as num_good,
sum(case when rating = 'Poor' then 1 else 0 end) as num_poor
from test 
where rating in ('Poor', 'Good')
group by restaurant;

如果你真的想要比率，我建议使用avg():

select restaurant, count(*) as num_ratings,
sum(case when rating = 'Good' then 1 else 0 end) as num_good,
sum(case when rating = 'Poor' then 1 else 0 end) as num_poor,
avg(case when rating = 'Good' then 1.0 else 0 end) as ratio_good,
avg(case when rating = 'Poor' then 1.0 else 0 end) as ratio_poor
from test 
where rating in ('Poor', 'Good')
group by restaurant;

注意1.0。SQL Server做整数除法和求平均值，所以这允许平均值有一个小数成分。