Hi,
We have a table where we want to device the weighted average for a price column using below formula,
sum( qty * price) / sum (qty)
We have few rows where any or all of the columns i.e. price or qty can be NULL.
We tried to write the function as below however, it doesn't complete even after 30 minutes, where as earlier to adding this function the query could been completed in 25 secs.
Please note that we are dealing with large data i.e. the table could have 50 million records. It has proper indexing & every other performance improvement components required.
We know we have done something wrong in creating below function, however we are unable to resolve it,
CREATE OR REPLACE FUNCTION "WEIGHTEDAVGPRICE" (PCOLNAME NUMBER)RETURN NUMBER
IS
TEMP NUMBER(26,13);
BEGIN
SELECT SUM(QUANTITY * PCOLNAME)/SUM(QUANTITY)INTO TEMP FROM CHARGE ;
RETURN TEMP;
END WEIGHTEDAVGPRICE;
Here we send the parameter PCOLNAME stands for the name of the price column for which we want to do a weighted average.
We know we could achieve this without adding a function, however we need to have then CASE statements in our SELECT clause. We use toplink to query the database, in this case it will be hard to covert.
Also, we want this function to be used at some other places as well.
Thanks,
@Bhagyesh Bhatewara