jdlev has asked for the wisdom of the Perl Monks concerning the following question:

I am struggling with a basic routine (well, for many of you it would probably be basic, but for me it is advanced). Program Overview:
I created a program that looks at the world market's financial data, and every 5 minutes add's this information to a mysql database. Easy enough right?

The hard part is I want to now upgrade this mysql database so that it monitors the *changes* in price from one time period to the next.

So here is the basic idear:

1) Program inputs epoch, timestamp, last price, and now, price change into mysql database.
2) 5 minutes goes by, this program goes out on the web and collects the same data. Only this time, it looks at the previous record to get the price that was entered 5 minutes before.
3) It runs a price change percentage equation (p2 - p1)/p1, and inserts that along with the latest price into my table.
4) Repeat

I am inserting about 9 new record sets everytime this routine runs, so I would like to keep it as short and sweet as possible. I know I could run a select statement and query the database to get "lastinsert id", but I'm not sure if that is the fastest way to do it. Thanks for any advice!

I love it when a program comes together - jdhannibal
  • Comment on (OT) Dearest Monks - Mysql Question - Compare current insert record with last record inserted.

Replies are listed 'Best First'.
Re: (OT) Dearest Monks - Mysql Question - Compare current insert record with last record inserted.
by moritz (Cardinal) on Nov 19, 2009 at 15:25 UTC
    You can do the calculation all in SQL. For example you can run a sub-select that queries the last value with something like 
    (SELECT stock FROM yourtable ORDER BY timestamp DESC LIMIT 1) as last_
+value

    [download]

    and use that to compute the difference for inserting.

    But the fact that such a simple thing looks rather complicated in SQL points into another direction: design smell. Storing both original data (the stock prices) and derived data (the difference) in the same table makes that rather hard.

    Maybe there's a better way, like storing the derived data in view and let mysql update it automatically.

    Perl 6 - links to (nearly) everything that is Perl 6.
[reply]
[d/l]
      So you guys think the smarter way to go would be to simply have the program calculate the percent change on the fly rather than have a value inserted into a table. I suppose that makes a lot more sense. I'm sure it would save a heck of a lot of space over the long run. I'm not sure I understand what you're saying though? So I could acutally create an SQL Select statement that will pull the values from record A and record B, and print the value? Or do I use the select statement to assign 2 separate values to two variables and do it that way?
      I love it when a program comes together - jdhannibal
[reply]
        Yes - unless there's a HUGE calculation penalty you really don't want to store data that can be calculated.

        (For a simple example, suppose your code glitches and stores several incorrect prices. Easy enough to fix, just get the correct prices and replace. If you're storing calculated price differences, you also need to recalculate. OTOH, if you had a view that did the calculation for you, once you've updated the price the view is automatically fixed.)

        You use SQL to compare the prices between rows. Personally, I'd use that SQL to create a view. A good example of such SQL is at MySQL Cookbook.

        Update:

        If your tables do not have a unique ID column (other than the timestamp), you may need somewhat more awkward SQL. For example, if a table has only two column - timestamp and price - the SQL might be: 

        select new.timestamp, new.price,
       new.price - old.price as price_change
from tablename new
left join tablename old
on old.timestamp = (
  select max(sub.timestamp)
  from tablename sub
  where sub.timestamp < new.timestamp
)

        [download]
[reply]
[d/l]
Re: (OT) Dearest Monks - Mysql Question - Compare current insert record with last record inserted.
by keszler (Priest) on Nov 19, 2009 at 15:19 UTC
    Is the program running once every five minutes, i.e. via cron or some such, or does it run continuously and check for online data every 5 minutes? I suspect the former; if the latter the program could just keep the last price in memory.

    If your database is huge and slow, there might be less overhead in opening and reading a file for the data. There are various modules for this on CPAN.

    But the biggest question I have is WHY? If I understand correctly, you're proposing to store on row 2 a calculation based on field X on row 1 and 2. Why store what can easily be calculated?

[reply]

Back to Seekers of Perl Wisdom