Snowflake环境下多条件有效订单统计SQL查询优化咨询

优化后的SQL #

SELECT
    YEAR(t1.Order_Date::DATE) * 100 + MONTH(t1.Order_Date::DATE) AS yearmonth,
    t1.Order_Date::DATE AS date_,
    SUM(CASE WHEN t1.Store_ID = 25 THEN 1 ELSE 0 END) AS cant_store_25,
    SUM(CASE WHEN t1.Store_ID = 25 AND COALESCE(order_iim, FALSE) THEN 1 ELSE 0 END) AS cant_store_25_iim,
    SUM(CASE WHEN t1.Store_ID NOT IN (1, 2, 3) THEN 1 ELSE 0 END) AS cant_store_plus,
    SUM(CASE WHEN t1.Store_ID NOT IN (1, 2, 3) AND COALESCE(order_iim, FALSE) THEN 1 ELSE 0 END) AS cant_store_plus_iim
FROM
    Table1 t1
LEFT JOIN (
    -- 预计算每个订单的整单IIM状态：只要有一个商品IIM为true，整单标记为true
    SELECT
        Customer_ID,
        Order_Number,
        MAX(IIM) AS order_iim
    FROM Table2
    GROUP BY Customer_ID, Order_Number
) t2 ON t1.Customer_ID = t2.Customer_ID AND t1.Order_Number = t2.Order_Number
WHERE
    t1.Is_Valid = 'Valid'
    AND YEAR(t1.Order_Date::DATE) * 100 + MONTH(t1.Order_Date::DATE) = 202208
GROUP BY
    yearmonth, date_;

优化核心点 #

1. 简化嵌套层级：原SQL三层嵌套的逻辑被压缩为两层，减少中间数据的生成和处理，降低执行计划复杂度。
2. 提前聚合明细数据：先对Table2按订单维度聚合，直接得到每个订单的整单IIM状态，避免主表与明细关联后产生大量冗余记录（原SQL因按IIM分组，同一订单会生成多条记录）。
3. 移除冗余操作：去掉原SQL中不必要的DISTINCT和中间层MAX，直接基于订单维度的属性计算统计值，逻辑更直接。
4. 过滤条件前置：将月份过滤放在主查询WHERE子句中，提前筛除无关数据，减少后续聚合的数据量。
5. 空值兼容处理：用COALESCE(order_iim, FALSE)处理无明细的订单，确保这类订单的IIM状态符合业务默认逻辑（无商品则整单IIM为false）。

额外性能建议 #

• 索引优化：给Table1的Is_Valid、Order_Date、Store_ID、Customer_ID、Order_Number字段创建组合索引；给Table2的Customer_ID、Order_Number、IIM字段创建索引，加速关联与聚合。
• 日期函数简化：可以用TO_CHAR(t1.Order_Date::DATE, 'YYYYMM')::INT替代YEAR(...) *100 + MONTH(...)生成yearmonth，写法更简洁。

内容的提问来源于stack exchange，提问作者Alan Iza