Asking for help with operating on data


#1

I have a few thousand rows of stock market data - one row for each
day. I’m using ActiveRecord to perform database operations.

I’m interested in performing a calculation on each row while
incorporating the result of the calculation on the previous row, and
then once the calculation has been performed for all rows, perform a
new calculation that uses the individual calculations of each row.

I figure that I’ll start with an array of size 3,000. Then I would
probably want to iterate over it, possibly saving the result of the
operation of each row to a new array but this area is a little foggy.

Any suggestions?


#2

eggman2001 wrote:

I have a few thousand rows of stock market data - one row for each
day. I’m using ActiveRecord to perform database operations.

I’m interested in performing a calculation on each row while
incorporating the result of the calculation on the previous row
[…]
I figure that I’ll start with an array of size 3,000. Then I would
probably want to iterate over it, possibly saving the result of the
operation of each row to a new array but this area is a little foggy.

Any suggestions?

Well, some ideas:

  • Get the whole dataset (or each batch of 3000) into memory with one
    query before operating. No sense in making the database bottleneck any
    worse than it has to be.

  • Find a way to do the calculations on the DB side, using aggregate
    functions and/or (gasp!) stored procedures. This will probably be
    easier if you’re using PostgreSQL or Oracle, since mySQL’s procedural
    language is so weak as to be unusable for non-trivial work.

  • Provide more detail and we might be able to help more.

Best,

Marnen Laibow-Koser
http://www.marnen.org
removed_email_address@domain.invalid


#3

Marnen - I’d rather do it in Ruby instead of on the database side. In
case my description was unclear, I’d like to iterate over an array,
and for each iteration to be able to access a value determined in the
previous iteration.

Wisccal - that looks like it’s close to what I’m looking to do.
However, I don’t understand the enumerable#each_with_object so I
probably need to do more reading in my ruby book.

On May 30, 2:47 am, Wisccal W. removed_email_address@domain.invalid


#4

eggman2001 wrote:

I have a few thousand rows of stock market data - one row for each
day. I’m using ActiveRecord to perform database operations.

I’m interested in performing a calculation on each row while
incorporating the result of the calculation on the previous row, and
then once the calculation has been performed for all rows, perform a
new calculation that uses the individual calculations of each row.

I figure that I’ll start with an array of size 3,000. Then I would
probably want to iterate over it, possibly saving the result of the
operation of each row to a new array but this area is a little foggy.

Any suggestions?

Assuming you want to calculate something like percent change for each
day, you could load the entire result set into an array, and then do
something like:

changes = @stock_data.each_with_object([]) do |data, array|
last_value = array.last.value
change = (data.value - last_value) / last_value * 100 unless
last_value.zero?
array << [data.transaction_date, change]
end