[ACCEPTED]-is there a PRODUCT function like there is a SUM function in Oracle SQL?-oracle
select exp(sum(ln(col))) from table;
if col always > 0
DECLARE @a int SET @a = 1 -- re-assign @a for each row in the result -- as what @a was before * the value in the row SELECT @a = @a * amount FROM theTable
There's a way to do string concat that is 1 similiar:
DECLARE @b varchar(max) SET @b = "" SELECT @b = @b + CustomerName FROM Customers
Here's another way to do it. This is definitely 19 the longer way to do it but it was part 18 of a fun project.
You've got to reach back 17 to school for this one, lol. They key to 16 remember here is that LOG is the inverse 15 of Exponent.
LOG10(X*Y) = LOG10(X) + LOG10(Y)
ln(X*Y) = ln(X) + ln(Y) (ln 14 = natural log, or simply Log base 10)
If 13 X=5 and Y=6
X * Y = 30
ln(5) + ln(6) = 3.4
ln(30) = 3.4
e^3.4 12 = 30, so does 5 x 6
EXP(3.4) = 30
So 11 above, if 5 and 6 each occupied a row in 10 the table, we take the natural log of each 9 value, sum up the rows, then take the exponent 8 of the sum to get 30.
Below is the code in 7 a SQL statement for SQL Server. Some editing 6 is likely required to make it run on Oracle. Hopefully 5 it's not a big difference but I suspect 4 at least the CASE statement isn't the same 3 on Oracle. You'll notice some extra stuff 2 in there to test if the sign of the row 1 is negative.
CREATE TABLE DUAL (VAL INT NOT NULL) INSERT DUAL VALUES (3) INSERT DUAL VALUES (5) INSERT DUAL VALUES (2) SELECT CASE SUM(CASE WHEN SIGN(VAL) = -1 THEN 1 ELSE 0 END) % 2 WHEN 1 THEN -1 ELSE 1 END * CASE WHEN SUM(VAL) = 0 THEN 0 WHEN SUM(VAL) IS NOT NULL THEN EXP(SUM(LOG(ABS(CASE WHEN SIGN(VAL) <> 0 THEN VAL END)))) ELSE NULL END * CASE MIN(ABS(VAL)) WHEN 0 THEN 0 ELSE 1 END AS PRODUCT FROM DUAL
The accepted answer by tuinstoel is correct, of course:
select exp(sum(ln(col))) from table;
But notice that 5 if
col is of type
NUMBER, you will find tremendous performance 4 improvement when using
BINARY_DOUBLE instead. Ideally, you 3 would have a
BINARY_DOUBLE column in your table, but 2 if that's not possible, you can still cast 1
BINARY_DOUBLE. I got a 100x improvement in a simple test that I documented here, for this cast:
select exp(sum(ln(cast(col as binary_double)))) from table;
Is there a reasonable technique that would 7 let you simulate it?
One technique could 6 be using
LISTAGG to generate product_expression 5 string and
GETXMLTYPE to evaluate it:
WITH cte AS ( SELECT grp, LISTAGG(l, '*') AS product_expression FROM t GROUP BY grp ) SELECT c.*, s.val AS product_value FROM cte c CROSS APPLY( SELECT * FROM XMLTABLE('/ROWSET/ROW/*' PASSING dbms_xmlgen.getXMLType('SELECT ' || c.product_expression || ' FROM dual') COLUMNS val NUMBER PATH '.') ) s;
+------+---------------------+---------------+ | GRP | PRODUCT_EXPRESSION | PRODUCT_VALUE | +------+---------------------+---------------+ | b | 2*6 | 12 | | a | 3*5*7 | 105 | +------+---------------------+---------------+
More 4 roboust version with handling single NULL 3 value in the group:
WITH cte AS ( SELECT grp, LISTAGG(l, '*') AS product_expression FROM t GROUP BY grp ) SELECT c.*, s.val AS product_value FROM cte c OUTER APPLY( SELECT * FROM XMLTABLE('/ROWSET/ROW/*' passing dbms_xmlgen.getXMLType('SELECT ' || c.product_expression || ' FROM dual') COLUMNS val NUMBER PATH '.') WHERE c.product_expression IS NOT NULL ) s;
*CROSS/OUTER APPLY(Oracle 2 12c) is used for convenience and could be 1 replaced with nested subqueries.
This approach could be used for generating different aggregation functions.
There are many different implmentations 13 of "SQL". When you say "does sql have" are 12 you referring to a specific ANSI version 11 of SQL, or a vendor specific implementation. DavidB's 10 answer is one that works in a few different 9 environments I have tested but depending 8 on your environment you could write or find 7 a function exactly like what you are asking 6 for. Say you were using Microsoft SQL Server 5 2005, then a possible solution would be 4 to write a custom aggregator in .net code 3 named PRODUCT which would allow your original 2 query to work exactly as you have written 1 it.
In c# you might have to do:
SELECT EXP(SUM(LOG([col]))) FROM table;
More Related questions