为什么 SQL 中没有 PRODUCT 聚合函数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3912204/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why is there no PRODUCT aggregate function in SQL?
提问by lock
Im looking for something like SELECT PRODUCT(table.price) FROM table GROUP BY table.salesimilar to how SUMworks.
我正在寻找SELECT PRODUCT(table.price) FROM table GROUP BY table.sale类似于SUM工作原理的东西。
Have I missed something on the documentation, or is there really no PRODUCTfunction?
我是否遗漏了文档中的某些内容，或者真的没有PRODUCT功能？
If so, why not?
如果是这样，为什么不呢？
Note: I looked for the function in postgres, mysql and mssql and found none so I assumed all sql does not support it.
注意：我在 postgres、mysql 和 mssql 中寻找该函数并没有找到，所以我假设所有 sql 都不支持它。
采纳答案by onedaywhen
There is no PRODUCTset function in the SQL Standard. It would appear to be a worthy candidate, though (unlike, say, a CONCATENATEset function: it's not a good fit for SQL e.g. the resulting data type would involve multivalues and pose a problem as regards first normal form).
PRODUCTSQL 标准中没有set 函数。不过，它似乎是一个有价值的候选者（与CONCATENATEset 函数不同：它不适合 SQL，例如，结果数据类型将涉及多值并在第一范式方面造成问题）。
The SQL Standards aim to consolidate functionality across SQL products circa 1990 and to provide 'thought leadership' on future development. In short, they document what SQL does and what SQL should do. The absence of PRODUCTset function suggests that in 1990 no vendor though it worthy of inclusion and there has been no academic interest in introducing it into the Standard.
SQL 标准旨在整合 1990 年左右的 SQL 产品的功能，并为未来的发展提供“思想领导力”。简而言之，它们记录了 SQL 做什么以及 SQL 应该做什么。PRODUCTset 函数的缺失表明，在 1990 年没有供应商尽管它值得包含在内，并且学术界也没有兴趣将其引入标准。
Of course, vendors always have sought to add their own functionality, these days usually as extentions to Standards rather than tangentally. I don't recall seeing a PRODUCTset function (or even demand for one) in any of the SQL products I've used.
当然，供应商总是试图添加他们自己的功能，这些天通常作为标准的扩展而不是切线。我不记得PRODUCT在我使用过的任何 SQL 产品中看到过set 函数（甚至对一个函数的需求）。
In any case, the work around is fairly simple using logand expscalar functions (and logic to handle negatives) with the SUMset function; see @gbn's answer for some sample code. I've never needed to do this in a business application, though.
在任何情况下，解决办法是相当简单的使用log和exp标量与所述的功能（以及逻辑到手柄底片）SUM组功能; 有关一些示例代码，请参阅 @gbn 的答案。不过，我从未需要在业务应用程序中执行此操作。
In conclusion, my best guess is that there is no demand from SQL end users for a PRODUCTset function; further, that anyone with an academic interest would probably find the workaround acceptable (i.e. would not value the syntactic sugar a PRODUCTset function would provide).
总之，我最好的猜测是 SQL 最终用户对PRODUCTset 函数没有需求；此外，任何有学术兴趣的人都可能会发现该解决方法是可以接受的（即不会重视PRODUCTset 函数提供的语法糖）。
Out of interest, there is indeed demand in SQL Server Land for new set functions but for those of the window function variety (and Standard SQL, too). For more details, including how to get involved in further driving demand, see Itzik Ben-Gan's blog.
出于兴趣，SQL Server Land 确实需要新的集合函数，但需要窗口函数种类（以及标准 SQL）。有关更多详细信息，包括如何参与进一步推动需求，请参阅Itzik Ben-Gan 的博客。
回答by gbn
For MSSQL you can use this. It can be adopted for other platforms: it's just maths and aggregates on logarithms.
对于 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
Taken from my answer here: SQL Server Query - groupwise multiplication
回答by Lord Peter
I don't know why there isn't one, but (take more care over negative numbers) you can use logs and exponents to do:-
我不知道为什么没有，但是（多注意负数）您可以使用对数和指数来做：-
select exp (sum (ln (table.price))) from table ...
回答by Fenton
You can perform a product aggregate function, but you have to do the maths yourself, like this...
您可以执行乘积聚合函数，但您必须自己进行数学运算，就像这样......
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
回答by David Airapetyan
There is a neat trick in T-SQL (not sure if it's ANSI) that allows to concatenate string values from a set of rows into one variable. It looks like it works for multiplying as well:
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
This can potentially be more numerically stable than the log/exp solution.
这可能比 log/exp 解决方案在数值上更稳定。
回答by naiem
I think that is because no numbering system is able to accommodate many products. As databases are designed for large number of records, a product of 1000 numbers would be super massive and in case of floating point numbers, the propagated error would be huge.
我认为这是因为没有编号系统能够容纳许多产品。由于数据库是为大量记录而设计的，1000 个数字的乘积将非常庞大，而在浮点数的情况下，传播的错误将是巨大的。
Also note that using log can be a dangerous solution. Although mathematically log(a*b) = log(a)*log(b), it might not be in computers as we are not dealing with real numbers. If you calculate 2^(log(a)+log(b)) instead of a*b, you may get unexpected results. For example:
另请注意，使用 log 可能是一个危险的解决方案。尽管在数学上 log(a*b) = log(a)*log(b)，但在计算机中可能不是这样，因为我们不是在处理实数。如果计算 2^(log(a)+log(b)) 而不是 a*b，可能会得到意想不到的结果。例如：
SELECT 9999999999*99999999974482, EXP(LOG(9999999999)+LOG(99999999974482))
SELECT 9999999999*99999999974482, EXP(LOG(9999999999)+LOG(99999999974482))
in Sql Server returns
在 Sql Server 中返回
999999999644820000025518, 9.99999999644812E+23
999999999644820000025518, 9.99999999644812E+23
So my point is when you are trying to do the product do it carefully and test is heavily.
所以我的观点是，当你尝试做产品时，要小心谨慎，而且测试量很大。
回答by Nicolás Sierra
One way to deal with this problem (if you are working in a scripting language) is to use the group_concat function.
For example, SELECT group_concat(table.price) FROM table GROUP BY table.sale
处理此问题的一种方法（如果您使用脚本语言）是使用 group_concat 函数。例如，SELECT group_concat(table.price) FROM table GROUP BY table.sale
This will return a string with all prices for the same sale value, separated by a comma. Then with a parser you can get each price, and do a multiplication. (In php you can even use the array_reduce function, in fact in the php.net manualyou get a suitable example).
这将返回一个字符串，其中包含相同销售值的所有价格，用逗号分隔。然后使用解析器，您可以获得每个价格，并进行乘法运算。（在 php 中你甚至可以使用 array_reduce 函数，事实上在php.net 手册中你得到了一个合适的例子）。
Cheers
干杯
回答by Tomá? Zálusky
The problem can be solved using modern SQL features such as window functions and CTEs. Everything is standard SQL and - unlike logarithm-based solutions - does not require switching from integer world to floating point world nor handling nonpositive numbers. Just number rows and evaluate product in recursive query until no row remain:
可以使用现代 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);
As your question involves grouping by sale column, things got little bit complicated but it's still solvable:
由于您的问题涉及按销售列分组，因此事情变得有点复杂，但仍然可以解决：
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;
Result:
结果：
sale,price
"look ma, zero works too!",0
multiplication,560
negatives work,-30
trivial,8
Tested on Postgres.
在 Postgres 上测试。
回答by Tomá? Zálusky
Another approach based on fact that the cardinality of cartesian product is product of cardinalities of particular sets ;-)
另一种方法基于笛卡尔积的基数是特定集合的基数的乘积;-)
? WARNING: This example is just for fun and is rather academic, don't use it in production! (apart from the fact it's just for positive and practically small integers)?
? 警告：这个例子只是为了好玩，而且是学术性的，不要在生产中使用它！（除了它仅适用于正整数和几乎小整数的事实）？
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;
