Postgres数据库如何关联两表查询对应仓库ID及退货总量

核心逻辑优先对订单数据做聚合后再关联供应链表，减少关联时的数据处理量，性能远高于先关联全量原始数据再做聚合的写法。

场景1：保留每个邮编对应仓库及退货量 #

如果需要保留所有有退货记录的邮编（即使对应邮编没有匹配的仓库信息），使用LEFT JOIN实现：

WITH returned_stats AS (
    SELECT 
        deliveryzipcode, 
        COUNT(OrderReturned) AS Total_returned
    FROM transactions_log
    WHERE OrderReturned = 'Yes'
    GROUP BY deliveryzipcode
)
SELECT 
    rs.deliveryzipcode,
    sc.warehouse_id,
    rs.Total_returned
FROM returned_stats rs
LEFT JOIN supply_chain sc 
    ON rs.deliveryzipcode = sc.zipcode;

如果仅需要返回两边表都能匹配到的记录，把LEFT JOIN替换为INNER JOIN即可。

场景2：统计每个仓库的总退货量 #

如果最终需要按仓库维度汇总退货量，直接在聚合后的结果上做二次汇总即可：

WITH returned_stats AS (
    SELECT 
        deliveryzipcode, 
        COUNT(OrderReturned) AS Total_returned
    FROM transactions_log
    WHERE OrderReturned = 'Yes'
    GROUP BY deliveryzipcode
)
SELECT 
    sc.warehouse_id,
    SUM(rs.Total_returned) AS warehouse_total_returned
FROM returned_stats rs
INNER JOIN supply_chain sc 
    ON rs.deliveryzipcode = sc.zipcode
GROUP BY sc.warehouse_id;

性能优化建议 #

如果数据量较大，可以添加对应索引进一步提升查询效率：

• 给transactions_log表的deliveryzipcode、OrderReturned字段添加联合索引
• 给supply_chain表的zipcode字段添加普通索引

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