1 2 Previous Next 16 Replies Latest reply: Aug 20, 2014 1:32 AM by user13450767 RSS

    Need a user defined function to get weighted average

    user13450767

      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



        • 1. Re: Need a user defined function to get weighted average
          BluShadow

          The reason for the slower performance when using the function is that you are calling the function from within a query, and that is causing a context switch between the SQL and PL/SQL engines.  That is known to slow down performance.  If you can do the task in SQL alone, you should do, in order to avoid context switching.

           

          It would be helpful if you provided some example data and expected output, as well as indicating your database version, so that people can understand and help you better.

           

          Please read:

          Re: 2. How do I ask a question on the forums?

          • 2. Re: Need a user defined function to get weighted average
            Frank Kulash

            Hi, Bhagyesh,

             

            In addition to what Blushadow said, if column names are not hard-coded, then you must use dynamic SQL.  If you really want to use a function, then you probably want somehting like this:

             

            CREATE OR REPLACE FUNCTION    weightedavgprice

            (   pcolname  VARCHAR2

            )

            RETURN NUMBER

            IS

                sql_txt   VARCHAR2 (100);

                temp      NUMBER;

            BEGIN

                sql_txt := 'SELECT  SUM (quantity * '

                        || pcolname

                        || ') / SUM (quantity) FROM charge';

                dbms_output.put_line (sql_txt || ' = sql_txt in weightavgprice');  -- Debugging only

                EXECUTE IMMEDIATE  sql_txt

                INTO  temp;

             

             

                RETURN temp;

            END  weightedavgprice;

            • 3. Re: Need a user defined function to get weighted average
              user13450767

              Thank you all for your responses. We are using the oracle 11g database.
              Below is sample data,

               

              qtypriceamount
              1000.253325.33
              1010.253425.5934
              1020.253525.857
              1030.253626.1208
              1040.253726.3848
              1050.253826.649
              1060.253926.9134
              1070.25427.178
              1080.254127.4428
              1090.254227.7078
              1100.254327.973
              1110.254428.2384
              1120.254528.504
              1130.254628.7698
              1140.254729.0358

               

              So we want to achieve the output for weighted average price as below,

              407.698/1605 = 0.254017

              Hope this helps you to answer. Let me know if you need more information.

               

              Frank,

               

              I will try function mentioned by you & let you know how it works.

              • 4. Re: Need a user defined function to get weighted average
                Frank Kulash

                Hi,

                user13450767 wrote:

                 

                 

                ... I will try function mentioned by you & let you know how it works.

                If it doesn't, see the link that Blushadow gave in reply #1 above:

                Re: 2. How do I ask a question on the forums?

                • 5. Re: Need a user defined function to get weighted average
                  user13450767

                  Thanks Frank,

                   

                  I've tried the function provided by you. It does give me output within considerable time. However, doesn't give me correct output.

                  Below is my data & expected output & actual output.

                    

                  QUANTITYPRICE
                  19027356.00000000000000.0228600000000
                  19016417.00000000000000.0228600000000
                  19012769.00000000000000.0228600000000
                  19020063.00000000000000.0228600000000
                  19012769.00000000000000.0228600000000
                  19029180.00000000000000.0228600000000
                  19010946.00000000000000.0228600000000
                  19020063.00000000000000.0228600000000
                  18987242.00000000000000.0228600000000
                  18996359.00000000000000.0228600000000
                  19014593.00000000000000.0228600000000
                  19010946.00000000000000.0228600000000
                  19021886.00000000000000.0228600000000
                  19012769.00000000000000.0228600000000
                  19010946.00000000000000.0228600000000
                  19012769.00000000000000.0228600000000
                  19007300.00000000000000.0228600000000
                  18989066.00000000000000.0228600000000
                  18998183.00000000000000.0228600000000
                  19010946.00000000000000.0228600000000
                  19027356.00000000000000.0228600000000
                  19020063.00000000000000.0228600000000
                  19025534.00000000000000.0228600000000
                  19009122.00000000000000.0228600000000
                  19014593.00000000000000.0228600000000
                  19016417.00000000000000.0228600000000
                  19005476.00000000000000.0228600000000
                  19016417.00000000000000.0228600000000
                  19012769.00000000000000.0228600000000
                  19012769.00000000000000.0228600000000
                  19025534.00000000000000.0228600000000

                   

                  13473881.01/589408618 = 0.0228600000042755

                   

                  However, it gives me output 0.105521361271886.

                   

                  Looks like its not considering the filter criteria which is applied in main query. It's doing weighted average on all the table.

                  • 6. Re: Need a user defined function to get weighted average
                    BrendanP

                    Looks like its not considering the filter criteria which is applied in main query. It's doing weighted average on all the table.

                    Of course it is, and running a whole query execution at every row too, so no wonder it's slow. This is the wrong approach. The right approach is to just put the desired logic in the main query itself using analytics, like this:

                     

                    WITH test_data AS (

                        SELECT 100 qty, 0.2533 price FROM DUAL UNION

                        SELECT 101, 0.2534 FROM DUAL UNION

                        SELECT 102, 0.2535 FROM DUAL UNION

                        SELECT 103, 0.2536 FROM DUAL UNION

                        SELECT 104, 0.2537 FROM DUAL UNION

                        SELECT 105, 0.2538 FROM DUAL UNION

                        SELECT 106, 0.2539 FROM DUAL UNION

                        SELECT 107, 0.254 FROM DUAL UNION

                        SELECT 108, 0.2541 FROM DUAL UNION

                        SELECT 109, 0.2542 FROM DUAL UNION

                        SELECT 110, 0.2543 FROM DUAL UNION

                        SELECT 111, 0.2544 FROM DUAL UNION

                        SELECT 112, 0.2545 FROM DUAL UNION

                        SELECT 113, 0.2546 FROM DUAL UNION

                        SELECT 114, 0.2547 FROM DUAL

                    )

                    SELECT qty, price, Round (Sum( qty * price) OVER () / sum (qty) OVER (), 5) wtd_avg

                      FROM test_data

                    WHERE qty != 100

                    /

                    • 7. Re: Need a user defined function to get weighted average
                      user13450767

                      Thanks brendanp.

                       

                      We have the solution to use this in the query itself. However, there will be some rows where qty or price could be null. In this case we need to use CASE statements to achieve correct output. However, as I mentioned in my original thread, we use toplink. It will be again a task where we need to find out how the CASE statements can be included when you create the query using toplink.

                      The query could look like as below,

                       

                      Select qty,

                      case when SUM(qty) =0

                      THEN

                      0

                      ELSE

                      SUM(qty *price)/SUM(qty)

                        END

                      CASE

                      from tablename

                      where ....

                      group by....

                      • 8. Re: Need a user defined function to get weighted average
                        BrendanP

                        I doubt very much that that query is correct. Anyway, regarding toplink, which I don't know, worst case is you put the query in a view. No UDFs here.

                        • 9. Re: Need a user defined function to get weighted average
                          Chris Hunt

                          Give us some test data that we can work with, either in the form of INSERT statements, or (preferably) as a WITH statement as brendanp did. If your real data can contain NULLs, make sure your test data does too. Let us know what value you expect to get for this set of data and why, including how those NULL values should be treated.

                           

                          Then you might get some help.

                          • 10. Re: Re: Need a user defined function to get weighted average
                            Etbin

                            SUM(qty * price) / SUM(case when price is not null then qty end)


                            might be sufficient as sum skips null values


                            Regards


                            Etbin

                            • 11. Re: Need a user defined function to get weighted average
                              user13450767

                              Hello Everyone, thank you for your replies. It certainly helped me to take back our decision of having user defined function for weighted average. Instead we are going with CASE statements & including the formula in the main query to improve performance.

                              Below is the solution we are applying,

                               

                              Select qty,

                              case when SUM(qty) =0

                              THEN

                              0

                              ELSE

                              SUM(qty *price)/SUM(qty)

                                END

                              CASE

                              from tablename

                              where ....

                              group by....

                              • 12. Re: Re: Need a user defined function to get weighted average
                                Etbin

                                in your OP you stated: We have few rows where any or all of the columns i.e. price or qty can be NULL.

                                Consider this simple example:

                                 

                                with

                                quantity_price as

                                (select 10 qty,40 price from dual union all

                                select 50,null from dual union all

                                select null,null from dual union all

                                select null,100 from dual union all

                                select 40,35 from dual

                                )

                                select case when sum(qty) = 0

                                            then 0

                                            else sum(qty * price) / sum(qty)

                                       end avg_one,

                                       sum(qty * price) / sum(case when price is not null then qty end) avg_two

                                  from quantity_price

                                 

                                AVG_ONEAVG_TWO
                                1836

                                 

                                Based on the example above a story could be told: someone bought 10 pieces @ 40 units, next time he wanted to buy 50 pieces but there was no agreement about the price, a new meeting was set ending with no conclusions, after that he was willing to pay 100 units but no pieces were available and finally there was a new deal for 40 pieces @ 35 units.

                                How about the average price ? Which of the two should be considered correct ?

                                 

                                Regards

                                 

                                Etbin

                                • 13. Re: Need a user defined function to get weighted average
                                  user13450767

                                  Thanks for your reply.

                                  We are looking at weighted average than average.

                                  • 14. Re: Re: Need a user defined function to get weighted average
                                    Etbin

                                    The average weighted or not the problem doesn't go away.

                                    Dealing with null values it's a fact that calculating either averages column-wise (as you do) may give a different result than calculating the same let's call it row-wise (as I suggest).

                                     

                                    with

                                    quantity_price as

                                    (select 10 qty,40 price from dual union all

                                    select 50,null from dual union all

                                    select null,null from dual union all

                                    select null,100 from dual union all

                                    select 40,35 from dual

                                    )

                                    select case when sum(qty) = 0

                                                then 0

                                                else sum(qty * price) / sum(qty)

                                           end weighted_average_one,

                                           sum(qty * price) / sum(case when price is not null then qty end) weighted_average_two,

                                           avg(price) average_one,

                                           avg(case when qty is not null then price end) average_two     

                                      from quantity_price

                                     

                                    WEIGHTED_AVERAGE_ONEWEIGHTED_AVERAGE_TWOAVERAGE_ONEAVERAGE_TWO
                                    183658.333333333333333333333333333333333333337.5

                                     

                                    usually:

                                    average(1,2,3,4,5,6) = (1 + 2 + 3 + 4 + 5 + 6) / 6 = 3.5

                                    average(1,2,0,4,5,0) = (1 + 2 + 0 + 4 + 5 + 0) / 6 = 2.0

                                    average(1,2,null,4,5,null) = (1 + 2 + 4 + 5) / 4 = 3.0


                                    You must make a choice, I was just trying to draw your attention to.

                                     

                                    Regards

                                     

                                    Etbin

                                    1 2 Previous Next