播面

面试题：计算用户从注册到首单的平均耗时

1. 题目背景

在电商、O2O或内容付费平台中，“注册到首单转化时长”是衡量用户激活（Activation）效率的关键指标。快速促成首单意味着新用户留存率的提升。本题旨在考察候选人对时间差值计算、多表关联、聚合筛选（寻找首单）以及数理平均值计算的掌握程度。

2. 示例数据

表 1: 用户注册表 users

记录用户的基本信息及注册时间。

表 2: 订单表 orders

记录用户的历史下单订单及时间（包含一个用户多次下单的情况）。

(注：用户 104 注册后从未下单)

3. 期望输出结果

数据计算推导过程：

• 用户 101：注册 10:00:00，首单 15:30:00。耗时 = $5.50$ 小时。
• 用户 102：注册 12:00:00，首单 13:15:00。耗时 = $1.25$ 小时。
• 用户 103：注册 08:00:00，首单 10-04 08:00:00。耗时 = $48.00$ 小时。
• 用户 104：未下单，不纳入计算。
• 用户 105：注册 15:00:00，首单 15:10:00。耗时 = $0.17$ 小时（10分钟）。
• 平均耗时：$(5.50 + 1.25 + 48.00 + 0.17) / 4 = 54.92 / 4 \approx 13.73$ 小时。

4. SQL 解答方案

方案一：子查询分组定位首单（通用性强，推荐）

SELECT 
    ROUND(AVG(TIMESTAMPDIFF(SECOND, u.register_time, f.first_order_time) / 3600.0), 2) AS avg_hours_to_first_order
FROM users u
JOIN (
    -- 步骤 1：找出每个用户的首单时间
    SELECT 
        user_id, 
        MIN(order_time) AS first_order_time
    FROM orders
    GROUP BY user_id
) f ON u.user_id = f.user_id;

方案二：窗口函数定位首单（现代 SQL 推荐）

WITH ranked_orders AS (
    -- 步骤 1：对用户的订单按时间进行排序打标
    SELECT 
        user_id,
        order_time,
        ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY order_time) AS rn
    FROM orders
)
SELECT 
    ROUND(AVG(TIMESTAMPDIFF(SECOND, u.register_time, r.order_time) / 3600.0), 2) AS avg_hours_to_first_order
FROM users u
JOIN ranked_orders r ON u.user_id = r.user_id
WHERE r.rn = 1; -- 只取首单

5. 深度分析与面试应对指南

面对应试时，面试官往往不仅看你能不能写出 SQL，更看重你的逻辑严谨性与对边界情况的思考。以下是你可以主动向面试官提及的加分项：

① 为什么时间差计算要精确到秒（SECOND）而不是直接用小时（HOUR）？

• 避坑指南：很多候选人会直接使用 TIMESTAMPDIFF(HOUR, start, end)。在 MySQL 中，TIMESTAMPDIFF(HOUR) 会直接向下取整截断小数。
  • 例如：10 分钟差值（15:00:00 到 15:10:00），用 HOUR 计算得到的是 0。
  • 用 SECOND 计算再除以 3600.0，可以精确保留如 0.17 小时，确保平均值的精准度。

② 为什么选用 INNER JOIN 而不是 LEFT JOIN？

• 题目要求计算“从完成注册到在平台上下首个订单之间”的平均耗时。
• 对于注册了但从未下单的用户（如示例中的 104），由于无法获取首单时间，应当被排除在计算范围之外。
• INNER JOIN 会自动过滤掉订单表中没有记录（未下单）的用户。如果误用了 LEFT JOIN，会导致 first_order_time 为 NULL，虽不影响 AVG() 计算（AVG 会自动忽略 NULL），但会带来不必要的计算开销和逻辑混乱。

③ 窗口函数与 GROUP BY MIN() 的取舍

• GROUP BY + MIN()（方案一）：在只需要知道首单时间时，性能通常最好，因为可以通过索引快速定位。
• 窗口函数 ROW_NUMBER()（方案二）：如果题目升级，比如要求“首单且订单金额大于10”或者“计算第二单的耗时”，窗口函数扩展性极佳，只需微调 WHERE r.rn = 2 或在窗口函数内部加过滤条件即可。

④ 数据库方言适配（加分点）

如果面试官询问其他数据库（如 Hive/Spark/PostgreSQL）怎么写：

• MySQL: TIMESTAMPDIFF(SECOND, t1, t2) / 3600.0
• PostgreSQL: EXTRACT(EPOCH FROM (t2 - t1)) / 3600.0
• Hive: (unix_timestamp(t2) - unix_timestamp(t1)) / 3600.0