Oracle or SQL Server provide Aggregation functions for MIN/MAX/COUNT/SUM, but suppose a scenario when we want multiplication of rows across a partition or complete table.

Database does not provide any buildin function to achieve same.

Suppose we have below expectations, we need multiplication of a column data within a partition column scope and increase as partition scope increase.

As Date increase multiplier scope also increase.

To understand it better, please check below expected output

ITEM DATE_TIME MULTIPLER EXPECTED_OUTPUT

----- --------- ---------- -----------------

ITEM1 14-NOV-16 1 1

*ITEM1 21-NOV-16 2 2 --> 1*2
ITEM1 28-NOV-16 3 6 --> 1*2*3
ITEM1 05-DEC-16 4 24 --> 1*2*3*4
ITEM1 12-DEC-16 5 120 --> 1*2*3*4*5
ITEM1 19-DEC-16 6 720 --> 1*2*3*4*5*6
ITEM1 26-DEC-16 7 5040 --> 1*2*3*4*5*6*7
ITEM1 02-JAN-17 8 40320 --> 1*2*3*4*5*6*7*8*

ITEM2 14-NOV-16 9 9

ITEM2 21-NOV-16 10 90

ITEM2 28-NOV-16 11 990

ITEM2 05-DEC-16 12 11880

ITEM2 12-DEC-16 13 154440

ITEM2 19-DEC-16 14 2162160

ITEM2 26-DEC-16 15 32432400

ITEM2 02-JAN-17 16 518918400

As Date increase for a Item, Multiplication scope also increase based on ascending date.

For E.g. for Item1 with date 02-JAN-2017, expected output should be multiplication of all rows data within Item1 scope.

Before moving towards solution to achieve same, lets explore some mathematical function Oracle or SQL Server provides.

1. Exponential function : EXP

It return e raised to Nth Power, i.e. “e^N”

`SELECT 'Exponential Raised to Natural log e (2.71828183)' AS EXPLANATION , ROUND(EXP(2),2) FROM DUAL;`

`EXPLANATION ROUND(EXP(2),2)`

----------------------------------------------------------------------

Exponential Raised to Natural log e (2.71828183) 7.39

2. Natural Log function : (Oracle :: LN // SQL SERVER : LOG)

It return natural log of input values.

`SELECT 'Natural log with base as e (2.71828183) ' AS EXPLANATION , ROUND(LN(7.39),2) FROM DUAL;`

`EXPLANATION ROUND(LN(7.39),2)`

---------------------------------------- ------------------

Natural log with base as e (2.71828183) 2

Log is exponential factor with base as e.

i.e. e^2 := 7.39

Log of 7.39 with base as e := 2.

Reason to explore above mathematical function is to get our multiplication factor using log and exp formula.

e^x * e^y := e^(x+y)

i.e. log(x*y) := log(x) + log(y) (natural log)

example ::

`for 10*10 we will use logarithm and exponential as below.`

log(10) + log (10) := 2.302 + 2.302 := 4.604

later exp(4.604) := (2.71828183)^4.604 := 100 approximate.

For more information, please refer below link.

Multiplying Numbers Using Log Tables

After a bit of mathematics, lets see database code to achieve multiplication of data.

**SELECT A.*,
ROUND(EXP(SUM(LN(ABS(A.MULTIPLER))) OVER (PARTITION BY ITEMNAME ORDER BY DATE_TIME)),2) EXPECTED_OUTPUT
FROM MULTI_ANALYTICAL A;**

Basically we are using SUM of LOG and later using EXP with rounding function.

Using SUM of LN and later applying EXP, our expected output is multiplication of data.

And its beauty increase with help of analytical function.

To explore more on Analytical function check here. and here

Pingback: Multiplication of Rows Data and Analytical Functions. — Oracle Insight and Performance concepts – SutoCom Solutions