基于本文回答

播面

0
评论

找出过去 30 天内首次上架的新商品，统计每个新品在上架后 14 天内的累计销量、累计浏览量和加购转化率

面试真题：新商品上线 14 天首发表现分析

1. 模拟数据集

为了验证 SQL 的正确性，以下设计了两个核心业务表的模拟数据。假设当前分析日期（计算基准日）为 2023-10-25。

表 1：商品信息表 (product_info)

记录商品的 ID、名称以及首次上架日期。

product_id product_name launch_date
P001 智能运动手表 2023-10-01
P002 经典蓝牙耳机 2023-09-10
P003 无线降噪耳罩 2023-10-15
P004 极简保温杯 2023-10-24
表 2：用户行为日志表 (user_behavior)

记录用户对商品的操作行为，包含：浏览 (pv)、加购 (cart)、购买 (buy)。

user_id product_id action_type action_date quantity
U101 P001 pv 2023-10-02 NULL
U102 P001 cart 2023-10-03 NULL
U103 P001 buy 2023-10-05 2
U104 P001 pv 2023-10-15 NULL
U101 P001 buy 2023-10-16 5
U105 P002 pv 2023-10-10 NULL
U106 P003 pv 2023-10-16 NULL
U107 P003 cart 2023-10-17 NULL
U108 P003 pv 2023-10-18 NULL
U109 P003 buy 2023-10-20 1
U110 P004 pv 2023-10-25 NULL

2. SparkSQL 核心解法

sql 
WITH target_products AS (
    -- 1. 筛选出过去 30 天内首次上架的新商品
    SELECT 
        product_id,
        product_name,
        CAST(launch_date AS DATE) AS launch_date
    FROM 
        product_info
    WHERE 
        CAST(launch_date AS DATE) >= DATE_SUB(CAST('2023-10-25' AS DATE), 30)
        AND CAST(launch_date AS DATE) <= CAST('2023-10-25' AS DATE)
),
behavior_stats AS (
    -- 2. 关联行为表，并严格限制在上架后 14 天内（含上架当天）
    -- 注意：使用 LEFT JOIN 确保没有行为的新品也能保留，避免数据丢失
    SELECT 
        tp.product_id,
        tp.product_name,
        b.action_type,
        b.quantity
    FROM 
        target_products tp
    LEFT JOIN 
        user_behavior b 
    ON 
        tp.product_id = b.product_id
        AND CAST(b.action_date AS DATE) >= tp.launch_date 
        AND CAST(b.action_date AS DATE) <= DATE_ADD(tp.launch_date, 13)
)
-- 3. 分组聚合统计各项指标
SELECT 
    product_id,
    product_name,
    -- 累计销量：仅统计 buy 行为的 quantity 累加
    COALESCE(SUM(CASE WHEN action_type = 'buy' THEN quantity ELSE 0 END), 0) AS total_sales,
    -- 累计浏览量：统计 pv 行为发生的次数
    COALESCE(SUM(CASE WHEN action_type = 'pv' THEN 1 ELSE 0 END), 0) AS total_pv,
    -- 加购转化率 = 累计加购次数 / 累计浏览量
    -- 使用 NULLIF 处理分母为 0 的情况，避免报 ZeroDivisionError
    ROUND(
        COALESCE(SUM(CASE WHEN action_type = 'cart' THEN 1 ELSE 0 END), 0) * 1.0 / 
        NULLIF(COALESCE(SUM(CASE WHEN action_type = 'pv' THEN 1 ELSE 0 END), 0), 0), 
        4
    ) AS cart_conversion_rate
FROM 
    behavior_stats
GROUP BY 
    product_id,
    product_name
ORDER BY 
    product_id;

3. 预期输出结果

product_id product_name total_sales total_pv cart_conversion_rate
P001 智能运动手表 2 1 1.0000
P003 无线降噪耳罩 1 2 0.5000
P004 极简保温杯 0 1 0.0000

注：P002 因上架日期为 2023-09-10（早于 30 天限制）被过滤；P001 在 10-15 和 10-16 的行为因超过了 14 天窗口期（10-01 至 10-14）未被计入。

4. 面试深度剖析与考点分析

在 SparkSQL 开发或大厂面试中，此题是高频出现的“漏斗分析/生命周期”典型场景。面试官主要考察以下几个维度：

考点一：时间窗口限制与“左连接（LEFT JOIN）陷阱”
  • 面试官常挖的坑：很多候选人会把 b.action_date <= DATE_ADD(tp.launch_date, 13) 放在 WHERE 子句中。
  • 避坑指南：一旦放在 WHERE 中，LEFT JOIN 就会退化为 INNER JOIN。如果某个新品在 14 天内没有任何用户行为（如刚上架的 P004），该商品就会在结果中消失。必须将行为时间过滤条件写在 ON 子句中，确保主表商品完整性。
考点二：经典的安全除法（防止分母为 0）
  • 技术细节：分布式计算中，若分母（PV）为 0，直接相除会导致计算结果为 NULL、NaN 或报错。
  • 标准解法：使用 NULLIF(total_pv, 0)。当 PV 为 0 时，NULLIF 返回 NULL，任何数除以 NULL 在 SQL 中都会优雅地返回 NULL，再配合 COALESCE 或 CASE WHEN 包装成 0 即可。
考点三：Spark 性能优化（针对大宽表关联）

如果在面试中被追问：“如果 user_behavior 达到百亿级，而 product_info 只有十万级，如何优化这个 Spark 作业？”

  1. Map端 Join (Broadcast Hash Join)：
    由于 30 天内的新品数据量极小（target_products 过滤后可能只有几千条），可以显式使用广播提示，避免 Shuffle：
    sql 
    SELECT /*+ BROADCAST(tp) */ ... 
FROM target_products tp LEFT JOIN user_behavior b ON ...
  2. 避免全局过滤，利用分区剪裁 (Partition Pruning)：
    user_behavior 表在数仓中通常按天分区（如 dt）。在向行为表关联时，应先计算出行为发生的全局时间上下界，提前过滤行为表分区，防止全表扫描：
    sql 
    -- 先计算行为日期的最小和最大可能范围，用于限制分区过滤
WHERE b.dt >= '2023-09-25' AND b.dt <= '2023-10-25'
右滑查看面试常问