京东Hive SQL面试题实战:APP路径分析场景解析与幽默生存指南
“数据开发工程师的终极浪漫,就是把用户路径写成诗——用Hive SQL押韵。” —— 某不愿透露姓名的SQL诗人
一、题目背景:来自京东的真实需求
假设你是京东APP的数据工程师,现在需要分析用户在APP中的访问路径特征。原始日志表user_behavior
结构如下:
字段名 | 类型 | 说明 |
---|---|---|
user_id | BIGINT | 用户ID(脱敏) |
session_id | STRING | 会话标识 |
page_path | STRING | 访问页面路径 |
action_time | TIMESTAMP | 行为时间戳 |
业务需求:
- 计算每个会话的完整访问路径(按时间顺序排列)
- 统计TOP10高频访问路径模式(如首页→商品详情→购物车→支付)
- 分析关键转化路径的流失节点(如加入购物车后未支付的用户比例)
二、解题思路:窗口函数与字符串魔术
1. 路径序列构建
WITH path_sequence AS (
SELECT
user_id,
session_id,
COLLECT_LIST(page_path) OVER (
PARTITION BY session_id
ORDER BY action_time
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS full_path
FROM user_behavior
GROUP BY session_id, user_id, action_time, page_path
)
SELECT
session_id,
CONCAT_WS('→', full_path) AS journey_path
FROM path_sequence
GROUP BY session_id;
技术要点:
COLLECT_LIST
搭配窗口函数实现有序聚合CONCAT_WS
将数组转为可读路径字符串- 去重技巧:
GROUP