query [message #423412] |
Thu, 24 September 2009 01:31 |
shaz
Messages: 182 Registered: June 2009
|
Senior Member |
|
|
How can we get the product of the field in a table having multiple rows without using the loop.
for example:
I have this field in table test
a
100
200
300
then result should come -100*200*300= 6000000
i want to know as i was asked this ques in an interview.
|
|
|
Re: query [message #423413 is a reply to message #423412] |
Thu, 24 September 2009 01:33 |
|
Michel Cadot
Messages: 68704 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
There are many answers.
What did you try, search, find?
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code , use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.
Post a working Test case: create table and insert statements along with the result you want with these data.
Regards
Michel
[Updated on: Thu, 24 September 2009 01:34] Report message to a moderator
|
|
|
Re: query [message #423432 is a reply to message #423413] |
Thu, 24 September 2009 03:19 |
shaz
Messages: 182 Registered: June 2009
|
Senior Member |
|
|
I have tried searching on the net but cant find it.
Test Case:
create table test_product(
id number(2),
test_no number);
insert into test_product values( 1, 20);
insert into test_product values( 2, 10);
insert into test_product values( 3, 40);
insert into test_product values( 4, 10);
Expected result: product of test_no i.e -80000 from a single select query.
|
|
|
Re: query [message #423434 is a reply to message #423432] |
Thu, 24 September 2009 03:34 |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
That is a interesting case. What came to my mind first, was Tom Kyte's user defined STRAGG aggregate function.
I have adapted that to generate a "product" aggregate function:
--- Create Aggregate Type
CREATE OR REPLACE TYPE product_agg_type
AS OBJECT
(
total number,
STATIC FUNCTION
ODCIAggregateInitialize(sctx IN OUT product_agg_type )
RETURN NUMBER,
MEMBER FUNCTION
ODCIAggregateIterate(self IN OUT product_agg_type ,
value IN number )
RETURN NUMBER,
MEMBER FUNCTION
ODCIAggregateTerminate(self IN product_agg_type,
returnValue OUT number,
flags IN number)
RETURN NUMBER,
MEMBER FUNCTION
ODCIAggregateMerge(self IN OUT product_agg_type,
ctx2 IN product_agg_type)
RETURN NUMBER
);
/
--- Create Aggregate Type Body
CREATE OR REPLACE TYPE BODY product_agg_type
IS
STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT product_agg_type)
RETURN NUMBER
IS
BEGIN
sctx := product_agg_type( 1 );
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateIterate(self IN OUT product_agg_type,
value IN number )
RETURN NUMBER
IS
BEGIN
self.total := self.total * value;
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateTerminate(self IN product_agg_type,
returnValue OUT number,
flags IN number)
RETURN NUMBER
IS
BEGIN
returnValue := self.total;
return ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateMerge(self IN OUT product_agg_type,
ctx2 IN product_agg_type)
RETURN NUMBER
IS
BEGIN
self.total := self.total * ctx2.total;
return ODCIConst.Success;
END;
end;
/
-- Create aggregate function
CREATE OR REPLACE FUNCTION product(input number)
RETURN number
PARALLEL_ENABLE AGGREGATE USING product_agg_type;
/
Then you can use "product" like any other aggregate function:
SQL> create table test_product(
2 id number(2),
3 test_no number);
Table created.
SQL>
SQL> insert into test_product values( 1, 20);
1 row created.
SQL> insert into test_product values( 1, 30);
1 row created.
SQL> insert into test_product values( 2, 10);
1 row created.
SQL> insert into test_product values( 3, 40);
1 row created.
SQL> insert into test_product values( 3, 40);
1 row created.
SQL> insert into test_product values( 4, 10);
1 row created.
SQL>
SQL> SELECT product(test_no) FROM test_product;
PRODUCT(TEST_NO)
----------------
96000000
SQL>
SQL> SELECT id, product(test_no) FROM test_product GROUP BY id;
ID PRODUCT(TEST_NO)
---------- ----------------
1 600
2 10
3 1600
4 10
SQL>
|
|
|
|
|
Re: query [message #423438 is a reply to message #423434] |
Thu, 24 September 2009 03:49 |
shaz
Messages: 182 Registered: June 2009
|
Senior Member |
|
|
thanks Thomas, but the answer which I am looking for is something like we do for the summation of fields in a row by using cube/rollup.
|
|
|
Re: query [message #423441 is a reply to message #423438] |
Thu, 24 September 2009 03:55 |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
Well, when you are not able ask for what you really want in the first place, then you have to take what you get.
|
|
|
Re: query [message #423442 is a reply to message #423412] |
Thu, 24 September 2009 03:57 |
|
Michel Cadot
Messages: 68704 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
SQL> create table test_product (
2 id number(2),
3 test_no number);
Table created.
SQL> insert into test_product values (1, 20);
1 row created.
SQL> insert into test_product values (2, 10);
1 row created.
SQL> insert into test_product values (3, 40);
1 row created.
SQL> insert into test_product values (4, 10);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test_product order by 1;
ID TEST_NO
---------- ----------
1 20
2 10
3 40
4 10
4 rows selected.
SQL> select round(exp(sum(ln(test_no)))) prod from test_product;
PROD
----------
80000
1 row selected.
SQL> col prod new_value prod
SQL> select substr(max(sys_connect_by_path(test_no,'*')),2) prod
2 from test_product
3 connect by prior id = id - 1
4 start with id = 1
5 /
PROD
-----------
20*10*40*10
1 row selected.
SQL> select &prod prod from dual
2 /
PROD
----------
80000
1 row selected.
SQL> select prod
2 from ( select id, val prod
3 from test_product
4 model
5 dimension by (id)
6 measures (test_no, 1 val)
7 rules
8 ( val[any] order by id = nvl(val[cv()-1],1) * test_no[cv()] )
9 order by id desc
10 )
11 where rownum = 1
12 /
PROD
----------
80000
1 row selected.
Regards
Michel
[Updated on: Thu, 24 September 2009 03:57] Report message to a moderator
|
|
|
Re: query [message #423444 is a reply to message #423438] |
Thu, 24 September 2009 04:03 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Aha! A chance to use my degree!!!
You want Logarithms. You can use Logarithms and simple addition to perform multiplication:
Log(100*200*300) = Log(100) + Log(200) + Log(300)
You can implement this in Oracle like this:create table test_071 (col_1 number);
insert into test_071 values (100);
insert into test_071 values (200);
insert into test_071 values (300);
select power(10,sum(log(10,col_1))) from test_071;
[Drat - beaten to it]
[Updated on: Thu, 24 September 2009 04:05] Report message to a moderator
|
|
|
|
Re: query [message #423470 is a reply to message #423447] |
Thu, 24 September 2009 09:36 |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Please explain why all of a sudden you seem to be over-excited, while you dismissed ThomasG's reply as not useful.
|
|
|
Re: query [message #423490 is a reply to message #423470] |
Thu, 24 September 2009 11:27 |
|
Michel Cadot
Messages: 68704 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
We didn't (all) dismiss it and didn't (all) say it is not useful. It is just one way among several (as I said).
It is useful to show the usage of custom aggregate function but in the wrong place to show its usefulness. I also had to support such dismissal each time I posted a custom aggregate function (but once in Metalink forum many years ago) including the same "prod" function, maybe because the code is quite hard to read, understand how it works and needs some effort.
Regards
Michel
|
|
|