MS SQL и агрегатная функция умножения

Как известно, в MS SQL нет агрегатной функции, которая бы вычислила произведение всех элементов столбца (наподобие того, как SUM вычисляет сумму всех элементов). Но очень часто такая функция нужна.
Традиционно используется brute force решение: пробегается по курсору и выполняется перемножение элементов построчно. Медленно и глупо. Поиск вариантов решения привел к четырем идеям:
1) Курсор;
2) Математический;
3) Итеративный;
4) CLR.

Для тестов сделана таблицу из 1000 элементов в диапазоне от 0,5 до 1,5, по которой будет в цикле прогоняться 10000 операций вычисления произведения всех элементов.
Теперь решения:
1. Курсор
[code] declare @Value float, @Result float
set @Result = 1
declare c cursor for select value from #temp
open c
while 1=1
begin
fetch next from c into @Value
if @@fetch_status <> 0 break
set @Result = @Result*@Value
end
close c
deallocate c[/code]

Результат: 4,84804064970134E-22
Время выполнения: 220 секунд.

2. Математический
[code] declare @Result float
select @Result=exp(sum(log(Value))) from #temp[/code]

Результат: 4,84804064970128E-22
Время выполнения: 2.3 секунды.

3. Итерационный
[code] declare @Result float
set @Result=1
select @Result=Value*@Result from #temp[/code]

Результат: 4,84804064970134E-22
Время выполнения: 1.7 секунды.

4. CLR
а) Создаем решение — библиотеку классов в студии. Код класса, который будет выполнять работу агрегатора:
[code]
namespace TestCLR
{
[Serializable]
[SqlUserDefinedAggregate(
Format.UserDefined,
MaxByteSize = -1)
]
public class MultFunction : IBinarySerialize
{
public double AccValue;

public void Init()
{
AccValue = 1;
}

public void Accumulate(double value)
{
AccValue *= value;
}

public void Merge(MultFunction value)
{
AccValue *= value.AccValue;
}

public double Terminate()
{
return AccValue;
}

public void Read(BinaryReader r)
{
AccValue = r.ReadSingle();
}

public void Write(BinaryWriter w)
{
w.Write(AccValue);
}
}
}[/code]
б) Строим решение и регистрируем его в MS SQL:
[code] CREATE ASSEMBLY Mult FROM ‘C:\…\CLR_MULT.dll’;
GO
CREATE AGGREGATE Mult (@input float) RETURNS float
EXTERNAL NAME Mult.[TestCLR.MultFunction];[/code]
в) сам код:
[code] declare @Result float
select @Result=dbo.Mult(Value) from #temp[/code]

Результат: 4,84804064970134E-22
Время выполнения: 5.1 секунды.

Время дано среднее, по результатам 10 испытаний.

Выводы:
1) Курсор — швах.
2) Математический метод, который рекомендуют на форумах — швах (не самый быстрый и неточный).
3) CLR — хорошая штука, такая агрегатная функция конечно более затратна, чем 2 и 3 способы, но у агрегатных функций несколько отличная от запросов-подзапросов область применения. Так что полезность такой функции, да и возможности CLR, нельзя недооценивать из за легкой тормознутости (которая, вероятно, сойдет на нет в более сложной задаче).
4) Для вычислений в критических участках лучше всего подходит нативный запрос, ч и т.д.

Tags: , ,

Leave a Reply