Оператор SELECT

Получение итоговых значений

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

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

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

Консоль

Выполнить

SELECT value FROM (
VALUES(2),(3),(4),(5)
) X(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);

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);

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;

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),  Cистема управления реляционными базами данных (СУБД), разработанная корпорацией Microsoft.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;

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;

    Схема

Нет схемы