基于本文回答

播面 播面

文图音视，全方位拆解八股文
0
评论
请从左侧选择一个分类

计算每个用户首次下单实付金额，与第二次下单实付金额的差值，并统计全平台用户的平均差值

经典SQL面试题：用户首次与第二次下单金额差值分析

1. 题目背景

在电商、O2O等行业中，分析用户的留存与消费行为变化是非常常见的需求。通过计算用户「首次下单」与「第二次下单」的实付金额差值，可以帮助运营团队分析新客首单优惠（如新人大礼包）对后续消费的拉动效应，以及用户的消费升级/降级趋势。

2. 示例数据

订单表：t_order

order_id (订单ID) user_id (用户ID) order_time (下单时间) actual_amount (实付金额)
10001 101 2023-10-01 10:00:00 100.00
10002 102 2023-10-01 11:00:00 80.00
10003 101 2023-10-02 12:00:00 150.00
10004 103 2023-10-02 08:00:00 300.00
10005 101 2023-10-03 15:00:00 200.00
10006 102 2023-10-05 09:00:00 50.00
10007 104 2023-10-03 09:00:00 120.00
10008 104 2023-10-04 10:00:00 120.00

数据特点分析（面试官埋下的坑）：

  • 用户 101：有3笔订单（需准确提取第1笔 100.00 和第2笔 150.00，排除第3笔）。
  • 用户 102：有2笔订单（第1笔 80.00，第2笔 50.00）。
  • 用户 103：只有1笔订单（不满足有第二次下单的条件，应在计算中被剔除）。
  • 用户 104：有2笔订单且金额相同（差值为0）。

3. 面试题要求

  1. 子需求 1：计算每个有二次及以上购买行为的用户，其首次下单金额与第二次下单金额的差值（差值 = 首次金额 - 第二次金额）。
  2. 子需求 2：统计全平台满足条件用户的平均差值。

4. 解决方案 SQL

为了代码的可读性和性能，推荐使用 CTE（公用表表达式） 结合 窗口函数 和 行列转换（条件聚合）。

sql 
WITH ranked_orders AS (
    -- Step 1: 对每个用户的订单按时间进行排序，并标记序号
    SELECT 
        user_id,
        actual_amount,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_time ASC) AS rn
    FROM t_order
),
user_first_second_diff AS (
    -- Step 2: 筛选出每个用户的前两次订单，并利用条件聚合（CASE WHEN）计算差值
    SELECT 
        user_id,
        MAX(CASE WHEN rn = 1 THEN actual_amount END) AS first_amount,
        MAX(CASE WHEN rn = 2 THEN actual_amount END) AS second_amount,
        -- 差值：首单金额 - 次单金额
        MAX(CASE WHEN rn = 1 THEN actual_amount END) - MAX(CASE WHEN rn = 2 THEN actual_amount END) AS diff_amount
    FROM ranked_orders
    WHERE rn IN (1, 2)
    GROUP BY user_id
    -- 关键：限制必须同时拥有第一单和第二单的用户（过滤掉只有1单的用户）
    HAVING COUNT(rn) = 2 
)
-- Step 3: 同时输出每个用户的差值详情，以及全平台的平均差值（使用窗口函数在一张表中展现）
SELECT 
    user_id,
    first_amount,
    second_amount,
    diff_amount,
    -- 计算全平台满足条件用户的平均差值
    AVG(diff_amount) OVER() AS platform_avg_diff
FROM user_first_second_diff;

5. 预期输出结果

user_id first_amount second_amount diff_amount platform_avg_diff
101 100.00 150.00 -50.00 -6.67
102 80.00 50.00 30.00 -6.67
104 120.00 120.00 0.00 -6.67

计算解析：

  • 只有 101、102、104 符合条件（有2次及以上消费）。
  • 101 的差值 = 100 - 150 = -50
  • 102 的差值 = 80 - 50 = 30
  • 104 的差值 = 120 - 120 = 0
  • 平台平均差值 = (-50 + 30 + 0) / 3 = -20 / 3 ≈ -6.67

6. 核心考点与 SQL 分析（如何应对面试官提问）

考点一：如何准确定位“首次”与“第二次”？

  • 标准解法：使用 ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_time ASC)。
  • 避坑提示：不要使用 RANK() 或 DENSE_RANK()，除非面试官明确说明如果同一秒有两笔订单要并列第一。通常用 ROW_NUMBER() 可以保证序号的连续和唯一。

考点二：如何优雅地把“行”转成“列”做减法？

把第1行和第2行的数据拼到同一行做减法，通常有两种方法：

  1. 条件聚合（推荐）：使用 MAX(CASE WHEN rn = 1 THEN ...)。这种方式只需要对数据进行一次 GROUP BY，性能最好。
  2. 自连接（Self-Join）：让表自己和自己关联，ON a.user_id = b.user_id AND a.rn = 1 AND b.rn = 2。这种方式在大数据量下会产生大量的 Shuffle，性能较差，面试时写出条件聚合会更彰显功底。

考点三：如何过滤只有1单的用户？

  • 细节决定成败：用户 103 只有 1 单。如果直接用 rn IN (1, 2) 过滤后计算，103 的 second_amount 就会是 NULL，做减法后也是 NULL。
  • 处理方案：在 GROUP BY user_id 后面，加上 HAVING COUNT(rn) = 2（或者 HAVING COUNT(1) = 2）。这样可以直接在分组阶段就把只有一单的用户剔除，保证后续平均值计算的准确性。

考点四：如何同时呈现“个人差值”与“全平台平均值”？

  • 题目要求既要计算“每个用户...的差值”，又要“统计全平台用户的平均差值”。
  • 高级写法：在最终的 SELECT 中，使用窗口函数 AVG(diff_amount) OVER()。它可以在保留每一行详细数据的同时，自动计算整张表的平均值并附在最后一列。这比单独写两个 SQL 或者用 UNION 更显得专业。
00:00
00:00