Post reply

Update records using previous row and calculated value of current row.

  • February 9, 2023 at 2:15 pm

    #4147550

    Hi All,

    I am trying to update a table's column avg_gain based on previous avg_gain and current gain column for RSI calculation. I was able to implement this logic through cursor but it is time consuming, so was trying to see if there are other techniques to achieve it for having performance gains;

    The Formula for Average_Gain = ((Previous_Average_Gain *13) + Current_Gain)/14; Where, Previous_Average_Gain is the value of previous record; I was able to do it by using cursor, but it was taking long time around 2.5 mts for around 500,000 thousand records;

    I tried to use the row_number() and window functions but it seems something is missing. I am attaching an excel file, where I have marked row 17, and I would like to replicate similar functionality in sql server. Please let me know, if you need sample table and data as well.

    Thanks for your time.

     

     

     

     

    Attachments:
    You must be logged in to view attached files.

  • February 9, 2023 at 3:03 pm

    #4147591

    Look into LAG.

    https://learn.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-ver16

    You could also do a "rolling average" using AVG() OVER but it would likely be much more expensive to do it that way.

     

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 9, 2023 at 4:26 pm

    #4147649

    Hi Jeff,

    Thanks for the response. The Window functions cannot be used in update statements directly, so I used a different technique, whereby I had partitioned the data of unique stocks/dates with  ids in increasing order and then updated them by using variables through recursion technique.

    Thank you.

  • February 9, 2023 at 5:08 pm

    #4147691

    learnsql wrote:

    Hi Jeff,

    Thanks for the response. The Window functions cannot be used in update statements directly, so I used a different technique, whereby I had partitioned the data of unique stocks/dates with  ids in increasing order and then updated them by using variables through recursion technique.

    Thank you.

    The window functions can be used in a CTE in conjunction with an UPDATE statement.  If you're going to use LAG() for a rolling average, you'll also need to remove the gain from 14 days ago.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • February 9, 2023 at 5:43 pm

    #4147700

    Hi Drew,

    Thanks for that suggestion. For now, I was able to solve it via direct update through recursion option. Now, the performance has dramatically improved and takes less than 10 seconds to update >500,000 records compared to earlier iterative cursor update of around 2.5 mts.

    Thank you.

     

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply