聚合乘积函数 [英] Aggregate multiplicate function

问题描述

我有一张下表:

1    X    10
2    X    30
3    Y    5
4    Y    2
...etc

我需要将其转换为:

X    300   //(10 * 30)
Y    10    //(5 * 2)

所以我实际上是在寻找一种我可以使用的求和函数，例如sum，avg和东西……这存在吗?

so I'm in fact looking for a kind of a multiplication function that I could use like sum, avg and stuff... does this exist guys?

例如:

select field2, **multiply**(field3)
from t
group by field2

谢谢

推荐答案

理想情况下，Access SQL具有可用的PRODUCT聚合函数，但没有.但是，我们可以通过记住我们在学校(或不...)上了解的对数知识，并记住对数和的反对数等于乘积来模拟它:

Ideally, Access SQL would have a PRODUCT aggregate function available, but it doesn't. We can however simulate it by remembering what we learned about logarithms at school (or not...), and remembering that the anti-log of the sum of logs is equal to the product:

SELECT field2, EXP(Sum(LOG(Field3))) AS ProductOfField3
FROM t
GROUP BY Field2

请注意，如果有一个零值，那么真正的PRODUCT函数只会为一个组返回0，而如果有零值，则此解决方案将失败，因此请当心那.此外，如果存在任何负值，此方法将行不通.

Note that whereas a true PRODUCT function would simply return 0 for a group if there are any zero values, this solution will fail if there are any zero values, so watch out for that. Also, this approach won't work if there are any negative values.

要处理零，我们可以这样做:

To deal with zeroes we could do this:

SELECT
    field2, 
    EXP(Sum(LOG(IIf(Field3 = 0, 1, Field3)))) AS ProductOfField3,
    MIN(ABS(Field3)) AS MinOfAbsField3
FROM t
GROUP BY Field2

，然后忽略MinOfAbsField3为零的任何行的ProductOfField3值(因为这表示包含零的组，因此真"乘积应为0)

and then disregard the ProductOfField3 value for any row where MinOfAbsField3 is zero (as this indicates a group containing a zero, thus the 'true' product should be 0)

要处理负值，我们可以进一步这样做:

To deal with negative values we could further do this:

SELECT
    field2, 
    EXP(Sum(LOG(IIf(Field3 = 0, 1, ABS(Field3))))) AS ProductOfField3,
    MIN(ABS(Field3)) AS MinOfAbsField3,
    SUM(IIf(Field3 < 0, 1, 0)) AS SumOfNegativeIndicator
FROM t
GROUP BY Field2

并使用以下规则解释结果:

and interpret the results with these rules:

• 如果MinOfAbsField3为零，则忽略该行的ProductOfField3-乘积为零
• 否则，给定行的必需答案为ProductOfField3，否定，如果该行中的SumOfNegativeIndicator是奇数

• If MinOfAbsField3 is zero, disregard ProductOfField3 for that row - the product is zero
• Otherwise, the required answer for a given row is ProductOfField3, negated if SumOfNegativeIndicator is odd in that row

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