Ответ 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'
Более сложная часть - это то, где вы хотите "динамически указать", в каком поле [данные] и какое поле [вес]. Короткий ответ заключается в том, что вам реально придется использовать Dynamic SQL. Что-то вроде:
- Создать шаблон строки
- Заменить все экземпляры [tbl].data соответствующим полем данных
- Замените все экземпляры [tbl].weight с соответствующим полем веса
- Выполнить строку
Динамический SQL, однако, несет в себе собственные накладные расходы. Являются ли запросы относительно нечастыми, или время выполнения самого запроса относительно велико, это может быть неважно. Однако, если они являются общими и короткими, вы можете заметить, что использование динамического sql вводит заметные служебные данные. (Не говоря уже о том, чтобы быть осторожным с атаками SQL-инъекций и т.д.)
EDIT:
В последнем примере вы выделите три поля:
- RecordDate
- KPI
- Actual
Когда значение [KPI] равно "Вес Y", тогда [Фактический] используется весовой коэффициент.
Когда [KPI] является "Tons Milled", тогда [Actual] - данные, которые вы хотите скопировать.
Некоторые вопросы у меня есть:
- Есть ли другие поля?
- Есть ли только один ОДИН фактический по дате на KPI?
Причина, по которой я спрашиваю, что вы хотите, чтобы JOIN вы делали, всегда 1:1. (Вы не хотите, чтобы 5 Actuals присоединились к 5 весам, давая 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, а также только один раз сканировать таблицу.
Он полагается на то, что при вычислении AVG() значения NULL игнорируются.