如何计算各图书的平均星级评分？含SQL查询场景

根据你提供的需求和数据，要实现公式中的平均星级评分（AR），可以通过以下两种SQL查询方式完成：

方法一：严格匹配公式逻辑 #

这种写法完全对应你给出的计算公式，清晰展示每个星级的统计过程：

SELECT 
    book_id,
    ROUND(
        (SUM(CASE WHEN rating_date = 1 THEN 1 ELSE 0 END) * 1 +
         SUM(CASE WHEN rating_date = 2 THEN 1 ELSE 0 END) * 2 +
         SUM(CASE WHEN rating_date = 3 THEN 1 ELSE 0 END) * 3 +
         SUM(CASE WHEN rating_date = 4 THEN 1 ELSE 0 END) * 4 +
         SUM(CASE WHEN rating_date = 5 THEN 1 ELSE 0 END) * 5) 
         / COUNT(*),
         2 -- 可根据需求调整小数位数
    ) AS average_rating
FROM review
GROUP BY book_id
ORDER BY book_id;

逻辑说明 #

• SUM(CASE ...) 分别统计每个星级的评论数量（对应公式中的a、b、c、d、e）
• 用每个星级的数量乘以对应星级值，求和后除以总评论数（COUNT(*)）得到平均评分
• ROUND 函数用于格式化结果的小数位数，按需调整即可

方法二：简化加权平均写法 #

由于公式本质是加权平均数，可以直接通过求和与计数的比值实现，写法更简洁高效：

SELECT 
    book_id,
    ROUND(SUM(rating_date) / COUNT(*), 2) AS average_rating
FROM review
GROUP BY book_id
ORDER BY book_id;

逻辑说明 #

• SUM(rating_date) 直接计算所有星级的加权总和（等价于 1*a + 2*b + ... +5*e）
• COUNT(*) 是该图书的总评论数（等价于 a+b+c+d+e）
• 两者相除得到的结果与方法一完全一致，执行效率更高

验证结果 #

基于你提供的样本数据，执行上述查询后会得到以下结果：

book_id | average_rating
3       | 2.00
5       | 4.00

内容的提问来源于stack exchange，提问作者quocanhcute