是否有像 Oracle SQL 中的 SUM 函数那样的 PRODUCT 函数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/403924/
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
is there a PRODUCT function like there is a SUM function in Oracle SQL?
提问by EvilTeach
I have a coworker looking for this, and I don't recall ever running into anything like that.
我有一个同事在找这个，我不记得遇到过这样的事情。
Is there a reasonable technique that would let you simulate it?
是否有一种合理的技术可以让您模拟它？
SELECT PRODUCT(X)
FROM
(
SELECT 3 X FROM DUAL
UNION ALL
SELECT 5 X FROM DUAL
UNION ALL
SELECT 2 X FROM DUAL
)
would yield 30
将产生 30
回答by tuinstoel
select exp(sum(ln(col)))
from table;
edit:
编辑：
if col always > 0
如果 col 总是 > 0
回答by Amy B
DECLARE @a int
SET @a = 1
-- re-assign @a for each row in the result
-- as what @a was before * the value in the row
SELECT @a = @a * amount
FROM theTable
There's a way to do string concat that is similiar:
有一种方法可以进行类似的字符串连接：
DECLARE @b varchar(max)
SET @b = ""
SELECT @b = @b + CustomerName
FROM Customers
回答by Eric Sabine
Here's another way to do it. This is definitely the longer way to do it but it was part of a fun project.
这是另一种方法。这绝对是更长的方法，但它是一个有趣项目的一部分。
You've got to reach back to school for this one, lol. They key to remember here is that LOG is the inverse of Exponent.
你必须回到学校才能找到这个，哈哈。这里要记住的关键是 LOG 是指数的倒数。
LOG10(X*Y) = LOG10(X) + LOG10(Y)
LOG10(X*Y) = LOG10(X) + LOG10(Y)
or
或者
ln(X*Y) = ln(X) + ln(Y) (ln = natural log, or simply Log base 10)
ln(X*Y) = ln(X) + ln(Y)（ln = 自然对数，或简单地以 10 为底的对数）
Example
If X=5 and Y=6
示例
如果 X=5 且 Y=6
X * Y = 30
X * Y = 30
ln(5) + ln(6) = 3.4
ln(5) + ln(6) = 3.4
ln(30) = 3.4
ln(30) = 3.4
e^3.4 = 30, so does 5 x 6
e^3.4 = 30，所以 5 x 6
EXP(3.4) = 30
经验（3.4）= 30
So above, if 5 and 6 each occupied a row in the table, we take the natural log of each value, sum up the rows, then take the exponent of the sum to get 30.
所以上面，如果5和6分别占据了表中的一行，我们取每个值的自然对数，将行相加，然后取总和的指数，得到30。
Below is the code in a SQL statement for SQL Server. Some editing is likely required to make it run on Oracle. Hopefully it's not a big difference but I suspect at least the CASE statement isn't the same on Oracle. You'll notice some extra stuff in there to test if the sign of the row is negative.
下面是 SQL Server 的 SQL 语句中的代码。可能需要进行一些编辑才能使其在 Oracle 上运行。希望这不是一个很大的区别，但我怀疑至少 CASE 语句在 Oracle 上是不一样的。你会注意到那里有一些额外的东西来测试行的符号是否为负。
CREATE TABLE DUAL (VAL INT NOT NULL)
INSERT DUAL VALUES (3)
INSERT DUAL VALUES (5)
INSERT DUAL VALUES (2)
SELECT
CASE SUM(CASE WHEN SIGN(VAL) = -1 THEN 1 ELSE 0 END) % 2
WHEN 1 THEN -1
ELSE 1
END
* CASE
WHEN SUM(VAL) = 0 THEN 0
WHEN SUM(VAL) IS NOT NULL THEN EXP(SUM(LOG(ABS(CASE WHEN SIGN(VAL) <> 0 THEN VAL END))))
ELSE NULL
END
* CASE MIN(ABS(VAL)) WHEN 0 THEN 0 ELSE 1 END
AS PRODUCT
FROM DUAL
回答by Shane Delmore
There are many different implmentations of "SQL". When you say "does sql have" are you referring to a specific ANSI version of SQL, or a vendor specific implementation. DavidB's answer is one that works in a few different environments I have tested but depending on your environment you could write or find a function exactly like what you are asking for. Say you were using Microsoft SQL Server 2005, then a possible solution would be to write a custom aggregator in .net code named PRODUCT which would allow your original query to work exactly as you have written it.
“SQL”有许多不同的实现。当您说“sql 是否具有”时，您指的是特定的 ANSI 版本的 SQL，还是供应商特定的实现。DavidB 的答案适用于我测试过的几种不同环境，但根据您的环境，您可以编写或找到完全符合您要求的功能。假设您使用的是 Microsoft SQL Server 2005，那么可能的解决方案是在 .net 代码中编写一个名为 PRODUCT 的自定义聚合器，这将使您的原始查询完全按照您编写的方式工作。
回答by user3704628
In c# you might have to do:
在 c# 中，您可能必须执行以下操作：
SELECT EXP(SUM(LOG([col])))
FROM table;
回答by Lukas Eder
The accepted answer by tuinstoelis correct, of course:
在通过tuinstoel接受的答案是正确的，当然是：
select exp(sum(ln(col)))
from table;
But notice that if colis of type NUMBER, you will find tremendousperformance improvement when using BINARY_DOUBLEinstead. Ideally, you would have a BINARY_DOUBLEcolumn in your table, but if that's not possible, you can still cast colto BINARY_DOUBLE. I got a 100x improvement in a simple test that I documented here, for this cast:
但请注意，如果col是类型NUMBER，你会发现巨大的使用时的性能提升BINARY_DOUBLE，而不是。理想情况下，您BINARY_DOUBLE的表中应该有一列，但如果不可能，您仍然可以强制转换col为BINARY_DOUBLE. 我在此处记录的一个简单测试中获得了 100 倍的改进，对于这个演员：
select exp(sum(ln(cast(col as binary_double))))
from table;
