題目描述

西瓜視頻近期開展了”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';