面试真题：2025年度首尾购商品检索

1. 题目背景与要求

在电商数据分析中，分析用户在特定年度的“首次行为”和“末次行为”是常见的画像分析需求。
请编写一个 SQL 查询，找出在 2025 年有购买行为的每个用户，在该年度购买的第一件商品和最后一件商品的名称及购买时间。

2. 示例数据

表 1: products (商品信息表)

表 2: orders (订单明细表)

3. 期望输出结果

注：用户 1004 在 2025 年没有购买行为，故排除；用户 1003 在 2024 年底的订单被过滤，仅计算其 2025 年内的首尾订单。

4. SQL 解答

WITH ranked_orders AS (
    SELECT
        o.user_id,
        p.product_name,
        o.purchase_time,
        ROW_NUMBER() OVER(
            PARTITION BY o.user_id 
            ORDER BY o.purchase_time ASC
        ) AS rn_asc,
        ROW_NUMBER() OVER(
            PARTITION BY o.user_id 
            ORDER BY o.purchase_time DESC
        ) AS rn_desc
    FROM orders o
    JOIN products p ON o.product_id = p.product_id
    WHERE o.purchase_time >= '2025-01-01 00:00:00'
      AND o.purchase_time < '2026-01-01 00:00:00'
)
SELECT
    user_id,
    MAX(CASE WHEN rn_asc = 1 THEN product_name END) AS first_product_name,
    MAX(CASE WHEN rn_asc = 1 THEN purchase_time END) AS first_purchase_time,
    MAX(CASE WHEN rn_desc = 1 THEN product_name END) AS last_product_name,
    MAX(CASE WHEN rn_desc = 1 THEN purchase_time END) AS last_purchase_time
FROM ranked_orders
WHERE rn_asc = 1 OR rn_desc = 1
GROUP BY user_id;

5. 核心考点与 SQL 分析（面试通关秘籍）

考点一：限定时间范围的过滤（Sargable 写入规范）

• 解析：面试官非常看重对时间字段过滤的写法。避免使用 YEAR(purchase_time) = 2025，因为在函数中包裹索引列会导致索引失效（无法进行 Index Range Scan）。
• 标准写法：使用范围查询 purchase_time >= '2025-01-01 00:00:00' AND purchase_time < '2026-01-01 00:00:00'，这能让数据库高效利用 purchase_time 上的索引。

考点二：窗口函数 ROW_NUMBER() 的双向排序

• 解析：要同时获取“第一件”和“最后一件”，最巧妙的方法是在同一个 CTE（公用表表达式）中，利用 ROW_NUMBER() 进行双向排序：
  • rn_asc: 按时间升序排，rn_asc = 1 即为首单。
  • rn_desc: 按时间降序排，rn_desc = 1 即为尾单。
• 避坑：不要使用 RANK() 或 DENSE_RANK()，除非业务明确要求“若同一秒购买多件，则并列第一”。通常使用 ROW_NUMBER() 可以确保即使时间戳完全相同，也只会返回一条记录。

考点三：行转列（Conditional Aggregation）与行聚合过滤

• 解析：使用 WHERE rn_asc = 1 OR rn_desc = 1 过滤出每个用户只有首尾两笔（或一笔）的订单数据。之后，通过 GROUP BY user_id 配合 MAX(CASE WHEN ...) 实现行转列。
• 边界情况处理：对于在 2025 年只购买过一次的用户（如 1002），该条记录的 rn_asc 和 rn_desc 都会是 1。
  • 在聚合函数 MAX(CASE WHEN...) 中，该用户的首尾商品和时间将会展示为完全相同的内容，完全符合业务逻辑。