Row Data Multiplication in Oracle

Aggregate functions return a single result row based on a group of rows. This differentiates them from Single-Row functions which act on each row. These functions are extensively used with the GROUP BY clause in SQL statements. AVG (), COUNT (), SUM () … are few aggregate functions which are quite commonly used. Today, one of my colleague asked me if there is some aggregation function for Multiplication. I thought about it for a while and found myself surprised that I have never thought about doing such a thing :)

So, How do we do the multiplication then? I tried it but just couldn’t do it in SQL. So, I asked this question on our internal self help channel and I got a pretty impressive reply:
“Using a mathematical approach…”

After understanding the solution, I was surprisingly happy with the simplicity of the approach and found it worth sharing. Let’s assume that we have a table “tbl” with one column “num”. This table has three rows having values 2, 3 & 4 for column “num”.

WITH tbl AS
     (SELECT 2 num
        FROM DUAL
      UNION
      SELECT 3 num
        FROM DUAL
      UNION
      SELECT 4 num
        FROM DUAL)
SELECT num
  FROM tbl;
 
We need the multiplication of row’s data for this column. So essentially, we are looking for an aggregate function MUL (num).

There is no such function as MUL () in Oracle (I actually tried using it). Here comes the computational part of the puzzle. A multiplication operation can be mathematically expressed as:

MUL (num) = EXP (SUM (LN (num)))

Not very clear at first, I agree. Lets review the maths behind it:

x = (2 * 3 * 4)
ln(x) = ln(2 * 3 * 4)
ln(x) = ln(2) + ln(3) + ln(4) => SUM(LN(num))
ln(x) = .693 + 1.098 + 1.386
ln(x) = 3.178
x = e (3.178) => EXP(SUM(LN(num)))
x = 24


And that’s it. We just created our own multiplication function and now the result can be calculated as:
  WITH tbl AS
     (SELECT 2 num
        FROM DUAL
      UNION
      SELECT 3 num
        FROM DUAL
      UNION
      SELECT 4 num
        FROM DUAL)
SELECT EXP (SUM (LN (num))) MUL
  FROM tbl;
 
Result: 24

Everything looks perfect. But hey, I have got negative values. The moment you put a negative value in the dataset, you are bound to get the following Oracle error:

“ORA-01428: argument ‘x’ is out of range”

This is because the range for LN () argument is > 0. But this is now easy to handle, here is how:

WITH tbl AS
     (SELECT -2 num
        FROM DUAL
      UNION
      SELECT -3 num
        FROM DUAL
      UNION
      SELECT -4 num
        FROM DUAL),
     sign_val AS
     (SELECT CASE MOD (COUNT (*), 2)
                WHEN 0 THEN 1
                ELSE -1
             END val
        FROM tbl
       WHERE num < 0)
SELECT   EXP (SUM (LN (ABS (num)))) * val
    FROM tbl, sign_val
GROUP BY val
 
Result: -24

So, we first counted the negative records in the table. If the count is odd, the final result should be negative and vice versa. We then multiplied this signed value with the multiplication of the absolute values. A subquery can also be used instead of GROUP BY but that’s trivial. Now the solution is complete and we are able to handle the negative values too.

Comments

Post a Comment

Popular posts from this blog

Swift Learning Roadmap

Image
Recently one of my email subscribers wrote me this: What I find is that I’m learning the various steps but I do not understand the “big picture”. For me, a guideline, would be helpful. I know Swift is available but I do not even know object oriented programming. I’d really like to see a “roadmap” that says: if you know or do not know this, start here and learn this, then this, etc. He is not alone. Talking to people and reading comments online I found that many share the same feeling. There is a huge amount of information about iOS development and so many things to learn. It is easy to get lost and not know what is important to know and what should be learned first. I know that feeling myself. When I started adventuring into Mac OS X programming in 2005 (the iPhone did not exist yet) I found myself in the same situation. Back then, resources were scarcer than nowadays. I relied a lot on Apple’s documentation and started venturing into the world of Cocoa and...
Read more

How to Apply for an iOS Developer Job

This is the first part of a three part series on applying and interviewing for an iOS developer job. In this first part of the series, you will focus on the applying part of the equation – i.e. preparing your resume and cover letter. In this second part of the series, Ray will show you a few examples of some iOS resumes and cover letters he likes that might be useful for reference or models. In the third part of the series, you will focus on the interviewing part of the equation – i.e. what types of questions to expect and prepare for, and how to do your best in an interview. Note that none of these articles will focus on how to get the required iOS skills (although that might be a good follow-up article some day). Let’s assume you’re already a seasoned iOS developer. After all, if you’re reading this blog, that’s a good sign that honing your iOS skills is important to you! :] Also, none of these articles will focus on where to find iOS jobs (although that’s another...
Read more

Objective-C Guide For Developers, Part 1

When, as a developer, you come from another language to Objective-C, you usually want to map the general concepts about programming you already know to this new language that at first might seems obscure. I remember myself being confused by the Objective-C syntax when I started learning it. What at first look might not make sense actually does a lot when you get a grasp of it and in my opinion (and the opinion of many other developers) makes the code much more readable. What at first sight might seem complicated is just something a bit different from what you are used to, thus feeling unfamiliar. This guide is intended for developers who already know object oriented programming and goes over all the concepts you need to understand in order to use the language and learn programming for iOS and OS X. It does not assume, though, that you know already C as other guides do. Many times, when people approach the language the first time, they get said to study C first....
Read more