字节跳动大数据面试SQL-用户行为漏斗分析

数据仓库晨曦

发布于 2026-06-23 17:31:02

490

文章被收录于专栏：数据仓库技术数据仓库技术

一、题目背景

用户在抖音电商的典型路径是：浏览商品 → 加入购物车 → 提交订单 → 完成支付。每一步都会有用户流失，产品经理需要通过"漏斗分析"定位流失最严重的环节。

业务场景：如果浏览到加购的转化率很低（比如只有10%），说明商品详情页不够吸引人；如果支付转化率很低，可能是支付流程太复杂。漏斗分析就是"用数据定位问题"。

二、题目

现有四张用户行为表，分别记录了浏览、加购、下单、支付四个环节。请统计整体的漏斗转化率。

表结构：

t11_zj_view       (user_id, product_id, view_time)      -- 浏览
t11_zj_cart       (user_id, product_id, cart_time)       -- 加购
t11_zj_order      (user_id, product_id, order_time)      -- 下单
t11_zj_pay        (user_id, product_id, pay_time)        -- 支付

具体样例数据（时间通过函数生成，模拟真实用户行为链路）：

-- t11_zj_view 表数据
+----------+-------------+----------------------+
| user_id  | product_id  |      view_time       |
+----------+-------------+----------------------+
| 1        | A           | 2024-01-10 08:00:00  |
| 1        | B           | 2024-01-10 08:05:00  |
| 2        | A           | 2024-01-10 09:00:00  |
| 2        | C           | 2024-01-10 09:30:00  |
| 3        | A           | 2024-01-10 10:00:00  |
| 3        | B           | 2024-01-10 10:30:00  |
| 4        | A           | 2024-01-10 14:00:00  |
| 5        | A           | 2024-01-10 16:00:00  |
+----------+-------------+----------------------+
-- t11_zj_cart 表数据
+----------+-------------+----------------------+
| user_id  | product_id  |      cart_time       |
+----------+-------------+----------------------+
| 1        | A           | 2024-01-10 08:10:00  |
| 2        | A           | 2024-01-10 09:35:00  |
| 3        | A           | 2024-01-10 10:35:00  |
| 3        | B           | 2024-01-10 10:36:00  |
| 4        | A           | 2024-01-10 14:30:00  |
+----------+-------------+----------------------+
-- t11_zj_order
+----------+-------------+----------------------+
| user_id  | product_id  |      order_time      |
+----------+-------------+----------------------+
| 1        | A           | 2024-01-10 08:20:00  |
| 2        | A           | 2024-01-10 09:40:00  |
| 3        | A           | 2024-01-10 10:40:00  |
| 4        | A           | 2024-01-10 14:50:00  |
+----------+-------------+----------------------+
-- t11_zj_pay 表数据
+----------+-------------+----------------------+
| user_id  | product_id  |       pay_time       |
+----------+-------------+----------------------+
| 1        | A           | 2024-01-10 08:25:00  |
| 2        | A           | 2024-01-10 09:45:00  |
| 4        | A           | 2024-01-10 15:00:00  |
+----------+-------------+----------------------+

用户漏斗行为链路：

-- 用户1：完整漏斗（浏览A→加购A→下单A→支付A），另浏览了B但未加购
-- 用户2：完整漏斗（浏览A→加购A→下单A→支付A），另浏览了C但未加购
-- 用户3：浏览A、B并加购，完成下单A，但最终未支付（下单→支付流失）
-- 用户4：完整漏斗（浏览A→加购A→下单A→支付A）
-- 用户5：仅浏览A，未加购（浏览→加购流失）

期望输出：每个环节的独立用户数，以及相对于上一环节的转化率。

三、思路分析

漏斗分析的本质是"逐级 COUNT DISTINCT"：

每个环节 COUNT DISTINCT user_id
转化率 = 本环节人数 / 上一环节人数
用 LAG 窗口函数或直接子查询获取上一环节人数

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

四、逐步推导

步骤1：各环节独立去重统计

Spark SQL

SELECT '浏览' AS step, COUNT(DISTINCT user_id) AS users FROM t11_zj_view
UNION ALL
SELECT '加购', COUNT(DISTINCT user_id) FROM t11_zj_cart
UNION ALL
SELECT '下单', COUNT(DISTINCT user_id) FROM t11_zj_order
UNION ALL
SELECT '支付', COUNT(DISTINCT user_id) FROM t11_zj_pay;

结果：

+-------+--------+
| step  | users  |
+-------+--------+
| 浏览    | 5      |
| 加购    | 4      |
| 下单    | 4      |
| 支付    | 3      |
+-------+--------+
4 rows selected (11.165 seconds)(https://www.dwsql.com)

步骤2：计算环节间转化率

Spark SQL

WITH funnel AS (
    SELECT '浏览' AS step, 1 as step_order, COUNT(DISTINCT user_id) AS users FROM t11_zj_view
    UNION ALL
    SELECT '加购', 2 as step_order, COUNT(DISTINCT user_id) FROM t11_zj_cart
    UNION ALL
    SELECT '下单', 3 as step_order, COUNT(DISTINCT user_id) FROM t11_zj_order
    UNION ALL
    SELECT '支付', 4 as step_order, COUNT(DISTINCT user_id) FROM t11_zj_pay
)
SELECT
    step,
    step_order,
    users,
    lag(users) over (order by step_order) as prev_users,
    round(users * 100.0 / lag(users) over (order by step_order), 1) as conversion_pct
FROM funnel
ORDER BY step_order

最终结果：

+-------+-------------+--------+-------------+-----------------+
| step  | step_order  | users  | prev_users  | conversion_pct  |
+-------+-------------+--------+-------------+-----------------+
| 浏览    | 1           | 5      | NULL        | NULL            |
| 加购    | 2           | 4      | 5           | 80.0            |
| 下单    | 3           | 4      | 4           | 100.0           |
| 支付    | 4           | 3      | 4           | 75.0            |
+-------+-------------+--------+-------------+-----------------+
4 rows selected (1.636 seconds)(https://www.dwsql.com)

结果解读：

浏览→加购转化率 80%：用户5流失（浏览了A但未加购）
加购→下单转化率 100%：加购的4人全部下单
下单→支付转化率 75%：用户3流失（下单了A但未支付）
整体转化率（浏览→支付）：3/5 = 60%

五、常见坑点

坑1：漏斗中"整体转化率"和"环节转化率"是两回事

整体转化率 = 支付人数 / 浏览人数 = 3/5 = 60%。环节转化率是每一步的"截面"对比。面试时要区分清楚。

坑2：UNION ALL 的 step 顺序

UNION ALL 不保证顺序，LAG 依赖排序。添加step_order 来标识顺序，确保lag函数按顺序计算。

坑3：需要细粒度漏斗

上述是"用户级"漏斗（只看这个人有没有做过这个动作）。面试官可能追问：如果要看"商品级"漏斗（如"浏览了A → 加购了A → 下单了A"），需要 JOIN 商品维度来追踪同一商品的完整路径。

坑4：严格要求用户按照步骤计算转化率

样例数据相对简单，实际业务中用户可能通过其他渠道加购，在本渠道下单支付，导致中间过程缺失，不能算作转化过程，则需要更加复杂的分析。

六、举一反三

按天趋势：在 UNION ALL 的每个 SELECT 中加入 DATE(view_time)，可以看到每天的漏斗变化
多维对比：加 WHERE channel = '直播' 和 WHERE channel = '搜索'，对比不同渠道的漏斗
流失用户画像：找出"浏览了但没加购"的用户，关联用户属性表分析他们的特征（新用户？低活跃？）

七、知识点总结

考点	说明
COUNT DISTINCT	每个环节的去重用户数
UNION ALL	合并多环节统计结果
LAG 窗口函数	获取上一环节人数，计算环比转化率
转化率公式	本环节人数 / 上一环节人数 × 100%

八、建表语句和数据插入

-- ==========================================
-- 建表语句
-- ==========================================
CREATE TABLE IF NOT EXISTS t11_zj_view (
    user_id    INT,
    product_id STRING,
    view_time  STRING
);
CREATE TABLE IF NOT EXISTS t11_zj_cart (
    user_id    INT,
    product_id STRING,
    cart_time  STRING
);
CREATE TABLE IF NOT EXISTS t11_zj_order (
    user_id    INT,
    product_id STRING,
    order_time STRING
);
CREATE TABLE IF NOT EXISTS t11_zj_pay (
    user_id    INT,
    product_id STRING,
    pay_time   STRING
);

-- 浏览数据（用户1-5，8条记录）
INSERT INTO t11_zj_view VALUES
(1, 'A', '2024-01-10 08:00:00'),
(1, 'B', '2024-01-10 08:05:00'),
(2, 'A', '2024-01-10 09:00:00'),
(2, 'C', '2024-01-10 09:30:00'),
(3, 'A', '2024-01-10 10:00:00'),
(3, 'B', '2024-01-10 10:30:00'),
(4, 'A', '2024-01-10 14:00:00'),
(5, 'A', '2024-01-10 16:00:00');

-- 加购数据（用户1-4，5条记录，用户5流失）
INSERT INTO t11_zj_cart VALUES
(1, 'A', '2024-01-10 08:10:00'),
(2, 'A', '2024-01-10 09:35:00'),
(3, 'A', '2024-01-10 10:35:00'),
(3, 'B', '2024-01-10 10:36:00'),
(4, 'A', '2024-01-10 14:30:00');

-- 下单数据（用户1-4，4条记录）
INSERT INTO t11_zj_order VALUES
(1, 'A', '2024-01-10 08:20:00'),
(2, 'A', '2024-01-10 09:40:00'),
(3, 'A', '2024-01-10 10:40:00'),
(4, 'A', '2024-01-10 14:50:00');

-- 支付数据（用户1,2,4，3条记录，用户3流失于支付环节）
INSERT INTO t11_zj_pay VALUES
(1, 'A', '2024-01-10 08:25:00'),
(2, 'A', '2024-01-10 09:45:00'),
(4, 'A', '2024-01-10 15:00:00');

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2026-06-14，如有侵权请联系 cloudcommunity@tencent.com 删除

面试