MySQL - 从基本评论表中检索评级和评论摘要的最佳方法，例如谷歌

Question

我已经构建了一个基本表来存储我的评论和评分，并希望检索上述插件的详细信息。

review_id | company_id | user_id | rating | review
1           1            1         3        Great
2           1            2         5        Thank you
3           1            3         5        Great
4           1            4         4        Thank you
5           1            5         1        Great
6           1            6         2        Thank you
7           2            5         1        Great
8           2            6         2        Thank you

获取摘要的 SQL

SELECT company_id as cid, ROUND(AVG(rating)) AS rate, COUNT(*) AS review_count, 
(SELECT COUNT(*) FROM `reviews` WHERE `rating` = 5 AND `company_id` = cid) AS rating_5,
((SELECT COUNT(*) FROM `reviews` WHERE `rating` = 5 AND `company_id` = cid) / COUNT(*) * 100) AS rating_5_avg,
(SELECT COUNT(*) FROM `reviews` WHERE `rating` = 4 AND `company_id` = cid) AS rating_4,
((SELECT COUNT(*) FROM `reviews` WHERE `rating` = 4 AND `company_id` = cid) / COUNT(*) * 100) AS rating_4_avg,
(SELECT COUNT(*) FROM `reviews` WHERE `rating` = 3 AND `company_id` = cid) AS rating_3,
((SELECT COUNT(*) FROM `reviews` WHERE `rating` = 3 AND `company_id` = cid) / COUNT(*) * 100) AS rating_3_avg,
(SELECT COUNT(*) FROM `reviews` WHERE `rating` = 2 AND `company_id` = cid) AS rating_2,
((SELECT COUNT(*) FROM `reviews` WHERE `rating` = 2 AND `company_id` = cid) / COUNT(*) * 100) AS rating_2_avg,
(SELECT COUNT(*) FROM `reviews` WHERE `rating` = 1 AND `company_id` = cid) AS rating_1,
((SELECT COUNT(*) FROM `reviews` WHERE `rating` = 1 AND `company_id` = cid) / COUNT(*) * 100) AS rating_1_avg

FROM `reviews` GROUP BY company_id

结果:

我确信有更好的方法(优化)来做到这一点，但我无法猜测，所以我再次需要帮助。

问题:如何进一步优化？

Answer 1

您可以使用条件聚合来完成此任务:

SELECT company_id as cid, 
       ROUND(AVG(rating)) AS rate, 
       COUNT(*) AS review_count,
       COUNT(CASE WHEN `rating` = 5 THEN 1 END) AS rating_5,
       COUNT(CASE WHEN `rating` = 5 THEN 1 END) / (COUNT(*) * 100) AS rating_5_avg,
        ... etc 
FROM `reviews` 
GROUP BY company_id

使用包含 CASE 表达式的聚合:

COUNT(CASE WHEN `rating` = 5 THEN 1 END)

您可以根据 rating 字段的值获取 company_id 记录子集的计数。