Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2002
    Posts
    15

    Answered: Recursion Function

    Given a fictional table such as : (ID INTEGER, VALUE DEC(16,10) )
    that contains multiple IDs, each with a varying number of up to say 10 value records per ID.

    I need to do some probability math that requires multiplying each of the VALUE records by the next, grouped by ID.
    So if ID = 5 and contains 3 records being :
    0.2494747899
    0.281512605
    0.3159138655

    I need to be able to calculate (0.2494747899*0.281512605*0.3159138655) returning a result of : 5,0.022186724
    I can kind of figure how to do so but only for individual IDs and having to execute a recursion UDF, that has to be executed for each ID.

    Is there a way using say a built in scalar function of sorts to do so over the entire table in one single SQL statement ?

    Many thanks, Fin.

  2. Best Answer
    Posted by mark.b

    "If there is no such a requirement to use a recursion, you can use some useful math from the school like this:
    Code:
    select id, power(10, sum(log10(value)))
    from my_table
    group by id
    The idea used here is that the logarithm of a product is the sum of logarithms of the factors."


  3. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    If there is no such a requirement to use a recursion, you can use some useful math from the school like this:
    Code:
    select id, power(10, sum(log10(value)))
    from my_table
    group by id
    The idea used here is that the logarithm of a product is the sum of logarithms of the factors.
    Regards,
    Mark.

  4. #3
    Join Date
    Oct 2002
    Posts
    15
    Hi Mark,

    WOW ! .. heh I thought there might have been something that would be able to do what I had hoped, but had no idea what it was and would never have come up with such an elegant and apparently straight forward solution. I really must return to school obviously I must have missed this lesson .. heh

    Thanks again, perfect.

  5. #4
    Join Date
    Nov 2004
    Posts
    67
    Elegant solution, Mark,

    Sometimes you have to step outside of your usual ways to get a solution. You figured out how to do it.

    Ferdinand

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •