跳到主要内容

得物大数据面试SQL-用户潮流偏好标签

⚠️ 待修正

一、题目背景​

这道题来自得物的数据分析岗面试。得物是潮流电商平台，数据分析师需要从海量业务数据中挖掘洞见。

业务场景：数据分析相关需求在日常工作中频繁出现，这类型的SQL题是面试高频考点。

一、题目​

得物需要为每个用户打上潮流偏好标签，用于首页个性化推荐。根据用户的历史浏览、收藏和购买行为，将用户划分为不同的偏好群体。给定 user_behavior 表。

user_behavior 用户行为表：

behavior_iduser_idproduct_categorybrandaction_typeaction_time
B001U401运动鞋Nikepurchase2025-01-05 10:00:00
B002U401运动鞋Air Jordanfavorite2025-01-06 14:30:00
B003U401运动鞋Adidasview2025-01-08 09:15:00
B004U402服装Supremepurchase2025-01-06 11:00:00
B005U402服装Off-Whitepurchase2025-01-10 16:20:00
B006U402服装Fear of Godview2025-01-12 10:30:00
B007U403配饰G-Shockpurchase2025-01-07 13:00:00
B008U403配饰Casiofavorite2025-01-09 15:45:00
B009U404运动鞋Nikeview2025-01-08 08:30:00
B010U404运动鞋Air Jordanpurchase2025-01-11 12:00:00
B011U404服装Supremeview2025-01-12 14:15:00
B012U405运动鞋Yeezypurchase2025-01-03 10:00:00
B013U405运动鞋Air Jordanpurchase2025-01-07 11:20:00
B014U405服装BAPEpurchase2025-01-09 09:00:00
B015U406配饰G-Shockview2025-01-04 16:00:00
B016U406配饰DWview2025-01-06 10:45:00
B017U407服装Supremefavorite2025-01-10 11:30:00
B018U407运动鞋Nikepurchase2025-01-13 14:00:00

要求：

  1. 统计每个用户在各品类的行为次数（分 action_type），并按用户总行为次数加权
  2. 为用户打上偏好标签：
    • 购买次数最多的品类为"核心偏好"
    • 浏览次数最多的品类为"兴趣偏好"
    • 如果某个品类既有购买又有收藏，标记为"重度偏好"
  3. 最终输出每个用户的偏好标签列表，如果多个标签取优先级最高的（重度偏好 > 核心偏好 > 兴趣偏好）

三、思路分析​

本题是用户画像打标的综合性题目，核心考察多维度聚合 + 条件判断 + 窗口排名的组合运用。难点在于标签优先级逻辑和"重度偏好"的判断——需要同时检查 purchase 和 favorite 两种行为类型。

维度评分
题目难度⭐⭐⭐
题目清晰度⭐⭐⭐
业务常见度⭐⭐⭐⭐⭐

解题思路：

  1. 第一步：按 (user_id, product_category) 聚合，分别统计 purchase 次数、favorite 次数、view 次数
  2. 第二步：使用 ROW_NUMBER() 分别对每个用户的购买次数和浏览次数排名，找出排名第一的品类
  3. 第三步：判断是否存在"重度偏好"：某个品类购买次数>0 且收藏次数>0
  4. 第四步：按优先级输出标签：重度偏好品类 > 核心偏好品类 > 兴趣偏好品类

四、逐步推导​

步骤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,
    COUNT(*) AS total_actions
FROM user_behavior
GROUP BY user_id, product_category
ORDER BY user_id, total_actions DESC;

执行结果（部分）：

user_idproduct_categorypurchase_cntfavorite_cntview_cnttotal_actions
U401运动鞋1113
U402服装2013
U403配饰1102
U404运动鞋1012
U404服装0011
U405运动鞋2002
U405服装1001
U406配饰0022
U407运动鞋1001
U407服装0101

步骤2：为用户打上偏好标签

WITH category_stats AS (
    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 user_behavior
    GROUP BY user_id, product_category
),
ranked AS (
    SELECT
        user_id,
        product_category,
        purchase_cnt,
        favorite_cnt,
        view_cnt,
        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,
        CASE WHEN purchase_cnt > 0 AND favorite_cnt > 0 THEN 1 ELSE 0 END AS is_heavy
    FROM category_stats
)
SELECT
    user_id,
    -- 标签优先级：重度偏好 > 核心偏好 > 兴趣偏好
    CASE
        WHEN MAX(CASE WHEN is_heavy = 1 THEN 1 ELSE 0 END) = 1
             THEN CONCAT('重度偏好：', MAX(CASE WHEN is_heavy = 1 THEN product_category END))
        WHEN SUM(CASE WHEN purchase_rank = 1 THEN purchase_cnt ELSE 0 END) > 0
             THEN CONCAT('核心偏好：', MAX(CASE WHEN purchase_rank = 1 THEN product_category END))
        ELSE CONCAT('兴趣偏好：', MAX(CASE WHEN view_rank = 1 THEN product_category END))
    END AS preference_tag,
    SUM(purchase_cnt) AS total_purchases,
    SUM(view_cnt) AS total_views
FROM ranked
GROUP BY user_id
ORDER BY user_id;

执行结果：

user_idpreference_tagtotal_purchasestotal_views
U401重度偏好：运动鞋11
U402核心偏好：服装21
U403重度偏好：配饰10
U404核心偏好：运动鞋12
U405核心偏好：运动鞋30
U406兴趣偏好：配饰02
U407核心偏好：运动鞋10

五、常见坑点​

坑1：NULL值的隐式处理 — 聚合函数跳过NULL，窗口函数不跳过。WHERE需显式过滤或用COALESCE替代。

坑2：数据类型不一致导致JOIN失效 — string vs int隐式转换可能触发全表扫描，性能骤降。

六、举一反三​

  1. 增加时间维度对比：按天/周/月分组，观察指标的趋势和季节性波动

  2. 增加分组维度：按品类/地区/渠道拆解，发现差异化的业务洞察

  3. 设置预警阈值：在WHERE中加阈值判断，自动筛选异常数据

七、知识点总结​

考点说明
ROW_NUMBER + 差值法连续问题经典解法：日期-行号=分组标识，相同差值同一连续段
SUM() OVER 累积求和窗口聚合实现滚动累计，配合ORDER BY实现时间轴展开
CASE WHEN 条件聚合灵活实现分段统计、条件计数、标签化处理
GROUP BY + 聚合函数分组聚合是数据分析的基础，配合HAVING筛选分组结果

八、建表语句和数据插入​

点击展开 DDL & DML
CREATE TABLE user_behavior (
    behavior_id VARCHAR(10) PRIMARY KEY,
    user_id VARCHAR(10),
    product_category VARCHAR(20),
    brand VARCHAR(30),
    action_type VARCHAR(20),
    action_time DATETIME
);

INSERT INTO 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真题

交流微信二维码

你可能还想看

相关题目
01. 商品真伪鉴定通过率02. 限量款抽签中签率03. 潮牌二级市场价格波动