播面

面试题：商品降价对销量的弹性影响分析

1. 题目背景

在电商数据分析中，价格弹性（Price Elasticity）是衡量价格变动对需求量影响的重要指标。为了评估某次降价促销的效果，我们需要对比商品降价前 7 天与降价后 7 天（含降价当天）的日均销量。

现要求使用 SparkSQL 找出所有单次降价幅度超过 10% 的商品，并计算其降价前后的日均销量对比。

2. 示例数据

表 1：商品价格变更历史表 (product_price_history)

记录商品价格变动的日期及变动后的新价格（只有价格发生变动时才会产生记录）。

表 2：商品每日销量表 (product_sales)

记录每日商品的实际销量。

3. SparkSQL 解决方案

WITH price_lag AS (
    -- 1. 使用窗口函数获取上一次的价格，并计算降价幅度
    SELECT 
        product_id,
        change_date AS drop_date,
        price AS new_price,
        LAG(price, 1) OVER (PARTITION BY product_id ORDER BY change_date) AS prev_price
    FROM product_price_history
),
significant_drops AS (
    -- 2. 过滤出降幅超过 10% 的记录
    SELECT 
        product_id,
        drop_date,
        prev_price,
        new_price,
        ((prev_price - new_price) / prev_price) AS drop_ratio
    FROM price_lag
    WHERE prev_price IS NOT NULL 
      AND ((prev_price - new_price) / prev_price) > 0.10
)
-- 3. 关联销量表，计算降价前后 7 天的日均销量（无销量的日期按 0 计算，分母固定为 7）
SELECT 
    d.product_id,
    d.drop_date,
    d.prev_price,
    d.new_price,
    ROUND(d.drop_ratio * 100, 2) AS drop_ratio_pct,
    -- 降价前 7 天 (drop_date - 7 至 drop_date - 1)
    ROUND(COALESCE(SUM(CASE WHEN s.sales_date BETWEEN DATE_SUB(d.drop_date, 7) AND DATE_SUB(d.drop_date, 1) 
                            THEN s.sales_volume ELSE 0 END), 0) / 7.0, 2) AS pre_7d_avg_sales,
    -- 降价后 7 天 (drop_date 至 drop_date + 6)
    ROUND(COALESCE(SUM(CASE WHEN s.sales_date BETWEEN d.drop_date AND DATE_ADD(d.drop_date, 6) 
                            THEN s.sales_volume ELSE 0 END), 0) / 7.0, 2) AS post_7d_avg_sales
FROM significant_drops d
LEFT JOIN product_sales s ON d.product_id = s.product_id
GROUP BY 
    d.product_id, 
    d.drop_date, 
    d.prev_price, 
    d.new_price, 
    d.drop_ratio;

4. 核心考察点与面试应对解析

在解答此题及面对面试官追问时，需重点展现以下几个维度的思考：

1. 窗口函数（Window Functions）的熟练运用

• 考察点：如何识别价格“变化”？
• 解析：价格表只在价格变动时写入。通过 LAG(price, 1) OVER (PARTITION BY product_id ORDER BY change_date) 可以获取当前变动前的上一次价格，从而计算出降价幅度。
• 避坑指南：注意处理首条数据（prev_price IS NOT NULL），因为第一条记录没有更早的价格。

2. 日期边界与缺失值处理（Data Completeness）

• 考察点：如何计算“日均”销量？
• 解析：不能直接使用 AVG(sales_volume)。如果某天没有销售记录，在 sales 表中是没有这一行数据的。直接 AVG 会导致分母变小（例如 7 天内只有 3 天有销量，AVG 会除以 3 而不是 7）。
• 正确做法：使用 SUM(CASE WHEN ... ELSE 0 END) / 7.0。这样即使某天无销售，也会被累加为 0，且分母始终保持为 7，保证了均值的准确性。

3. SparkSQL 性能优化（面试加分项）

• 追问 1：如果这两个表数据量极大，怎么优化这个 Query？

  • 广播连接（Broadcast Join）：通常商品价格变更历史表（product_price_history）过滤后的降价事件（significant_drops）数据量不会太大，而销量表（product_sales）数据量极大。可以使用广播变量将降价事件表广播出去：
    sql

    SELECT /*+ BROADCAST(d) */ ... FROM significant_drops d LEFT JOIN product_sales s ...

  • 提前过滤（Filter Pushdown）：在关联 product_sales 之前，如果能确定分析的时间范围（如最近半年），应先对 product_sales 进行分区裁剪（Partition Pruning），避免全表扫描。

• 追问 2：如何避免数据倾斜（Data Skew）？

  • 如果某些热门商品（如爆款手机）的销量数据极大，导致 JOIN 时出现数据倾斜。
  • 解决方案：由于我们是用 product_id 进行关联，可以对 significant_drops 和 product_sales 进行加盐（Salted）关联，或者将倾斜的 Key 单独抽出来用 MapJoin 处理，其余非倾斜数据走常规 Shuffle Hash Join。