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]为“吨磨”时,则[实际]是您要汇总的数据。
我有一些问题是:
我问的原因是您要确保自己所做的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()
。