SQL查询以查找同一列中值的乘积

Question

之前问过的面试问题。

给定一个包含列 boxName 和 value 的表格，找出每个盒子的体积。值字段具有立方体的长度、宽度和高度。我需要乘以所有树的维度。

如果需要计算总和，我可以使用groupby，但这里需要乘积

box1 = 12*13*1  = 156
box2 = 1*23*6   = 138
box3 = 12*1*20  = 240

+---------+-------+
| boxName | value |
+---------+-------+
| box1    |    12 |
| box1    |    13 |
| box1    |     3 |
| box2    |     1 |
| box2    |    23 |
| box2    |     6 |
| box3    |    12 |
| box3    |     1 |
| box4    |    30 |
+---------+-------+

Answer 1

试试这个

select EXP(SUM(LN(value))) As Product_val ,boxName
from yourTable
Group by boxName

注意: value <= 0 LOG 将失败。

当你的值 <= 0 时使用它。

SELECT
    boxName,
    CASE
       WHEN MinVal = 0 THEN 0
       WHEN Neg % 2 = 1 THEN -1 * EXP(ABSMult)
       ELSE EXP(ABSMult)
    END
FROM
    (
    SELECT
       boxName, 
       --log of +ve row values
       SUM(LN(ABS(NULLIF(Value, 0)))) AS ABSMult,
       --count of -ve values. Even = +ve result.
       SUM(SIGN(CASE WHEN Value < 0 THEN 1 ELSE 0 END)) AS Neg,
       --anything * zero = zero
       MIN(ABS(Value)) AS MinVal
    FROM
       yourTable
    GROUP BY
       boxName
    ) foo

引用自此answer