0

I've been working with AWS Athena recently, and I needed to multiply the values of a particular column (all of which were positive). Seeing as SUM(column1) is a readily available aggregate function in Presto (which is what the Athena query engine is built on top of), I was hoping I could find something similar that instead returned a product.

Unfortunately, as spelled out in this question, even SQL doesn't have such a thing. Which means... surprise surprise, neither does Presto! So, knowing there's no built in solution, how could we make a multiplicative aggregate operator?

Piotr Findeisen
  • 19,480
  • 2
  • 52
  • 82
yungblud
  • 388
  • 4
  • 17

1 Answers1

2

Turns out there's a super quick and easy hack! Harkening back to high school math class, given some {a0, a1, a2, ..., an} where all of the ai > 0 (THIS IS A BIG DEAL! We need all of the inputs to be positive, otherwise the natural logarithm is undefined!), we know that

exp(ln(a0) + ln(a1) + ... + ln(an)) = exp(ln(a0))exp(ln(a1))...exp(ln(an)) = a0a1a2...an

where ln(x) is the natural logarithm of x and exp(x) is euler's number raised to the power x. But this is precisely what we want! Even better, presto has the functions exp(), sum(), and ln() built in, so all we have to do to is write 

SELECT exp(sum(ln(column1))) AS product FROM yourtable;

and voila!

yungblud
  • 388
  • 4
  • 17