SQL 中的乘法聚合运算符 [英] Multiplication aggregate operator in SQL

问题描述

在 SQL 中有聚合运算符，例如 AVG、SUM、COUNT.为什么它没有乘法运算符?MUL"什么的.

In SQL there are aggregation operators, like AVG, SUM, COUNT. Why doesn't it have an operator for multiplication? "MUL" or something.

我想知道，它是否存在于 Oracle、MSSQL、MySQL 中?如果没有，是否有解决方法会导致这种行为?

I was wondering, does it exist for Oracle, MSSQL, MySQL ? If not is there a workaround that would give this behaviour?

推荐答案

MUL 是指值的渐进乘法吗?

By MUL do you mean progressive multiplication of values?

即使有 100 行的小尺寸(比如 10 秒)，您的 MUL(列)也会溢出任何数据类型！由于误用/滥用的可能性如此之高，而且使用范围非常有限，因此它不需要是 SQL 标准.正如其他人所展示的，有数学方法可以解决这个问题，就像在 SQL 中使用标准(和常用)方法进行棘手计算的方法有很多一样.

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.

示例数据:

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 )

为了完整起见，Oracle、MSSQL、MySQL 核心实现 *

For completeness, the Oracle, MSSQL, MySQL core implementations *

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)))

• 在 SQL Server 中使用 EXP/LOG 时要小心，注意返回类型 http://msdn.microsoft.com/en-us/library/ms187592.aspx
• POWER 形式允许更大的数(使用大于欧拉数的基数)，并且如果结果变得太大而无法使用 POWER 将其返回，您可以只返回对数值并计算除 Euler 数之外的实际数SQL 查询

<小时>* LOG(0) 和 LOG(-ve) 未定义.下面仅显示如何在 SQL Server 中处理此问题.可以找到其他 SQL 风格的等效项，使用相同的概念

---

* LOG(0) and LOG(-ve) are undefined. The below shows only how to handle this in SQL Server. Equivalents can be found for the other SQL flavours, using the same concept

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

成分:

• 取数据的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=>四舍五入到-1)如果有奇数个负数
  • -->(0.5-0= 0.5=>舍入到1)如果有偶数个负数
  • 我们将最终的 1/-1 与实际结果的 SUM-PRODUCT 值相乘
  • 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 0 and -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.
    • % 2 against 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

    这篇关于SQL 中的乘法聚合运算符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！