题目描述

西瓜视频近期开展了”2020百大人气创作者”优质内容扶持项目，鼓励用户产出优质的视频内容。

现需要统计2020年11月01日至2020年11月30日期间创作的视频中，

“科技”大类下“数码测评"子类的视频好评率（好评率=好评数/视频观看次数），写出sql语句进行查询。

用户观看视频后的评价详情表：content_action_infoid（视频id，主键）create_time (创建时间，格式‘2020-11-01’)user_id（观看者id）content_id (视频id，外键)content_action (视频评价，包括’点赞‘，’差评‘，‘无评价’)视频详情表：dim_contentcontent_id (外键)creator_id（创作者id）content_category (商品类目)content_sub_category (品牌名称)

SELECT dc.content_id,dc.content_category,dc.content_sub_category,COUNT(CASE WHEN ca.content_action = '点赞' THEN 1 END) AS 好评数,COUNT(DISTINCT ca.user_id) AS 视频观看次数,ROUND(COUNT(CASE WHEN ca.content_action = '点赞' THEN 1 END) / COUNT(DISTINCT ca.user_id), 4) AS 好评率
FROM dim_content dc
JOIN content_action_info ca ON dc.content_id = ca.content_id
WHERE dc.content_category = '科技'AND dc.content_sub_category = '数码测评'AND ca.create_time BETWEEN '2020-11-01' AND '2020-11-30'
GROUP BY dc.content_id, dc.content_category, dc.content_sub_category
ORDER BY 好评率 DESC;

• 若需要统计所有符合条件视频的整体好评率，可移除GROUP BY并直接计算总和

SELECTCOUNT(1) AS all_action,SUM(CASE WHEN content_action = '点赞' THEN 1 ELSE 0 END) AS like_action,SUM(CASE WHEN content_action = '点赞' THEN 1 ELSE 0 END) / COUNT(1) AS like_rate
FROM content_action_info AS a
JOIN dim_content AS b ON a.content_id = b.content_id
WHERE b.content_category = '科技'AND b.content_sub_category = '数码测评'AND a.create_time BETWEEN '2020-11-01' AND '2020-11-30';