Произведение значений столбца

Почему среди агрегатных функций SQL нет произведения?

Такой вопрос часто задают в профессиональных социальных сетях. Речь идёт о произведении значений столбца таблицы при выполнении группировки. Функции типа PRODUCT нет в стандарте языка, и я не знаю СУБД, которая её бы имела. Хорошей же новостью является то, что такую функцию просто выразить через три другие, которые есть в арсенале практически всех серверов БД. Итак.

Пусть требуется перемножить значения столбца value следующей таблицы:

select value 
from (values(2),(3),(4),(5)
     ) X(value);

Воспользуемся следующим свойством логарифмов: логарифм произведения равен сумме логарифмов, для нашего примера это означает

ln(2*3*4*5) = ln(2) + ln(3) + ln(4) + ln(5)

Если теперь применить обратную к натуральному логарифму (Ln) функцию экспоненты (exp), то получим

exp(ln(2*3*4*5)) = 2*3*4*5 = exp(ln(2) + ln(3) + ln(4) + ln(5))

Итак, произведение чисел мы можем заменить выражением, стоящим в равенстве справа. Осталось записать эту формулу на языке SQL, учитывая, что сами числа находятся в столбце value.

select exp(sum(log(value))) product 
from (values(2),(3),(4),(5)
     ) X(value);

Правильность результата легко проверить устным счетом, или в Excel :-).

Рассмотренное решение не является универсальным. Поскольку логарифм не определен для чисел <=0, то если в столбце появятся такие значения, например,

select exp(sum(log(value))) product 
from (values(2),(-3),(4),(-5)
     ) X(value);

Для учета “недопустимых” значений доработаем наше решение в соответствии со следующим алгоритмом:

1. Если среди значений есть нули, то результатом будет 0.
2. Если число отрицательных значений нечетное, то домножаем произведение абсолютных значений столбца на -1.
3. Если число отрицательных значений четное, то результатом будет произведение абсолютных значений столбца.

Вот решение с комментариями, реализующее этот алгоритм:

with T as
(select * 
from (values(-2),(-3),(4),(-5)
    ) X(value)
),
P as 
(select sum(case when value<0 then 1 else 0 end) neg, -- число отрицательных значений
    sum(case when value>0 then 1 else 0 end) pos, -- число положительных значений
    count(*) total -- общее число значений
from T
)
select 
    case 
        when total <> pos+neg /* есть нули */ 
        then 0 
        else
            (case when neg%2=1 then -1 else +1 end) *exp(sum(log(abs(value))))
    end product
from T,P
where value <> 0
group by neg, pos, total;

Обратите внимание на условие value <> 0 в последней строке запроса. Его присутствие связано с тем, что, хотя ветвь оператора CASE с вычислением выражения через логарифм не реализуется при наличии нулей среди значений столбца (возвращается 0), SQL Server всё равно вычисляет это выражение и возвращает ошибку.

Сообразительные уже спросили: “А как быть с NULL?”

Действительно, наше решение даёт в этом случае 0. Будем следовать общей логике поведения агрегатных функций - не учитывать NULL. Ниже приводится окончательное решение, которое имеет одно отличие по сравнению с предыдущим решением. Кто догадается какое?

with T as
(select * 
from (values(-2),(-3),(4),(-5), (NULL) 
     ) X(value)
),
P as 
(select sum(case when value<0 then 1 else 0 end) neg, -- число отрицательных значений
    sum(case when value>0 then 1 else 0 end) pos, -- число положительных значений
    count(value) total -- общее число значений
from T)
select 
    case 
        when total <> pos+neg /* есть нули */ 
        then 0 
        else
            (case when neg%2=1 then -1 else +1 end) *exp(sum(log(abs(value))))
    end product 
from T,P 
where value <> 0 
group by neg, pos, total;