PSQL按时间维度聚合销售数据：如何优化过度依赖Left Join的查询脚本

原脚本存在的核心问题 #

• 语法级错误：
  • 主查询关联用的ACC子查询仅查询了client、name字段，不存在关联用的id字段，关联逻辑不成立
  • 第三个关联的table_3没有定义别名c，后续引用c.product_name会报错
  • 输出字段中SUM(a.spend)和SUM(b.spend)别名都为spend，重复别名会触发执行报错
• 性能级问题：
  • 多次LEFT JOIN同一张table_2，每关联一个月份就全表扫描一次table_2，数据量超过10万级时性能会出现明显下降
  • 多余的嵌套子查询增加了执行计划的解析成本，且未过滤无效字段、无效数据

优化后写法（条件聚合替代多表关联） #

用条件聚合一次性读取table_2的所需月份数据，仅扫描一次表即可完成多月份指标计算：

SELECT 
    t1.id,
    SUM(CASE WHEN t2.month = 11 THEN t2.spend ELSE 0 END) AS nov_spend,
    SUM(CASE WHEN t2.month = 11 THEN t2.revenue ELSE 0 END) AS nov_revenue,
    SUM(CASE WHEN t2.month = 10 THEN t2.spend ELSE 0 END) AS oct_spend,
    SUM(CASE WHEN t2.month = 10 THEN t2.revenue ELSE 0 END) AS oct_revenue,
    t3.product_name
FROM table_1 t1
LEFT JOIN table_2 t2 
    ON t1.id = t2.id 
    AND t2.month IN (10,11) -- 仅过滤所需月份，减少数据扫描量
LEFT JOIN table_3 t3 
    ON t1.id = t3.id
GROUP BY t1.id, t3.product_name
ORDER BY nov_spend;

额外性能优化建议 #

• 给table_2的(month, id)建立联合索引，匹配关联条件和过滤条件，可直接走索引查询避免全表扫描
• 如果仅需要存在销售数据的客户记录，可将LEFT JOIN table_2改为INNER JOIN，进一步减少返回数据量
• 不需要的字段不要查询返回，减少数据传输和内存占用

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