基于本文回答

播面 播面

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

计算某商品降价（降幅超 10%）前 7 天与降价后 7 天的日均销量对比，用以计算该商品的销量弹性

面试题：计算商品降价前后的日均销量对比（销量弹性）

背景描述

在零售和电商行业中，价格弹性（Price Elasticity of Demand）是衡量价格变动对需求量影响的重要指标。
现准备评估某次降价活动的效果。请编写 SQL 查询，找出降价幅度超过 10% 的商品，并计算其降价前 7 天（不含降价当天）与降价后 7 天（含降价当天）的日均销量对比。

示例数据

1. 价格变更历史表 product_price_history

记录商品价格变动的历史，每次价格变更会生成一条记录。

product_id (商品ID) change_date (价格生效日期) price (新价格)
101 2023-10-01 100.00
101 2023-10-15 85.00
101 2023-10-25 80.00
102 2023-10-01 50.00
102 2023-10-10 48.00
2. 每日销售明细表 sales_daily

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

product_id (商品ID) sales_date (销售日期) quantity (销量)
101 2023-10-08 10
101 2023-10-09 12
101 2023-10-10 11
101 2023-10-11 9
101 2023-10-12 10
101 2023-10-13 8
101 2023-10-14 10
101 2023-10-15 15
101 2023-10-16 18
101 2023-10-17 16
101 2023-10-18 15
101 2023-10-19 17
101 2023-10-20 14
101 2023-10-21 16

考察点

  1. 窗口函数（Window Functions）：如何利用 LAG() 获取上一次的价格并计算降幅。
  2. 日期区间关联（Interval Joins）：如何根据降价日期，动态关联前后 7 天的销量数据。
  3. 条件聚合（Conditional Aggregation）：如何在同一行中，用 CASE WHEN 优雅地分别计算前 7 天与后 7 天的平均值。
  4. 边界情况处理：对日期、除数为0或缺失日期的鲁棒性处理。

标准 SQL 答案（基于 PostgreSQL/Hive 语法）

sql 
WITH price_changes AS (
    -- 1. 计算每次价格变动的降幅，筛选出降幅超 10% 的事件
    SELECT 
        product_id,
        change_date AS drop_date,
        LAG(price) OVER(PARTITION BY product_id ORDER BY change_date) AS pre_price,
        price AS post_price,
        (LAG(price) OVER(PARTITION BY product_id ORDER BY change_date) - price) 
            / LAG(price) OVER(PARTITION BY product_id ORDER BY change_date) AS drop_rate
    FROM product_price_history
),
valid_drops AS (
    -- 2. 仅保留降幅大于 10% 的记录
    SELECT 
        product_id,
        drop_date,
        pre_price,
        post_price,
        drop_rate
    FROM price_changes
    WHERE drop_rate > 0.10
)
-- 3. 关联销量表计算前后 7 天日均销量
SELECT 
    v.product_id,
    v.drop_date,
    v.pre_price,
    v.post_price,
    ROUND(v.drop_rate * 100, 2) AS drop_rate_pct,
    -- 降价前 7 天：[drop_date - 7, drop_date - 1]
    ROUND(COALESCE(SUM(CASE WHEN s.sales_date BETWEEN v.drop_date - 7 AND v.drop_date - 1 THEN s.quantity ELSE 0 END), 0) / 7.0, 2) AS avg_qty_before,
    -- 降价后 7 天：[drop_date, drop_date + 6]
    ROUND(COALESCE(SUM(CASE WHEN s.sales_date BETWEEN v.drop_date AND v.drop_date + 6 THEN s.quantity ELSE 0 END), 0) / 7.0, 2) AS avg_qty_after,
    -- 销量提升比例
    ROUND(
        (COALESCE(SUM(CASE WHEN s.sales_date BETWEEN v.drop_date AND v.drop_date + 6 THEN s.quantity ELSE 0 END), 0) / 7.0 - 
         COALESCE(SUM(CASE WHEN s.sales_date BETWEEN v.drop_date - 7 AND v.drop_date - 1 THEN s.quantity ELSE 0 END), 0) / 7.0)
        / NULLIF(COALESCE(SUM(CASE WHEN s.sales_date BETWEEN v.drop_date - 7 AND v.drop_date - 1 THEN s.quantity ELSE 0 END), 0) / 7.0, 0) * 100, 2
    ) AS sales_lift_pct
FROM valid_drops v
LEFT JOIN sales_daily s 
  ON v.product_id = s.product_id 
 AND s.sales_date BETWEEN v.drop_date - 7 AND v.drop_date + 6
GROUP BY 
    v.product_id, 
    v.drop_date, 
    v.pre_price, 
    v.post_price, 
    v.drop_rate;

核心步骤解析与面试加分项

1. 识别降价事件：使用 LAG() 窗口函数

在 CTE price_changes 中，我们使用 LAG(price) OVER (PARTITION BY product_id ORDER BY change_date) 获取该商品前一次的价格。

  • 面试官加分点：能主动考虑到同一个商品可能有多次价格变更，必须用 PARTITION BY product_id 做好隔离，并按时间 ORDER BY change_date 升序排列。
2. 日期区间关联优化（非等值连接 Join）

我们通过 LEFT JOIN sales_daily s ON ... AND s.sales_date BETWEEN v.drop_date - 7 AND v.drop_date + 6 把销量表和降价事件关联起来。

  • 避坑指南：不要先做全表聚合，应当先通过 JOIN 过滤出目标日期区间（前后共 14 天）的销量，再做聚合。这样能极大减少参与运算的数据量，提高查询效率。
3. 优雅的条件聚合（Conditional Aggregation）

在 SELECT 阶段，不需要写多个子查询，而是利用 CASE WHEN 结合 SUM() 来分别过滤计算：

  • 前 7 天（不含降价日）：sales_date BETWEEN drop_date - 7 AND drop_date - 1
  • 后 7 天（含降价日）：sales_date BETWEEN drop_date AND drop_date + 6
  • 日均销量计算：直接除以 7.0（注意要写成小数避免整除截断）。
4. 健壮性处理（防错机制）
  • COALESCE(..., 0)：若某天无销售记录，求和结果可能为 NULL，用 COALESCE 转化为 0 避免计算报错。
  • NULLIF(..., 0)：在计算销量提升比例时，分母是“降价前日均销量”。如果降价前 7 天销量为 0，直接相除会导致 Division by zero 报错。使用 NULLIF(denominator, 0) 可以优雅地在分母为 0 时返回 NULL。
00:00
00:00