如何优化SQL查询以从Invoice和Contact两表获取特定分组结构数据？

嘿，我来帮你梳理并优化这个SQL查询！原语句确实存在不少冗余——重复关联表、重复计算和过滤条件，不仅写起来冗长，还会增加数据库的计算负担。我们可以通过窗口函数简化逻辑，同时保持你需要的结果结构。

原查询的核心问题 #

• 子查询已经筛选出全年总金额超3000的联系人，但后续又重复关联contact和invoice表，重复执行相同的日期、isBlocked过滤
• 重复计算sum(ti.subtotal) + sum(ti.vat)，其实可以合并为sum(ti.subtotal + ti.vat)，减少计算步骤
• 子查询中的order by是多余的，子查询作为数据源时排序不会影响最终结果

优化后的查询语句 #

SELECT
    tc.id,
    tc.name,
    tc.cif,
    SUM(ti.subtotal + ti.vat) AS Total,
    EXTRACT(QUARTER FROM ti.date) AS quarter
FROM invoice ti
INNER JOIN contact tc ON ti.contactId = tc.id
WHERE
    ti.isBlocked = FALSE
    AND ti.date BETWEEN '2020-01-01' AND '2020-12-31'
GROUP BY tc.id, tc.name, tc.cif, EXTRACT(QUARTER FROM ti.date)
-- 用窗口函数计算该联系人全年的总金额，筛选出符合条件的季度记录
HAVING SUM(ti.subtotal + ti.vat) OVER (PARTITION BY tc.id) > 3000
ORDER BY tc.cif ASC;

优化点说明 #

1. 合并计算：直接用sum(ti.subtotal + ti.vat)代替分开求和再相加，逻辑更简洁，数据库计算更高效
2. 窗口函数替代子查询关联：通过SUM(...) OVER (PARTITION BY tc.id)计算每个联系人的全年总金额，在HAVING子句中直接筛选出全年总金额超3000的记录，避免了额外的子查询和表关联
3. 去掉冗余过滤：只保留一次日期和isBlocked过滤，无需重复执行
4. 简化分组逻辑：直接按联系人+季度分组，一步得到你需要的结果结构

这个优化后的查询和原查询逻辑完全一致，但代码更简洁，执行效率也会更高——数据库不需要重复扫描和关联表，减少了不必要的计算开销。

内容的提问来源于stack exchange，提问作者David De Jesus