SQL 用户潮流偏好标签：购买+浏览+收藏多信号打标（得物面试题）
一、题目
得物需要为每个用户打上潮流偏好标签，用于首页个性化推荐。给定用户行为表 t4_user_behavior，记录了用户的浏览、收藏和购买行为，请根据以下规则为每个用户输出唯一的偏好标签：
+----------+--------------------------------------------------------------+------+
| 标签类型 | 判断规则 | 优先级 |
+----------+--------------------------------------------------------------+------+
| 重度偏好 | 某品类**同时**有购买行为（purchase）和收藏行为（favorite） | 最高 |
| 核心偏好 | 购买次数最多的品类（不含重度偏好已命中的品类） | 中 |
| 兴趣偏好 | 浏览次数最多的品类（不含以上已命中的品类） | 最低 |
+----------+--------------------------------------------------------------+------+
输出字段：user_id、preference_tag（格式如"重度偏好：运动鞋"）、total_purchases（该用户总购买次数）、total_views（该用户总浏览次数）。
t4_user_behavior 用户行为表：
+-------------+---------+-----------------+------------+------------+---------------------+
| behavior_id | user_id | product_category | brand | action_type | action_time |
+-------------+---------+-----------------+------------+------------+---------------------+
| B001 | U401 | 运动鞋 | Nike | purchase | 2025-01-05 10:00:00 |
| B002 | U401 | 运动鞋 | Air Jordan | favorite | 2025-01-06 14:30:00 |
| B003 | U401 | 运动鞋 | Adidas | view | 2025-01-08 09:15:00 |
| B004 | U402 | 服装 | Supreme | purchase | 2025-01-06 11:00:00 |
| B005 | U402 | 服装 | Off-White | purchase | 2025-01-10 16:20:00 |
| B006 | U402 | 服装 | Fear of God | view | 2025-01-12 10:30:00 |
| B007 | U403 | 配饰 | G-Shock | purchase | 2025-01-07 13:00:00 |
| B008 | U403 | 配饰 | Casio | favorite | 2025-01-09 15:45:00 |
| B009 | U404 | 运动鞋 | Nike | view | 2025-01-08 08:30:00 |
| B010 | U404 | 运动鞋 | Air Jordan | purchase | 2025-01-11 12:00:00 |
| B011 | U404 | 服装 | Supreme | view | 2025-01-12 14:15:00 |
| B012 | U405 | 运动鞋 | Yeezy | purchase | 2025-01-03 10:00:00 |
| B013 | U405 | 运动鞋 | Air Jordan | purchase | 2025-01-07 11:20:00 |
| B014 | U405 | 服装 | BAPE | purchase | 2025-01-09 09:00:00 |
| B015 | U406 | 配饰 | G-Shock | view | 2025-01-04 16:00:00 |
| B016 | U406 | 配饰 | DW | view | 2025-01-06 10:45:00 |
| B017 | U407 | 服装 | Supreme | favorite | 2025-01-10 11:30:00 |
| B018 | U407 | 运动鞋 | Nike | purchase | 2025-01-13 14:00:00 |
+-------------+---------+-----------------+------------+------------+---------------------+
二、思路分析
本题是用户画像打标的综合题，核心考察多维度聚合 + 条件判断 + 窗口排名的组合运用。难点在于标签优先级逻辑和"重度偏好"的判断——需要确认购买和收藏发生在同一品类，而非单纯看用户是否有过两种行为。
+----------+------+
| 维度 | 评分 |
+----------+------+
| 题目难度 | ⭐⭐⭐ |
| 题目清晰度 | ⭐⭐⭐ |
| 业务常见度 | ⭐⭐⭐⭐⭐ |
+----------+------+
- 第一步：按
(user_id, product_category)聚合，分别统计 purchase、favorite、view 三种行为的次数 - 第二步：用
ROW_NUMBER()对每个用户的购买次数和浏览次数分别排名，标记每个品类是否为"重度偏好"（purchase_cnt > 0 AND favorite_cnt > 0） - 第三步：按优先级（重度 > 核心 > 兴趣）为每个用户筛选唯一标签
三、逐步推导
1.按用户和品类聚合行为次数
统计每个用户在各品类的购买、收藏、浏览次数。
执行SQL
-- 按用户+品类聚合，使用 sum(case when) 将行为类型转为独立计数列
select
user_id,
product_category,
sum(case when action_type = 'purchase' then 1 else 0 end) as purchase_cnt,
sum(case when action_type = 'favorite' then 1 else 0 end) as favorite_cnt,
sum(case when action_type = 'view' then 1 else 0 end) as view_cnt
from t4_user_behavior
group by user_id, product_category
order by user_id, purchase_cnt desc;
执行结果
+----------+-------------------+---------------+---------------+-----------+
| user_id | product_category | purchase_cnt | favorite_cnt | view_cnt |
+----------+-------------------+---------------+---------------+-----------+
| U401 | 运动鞋 | 1 | 1 | 1 |
| U402 | 服装 | 2 | 0 | 1 |
| U403 | 配饰 | 1 | 1 | 0 |
| U404 | 运动鞋 | 1 | 0 | 1 |
| U404 | 服装 | 0 | 0 | 1 |
| U405 | 运动鞋 | 2 | 0 | 0 |
| U405 | 服装 | 1 | 0 | 0 |
| U406 | 配饰 | 0 | 0 | 2 |
| U407 | 运动鞋 | 1 | 0 | 0 |
| U407 | 服装 | 0 | 1 | 0 |
+----------+-------------------+---------------+---------------+-----------+
10 rows selected (0.422 seconds)(https://www.dwsql.com)
2.排名 + 判断重度偏好
在步骤1聚合结果上，为每个品类标记是否"重度偏好"（购买+收藏都有），并对购买/浏览次数排名。
执行SQL
-- 排名 + 判断是否重度偏好（同一品类既有购买又有收藏）
select
user_id,
product_category,
purchase_cnt,
favorite_cnt,
view_cnt,
case when purchase_cnt > 0 and favorite_cnt > 0 then 1 else 0 end as is_heavy,
row_number() over (partition by user_id order by purchase_cnt desc) as purchase_rank,
row_number() over (partition by user_id order by view_cnt desc) as view_rank
from (
-- 复用步骤1的聚合结果
select
user_id,
product_category,
sum(case when action_type = 'purchase' then 1 else 0 end) as purchase_cnt,
sum(case when action_type = 'favorite' then 1 else 0 end) as favorite_cnt,
sum(case when action_type = 'view' then 1 else 0 end) as view_cnt
from t4_user_behavior
group by user_id, product_category
) category_stats
执行结果
+----------+-------------------+---------------+---------------+-----------+-----------+----------------+------------+
| user_id | product_category | purchase_cnt | favorite_cnt | view_cnt | is_heavy | purchase_rank | view_rank |
+----------+-------------------+---------------+---------------+-----------+-----------+----------------+------------+
| U401 | 运动鞋 | 1 | 1 | 1 | 1 | 1 | 1 |
| U402 | 服装 | 2 | 0 | 1 | 0 | 1 | 1 |
| U403 | 配饰 | 1 | 1 | 0 | 1 | 1 | 1 |
| U404 | 运动鞋 | 1 | 0 | 1 | 0 | 1 | 1 |
| U404 | 服装 | 0 | 0 | 1 | 0 | 2 | 2 |
| U405 | 运动鞋 | 2 | 0 | 0 | 0 | 1 | 1 |
| U405 | 服装 | 1 | 0 | 0 | 0 | 2 | 2 |
| U406 | 配饰 | 0 | 0 | 2 | 0 | 1 | 1 |
| U407 | 运动鞋 | 1 | 0 | 0 | 0 | 1 | 1 |
| U407 | 服装 | 0 | 1 | 0 | 0 | 2 | 2 |
+----------+-------------------+---------------+---------------+-----------+-----------+----------------+------------+
10 rows selected (0.456 seconds)(https://www.dwsql.com)
U401的运动鞋 purchase+favorite 都有 → is_heavy=1。U404 有两个品类，运动鞋 purchase_rank=1，服装 view_rank=1（并列），后续按优先级选取。
3.打标签 + 按优先级去重
只保留每个用户各维度的第一名品类（is_heavy=1 or rank=1），拼接标签名称，再按优先级去重——每个用户最终只输出一条最高优先级标签。
执行SQL
with ranked as (
-- 复用步骤2的排名结果
select
user_id,
product_category,
purchase_cnt,
favorite_cnt,
view_cnt,
case when purchase_cnt > 0 and favorite_cnt > 0 then 1 else 0 end as is_heavy,
row_number() over (partition by user_id order by purchase_cnt desc) as purchase_rank,
row_number() over (partition by user_id order by view_cnt desc) as view_rank
from (
select
user_id,
product_category,
sum(case when action_type = 'purchase' then 1 else 0 end) as purchase_cnt,
sum(case when action_type = 'favorite' then 1 else 0 end) as favorite_cnt,
sum(case when action_type = 'view' then 1 else 0 end) as view_cnt
from t4_user_behavior
group by user_id, product_category
) category_stats
)
select user_id, preference_tag, total_purchases, total_views
from (
-- 按优先级排序，rn=1 即最高优先级
select user_id, preference_tag, total_purchases, total_views,
is_heavy, purchase_rank, view_rank,
row_number() over (
partition by user_id
order by case when is_heavy = 1 then 1
when purchase_rank = 1 then 2
else 3 end
) as rn
from (
-- 筛选有排名的品类 + 拼接标签 + 统计用户总次数
select
user_id,
is_heavy,
purchase_rank,
view_rank,
concat(
case
when is_heavy = 1 then '重度偏好'
when purchase_rank = 1 then '核心偏好'
when view_rank = 1 then '兴趣偏好'
end,
'：',
product_category
) as preference_tag,
sum(purchase_cnt) over (partition by user_id) as total_purchases,
sum(view_cnt) over (partition by user_id) as total_views
from ranked
where is_heavy = 1 or purchase_rank = 1 or view_rank = 1
) t1
) t2
where rn = 1
order by user_id;
执行结果
+----------+-----------------+------------------+--------------+
| user_id | preference_tag | total_purchases | total_views |
+----------+-----------------+------------------+--------------+
| U401 | 重度偏好：运动鞋 | 1 | 1 |
| U402 | 核心偏好：服装 | 2 | 1 |
| U403 | 重度偏好：配饰 | 1 | 0 |
| U404 | 核心偏好：运动鞋 | 1 | 1 |
| U405 | 核心偏好：运动鞋 | 2 | 0 |
| U406 | 核心偏好：配饰 | 0 | 2 |
| U407 | 核心偏好：运动鞋 | 1 | 0 |
+----------+-----------------+------------------+--------------+
7 rows selected (1.558 seconds)(https://www.dwsql.com)
四、常见坑点
坑1：ROW_NUMBER 同排名处理 — 当购买次数相同时（如多个品类 purchase_cnt = 0），ROW_NUMBER() 会任意取一行作为 rank=1。可增加二级排序字段（如 product_category）确保结果稳定，或在业务上明确定义同分时的取舍规则。
坑2：重度偏好必须同一品类 — 判断"重度偏好"的条件是 purchase_cnt > 0 AND favorite_cnt > 0，这两个条件必须在同一个 product_category 内同时成立。不能跨品类判断，否则会把"在A品类购买、在B品类收藏"的用户误标为重度偏好。
**坑3：sum(case when) 中 else 0 不可省略** — 如果 sum(case when action_type = 'purchase' then 1 end)不写else 0，当某品类缺少 purchase 记录时，该行的 purchase_cnt会是 NULL 而非 0。NULL 参与ROW_NUMBER() ... ORDER BY排序会导致该行被排到最后，与预期（0次应参与正常排名）不符。必须显式写else 0`。
五、知识点总结
+---------------------------+--------------------------------------------------------------+
| 考点 | 说明 |
+---------------------------+--------------------------------------------------------------+
| `sum(case when)` 条件聚合 | 按行条件计数，`else 0` 必须显式写以避免 NULL 参与排序 |
| `ROW_NUMBER()` 窗口排名 | 分组排序取 Top 品类，注意同分时的行选择 |
| `QUALIFY` 窗口过滤 | 在窗口函数结果上直接过滤，避免再套一层子查询 |
| 优先级标签逻辑 | 用 `case when` 链实现多级优先级判断，配合 `order by` 控制筛选顺序 |
+---------------------------+--------------------------------------------------------------+
六、建表语句和数据插入
点击展开 DDL & DML
create table t4_user_behavior (
behavior_id string,
user_id string,
product_category string,
brand string,
action_type string,
action_time string
);
insert into t4_user_behavior values
('B001', 'U401', '运动鞋', 'Nike', 'purchase', '2025-01-05 10:00:00'),
('B002', 'U401', '运动鞋', 'Air Jordan', 'favorite', '2025-01-06 14:30:00'),
('B003', 'U401', '运动鞋', 'Adidas', 'view', '2025-01-08 09:15:00'),
('B004', 'U402', '服装', 'Supreme', 'purchase', '2025-01-06 11:00:00'),
('B005', 'U402', '服装', 'Off-White', 'purchase', '2025-01-10 16:20:00'),
('B006', 'U402', '服装', 'Fear of God', 'view', '2025-01-12 10:30:00'),
('B007', 'U403', '配饰', 'G-Shock', 'purchase', '2025-01-07 13:00:00'),
('B008', 'U403', '配饰', 'Casio', 'favorite', '2025-01-09 15:45:00'),
('B009', 'U404', '运动鞋', 'Nike', 'view', '2025-01-08 08:30:00'),
('B010', 'U404', '运动鞋', 'Air Jordan', 'purchase', '2025-01-11 12:00:00'),
('B011', 'U404', '服装', 'Supreme', 'view', '2025-01-12 14:15:00'),
('B012', 'U405', '运动鞋', 'Yeezy', 'purchase', '2025-01-03 10:00:00'),
('B013', 'U405', '运动鞋', 'Air Jordan', 'purchase', '2025-01-07 11:20:00'),
('B014', 'U405', '服装', 'BAPE', 'purchase', '2025-01-09 09:00:00'),
('B015', 'U406', '配饰', 'G-Shock', 'view', '2025-01-04 16:00:00'),
('B016', 'U406', '配饰', 'DW', 'view', '2025-01-06 10:45:00'),
('B017', 'U407', '服装', 'Supreme', 'favorite', '2025-01-10 11:30:00'),
('B018', 'U407', '运动鞋', 'Nike', 'purchase', '2025-01-13 14:00:00');
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
