Multiplication across rows and Analytical Functions.

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.

multi

To explore more on Analytical function check here. and here

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Helper SQL, Oracle Internal, SQL Server Learning and tagged , , , , , . Bookmark the permalink.

One Response to Multiplication across rows and Analytical Functions.

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s