每日进步：电商数仓开发SQL优化案例总结

很高兴你能来阅读，过去一年多，我主要从事天猫国际商品以及订单相关数仓开发与数据分析工作。接下来会陆续分享这段经历中的实战问题、对应解决思路，以及数仓基础的进阶学习总结，希望能给有需要的朋友带来参考和帮助～

场景一: GMV 日维度统计 - 聚合优化（预聚合 + 分区裁剪）

背景

• 场景：业务需每日统计天猫国际各品类 GMV（成交金额）、订单数、客单价，原始 SQL 直接从订单明细表全量扫描计算，SQL 如下：

  sql

  SELECT 
      dt,
      category_id,
      SUM(paid_amount) AS gmv,
      COUNT(DISTINCT order_id) AS order_cnt,
      AVG(paid_amount) AS avg_price
  FROM ods_tmall_order_item
  WHERE dt >= '2025-01-01' -- 业务常查近3个月数据
  GROUP BY dt, category_id;
  

• 原始问题：订单明细表无分区裁剪（仅按 dt 分区，但 SQL 未限制 dt 上限），且每日全量聚合，扫描数据量达 5000w，执行时间超 0.5 小时，且重复计算（每日跑全量）。

优化方案

1. 严格分区裁剪：限定dt范围为dt BETWEEN '2025-01-01' AND '2025-12-17'（当前日期），仅扫描所需分区；

2. 预聚合分层：在 DWD 层新增预聚合表dwd_tmall_order_item_gmv_d，每日仅计算当日数据并增量插入，统计时直接从预聚合表查询：

   sql

   -- 预聚合表每日增量计算
   INSERT INTO dwd_tmall_order_item_gmv_d
   SELECT 
       dt,
       category_id,
       SUM(paid_amount) AS gmv,
       COUNT(DISTINCT order_id) AS order_cnt,
       AVG(paid_amount) AS avg_price
   FROM ods_tmall_order_item
   WHERE dt = '${bizdate}' -- 当日分区
   GROUP BY dt, category_id;
   
   -- 业务查询（直接读预聚合表）
   SELECT dt, category_id, gmv, order_cnt, avg_price
   FROM dwd_tmall_order_item_gmv_d
   WHERE dt >= '2025-01-01';
   

3. 去重优化：将COUNT(DISTINCT order_id)改为COUNT(1)（预聚合表中order_id+category_id已唯一）。

优化效果

执行时间从 0.5 小时降至 1 分钟，扫描数据量从 5000万w降至 300w。

经验总结：

• 对于 SQL 逻辑冗长、关联表数据量过大，或整体处理耗时久的任务，我们可以采用预处理的思路 —— 提前执行任务，把部分数据计算完成后固化下来，从而减轻核心任务的计算压力。
• 从经验上来看 如上业务历史订单数据肯定是固定的，部分统计指标我们只需要执行任务算一次即可，不需要重复计算。

---

场景二: 退款订单分析 - 子查询优化（改写为 JOIN + 消除嵌套子查询）

背景

• 场景：统计近 30 天有退款的订单信息，需关联订单表、退款表，原始 SQL 使用多层嵌套子查询：

  sql

  SELECT o.order_id, o.buyer_id, o.pay_time, r.refund_amount
  FROM ods_tmall_order o
  WHERE o.order_id IN (
      SELECT r1.order_id 
      FROM ods_tmall_refund r1
      WHERE r1.refund_status = 'SUCCESS' 
      AND r1.dt IN (
          SELECT dt FROM dim_date WHERE dt >= DATE_SUB(CURRENT_DATE(), 30)
      )
  )
  AND o.dt >= DATE_SUB(CURRENT_DATE(), 30);
  

• 原始问题：多层嵌套子查询导致 Hive 解析执行计划低效，且IN子查询对大结果集（退款订单约 100 万）性能差，执行时间超 1 小时。

优化方案

1. 消除嵌套子查询：将日期筛选直接写在 WHERE 条件，替代dim_date子查询；
2. 改写 IN 子查询为 JOIN：使用 INNER JOIN 替代IN，减少子查询执行次数；
3. 过滤先行：先过滤退款表的成功退款和日期范围，再关联订单表。

sql

SELECT o.order_id, o.buyer_id, o.pay_time, r.refund_amount
FROM ods_tmall_order o
INNER JOIN (
    SELECT order_id, refund_amount 
    FROM ods_tmall_refund
    WHERE refund_status = 'SUCCESS' 
    AND dt >= DATE_SUB(CURRENT_DATE(), 30)
) r ON o.order_id = r.order_id
WHERE o.dt >= DATE_SUB(CURRENT_DATE(), 30);

优化效果

执行时间从 30 小时降至 5 分钟，执行计划复杂度降低 60%。

经验总结：

• 「INNER JOIN」是最稳定、最易优化的选择：优化器对 JOIN 的支持最成熟，只要关联字段有索引，无论数据量大小，效率都不会差；
• 「IN」适合子查询结果集极小（如几十 / 几百条）的场景，避免子查询返回大量数据；

---

场景三:多维度订单筛选-先过滤再关联

背景

• 场景：筛选 “2025 年双 11 期间（11.01-11.11）、美妆品类、实付金额> 500 元、买家等级为 VIP” 的订单，原始 SQL 过滤顺序不合理，且未开启谓词下推：

  sql

  SELECT o.order_id, o.paid_amount, b.buyer_level, i.category_name
  FROM ods_tmall_order o
  JOIN ods_tmall_order_item i ON o.order_id = i.order_id
  JOIN ods_tmall_buyer b ON o.buyer_id = b.buyer_id
  WHERE 
      b.buyer_level = 'VIP'
      AND i.category_name = '美妆'
      AND o.paid_amount > 500
      AND o.pay_time BETWEEN '2025-11-01' AND '2025-11-11';
  

• 原始问题：过滤条件写在最后，且未开启 Hive 谓词下推，导致先关联所有表再过滤，中间结果集达 5000 万 +，执行时间超 1.5 小时。

优化方案

1. 开启谓词下推：设置参数hive.optimize.ppd = true（Hive 默认开启，确认配置生效），让过滤条件下推到数据源层；
2. 调整过滤顺序：先过滤大表（订单表）的时间范围和金额，再关联明细表和买家表，减少关联数据量；
3. 列裁剪：仅查询所需字段，避免 SELECT *。

sql

SELECT o.order_id, o.paid_amount, b.buyer_level, i.category_name
FROM (
    SELECT order_id, paid_amount, buyer_id 
    FROM ods_tmall_order
    WHERE pay_time BETWEEN '2025-11-01' AND '2025-11-11'
    AND paid_amount > 500
) o
JOIN (
    SELECT order_id, category_name 
    FROM ods_tmall_order_item
    WHERE category_name = '美妆'
) i ON o.order_id = i.order_id
JOIN (
    SELECT buyer_id, buyer_level 
    FROM ods_tmall_buyer
    WHERE buyer_level = 'VIP'
) b ON o.buyer_id = b.buyer_id;

优化效果

中间结果集从 3000 万降至 100 万，执行时间从 1.5 小时降至 15 分钟，IO 扫描量减少 80%。

经验总结：过滤先行，先过滤再关联，减少中间结果集；

---

📣非常感谢你阅读到这里，如果这篇文章对你有帮助，希望能留下你的点赞👍 关注❤️ 分享👥 留言💬thanks！！！