基于多条件关联表查询实现最终收入计算的SQL需求

核心思路 #

放弃手动维护成本条件的方式，直接将每日更新的门店采购分区表与中心成本表（Table A）通过**商品(Item)、门店(Store)、城市(City)**三个维度关联，自动匹配对应成本和配送费，再按商品汇总计算总收入。

采购分区表需含字段 #

你提到的"按门店划分的定时分区表"（假设表名为store_purchases）需要包含以下核心字段：

• Item：商品名称（Apples/Pears）
• Store：门店名称（SnakMarket/DollarFood）
• City：所在城市（Townville/Cityshire）
• Quantity：采购数量
• PurchaseDate：采购日期（用于分区筛选，提升查询效率）

最终SQL查询语句 #

SELECT
    p.Item,
    SUM(p.Quantity) AS Quantity,
    -- 单条采购总成本=(单品成本+单品配送费)*采购量，再汇总所有记录
    SUM((a."Costper item" + a."Delivery fee per item") * p.Quantity) AS "Total Revenue"
FROM
    store_purchases p
-- 关联中心成本表，精准匹配多维度成本规则
JOIN
    "Table A" a 
    ON p.Item = a.Item 
    AND p.Store = a.Store 
    AND p.City = a.City
-- 可选：限定时间范围，适配分区表定时更新特性（示例为最近30天数据）
WHERE
    p.PurchaseDate >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY
    p.Item
ORDER BY
    p.Item;

关键说明 #

1. 精准匹配：通过Item+Store+City的组合关联，确保每笔采购都能获取对应维度的成本和配送费，完全覆盖多维度成本差异。
2. 自动同步：后续只需维护Table A的成本数据，无需修改SQL语句，查询结果会自动同步最新成本规则。
3. 性能优化：利用PurchaseDate筛选时间范围，仅扫描指定分区的数据，避免全表扫描拖慢查询速度。

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