京东大数据面试SQL-商品评论情感分析统计
一、题目背景
这道题来自京东商家服务部的数据分析岗面试。京东每个商品详情页下方都有用户评论，运营人员需要定期生成"商品口碑报表"——哪些商品好评率高可以加大推广，哪些商品差评集中需要联系供应商改进。
📝 评分体系 京东采用 1-5 星评分体系。通常约定：
- 好评（4-5 星）：用户满意，愿意复购
- 中评（3 星）：无功无过，有改进空间
- 差评（1-2 星）：用户不满，需重点关注
这是电商数据分析中最基础的"情感分类"（Sentiment Classification）任务。
二、题目
现有一张商品评论表 t4_jd_product_review，记录了用户对商品的评分（1-5分）和评论内容。请按商品统计好评、中评、差评的数量及占比。
t4_jd_product_review 表
+----------+------------+--------+-------------------+
| review_id| product_id | rating | review_text |
+----------+------------+--------+-------------------+
| 1 | P001 | 5 | 非常好用 |
| 2 | P001 | 4 | 不错 |
| 3 | P001 | 2 | 一般般 |
| 4 | P002 | 5 | 性价比高 |
| 5 | P002 | 1 | 质量太差了 |
| 6 | P001 | 3 | 还行 |
| 7 | P002 | 4 | 物流很快 |
| 8 | P001 | 1 | 收到就是坏的 |
| 9 | P003 | 5 | 完美 |
| 10 | P003 | 5 | 推荐购买 |
| 11 | P003 | 4 | 不错的产品 |
| 12 | P002 | 5 | 第二次购买了 |
+----------+------------+--------+-------------------+
分类规则：
- 好评：评分大于等于 4
- 中评：评分等于 3
- 差评：评分小于等于 2
期望输出：每个商品的 评论总数、好评数、中评数、差评数、好评率(%)。
三、思路分析
本题核心是 CASE WHEN + SUM 条件聚合，是 SQL 面试中必考的基础题型。
- 分类映射：用 CASE WHEN 把 1-5 评分映射为"好评/中评/差评"三类
- 条件计数：
SUM(CASE WHEN ... THEN 1 ELSE 0 END)统计各类别数量 - 占比计算：
SUM(CASE WHEN 好评 THEN 1 END) * 100.0 / COUNT(*)得到好评率
💡 COUNT vs SUM 条件计数有两种写法，推荐用 SUM：
两者结果相同，但 SUM 写法更直观——"满足条件加 1，否则加 0"。
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
四、逐步推导
步骤1：先看原始数据分布
在写聚合 SQL 之前，先快速扫一眼各商品评论情况。
SELECT
product_id,
rating,
COUNT(*) AS cnt
FROM t4_jd_product_review
GROUP BY product_id, rating
ORDER BY product_id, rating;
执行结果：
+------------+--------+-----+
| product_id | rating | cnt |
+------------+--------+-----+
| P001 | 1 | 1 |
| P001 | 2 | 1 |
| P001 | 3 | 1 |
| P001 | 4 | 1 |
| P001 | 5 | 1 |
| P002 | 1 | 1 |
| P002 | 4 | 1 |
| P002 | 5 | 2 |
| P003 | 4 | 1 |
| P003 | 5 | 2 |
+------------+--------+-----+
直观感受：
- P001：评分非常分散（1-5 各一条），口碑两极分化
- P002：偏正面，4-5 分占 3/4，但有 1 条 1 分差评
- P003：评分高度集中在 4-5 分，口碑最好
步骤2：用 CASE WHEN 逐行打标签
先不聚合，给每条评论打上情感标签，验证分类逻辑是否正确。
SELECT
product_id,
rating,
CASE
WHEN rating >= 4 THEN '好评'
WHEN rating = 3 THEN '中评'
ELSE '差评'
END AS sentiment
FROM t4_jd_product_review;
执行结果：
+-------------+---------+------------+
| product_id | rating | sentiment |
+-------------+---------+------------+
| P001 | 5 | 好评 |
| P001 | 4 | 好评 |
| P001 | 2 | 差评 |
| P002 | 5 | 好评 |
| P002 | 1 | 差评 |
| P001 | 3 | 中评 |
| P002 | 4 | 好评 |
| P001 | 1 | 差评 |
| P003 | 5 | 好评 |
| P003 | 5 | 好评 |
| P003 | 4 | 好评 |
| P002 | 5 | 好评 |
+-------------+---------+------------+
12 rows selected (7.686 seconds)(dwsql.com)
步骤3：条件聚合统计
把标签列"折叠"为计数列——每个 product_id 一行，每种情感一列。
SELECT
product_id,
COUNT(*) AS total_reviews,
SUM(CASE WHEN rating >= 4 THEN 1 ELSE 0 END) AS good_reviews,
SUM(CASE WHEN rating = 3 THEN 1 ELSE 0 END) AS neutral_reviews,
SUM(CASE WHEN rating <= 2 THEN 1 ELSE 0 END) AS bad_reviews
FROM t4_jd_product_review
GROUP BY product_id
ORDER BY product_id;
执行结果：
+-------------+----------------+---------------+------------------+--------------+
| product_id | total_reviews | good_reviews | neutral_reviews | bad_reviews |
+-------------+----------------+---------------+------------------+--------------+
| P001 | 5 | 2 | 1 | 2 |
| P002 | 4 | 3 | 0 | 1 |
| P003 | 3 | 3 | 0 | 0 |
+-------------+----------------+---------------+------------------+--------------+
3 rows selected (1.518 seconds)(dwsql.com)
步骤4：加好评率
SELECT
product_id,
COUNT(*) AS total_reviews,
SUM(CASE WHEN rating >= 4 THEN 1 ELSE 0 END) AS good_reviews,
SUM(CASE WHEN rating = 3 THEN 1 ELSE 0 END) AS neutral_reviews,
SUM(CASE WHEN rating <= 2 THEN 1 ELSE 0 END) AS bad_reviews,
ROUND(SUM(CASE WHEN rating >= 4 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS good_rate
FROM t4_jd_product_review
GROUP BY product_id
ORDER BY product_id;
最终结果：
+-------------+----------------+---------------+------------------+--------------+------------+
| product_id | total_reviews | good_reviews | neutral_reviews | bad_reviews | good_rate |
+-------------+----------------+---------------+------------------+--------------+------------+
| P001 | 5 | 2 | 1 | 2 | 40.0 |
| P002 | 4 | 3 | 0 | 1 | 75.0 |
| P003 | 3 | 3 | 0 | 0 | 100.0 |
+-------------+----------------+---------------+------------------+--------------+------------+
3 rows selected (0.535 seconds)(dwsql.com)
分析洞察：
- P001 需要重点关注：好评率仅 40%，差评率 40%，口碑严重分化。建议深入阅读差评文本，定位是"质量"还是"物流"问题
- P002 表现不错：75% 好评，但有一条 1 分差评——可能是偶发个例，可回复安抚
- P003 口碑完美：100% 好评，可加大广告投入和首页推荐
五、常见坑点
⚠️ SUM 的 ELSE 0 不能省略
虽然 SUM 忽略 NULL 的行为和 SUM(0) 一样，但面试中显式写 ELSE 0 说明你理解边界情况。
⚠️ 整数除法陷阱
* 100.0中的.0是关键——它把整数运算转为浮点运算。忘记写.0是面试中最常见的低级错误。
六、举一反三
- 按时间维度统计：如果评论表有
review_date字段，加GROUP BY DATE_FORMAT(review_date, 'yyyy-MM')可以看到好评率的月度变化趋势 - 加权好评率：电商中常用"有图评论权重更高"——
SUM(CASE WHEN rating>=4 AND has_image=1 THEN 2 ELSE CASE WHEN rating>=4 THEN 1 ELSE 0 END) - NPS 净推荐值：升级版指标，"推荐者（5分）占比 - 贬损者（1-2分）占比"，公式：
(SUM(CASE WHEN rating=5 THEN 1 END) - SUM(CASE WHEN rating<=2 THEN 1 END)) * 100.0 / COUNT(*)
七、知识点总结
💡 条件聚合标准模板
| 考点 | 说明 |
|---|---|
| CASE WHEN + SUM | 条件聚合，将行级标签折叠为列级计数 |
* 100.0 | 触发浮点除法，避免整数截断 |
| ELSE 0 | 显式默认值，确保语义清晰 |
| GROUP BY | 按商品维度汇总，配合聚合函数使用 |
八、建表语句和数据插入
点击展开 DDL & DML
CREATE TABLE IF NOT EXISTS t4_jd_product_review (
review_id INT,
product_id STRING,
rating INT,
review_text STRING
);
INSERT INTO t4_jd_product_review VALUES
(1, 'P001', 5, '非常好用'),
(2, 'P001', 4, '不错'),
(3, 'P001', 2, '一般般'),
(4, 'P002', 5, '性价比高'),
(5, 'P002', 1, '质量太差了'),
(6, 'P001', 3, '还行'),
(7, 'P002', 4, '物流很快'),
(8, 'P001', 1, '收到就是坏的'),
(9, 'P003', 5, '完美'),
(10, 'P003', 5, '推荐购买'),
(11, 'P003', 4, '不错的产品'),
(12, 'P002', 5, '第二次购买了');
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
