in reply to (OT) Dearest Monks - Mysql Question - Compare current insert record with last record inserted.

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.
  • Comment on Re: (OT) Dearest Monks - Mysql Question - Compare current insert record with last record inserted.
  • Download Code

Replies are listed 'Best First'.
Re^2: (OT) Dearest Monks - Mysql Question - Compare current insert record with last record inserted.
by jdlev (Scribe) on Nov 19, 2009 at 18:29 UTC
    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]
        I actually used the epoch as the unique ID...will that make all this easier?
        I love it when a program comes together - jdhannibal
[reply]

In Section Seekers of Perl Wisdom