T-SQL中的加权平均值（如Excel的SUMPRODUCT）

一尘不染

T-SQL中的加权平均值（如Excel的SUMPRODUCT）

sql

我正在寻找一种从具有相同列数的两行数据中得出加权平均值的方法，其中平均值如下（借用Excel表示法）：

(A1*B1)+(A2*B2)+...+(An*Bn)/SUM(A1:An)

第一部分反映了与Excel的SUMPRODUCT（）函数相同的功能。

我要注意的是，我需要动态指定要对权重进行平均的行，权重来自哪一行以及日期范围。

编辑：这比我想的要容易，因为Excel使我认为我需要某种枢轴。到目前为止，我的解决方案是：

select sum(baseSeries.Actual * weightSeries.Actual) / sum(weightSeries.Actual)
from (
    select RecordDate , Actual 
    from CalcProductionRecords 
    where KPI = 'Weighty'
) baseSeries inner join (       
    select RecordDate , Actual 
    from CalcProductionRecords 
    where KPI = 'Tons Milled'   
) weightSeries on baseSeries.RecordDate = weightSeries.RecordDate

阅读 494

2021-03-17

共1个答案

一尘不染

Quassnoi的答案显示了如何执行SumProduct，并且使用WHERE子句将允许您通过Date字段进行限制…

SELECT
   SUM([tbl].data * [tbl].weight) / SUM([tbl].weight)
FROM
   [tbl]
WHERE
   [tbl].date >= '2009 Jan 01'
   AND [tbl].date < '2010 Jan 01'

更复杂的部分是您要“动态指定”什么字段是[数据]和什么字段是[权重]。简短的答案是，实际上，您必须使用动态SQL。遵循以下方式：
-创建字符串模板
-用适当的数据字段
替换[tbl] .data的所有实例-用适当的权重字段替换[tbl] .weight的所有实例
-执行字符串

但是，动态SQL有其自身的开销。是查询相对不频繁，还是查询本身的执行时间相对较长，这可能并不重要。但是，如果它们很常见且很短，则您可能会注意到，使用动态sql会带来明显的开销。（更不用说注意SQL注入攻击等了。）

编辑：

在最后一个示例中，突出显示三个字段：

记录日期
关键绩效指标
实际的

当[KPI]为“ Weight Y”时，则[Actual]要使用的加权因子。
当[KPI]为“吨磨”时，则[实际]是您要汇总的数据。

我有一些问题是：

还有其他领域吗？
每个KPI每个日期是否只有一个实际值？

我问的原因是您要确保自己所做的JOIN永远只有1：1。（您不希望将5个Reals与5个Weights结合在一起，给出25个结果记录）

无论如何，您的查询当然可以稍微简化一下…

SELECT
   SUM([baseSeries].Actual * [weightSeries].Actual) / SUM([weightSeries].Actual)
FROM
   CalcProductionRecords AS [baseSeries]
INNER JOIN
   CalcProductionRecords AS [weightSeries]
      ON [weightSeries].RecordDate = [baseSeries].RecordDate
--    AND [weightSeries].someOtherID = [baseSeries].someOtherID
WHERE
   [baseSeries].KPI = 'Tons Milled'
   AND [weightSeries].KPI = 'Weighty'

仅当您需要其他谓词以确保数据和权重之间为1：1的关系时，才需要使用带注释的行。

如果您不能保证每个日期仅提供一个值，并且没有其他要加入的字段，则可以稍微修改基于sub_query的版本…

SELECT
   SUM([baseSeries].Actual * [weightSeries].Actual) / SUM([weightSeries].Actual)
FROM
(
    SELECT
        RecordDate,
        SUM(Actual)
    FROM
        CalcProductionRecords
    WHERE
        KPI = 'Tons Milled'
    GROUP BY
        RecordDate
)
   AS [baseSeries]
INNER JOIN
(
    SELECT
        RecordDate,
        AVG(Actual)
    FROM
        CalcProductionRecords
    WHERE
        KPI = 'Weighty'
    GROUP BY
        RecordDate
)
   AS [weightSeries]
      ON [weightSeries].RecordDate = [baseSeries].RecordDate

假设同一天有多个砝码，则砝码的AVG有效。

编辑： 有人刚刚为此投票，所以我认为我会改善最终答案:)

SELECT
   SUM(Actual * Weight) / SUM(Weight)
FROM
(
    SELECT
        RecordDate,
        SUM(CASE WHEN KPI = 'Tons Milled' THEN Actual ELSE NULL END)   AS Actual,
        AVG(CASE WHEN KPI = 'Weighty'     THEN Actual ELSE NULL END)   AS Weight
    FROM
        CalcProductionRecords
    WHERE
        KPI IN ('Tons Milled', 'Weighty')
    GROUP BY
        RecordDate
)
   AS pivotAggregate

这样可以避免JOIN，并且只扫描表一次。

它基于这样一个事实，即NULL在计算时会忽略值AVG()。

2021-03-17