获取行乘积(乘法) [英] Get rows product (multiplication)

问题描述

SO

问题

我对行乘法有问题.在SQL中，有一个SUM()函数，它计算一组行中某个字段的总和.我想乘法，即用于表

I have an issue with rows multiplication. In SQL, there is a SUM() function which calculates sum for some field for set of rows. I want to get multiplication, i.e. for table

+------+
| data |
+------+
|    2 |
|   -1 |
|    3 |
+------+

结果为的

.我正在使用 DOUBLE 数据类型来存储我的数据值.

that will be 2*(-1)*3 = -6 as a result. I'm using DOUBLE data type for storing my data values.

我的方法

从学校数学中学到，log(A x B) = log(A) + log(B)-可以用来创建所需的表达式，例如:

From school math it is known that log(A x B) = log(A) + log(B) - so that could be used to created desired expression like:

SELECT
  IF(COUNT(IF(SIGN(`col`)=0,1,NULL)),0,
    IF(COUNT(IF(SIGN(`col`)<0,1,NULL))%2,-1,1)
    *
    EXP(SUM(LN(ABS(`col`))))) as product
FROM `test`;

-在这里您会看到此方法的缺点-因为log(X)在X<=0时是未定义的-我需要在计算整个表达式之前计算负号.在此小提琴中中给出了示例数据和查询. 另一个缺点是，我们需要确定列值之间是否为0(因为它是一个样本，所以在实际情况下，我将为某些条件下的表行的子集选择乘积-即，我不能简单地从我的表格中删除0-s，因为结果零积对于某些行子集是有效且预期的结果)

-here you see weakness of this method - since log(X) is undefined when X<=0 - I need to count negative signs before calculating whole expression. Sample data and query for this is given in this fiddle. Another weakness is that we need to find if there is 0 among column values (Since it is a sample, in real situation I'm going to select product for some subset of table rows with some condition(s) - i.e. I can not simply remove 0-s from my table, because result zero product is a valid and expected result for some rows subsets)

具体

现在，最后，我的问题主要部分:当我们有如下表达式时如何处理情况:X*Y*Z和此处X < MAXF，Y<MAXF，但X*Y>MAXF和X*Y*Z<MAXF-这样我们就有了可能的数据类型溢出(此处MAXF是 double MySQL数据类型的限制).该示例位于此处.上面的查询效果很好，但是我始终可以确定它会正确处理吗? IE.当某些子产品引起溢出，但整个产品还可以(没有溢出)时，可能还有另一种发生溢出问题的情况.

And now, finally, my question main part: how to handle situation when we have expression like: X*Y*Z and here X < MAXF, Y<MAXF, but X*Y>MAXF and X*Y*Z<MAXF - so we have possible data type overflow (here MAXF is limit for double MySQL data type). The sample is here. Query above works well, but can I always be sure that it will handle that properly? I.e. may be there is another case with overflow issue when some sub-products causing overflow, but entire product is ok (without overflow).

或者可能还有另一种查找行产品的方法?另外，在表中可能有数百万条记录(主要是-1.1<X<=1.1，但可能具有100或1000之类的值，即如果乘以一定数量，则高到足以溢出 DOUBLE ，如果我们有一个问题我已经在上面描述过)-可能通过log计算会很慢吗?

Or may be there is another way to find rows product? Also, in table there possibly be millions of records (-1.1<X<=1.1 mainly, but probably with values such as 100 or 1000 - i.e. high enough to overflow DOUBLE if multiplied with certain quantity if we have an issue that I've described above) - may be calculating via log will be slow?

推荐答案

如果经常需要这种类型的计算，建议您将符号和对数存储在单独的列中.

If you need this type of calculations often, I suggest you store the signs and the logarithms in separate columns.

符号可以存储为1(对于正数)，-1(对于负数)和0(对于零).

The signs can be stored as 1 (for positives), -1 (for negatives) and 0 (for zero.)

对数可以指定为零，作为0(或任何其他值)，但不应在计算中使用.

The logarithm can be assigned for zero as 0 (or any other value) but it should not be used in calculations.

那么计算将是:

SELECT 
    CASE WHEN EXISTS (SELECT 1 FROM test WHERE <condition> AND datasign = 0)
         THEN 0
         ELSE (SELECT 1-2*(SUM(datasign=-1)%2) FROM test WHERE <condition>)
    END AS resultsign,

    CASE WHEN EXISTS (SELECT 1 FROM test WHERE <condition> AND datasign = 0)
         THEN -1            -- undefined log for result 0
         ELSE (SELECT SUM(datalog) FROM test WHERE <condition> AND datasign <> 0)
    END AS resultlog
  ;

这样，您就不会出现溢出问题.您可以检查resultlog是否超出某些限制，或者尝试计算resultdata = resultsign * EXP(resultlog)并查看是否抛出错误.

This way, you have no overflow problems. You can check the resultlog if it exceeds some limits or just try to calculate resultdata = resultsign * EXP(resultlog) and see if an error is thrown.

这篇关于获取行乘积(乘法)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！