Month: October 2013

Find a multiplication product on a numeric column.

Zahar HilkevichLeave a comment

Long ago I was challenged to write a query that would get me a product of all numerical values in a column. I was given a hint to use a logarithmic function which made things fairly simple.

Off course, you can develop a custom group function, though at the time I was challenged with this puzzle, such feature was not yet available.

A solution presented below is based on the well known basic math fact that a sum of logarithms is a logarithm of the product:

Log(a) + Log(b) = Log(a*b)

In Oracle syntax it is:

log(n, a) + log(n, b) = log(n, a*b)

Where n is a basis and has to be positive and not equal to 1. If we want to use decimal logarithms, we will use n=10.

Since we can use SQL sum function, we can get a sum of all logarithm values in the column (the only requirement is that all the arguments must be positive) which will be equal to the logarithm of the product (this is what we want!). Getting the logarithm’s argument is also simple – we just need to take the number 10 (or any other basis n of the logarithm that we use) to the power of the logarithm value:

a*b = Power(10, Log(10, a*b))

For the illustration, let’s get a product on all deptno values from dept table (scott schema).

SELECT POWER(10, SUM(LOG(10,deptno))) product
FROM dept

Result:

 PRODUCT
--------
  240000

If you need to multiply both, negative and positive numbers, you can first multiply absolute values and then figure out which sign the product has based on the number of negative multipliers.

First, let’s simulate such table:

SELECT 2-level AS num_value
FROM dual
WHERE level!=2
CONNECT BY level<6

Result:

NUM_VALUE
---------
        1
       -1
       -2
       -3

Solution:

WITH x AS (
SELECT 2-level AS num_value
FROM dual
WHERE level!=2
CONNECT BY level<6
)
SELECT POWER(-1, COUNT(DECODE(SIGN(num_value),-1,1)) ) AS "Sign", 
       POWER(10.0, SUM(LOG(10,ABS(num_value)))) AS "Product"
FROM x

Result:

      Sign    Product
---------- ----------
        -1          6

Here is a workaround:

WITH x AS (
SELECT 2-level                                  AS num_value,
       COUNT(DECODE(SIGN(2-level),-1,1)) OVER() AS prod_sign
FROM dual
WHERE level!=2
CONNECT BY level<6
)
SELECT POWER(-1, prod_sign) AS "Sign",
       CASE WHEN MIN(ABS(num_value))=0 THEN 0
            ELSE POWER(10, SUM(LOG(10,ABS(num_value))))
       END AS "Product"
FROM x
GROUP BY prod_sign

Result:

      Sign    Product
---------- ----------
        -1          6

If you don’t know if the column values contain zeros, which would automatically mean that the entire product is also zero, check it first using case function.

To implement that check, it would be easier to use the last workaround and modify the with clause:

WITH x AS (
SELECT 2-level                                  AS num_value,
       COUNT(DECODE(SIGN(2-level),-1,1)) OVER() AS prod_sign,
       DECODE(2-level,0,NULL,2-level)           AS no_zero_value
FROM dual
--WHERE level!=2
CONNECT BY level<6
)
SELECT CASE WHEN MIN(ABS(num_value))=0 THEN 0
            ELSE POWER(-1, prod_sign)*POWER(10, SUM(LOG(10,ABS(no_zero_value))))
       END AS product
FROM x
GROUP BY prod_sign