Почему в SQL нет агрегатной функции PRODUCT?

Ответ 1

В стандарте SQL нет функции set PRODUCT. Он, кажется, был достойным кандидатом, хотя (в отличие от, скажем, функции CONCATENATE set: он не подходит для SQL, например, результирующий тип данных будет включать в себя многозначность и представлять проблему в отношении первой нормальной формы).

Стандарты SQL нацелены на консолидацию функциональных возможностей продуктов SQL примерно в 1990 году и обеспечение "лидерства мышления" в отношении будущего развития. Короче говоря, они документируют, что делает SQL и что должен делать SQL. Отсутствие функции set PRODUCT предполагает, что в 1990 году ни один поставщик, хотя и достойный включения, и академический интерес к его внедрению в Стандарт не представлял.

Конечно, поставщики всегда стремились добавить свою собственную функциональность, в наши дни обычно это скорее степени, чем стандарты, а не касательные. Я не помню, как функция PRODUCT set (или даже требование для одного) в любом из продуктов SQL, которые я использовал.

В любом случае, работа вокруг довольно проста, используя log и exp скалярные функции (и логику для обработки негативов) с помощью функции set SUM; см. ответ @gbn для некоторого примера кода. Однако мне никогда не приходилось делать это в бизнес-приложении.

В заключение, я думаю, что нет никакой потребности у конечных пользователей SQL для функции set PRODUCT; кроме того, что любой, у кого есть академический интерес, вероятно, найдет приемлемое решение (т.е. не будет оценивать синтаксический сахар, который будет предоставлять функция set PRODUCT).

Из интереса, действительно нет спроса в SQL Server Land для новых функций набора, но для разновидностей оконной функции (и Standard SQL тоже). Более подробную информацию, в том числе о том, как принять участие в дальнейшем востребованном спросе, см. блог Itzik Ben-Gan.

Ответ 2

Для MSSQL вы можете использовать это. Он может быть принят для других платформ: он просто математики и агрегаты по логарифмам.

SELECT
    GrpID,
    CASE
       WHEN MinVal = 0 THEN 0
       WHEN Neg % 2 = 1 THEN -1 * EXP(ABSMult)
       ELSE EXP(ABSMult)
    END
FROM
    (
    SELECT
       GrpID, 
       --log of +ve row values
       SUM(LOG(ABS(NULLIF(Value, 0)))) AS ABSMult,
       --count of -ve values. Even = +ve result.
       SUM(SIGN(CASE WHEN Value < 0 THEN 1 ELSE 0 END)) AS Neg,
       --anything * zero = zero
       MIN(ABS(Value)) AS MinVal
    FROM
       Mytable
    GROUP BY
       GrpID
    ) foo

Взято из моего ответа здесь: Запрос SQL Server - групповое умножение

Ответ 3

Я не знаю, почему его нет, но (более осторожно относитесь к отрицательным числам) вы можете использовать журналы и показатели: -

select exp (sum (ln (table.price))) from table ...

Ответ 4

Вы можете выполнять функцию агрегации продукта, но вы должны сами выполнять математику, как это...

SELECT
    Exp(Sum(IIf(Abs([Num])=0,0,Log(Abs([Num])))))*IIf(Min(Abs([Num]))=0,0,1)*(1-2*(Sum(IIf([Num]>=0,0,1)) Mod 2)) AS P
FROM
   Table1

Источник: http://productfunctionsql.codeplex.com/

Ответ 5

В T-SQL существует четкий трюк (не уверен, если он ANSI), который позволяет объединить строковые значения из набора строк в одну переменную. Похоже, он работает и для умножения:

declare @Floats as table (value float)
insert into @Floats values (0.9)
insert into @Floats values (0.9)
insert into @Floats values (0.9)

declare @multiplier float = null

select 
    @multiplier = isnull(@multiplier, '1') * value
from @Floats

select @multiplier

Это потенциально может быть более численно устойчивым, чем решение log/exp.

Ответ 6

Я думаю, это потому, что никакая система нумерации не может разместить много продуктов. Поскольку базы данных предназначены для большого количества записей, продукт из 1000 номеров будет супермассивным, а в случае чисел с плавающей запятой распространенная ошибка будет огромной.

Также обратите внимание, что использование журнала может быть опасным решением. Хотя математически log (a * b) = log (a) * log (b), это может быть не в компьютерах, поскольку мы не имеем дело с действительными числами. Если вы вычисляете 2 ^ (log (a) + log (b)) вместо a * b, вы можете получить неожиданные результаты. Например:

SELECT 9999999999 * 99999999974482, EXP (LOG (9999999999) + LOG (99999999974482))

в Sql Server возвращает

999999999644820000025518, 9.99999999644812E + 23

Итак, моя точка зрения заключается в том, что вы пытаетесь сделать продукт тщательно и тщательно тестируете.

Ответ 7

Один из способов решения этой проблемы (если вы работаете на языке сценариев) - использовать функцию group_concat. Например, SELECT group_concat(table.price) FROM table GROUP BY table.sale

Это вернет строку со всеми ценами для одной и той же стоимости продажи, разделенной запятой. Затем с помощью парсера вы можете получить каждую цену и делать умножение. (В php вы можете даже использовать функцию array_reduce, фактически в руководстве php.net вы получите подходящий пример).

Приветствия

Ответ 8

Проблема может быть решена с использованием современных функций SQL, таких как оконные функции и CTE. Все является стандартным SQL и - в отличие от логарифмических решений - не требует переключения из целочисленного мира в мир с плавающей запятой или обработки неположительных чисел. Просто нумеруйте строки и оцените продукт в рекурсивном запросе, пока не останется ни одной строки:

   with recursive t(c) as (
     select unnest(array[2,5,7,8])
   ), r(c,n) as (
     select t.c, row_number() over () from t
   ), p(c,n) as (
     select c, n from r where n = 1
     union all
     select r.c * p.c, r.n from p join r on p.n + 1 = r.n
   )
   select c from p where n = (select max(n) from p);

Поскольку ваш вопрос касается группировки по столбцам продаж, все немного усложнилось, но все еще решаемо:

   with recursive t(sale,price) as (
     select 'multiplication', 2 union
     select 'multiplication', 5 union
     select 'multiplication', 7 union
     select 'multiplication', 8 union
     select 'trivial', 1 union
     select 'trivial', 8 union
     select 'negatives work', -2 union
     select 'negatives work', -3 union
     select 'negatives work', -5 union
     select 'look ma, zero works too!', 1 union
     select 'look ma, zero works too!', 0 union
     select 'look ma, zero works too!', 2
   ), r(sale,price,n,maxn) as (
     select t.sale, t.price, row_number() over (partition by sale), count(1) over (partition by sale)
     from t
   ), p(sale,price,n,maxn) as (
     select sale, price, n, maxn
     from r where n = 1
     union all
     select p.sale, r.price * p.price, r.n, r.maxn
     from p
     join r on p.sale = r.sale and p.n + 1 = r.n
   )
   select sale, price
   from p
   where n = maxn
   order by sale;

Результат:

sale,price
"look ma, zero works too!",0
multiplication,560
negatives work,-30
trivial,8

Проверено на Postgres.

Ответ 9

Другой подход, основанный на том факте, что мощность декартового произведения есть произведение мощностей определенных множеств ;-)

⚠ ВНИМАНИЕ: Этот пример просто для развлечения и довольно академический, не используйте его в производстве! (кроме того, что это только для положительных и практически малых целых чисел) ⚠

with recursive t(c) as (
  select unnest(array[2,5,7,8])
), p(a) as (
  select array_agg(c) from t
  union all
  select p.a[2:]
  from p
  cross join generate_series(1, p.a[1])
)
select count(*) from p where cardinality(a) = 0;

Ответ 10

Один из подходов состоит в объединении двух встроенных функций:

SELECT table.sale, AVG(table.price) * COUNT(table.price) FROM table GROUP BY table.sale