阿里巴巴大数据面试SQL-购物车加购转化率

数据仓库晨曦

发布于 2026-07-13 20:30:20

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

一、题目背景

淘宝每天有数亿次加购行为，但从"加入购物车"到"真正下单付款"之间存在巨大的流失——超过50%的加购商品最终没有被购买。运营团队需要知道用户在加购后多久内最可能下单，以便在最佳时间窗口内推送优惠券或限时折扣提醒。

业务场景：淘宝APP的"购物车降价提醒"和"库存紧张提示"功能，背后就是加购转化分析。数据分析师计算出"加购后1小时内的下单转化率最高"后，产品经理设计在加购后30分钟推送一张限时优惠券——这就是数据驱动产品决策的典型场景。

二、题目描述

现有加购行为表 t13_cart_log 和下单表 t13_order_log。请统计加购商品在 24小时内、3天内、7天内 的下单转化率，以及各时间窗口的转化人数和占比。

加购行为表 t13_cart_log：

+----------+----------+----------+---------------------+
| user_id  | item_id  | cart_id  |      cart_time      |
+----------+----------+----------+---------------------+
| u01      | I1001    | C001     | 2023-03-01 10:00:00 |
| u01      | I1002    | C002     | 2023-03-01 10:30:00 |
| u02      | I1001    | C003     | 2023-03-01 11:00:00 |
| u02      | I1003    | C004     | 2023-03-01 12:00:00 |
| u03      | I1001    | C005     | 2023-03-02 09:00:00 |
| u03      | I1002    | C006     | 2023-03-02 10:00:00 |
| u01      | I1004    | C007     | 2023-03-03 08:00:00 |
| u04      | I1001    | C008     | 2023-03-03 15:00:00 |
+----------+----------+----------+---------------------+

下单表 t13_order_log：

+----------+----------+----------+---------------------+
| order_id | user_id  | item_id  |      order_time     |
+----------+----------+----------+---------------------+
| O001     | u01      | I1001    | 2023-03-01 10:15:00 |
| O002     | u02      | I1001    | 2023-03-01 13:00:00 |
| O003     | u01      | I1002    | 2023-03-03 14:00:00 |
| O004     | u03      | I1001    | 2023-03-04 09:00:00 |
| O005     | u04      | I1001    | 2023-03-04 10:00:00 |
+----------+----------+----------+---------------------+

注意：同一用户对同一商品可能多次加购，每次加购独立统计转化。下单的 user_id + item_id 与加购记录匹配。

三、思路分析

核心是加购表为主表 + LEFT JOIN下单表 + 时间窗口分类 + COUNT DISTINCT：

关联匹配：加购表 LEFT JOIN 下单表，关联条件为同一用户、同一商品，且下单时间在加购之后
时间差计算：unix_timestamp(order_time) - unix_timestamp(cart_time) 得到秒数，除以3600得小时数
窗口分类：CASE WHEN 分为 ≤24h / ≤3d(72h) / ≤7d(168h) / >7d(未转化)
转化率计算：各窗口的转化人数 / 总加购人数

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

四、逐步推导

步骤1：加购表 LEFT JOIN 下单表，计算时间差

select c.user_id, c.item_id, c.cart_time,
       o.order_time,
       (unix_timestamp(o.order_time) - unix_timestamp(c.cart_time)) / 3600 as diff_hours
from t13_cart_log c
left join t13_order_log o
    on c.user_id = o.user_id and c.item_id = o.item_id
   and o.order_time >= c.cart_time

执行结果

+----------+----------+----------------------+----------------------+-------------+
| user_id  | item_id  |      cart_time       |      order_time      | diff_hours  |
+----------+----------+----------------------+----------------------+-------------+
| u01      | I1001    | 2023-03-01 10:00:00  | 2023-03-01 10:15:00  | 0.25        |
| u01      | I1002    | 2023-03-01 10:30:00  | 2023-03-03 14:00:00  | 51.5        |
| u02      | I1001    | 2023-03-01 11:00:00  | 2023-03-01 13:00:00  | 2.0         |
| u02      | I1003    | 2023-03-01 12:00:00  | NULL                 | NULL        |
| u03      | I1001    | 2023-03-02 09:00:00  | 2023-03-04 09:00:00  | 48.0        |
| u03      | I1002    | 2023-03-02 10:00:00  | NULL                 | NULL        |
| u01      | I1004    | 2023-03-03 08:00:00  | NULL                 | NULL        |
| u04      | I1001    | 2023-03-03 15:00:00  | 2023-03-04 10:00:00  | 19.0        |
+----------+----------+----------------------+----------------------+-------------+
8 rows selected (0.492 seconds)(https://www.dwsql.com)

步骤2：按加购记录标记转化窗口

 with cart_gap as (
      select c.cart_id, c.user_id, c.item_id,
             min((unix_timestamp(o.order_time) - unix_timestamp(c.cart_time)) / 3600) as min_hours
      from t13_cart_log c
      leftjoin t13_order_log o
          on c.user_id = o.user_id
         and c.item_id = o.item_id
         and o.order_time >= c.cart_time
      groupby c.cart_id, c.user_id, c.item_id
  )
select cart_id, user_id, item_id,
         case
             when min_hours isnullthen'未转化'
             when min_hours <= 24then'24h内转化'
             when min_hours <= 72then'3天内转化'
             when min_hours <= 168then'7天内转化'
             else'未转化'
         endas conversion_window
from cart_gap;

执行结果

+----------+----------+----------+--------------------+
| cart_id  | user_id  | item_id  | conversion_window  |
+----------+----------+----------+--------------------+
| C002     | u01      | I1002    | 3天内转化              |
| C003     | u02      | I1001    | 24h内转化             |
| C001     | u01      | I1001    | 24h内转化             |
| C004     | u02      | I1003    | 未转化                |
| C008     | u04      | I1001    | 24h内转化             |
| C006     | u03      | I1002    | 未转化                |
| C007     | u01      | I1004    | 未转化                |
| C005     | u03      | I1001    | 3天内转化              |
+----------+----------+----------+--------------------+
8 rows selected (1.028 seconds)(https://www.dwsql.com)

用 MIN() 取最早一次下单时间——加购后可能多次购买同一商品，取最早那次衡量转化速度。

步骤3：统计各窗口转化率

执行SQL

with cart_gap as (
      -- 计算每个购物车的距离首次下单的小时数
      select c.cart_id,
             min((unix_timestamp(o.order_time) - unix_timestamp(c.cart_time)) / 3600) as min_hours
      from t13_cart_log c
      leftjoin t13_order_log o
          on c.user_id = o.user_id
         and c.item_id = o.item_id
         and o.order_time >= c.cart_time
      groupby c.cart_id
  ),
  tagged as (
      -- 打转化标签（min_hours 只算一次）
      select cart_id,
             case
                 when min_hours isnullthen'未转化'
                 when min_hours <= 24then'24h内转化'
                 when min_hours <= 72then'3天内转化'
                 when min_hours <= 168then'7天内转化'
                 else'未转化'
             endas conversion_window
      from cart_gap
  )
select conversion_window,
         count(distinct cart_id) as cart_cnt,
         round(count(distinct cart_id) * 100.0 / sum(count(distinct cart_id)) over (), 2) as pct
from tagged
groupby conversion_window
orderbycase conversion_window
               when'24h内转化'then1
               when'3天内转化'then2
               when'7天内转化'then3
               when'未转化'    then4
           end;

执行结果

+--------------------+-----------+--------+
| conversion_window  | cart_cnt  |  pct   |
+--------------------+-----------+--------+
| 24h内转化             | 3         | 37.50  |
| 3天内转化              | 2         | 25.00  |
| 未转化                | 3         | 37.50  |
+--------------------+-----------+--------+
3 rows selected (1.934 seconds)(https://www.dwsql.com)

五、常见坑点

坑1：一个加购可能对应多次下单

同一用户加购后可能在24h内买一次、7天内又买一次。用 MIN(order_time) 取最早下单时间来判断"首次转化在哪个窗口"，符合业务直觉（首单转化速度）。

坑2：下单时间 < 加购时间的情况

如果用户先下单再加购（即数据时序异常），order_time >= cart_time 条件会自然排除。但实际中可能存在"先买后加购"的合法场景（如退款重买），需要确认业务口径。

坑3：分母用加购人数 vs 加购次数

用 COUNT(DISTINCT user_id) 算的是"加购用户中有多少转化了"，用 COUNT(*) 算的是"加购事件中有多少转化了"。本题以加购事件为粒度更细，能反映每次加购的转化效率。

六、举一反三

按品类拆分转化率：关联商品品类表，GROUP BY 加 category_id，对比各品类的加购转化率差异
加购但未转化原因：对"未转化"的用户关联近期浏览/收藏行为，判断是"还在犹豫"还是"已放弃"
最优推送时机：按小时粒度统计"加购后第N小时转化"，画转化时间分布直方图，找到转化高峰窗口
加购商品降价提醒：关联商品价格表，监控加购后商品降价 >10% 的用户，推送降价通知推动转化

七、知识点总结

考点	说明
LEFT JOIN + 时间条件	加购表为主，关联后续下单记录
unix_timestamp 时间差	秒数差 ÷ 3600 = 小时数
CASE WHEN 时间窗口分类	≤24h / ≤72h / ≤168h 三级窗口
MIN 取最早转化	多次下单取最早一次衡量首单速度

八、建表语句和数据插入

CREATE TABLE t13_cart_log (
    user_id   stringCOMMENT'用户ID',
    item_id   stringCOMMENT'商品ID',
    cart_id   stringCOMMENT'加购记录ID',
    cart_time stringCOMMENT'加购时间'
) COMMENT'加购行为表';

CREATETABLE t13_order_log (
    order_id   stringCOMMENT'订单ID',
    user_id    stringCOMMENT'用户ID',
    item_id    stringCOMMENT'商品ID',
    order_time stringCOMMENT'下单时间'
) COMMENT'下单表';

INSERTINTO t13_cart_log VALUES
('u01', 'I1001', 'C001', '2023-03-01 10:00:00'),
('u01', 'I1002', 'C002', '2023-03-01 10:30:00'),
('u02', 'I1001', 'C003', '2023-03-01 11:00:00'),
('u02', 'I1003', 'C004', '2023-03-01 12:00:00'),
('u03', 'I1001', 'C005', '2023-03-02 09:00:00'),
('u03', 'I1002', 'C006', '2023-03-02 10:00:00'),
('u01', 'I1004', 'C007', '2023-03-03 08:00:00'),
('u04', 'I1001', 'C008', '2023-03-03 15:00:00');

INSERTINTO t13_order_log VALUES
('O001', 'u01', 'I1001', '2023-03-01 10:15:00'),
('O002', 'u02', 'I1001', '2023-03-01 13:00:00'),
('O003', 'u01', 'I1002', '2023-03-03 14:00:00'),
('O004', 'u03', 'I1001', '2023-03-04 09:00:00'),
('O005', 'u04', 'I1001', '2023-03-04 10:00:00');

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

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

阿里巴巴