美团大数据面试SQL-商家评分Top N及变化趋势
⚠️ 待修正
一、题目背景
这道题来自美团到店事业群的数据分析岗面试。美团拥有数百万商家，商家评分是用户选择餐厅的核心参考，也是平台对商家分级管理的依据。评分Top N排名和环比变化趋势，能帮助运营快速识别"上升期优质商家"和"下滑中的问题商家"。
业务场景：商家运营团队每周出"评分Top 10黑马榜"——评分上升最快的新商家，给予流量扶持。同时监控Top商家的评分是否持续下滑（可能是服务质量下降的信号），及时触发商家质量预警。
二、题目
现有一张商家月度评分表 t3_shop_monthly_rating，记录了每个商家每月的综合评分。请查询当前月（2023-03）评分排名Top 3的商家，并统计其评分相较上月的变化。
商家月度评分表 t3_shop_monthly_rating：
+----------+---------+--------+
| shop_id | month | rating |
+----------+---------+--------+
| S001 | 2023-02 | 4.5 |
| S001 | 2023-03 | 4.7 |
| S002 | 2023-02 | 4.8 |
| S002 | 2023-03 | 4.6 |
| S003 | 2023-02 | 4.2 |
| S003 | 2023-03 | 4.4 |
| S004 | 2023-02 | 4.9 |
| S004 | 2023-03 | 4.9 |
| S005 | 2023-02 | 4.0 |
| S005 | 2023-03 | 4.1 |
| S006 | 2023-02 | 4.3 |
| S006 | 2023-03 | 4.5 |
+----------+---------+--------+
三、思路分析
本题考察排名（RANK）和环比变化（LAG）的组合应用。
解题步骤：
- 获取当前月（2023-03）和上月的评分数据；
- 对当前月数据按评分降序排名，取Top 3；
- 关联上月数据计算评分变化；
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
四、逐步推导
1. 提取当前月和上月评分
执行SQL
select shop_id,
max(case when month = '2023-03' then rating end) as cur_rating,
max(case when month = '2023-02' then rating end) as prev_rating
from t3_shop_monthly_rating
where month in ('2023-02', '2023-03')
group by shop_id
执行结果
+----------+------------+-------------+
| shop_id | cur_rating | prev_rating |
+----------+------------+-------------+
| S001 | 4.7 | 4.5 |
| S002 | 4.6 | 4.8 |
| S003 | 4.4 | 4.2 |
| S004 | 4.9 | 4.9 |
| S005 | 4.1 | 4.0 |
| S006 | 4.5 | 4.3 |
+----------+------------+-------------+
2. 排名并取Top 3，计算环比变化
执行SQL
select shop_id,
cur_rating,
prev_rating,
round(cur_rating - prev_rating, 2) as rating_change,
rn as rank_num
from (
select shop_id,
cur_rating,
prev_rating,
cur_rating - prev_rating as rating_change_val,
rank() over (order by cur_rating desc) as rn
from (
select shop_id,
max(case when month = '2023-03' then rating end) as cur_rating,
max(case when month = '2023-02' then rating end) as prev_rating
from t3_shop_monthly_rating
where month in ('2023-02', '2023-03')
group by shop_id
) t1
) t2
where rn <= 3
order by rn
执行结果
+----------+------------+-------------+---------------+----------+
| shop_id | cur_rating | prev_rating | rating_change | rank_num |
+----------+------------+-------------+---------------+----------+
| S004 | 4.9 | 4.9 | 0.0 | 1 |
| S001 | 4.7 | 4.5 | 0.2 | 2 |
| S002 | 4.6 | 4.8 | -0.2 | 3 |
+----------+------------+-------------+---------------+----------+
S004以4.9分稳居榜首，评分持平；S001评分上升0.2进入前三；S002虽排名第三但评分下降了0.2。
五、常见坑点
坑1：上月数据缺失时的处理
如果某商家上个月没有评分记录，LAG 返回 NULL，评分变化 = NULL。应该用 COALESCE 或 LEFT JOIN 上月表来区分"新上榜商家"和"评分未变化商家"。
坑2：RANK vs ROW_NUMBER 的选择
评分相同时，RANK 产生并列排名（1,1,3），ROW_NUMBER 硬排序（1,2,3）。"Top 3"如果用 ROW_NUMBER，两个并列第1的商家会挤掉第3名。通常评分排名用 RANK 更合理——并列评分应该并列名次。
坑3：只取当月Top 3但上月可能不在Top 3
在上月数据中 JOIN 时限定 WHERE month=上月，不受当前月排名影响。但上月不在Top 3的商家，评分变化仍可计算，只要上月有记录即可。
六、举一反三
- 分层Top N：加上 category_id 分组，实现"各品类评分Top N"
- 评分趋势预警：连续2个月评分下降 ≥ 0.3 的商家，标记为"质量下滑预警"推送商家运营
- 加权评分：综合评分 × 0.6 + 评论数归一化分数 × 0.4，让"高评分+高评论量"的商家更容易被发现
- 按城市/商圈维度：GROUP BY 加上 city_id，对比不同商圈的优质商家密度
七、知识点总结
| 考点 | 说明 |
|---|---|
| RANK() OVER | 按评分降序排名，处理并列值 |
| LAG() 环比 | 获取每个商家上月评分，计算评分变化 |
| WHERE 当月 + LEFT JOIN 上月 | 保留当月Top 3，关联上月数据做对比 |
| CONCAT + ROUND | 格式化评分变化为易读字符串 |
八、建表语句和数据插入
点击展开 DDL & DML
CREATE TABLE t3_shop_monthly_rating (
shop_id string COMMENT '商家ID',
month string COMMENT '月份',
rating decimal(2,1) COMMENT '综合评分'
) COMMENT '商家月度评分表';
-- 数据插入
INSERT INTO t3_shop_monthly_rating VALUES
('S001', '2023-02', 4.5),
('S001', '2023-03', 4.7),
('S002', '2023-02', 4.8),
('S002', '2023-03', 4.6),
('S003', '2023-02', 4.2),
('S003', '2023-03', 4.4),
('S004', '2023-02', 4.9),
('S004', '2023-03', 4.9),
('S005', '2023-02', 4.0),
('S005', '2023-03', 4.1),
('S006', '2023-02', 4.3),
('S006', '2023-03', 4.5);
「数据仓库技术」文章同步更新，不错过每一篇干货
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
