跳到主要内容

SQL 限时秒杀活动转化率：秒杀页浏览→下单→支付（拼多多面试题）

一、题目背景​

这道题来自拼多多用户增长与活动运营部门的数据分析岗面试。拼多多限时秒杀是平台的核心流量场景之一——每天数百个秒杀场次覆盖全品类，从9.9元的日用百货到补贴价的正品大牌，单场UV可达数十万。衡量秒杀活动效果的核心指标是转化漏斗：从浏览秒杀页面（view）→ 点击商品（click）→ 提交订单（order）→ 完成支付（pay），每一环的用户流失都直接影响GMV和ROI。

在实际数据仓库中，浏览、点击、下单三类行为数据通常由不同的埋点SDK和业务系统产出，分别存储在独立的事件表中。分析师需要将三张表通过 left join 串联，统一到秒杀场次维度，计算各环节的去重用户数和环节间转化率。其中最大的挑战在于：left join 导致的重复计数——一个用户浏览了一个场次的多个商品，join 点击表后浏览UV会被放大。

业务场景：秒杀运营团队每场活动结束后1小时内需要复盘数据——哪个秒杀场次转化率最高？哪个环节流失最严重？如果是"浏览→点击"转化低，说明选品吸引力不足；如果是"下单→支付"转化低，可能是支付流程体验差或价格优势不明显。考察的是多表关联、条件去重聚合、以及比率计算中除零防御的能力。

二、题目​

拼多多限时秒杀活动中，需要计算每个秒杀场次的用户转化漏斗：浏览→点击→下单→支付各环节的人数及转化率，找出转化率最低的环节。

假设有三张表：

  • t6_flash_view：用户浏览秒杀会场次记录
  • t6_flash_click：用户点击商品记录
  • t6_flash_order：用户下单及支付记录
-- t6_flash_view 浏览表
+---------+----------+-------------------+
| user_id | flash_id | view_time         |
+---------+----------+-------------------+
| u01     | f001     | 2025-06-01 10:00  |
| u02     | f001     | 2025-06-01 10:05  |
| u03     | f001     | 2025-06-01 10:06  |
| u01     | f002     | 2025-06-01 12:00  |
| u02     | f002     | 2025-06-01 12:01  |
+---------+----------+-------------------+

-- t6_flash_click 点击表
+---------+----------+-------------------+
| user_id | flash_id | click_time        |
+---------+----------+-------------------+
| u01     | f001     | 2025-06-01 10:01  |
| u02     | f001     | 2025-06-01 10:06  |
| u03     | f001     | 2025-06-01 10:06  |
| u01     | f002     | 2025-06-01 12:01  |
+---------+----------+-------------------+

-- t6_flash_order 下单支付表
+---------+----------+--------+-------------------+
| user_id | flash_id | status | order_time        |
+---------+----------+--------+-------------------+
| u01     | f001     | pay    | 2025-06-01 10:03  |
| u03     | f001     | order  | 2025-06-01 10:07  |
+---------+----------+--------+-------------------+

三、思路分析​

  1. 经典漏斗分析：分别统计浏览、点击、下单（含已支付）、支付四个环节的去重用户数；
  2. 使用 left join 串联各环节数据，避免因某环节缺失导致漏斗断裂；
  3. 站在每个秒杀场次（flash_id）维度，计算相邻环节的转化率 = 后一环节人数 / 前一环节人数。
维度评分
题目难度⭐️⭐️⭐️
题目清晰度⭐️⭐️⭐️⭐️⭐️
业务常见度⭐️⭐️⭐️⭐️⭐️

四、逐步推导​

步骤1：统计每个场次各环节的去重用户数​

select v.flash_id,
       count(distinct v.user_id) as view_uv,
       count(distinct c.user_id) as click_uv,
       count(distinct o.user_id) as order_uv,
       count(distinct case when o.status = 'pay' then o.user_id end) as pay_uv
from t6_flash_view v
left join t6_flash_click c
  on v.flash_id = c.flash_id and v.user_id = c.user_id
left join t6_flash_order o
  on v.flash_id = o.flash_id and v.user_id = o.user_id
group by v.flash_id

查询结果

+----------+---------+----------+----------+--------+
| flash_id | view_uv | click_uv | order_uv | pay_uv |
+----------+---------+----------+----------+--------+
| f001     | 3       | 3        | 2        | 1      |
| f002     | 2       | 1        | 0        | 0      |
+----------+---------+----------+----------+--------+

步骤2：计算各环节转化率​

select flash_id,
       view_uv,
       click_uv,
       round(click_uv / nullif(view_uv, 0), 4) as view_to_click_rate,
       order_uv,
       round(order_uv / nullif(click_uv, 0), 4) as click_to_order_rate,
       pay_uv,
       round(pay_uv / nullif(order_uv, 0), 4) as order_to_pay_rate
from (
    select v.flash_id,
           count(distinct v.user_id) as view_uv,
           count(distinct c.user_id) as click_uv,
           count(distinct o.user_id) as order_uv,
           count(distinct case when o.status = 'pay' then o.user_id end) as pay_uv
    from t6_flash_view v
    left join t6_flash_click c
      on v.flash_id = c.flash_id and v.user_id = c.user_id
    left join t6_flash_order o
      on v.flash_id = o.flash_id and v.user_id = o.user_id
    group by v.flash_id
) t

查询结果

+----------+---------+----------+-------------------+----------+--------------------+--------+------------------+
| flash_id | view_uv | click_uv | view_to_click_rate | order_uv | click_to_order_rate | pay_uv | order_to_pay_rate |
+----------+---------+----------+-------------------+----------+--------------------+--------+------------------+
| f001     | 3       | 3        | 1.0000            | 2        | 0.6667             | 1      | 0.5000           |
| f002     | 2       | 1        | 0.5000            | 0        | 0.0000             | 0      | NULL             |
+----------+---------+----------+-------------------+----------+--------------------+--------+------------------+

五、常见坑点​

坑1：left join 串联导致行膨胀，影响性能和计数准确性

同一用户可能在同一个秒杀场次中多次浏览（多条 view 记录），left join 点击表和下单表时会对这些重复行分别匹配，导致中间结果集急剧膨胀。虽然 count(distinct) 能保证最终的去重计数正确，但在大数据量下性能极差。正确的做法是：先对各表按 (flash_id, user_id) 预聚合去重，再执行 left join，将中间结果的行数压缩到最小。

坑2：order_to_pay_rate 除零导致 NULL

当某个秒杀场次无人下单时（如示例中 f002 的 order_uv = 0），pay_uv / order_uv 即 0 / 0，在 Spark SQL 中直接计算会返回 NULL（而非报错）。虽不中断查询，但 NULL 在报表展示和后续分析中容易造成误解。应使用 nullif(order_uv, 0) 将分母 0 转为 NULL，使除零结果明确为 NULL，与其他异常情况区分开，也便于用 coalesce 统一填充默认值（如 0）。

坑3：三表 left join 的 count distinct 口径需严格对齐

三张表的 user_id 覆盖范围各不相同——有浏览但不一定会点击，有点击但不一定下单。left join 以 t6_flash_view 为驱动表，count(distinct v.user_id) 统计所有浏览用户，count(distinct c.user_id) 只统计有点击行为的用户（NULL 被自动跳过）。关键在于 join 条件必须同时包含 flash_id 和 user_id，如果漏掉 user_id 条件，仅 on v.flash_id = c.flash_id 会导致跨用户匹配的笛卡尔积错误，点击UV可能超过浏览UV，造成漏斗倒挂。

六、举一反三​

  1. 按小时拆分秒杀时间窗口分析实时转化：用 substr(view_time, 12, 2) 截取小时维度，在 group by 中加入 hour 字段，分析秒杀活动开启后各时段的转化率变化曲线。例如对比开场前10分钟 vs 尾声10分钟的点击→下单转化率，辅助运营判断最佳push时机和补货节奏。

  2. 按秒杀场次流量规模分层对比转化效率：在 t6_flash_view 上先计算每场次的浏览UV总量，用 case when 将场次分为高流量（UV > 1000）、中流量、低流量三档，再分别统计各档的平均转化率。验证是否流量越大转化越低（稀释效应），辅助流量分配和场次排期的策略优化。

  3. 关联商品维度定位高转化商品：在 t6_flash_order 上 join 商品信息表获取 product_id 和 category，将漏斗分析从"场次维度"扩展到"场次×商品维度"，找出哪些商品的下单转化率最高、哪些商品"点击多但下单少"（高跳失商品），为秒杀选品提供数据支撑。

七、知识点总结​

考点说明
left join 多表串联以浏览表为主表驱动，保留所有场次的所有浏览用户，left join点击和下单数据，避免漏斗断裂
count(distinct case when) 条件去重在去重统计用户数的同时用case when过滤特定状态（如status='pay'），精确计算支付人数
nullif(value, 0) 除零处理将分母0转为NULL，使除零结果返回NULL而非报错，防御性编程的标准写法
子查询分层计算内层聚合统计各环节UV，外层计算转化率，结构清晰，便于调试和维护
group by 聚合按秒杀场次维度汇总，将明细行为数据转化为场次级漏斗指标

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

点击展开 DDL & DML
create table t6_flash_view (
    user_id   string comment '用户ID',
    flash_id  string comment '秒杀场次ID',
    view_time string comment '浏览时间'
) comment '秒杀浏览记录表';

create table t6_flash_click (
    user_id    string comment '用户ID',
    flash_id   string comment '秒杀场次ID',
    click_time string comment '点击时间'
) comment '秒杀点击记录表';

create table t6_flash_order (
    user_id    string comment '用户ID',
    flash_id   string comment '秒杀场次ID',
    status     string comment '订单状态：order=已下单, pay=已支付',
    order_time string comment '下单/支付时间'
) comment '秒杀订单记录表';

insert into t6_flash_view(user_id, flash_id, view_time) values
('u01','f001','2025-06-01 10:00'),
('u02','f001','2025-06-01 10:05'),
('u03','f001','2025-06-01 10:06'),
('u01','f002','2025-06-01 12:00'),
('u02','f002','2025-06-01 12:01');

insert into t6_flash_click(user_id, flash_id, click_time) values
('u01','f001','2025-06-01 10:01'),
('u02','f001','2025-06-01 10:06'),
('u03','f001','2025-06-01 10:06'),
('u01','f002','2025-06-01 12:01');

insert into t6_flash_order(user_id, flash_id, status, order_time) values
('u01','f001','pay','2025-06-01 10:03'),
('u03','f001','order','2025-06-01 10:07');
📱关注公众号

「数据仓库技术」文章同步更新，不错过每一篇干货

微信公众号二维码
💬加群交流

备注「数据仓库技术」加入社群，每日一道大厂SQL真题

交流微信二维码

你可能还想看

相关题目
01. 累加刚好超过GDP40%的地市02. 求连续段的起止位置03. 连续段最后一个数及段长