SQL 用户兴趣标签打标：基于行为权重的标签评分（小红书面试题）
一、题目
根据用户在笔记上的行为（浏览、点赞、收藏），为每个用户计算各品类的兴趣标签。规则如下，同一用户同一品类只取最高等级标签：
| 等级 | 条件 | 标签 |
|---|---|---|
| 兴趣用户 | 某品类笔记浏览 >= 3 次 | XX兴趣用户 |
| 爱好者 | 某品类笔记点赞 >= 2 次 | XX爱好者 |
| 深度用户 | 某品类笔记收藏 >= 1 次 | XX深度用户 |
注意：同一用户可多次浏览同一篇笔记，但点赞和收藏每篇笔记只能有一次（以首次为准）。
假设有用户行为表 t5_user_behavior，记录了用户每次对笔记的操作时间及笔记所属品类：
+---------+----------+----------+------------+-------------------+
| user_id | note_id | category | action | action_time |
+---------+----------+----------+------------+-------------------+
| u01 | N001 | 美妆 | view | 2025-06-01 08:00 |
| u01 | N001 | 美妆 | view | 2025-06-01 12:00 |
| u01 | N001 | 美妆 | view | 2025-06-02 20:00 |
| u01 | N002 | 美妆 | view | 2025-06-01 09:00 |
| u01 | N001 | 美妆 | like | 2025-06-01 12:00 |
| u01 | N002 | 美妆 | like | 2025-06-01 09:30 |
| u01 | N002 | 美妆 | save | 2025-06-01 09:30 |
| u02 | N001 | 美妆 | view | 2025-06-01 10:00 |
| u02 | N001 | 美妆 | view | 2025-06-02 08:00 |
| u02 | N001 | 美妆 | view | 2025-06-03 18:00 |
| u02 | N001 | 美妆 | like | 2025-06-01 10:30 |
| u02 | N002 | 美妆 | like | 2025-06-01 11:00 |
| u02 | N001 | 美妆 | save | 2025-06-01 10:30 |
| u03 | N003 | 穿搭 | view | 2025-06-01 07:00 |
| u03 | N003 | 穿搭 | view | 2025-06-02 12:00 |
| u03 | N003 | 穿搭 | view | 2025-06-03 22:00 |
| u03 | N003 | 穿搭 | like | 2025-06-01 07:30 |
+---------+----------+----------+------------+-------------------+
二、思路分析
- 浏览可以重复，直接用
count(case when action='view' then 1 end)统计浏览次数； - 点赞和收藏每篇笔记只有一次，但同一用户可能点赞多篇不同笔记，因此按品类汇总时仍需
count(case when ...)而非count(distinct note_id)； - 使用
case when根据三级阈值打标签，深度用户（最高等级）必须写在第一个 when； - 未达到任何等级的标记为"游客"。
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
三、逐步推导
1.按用户和品类统计行为次数
表内已有品类字段，直接用 count(case when ...) 按用户和品类分组统计各行为次数。
执行SQL
select user_id,
category,
count(case when action = 'view' then 1 end) as total_view,
count(case when action = 'like' then 1 end) as total_like,
count(case when action = 'save' then 1 end) as total_save
from t5_user_behavior
group by user_id, category
查询结果
+---------+----------+------------+------------+------------+
| user_id | category | total_view | total_like | total_save |
+---------+----------+------------+------------+------------+
| u01 | 美妆 | 4 | 2 | 1 |
| u02 | 美妆 | 3 | 2 | 1 |
| u03 | 穿搭 | 3 | 1 | 0 |
+---------+----------+------------+------------+------------+
2.根据规则打标
执行SQL
select user_id,
category,
total_view,
total_like,
total_save,
case when total_save >= 1 then concat(category, '深度用户')
when total_like >= 2 then concat(category, '爱好者')
when total_view >= 3 then concat(category, '兴趣用户')
else '游客' end as user_tag
from (
select user_id,
category,
count(case when action = 'view' then 1 end) as total_view,
count(case when action = 'like' then 1 end) as total_like,
count(case when action = 'save' then 1 end) as total_save
from t5_user_behavior
group by user_id, category
) t
查询结果
+---------+----------+------------+------------+------------+-------------+
| user_id | category | total_view | total_like | total_save | user_tag |
+---------+----------+------------+------------+------------+-------------+
| u01 | 美妆 | 4 | 2 | 1 | 美妆深度用户 |
| u02 | 美妆 | 3 | 2 | 1 | 美妆深度用户 |
| u03 | 穿搭 | 3 | 1 | 0 | 穿搭兴趣用户 |
+---------+----------+------------+------------+------------+-------------+
u01 和 u02 在美妆品类均有收藏行为（save>=1），同时满足三级条件，按最高等级取了"深度用户"。u03 仅满足浏览>=3，标记为"兴趣用户"。
四、常见坑点
坑1：CASE WHEN 条件顺序错误导致标签被覆盖 — case when 从上到下匹配，命中即停止。深度用户（save >= 1）必须写在第一个 when，爱好者其次，兴趣用户最后。如果先写 view >= 3，满足深度用户条件的行也会被错误标记为"兴趣用户"。
坑2：同一用户同一品类内 group by 不可遗漏 user_id — group by user_id, category 必须同时包含两个字段。若仅 group by category，所有用户的同类行为会被错误汇总，无法区分 u01 和 u02 各自的标签。
坑3：count(case when) 中 else 不可加 0 — count(case when action = 'view' then 1 end) 中未命中时返回 NULL，count 自动跳过 NULL，结果正确。若写成 count(case when action = 'view' then 1 else 0 end)，else 返回的 0 也会被 count 计入，导致所有行为计数相等 = 总行数，结果完全错误。注意与 sum(case when ... then 1 else 0 end) 的区别：sum 中 else 0 是必需的，count 中则绝不能加 else 0。
五、知识点总结
| 考点 | 说明 |
|---|---|
| count(case when) 条件计数 | 对原始行为日志按条件分类计数，未命中返回 NULL 被 count 自动跳过 |
| CASE WHEN 标签打标 | 根据多条件阈值生成层级标签，条件从高到低排列 |
| count vs sum 在条件聚合中的差异 | count 跳过 NULL 无需 else 0，sum 必须 else 0 否则结果为 NULL |
| concat 字符串拼接 | 将品类名称与标签等级拼接为可读标签，如"美妆深度用户" |
| group by 多字段分组 | 同时按 user_id + category 分组，确保每个用户每个品类独立统计 |
六、建表语句和数据插入
点击展开 DDL & DML
-- 建表语句
create table t5_user_behavior (
user_id string comment '用户id',
note_id string comment '笔记id',
category string comment '笔记品类',
action string comment '行为类型：view-浏览, like-点赞, save-收藏',
action_time string comment '操作时间'
) comment '用户行为表';
-- 插入数据（每行是一次独立行为，view可重复，like/save每篇笔记仅首次有效）
insert into t5_user_behavior(user_id, note_id, category, action, action_time) values
('u01','N001','美妆','view','2025-06-01 08:00'),
('u01','N001','美妆','view','2025-06-01 12:00'),
('u01','N001','美妆','view','2025-06-02 20:00'),
('u01','N002','美妆','view','2025-06-01 09:00'),
('u01','N001','美妆','like','2025-06-01 12:00'),
('u01','N002','美妆','like','2025-06-01 09:30'),
('u01','N002','美妆','save','2025-06-01 09:30'),
('u02','N001','美妆','view','2025-06-01 10:00'),
('u02','N001','美妆','view','2025-06-02 08:00'),
('u02','N001','美妆','view','2025-06-03 18:00'),
('u02','N001','美妆','like','2025-06-01 10:30'),
('u02','N002','美妆','like','2025-06-01 11:00'),
('u02','N001','美妆','save','2025-06-01 10:30'),
('u03','N003','穿搭','view','2025-06-01 07:00'),
('u03','N003','穿搭','view','2025-06-02 12:00'),
('u03','N003','穿搭','view','2025-06-03 22:00'),
('u03','N003','穿搭','like','2025-06-01 07:30');
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
