Произведение значений столбца
Почему среди агрегатных функций Язык структурированных запросов) — универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных. SQL нет произведения?
Такой вопрос часто задают в профессиональных социальных сетях. Речь идёт о произведении значений столбца таблицы при выполнении группировки. Функции типа PRODUCT нет в стандарте языка, и я не знаю СУБД, которая её бы имела. Хорошей же новостью является то, что такую функцию просто выразить через три другие, которые есть в арсенале практически всех серверов БД. Итак.
Пусть требуется перемножить значения столбца value следующей таблицы:
Консоль
Выполнить
Воспользуемся следующим свойством логарифмов: логарифм произведения равен сумме логарифмов, для нашего примера это означает
Если теперь применить обратную к натуральному логарифму ( Ln ) функцию экспоненты ( exp ), то получим
Итак, произведение чисел мы можем заменить выражением, стоящим в равенстве справа. Осталось записать эту формулу на языке SQL, учитывая, что сами числа находятся в столбце value.
Консоль
Выполнить
Правильность результата легко проверить устным счетом, или в Excel :-).
Рассмотренное решение не является универсальным. Поскольку логарифм не определен для чисел
Консоль
Выполнить
будет получено сообщение об ошибке:
An invalid floating point operation occurred.
(Попытка выполнить недопустимую операцию с плавающей запятой.)
Для учета «недопустимых» значений доработаем наше решение в соответствии со следующим алгоритмом:
- Если среди значений есть нули, то результатом будет 0.
- Если число отрицательных значений нечетное, то домножаем произведение абсолютных значений столбца на -1.
- Если число отрицательных значений четное, то результатом будет произведение абсолютных значений столбца.
Вот решение с комментариями, реализующее этот алгоритм:
Консоль
Выполнить
Обратите внимание на условие value <> 0 в последней строке запроса. Его присутствие связано с тем, что, хотя ветвь оператора CASE с вычислением выражения через логарифм не реализуется при наличии нулей среди значений столбца (возвращается 0), Cистема управления реляционными базами данных (СУБД), разработанная корпорацией Microsoft. SQL Server всё равно вычисляет это выражение и возвращает ошибку.
Сообразительные уже спросили: «А как быть с NULL?»
Действительно, наше решение даёт в этом случае 0. Будем следовать общей логике поведения агрегатных функций — не учитывать NULL. Ниже приводится окончательное решение, которое имеет одно отличие по сравнению с предыдущим решением. Кто догадается какое?
Консоль
Выполнить