如何在SQL Server中实现Excel的SUMPRODUCT功能（避免大表连接）

需求背景 #

现有三张业务表，需针对每笔销售记录，结合商品的原材料占比，计算对应销售日期的原材料单位总成本（对应Excel的SUMPRODUCT功能）：

• 商品销售交易表（表1）：超200万行，记录每笔商品销售的日期、数量
• 商品原材料构成表（表2）：约3000行，记录商品对应的原材料及占比
• 原材料日期价格表（表3）：约72000行，记录每日各原材料的单价

示例数据 #

表1：商品销售记录

表2：商品原材料构成

表3：原材料日期价格

期望结果 #

得到与商品销售交易表同粒度的计算结果：

用户初步方案是先关联商品原材料构成表和原材料日期价格表，再关联商品销售交易表，但担心数据处理量过大，寻求更高效的实现方式。

高效实现方案 #

核心思路是先预计算每个商品每日的单位原材料总成本，再将预计算结果与销售表关联，避免大表（商品销售交易表）直接参与多表关联，减少数据处理量。

1. 预计算商品每日单位成本 #

通过商品原材料构成表和原材料日期价格表的关联，计算每个商品在对应日期的单位原材料总成本：

WITH ItemDailyUnitCost AS (
    SELECT 
        t2.Item,
        t3.Date,
        SUM(CAST(REPLACE(t2.pct_of_total, '%', '') AS DECIMAL(5,2)) / 100 * t3.Part_unitprice) AS SUMPRODUCT_unitprice
    FROM 
        商品原材料构成表 t2
    JOIN 
        原材料日期价格表 t3 ON t2.Raw_material = t3.Raw_material
    GROUP BY 
        t2.Item, t3.Date
)

这里先将百分比字符串（如70%）转换为小数（0.7），再乘以对应日期的原材料单价，最后按商品和日期求和，得到每个商品每日的单位成本。

2. 关联销售表得到最终结果 #

将预计算的单位成本表与商品销售交易表关联，直接匹配商品和日期即可：

SELECT 
    t1.Item,
    t1.Date,
    t1.Qty_sold,
    iduc.SUMPRODUCT_unitprice
FROM 
    商品销售交易表 t1
JOIN 
    ItemDailyUnitCost iduc ON t1.Item = iduc.Item AND t1.Date = iduc.Date
ORDER BY 
    t1.Date;

3. 索引优化建议 #

针对关联字段添加索引，能大幅提升大表查询效率：

• 为商品原材料构成表添加索引：CREATE INDEX IX_ItemRawMaterial ON 商品原材料构成表(Item, Raw_material, pct_of_total);
• 为原材料日期价格表添加索引：CREATE INDEX IX_RawMaterialDate ON 原材料日期价格表(Raw_material, Date, Part_unitprice);
• 为商品销售交易表添加索引：CREATE INDEX IX_ItemDate ON 商品销售交易表(Item, Date);

方案优势 #

• 预计算阶段仅处理约75000行数据（表2+表3），生成的中间表数据量远小于直接三表关联的结果
• 商品销售交易表（200万行）仅需与小体量的中间表做等值关联，计算效率显著提升

结果验证 #

执行上述SQL后，得到的结果与期望完全一致，符合需求。

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