查询当年及去年同期3个月客户销售额SQL运行缓慢优化求助

原SQL性能问题根源 #

• 你用了关联子查询计算去年同期销售额：外层查询每返回1个客户，子查询都要单独扫描一次销售表，表数据量大时会导致扫描次数指数级上升，自然跑得慢。
• 重复多次计算to_date('&From_Dt')、to_date('&To_Dt')，虽不致命但也产生了不必要的开销。

高效实现方案：用条件聚合单次扫表完成计算 #

只需要扫描1次销售表，通过CASE WHEN判断日期属于今年统计区间还是去年同期区间，分别求和即可，性能提升非常明显。
优化后的SQL如下（以下用你样例给出的表字段编写，实际使用如果和你SQL里的Customer_No、invoice_date等字段名不一致，对应替换即可）：

WITH date_param AS (
    -- 统一计算时间参数，仅计算1次避免重复开销
    SELECT
        trunc(to_date('&From_Dt', 'DD/MM/YYYY'), 'mm') AS from_dt_trunc,
        trunc(to_date('&To_Dt', 'DD/MM/YYYY'), 'mm') AS to_dt_trunc
    FROM dual
),
sale_agg AS (
    SELECT
        t.Cust_ID,
        t.CName,
        -- 聚合今年近3个月销售额
        SUM(CASE WHEN t.Inv_Date BETWEEN add_months(d.from_dt_trunc, -3) AND add_months(d.to_dt_trunc, -1) THEN t.Sales ELSE 0 END) AS CY_Sum_Sales,
        -- 聚合去年同期3个月销售额
        SUM(CASE WHEN t.Inv_Date BETWEEN add_months(d.from_dt_trunc, -15) AND add_months(d.to_dt_trunc, -13) THEN t.Sales ELSE 0 END) AS PY_Sum_Sales
    FROM cust_table t, date_param d
    -- 过滤只扫描需要的6个月数据，进一步减少数据扫描量
    WHERE t.Inv_Date BETWEEN add_months(d.from_dt_trunc, -15) AND add_months(d.to_dt_trunc, -1)
    GROUP BY t.Cust_ID, t.CName
)
SELECT
    Cust_ID,
    CName,
    TO_CHAR(CY_Sum_Sales, 'fm999G999G999G999G999D0') AS TYear_Sale,
    TO_CHAR(PY_Sum_Sales, 'fm999G999G999G999G999D0') AS LYear_Sale
FROM sale_agg
ORDER BY TYear_Sale;

额外优化建议 #

• 给cust_table建**(Inv_Date, Cust_ID, CName, Sales)** 联合覆盖索引，查询时可以直接走索引不需要回表，性能还能再提升一个量级。
• 如果需要包含从来没有过销售记录的客户，先从客户主表取所有客户ID、名称，再LEFT JOIN上面的sale_agg结果即可。

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