SQL 中的乘法聚合运算符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5416169/
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
Multiplication aggregate operator in SQL
提问by maephisto
In SQL there are aggregation operators, like AVG, SUM, COUNT. Why doesn't it have an operator for multiplication? "MUL" or something.
在 SQL 中有聚合运算符，如 AVG、SUM、COUNT。为什么它没有乘法运算符？“MUL”什么的。
I was wondering, does it exist for Oracle, MSSQL, MySQL ? If not is there a workaround that would give this behaviour?
我想知道，它是否存在于 Oracle、MSSQL、MySQL 中？如果没有，是否有一种解决方法会导致这种行为？
回答by RichardTheKiwi
By MUL do you mean progressive multiplication of values?
MUL 是指值的渐进乘法吗？
Even with 100 rows of some small size (say 10s), your MUL(column) is going to overflow any data type! With such a high probability of mis/ab-use, and very limited scope for use, it does not need to be a SQL Standard. As others have shown there are mathematical ways of working it out, just as there are many many ways to do tricky calculations in SQL just using standard (and common-use) methods.
即使有 100 行的小尺寸（比如 10s），您的 MUL(column) 也会溢出任何数据类型！由于误用/滥用的概率如此之高，而且使用范围非常有限，因此它不需要是 SQL 标准。正如其他人所表明的，有数学方法可以解决这个问题，就像在 SQL 中使用标准（和常用）方法进行棘手计算的方法很多一样。
Sample data:
样本数据：
Column
1
2
4
8
COUNT : 4 items (1 for each non-null)
SUM : 1 + 2 + 4 + 8 = 15
AVG : 3.75 (SUM/COUNT)
MUL : 1 x 2 x 4 x 8 ? ( =64 )
For completeness, the Oracle, MSSQL, MySQL core implementations *
为完整起见，Oracle、MSSQL、MySQL 核心实现 *
Oracle : EXP(SUM(LN(column))) or POWER(N,SUM(LOG(column, N)))
MSSQL : EXP(SUM(LOG(column))) or POWER(N,SUM(LOG(column)/LOG(N)))
MySQL : EXP(SUM(LOG(column))) or POW(N,SUM(LOG(N,column)))
- Care when using EXP/LOG in SQL Server, watch the return type http://msdn.microsoft.com/en-us/library/ms187592.aspx
- The POWER form allows for larger numbers (using bases larger than Euler's number), and in cases where the result grows too large to turn it back using POWER, you can return just the logarithmic value and calculate the actual number outside of the SQL query
- 在 SQL Server 中使用 EXP/LOG 时要小心，注意返回类型http://msdn.microsoft.com/en-us/library/ms187592.aspx
- POWER 形式允许更大的数字（使用大于欧拉数的基数），并且在结果增长太大而无法使用 POWER 将其返回的情况下，您可以仅返回对数值并计算 SQL 查询之外的实际数字
* LOG(0) 和 LOG(-ve) 未定义。下面仅显示如何在 SQL Server 中处理此问题。可以找到其他 SQL 风格的等效项，使用相同的概念
create table MUL(data int)
insert MUL select 1 yourColumn union all
select 2 union all
select 4 union all
select 8 union all
select -2 union all
select 0
select CASE WHEN MIN(abs(data)) = 0 then 0 ELSE
EXP(SUM(Log(abs(nullif(data,0))))) -- the base mathematics
* round(0.5-count(nullif(sign(sign(data)+0.5),1))%2,0) -- pairs up negatives
END
from MUL
Ingredients:
原料：
- taking the abs() of data, if the min is 0, multiplying by whatever else is futile, the result is 0
- When data is 0, NULLIF converts it to null. The abs(), log() both return null, causing it to be precluded from sum()
- If data is not 0, abs allows us to multiple a negative number using the LOG method - we will keep track of the negativity elsewhere
- Working out the final sign
- sign(data) returns
1 for >0,0 for 0and-1 for <0. - We add another 0.5 and take the sign() again, so we have now classified 0 and 1 both as 1, and only -1 as -1.
- again use NULLIF to remove from COUNT() the 1's, since we only need to count up the negatives.
% 2against the count() of negative numbers returns either- --> 1 if there is an odd number of negative numbers
- --> 0 if there is an even number of negative numbers
- more mathematical tricks: we take 1 or 0 off 0.5, so that the above becomes
- --> (
0.5-1=-0.5=>round to -1) if there is an odd number of negative numbers - --> (
0.5-0= 0.5=>round to 1) if there is an even number of negative numbers - we multiple this final 1/-1 against the SUM-PRODUCT value for the real result
- sign(data) returns
- 取数据的abs()，如果min为0，再乘以其他无意义的，结果为0
- 当数据为 0 时，NULLIF 将其转换为 null。abs(), log() 都返回 null，导致它被排除在 sum() 之外
- 如果数据不为 0，abs 允许我们使用 LOG 方法乘以负数 - 我们将在其他地方跟踪负数
- 制定最终标志
- sign(data) 返回
1 for >0,0 for 0和-1 for <0。 - 我们再添加 0.5 并再次使用 sign()，因此我们现在将 0 和 1 都归类为 1，只有 -1 归类为 -1。
- 再次使用 NULLIF 从 COUNT() 中删除 1，因为我们只需要计算负数。
% 2针对负数的 count() 返回要么- --> 1 如果有奇数个负数
- --> 0 如果有偶数个负数
- 更多数学技巧：我们从 0.5 中减去 1 或 0，这样上面的就变成了
- --> (
0.5-1=-0.5=>round to -1) 如果有奇数个负数 - --> (
0.5-0= 0.5=>round to 1) 如果有偶数个负数 - 我们将最终的 1/-1 与 SUM-PRODUCT 值相乘以获得真实结果
- sign(data) 返回
回答by Konerak
No, but you can use Mathematics :)
不，但你可以使用数学:)
if yourColumnis always bigger than zero:
如果yourColumn总是大于零：
select EXP(SUM(LOG(yourColumn))) As ColumnProduct from yourTable
回答by Rob van Wijk
I see an Oracle answer is still missing, so here it is:
我看到仍然缺少 Oracle 答案，所以这里是：
SQL> with yourTable as
2 ( select 1 yourColumn from dual union all
3 select 2 from dual union all
4 select 4 from dual union all
5 select 8 from dual
6 )
7 select EXP(SUM(LN(yourColumn))) As ColumnProduct from yourTable
8 /
COLUMNPRODUCT
-------------
64
1 row selected.
Regards,
Rob.
问候，
罗布。
回答by gbn
You'll break any datatype fairly quickly as numbers mount up.
随着数字的增加，您将很快打破任何数据类型。
Using LOG/EXP is trickybecause of numbers <= 0 that will fail when using LOG. I wrote a solution in this questionthat deals with this
使用 LOG/EXP 很棘手，因为数字 <= 0 在使用 LOG 时会失败。我在这个问题中写了一个解决方案来解决这个问题
回答by Geeklab
With PostgreSQL, you can create your own aggregate functions, see http://www.postgresql.org/docs/8.2/interactive/sql-createaggregate.html
使用 PostgreSQL，您可以创建自己的聚合函数，请参阅http://www.postgresql.org/docs/8.2/interactive/sql-createaggregate.html
To create an aggregate function on MySQL, you'll need to build an .so (linux) or .dll (windows) file. An example is shown here: http://www.codeproject.com/KB/database/mygroupconcat.aspx
要在 MySQL 上创建聚合函数，您需要构建一个 .so (linux) 或 .dll (windows) 文件。此处显示了一个示例：http: //www.codeproject.com/KB/database/mygroupconcat.aspx
I'm not sure about mssql and oracle, but i bet they have options to create custom aggregates as well.
我不确定 mssql 和 oracle，但我敢打赌他们也可以选择创建自定义聚合。
回答by LINQ2Vodka
Using CTE in MS SQL:
在 MS SQL 中使用 CTE：
CREATE TABLE Foo(Id int, Val int)
INSERT INTO Foo VALUES(1, 2), (2, 3), (3, 4), (4, 5), (5, 6)
;WITH cte AS
(
SELECT Id, Val AS Multiply, row_number() over (order by Id) as rn
FROM Foo
WHERE Id=1
UNION ALL
SELECT ff.Id, cte.multiply*ff.Val as multiply, ff.rn FROM
(SELECT f.Id, f.Val, (row_number() over (order by f.Id)) as rn
FROM Foo f) ff
INNER JOIN cte
ON ff.rn -1= cte.rn
)
SELECT * FROM cte
回答by Rob Williams
Not sure about Oracle or sql-server, but in MySQL you can just use *like you normally would.
不确定 Oracle 或 sql-server，但在 MySQL 中，您可以*像往常一样使用。
mysql> select count(id), count(id)*10 from tablename;
+-----------+--------------+
| count(id) | count(id)*10 |
+-----------+--------------+
| 961 | 9610 |
+-----------+--------------+
1 row in set (0.00 sec)
