2

I have a simple table called that contains share prices in MySQL:

Table `share_prices`

+----------+-------+---------------------+
| stock_id | price | date                |    
+----------+-------+---------------------+    
|        1 |  0.05 | 2010-02-24 01:00:00 |
|        2 |  3.25 | 2010-02-24 01:00:00 |
|        3 |  3.30 | 2010-02-24 01:00:00 |
|        1 |  0.50 | 2010-02-23 23:00:00 |
|        2 |  1.90 | 2010-02-23 23:00:00 | 
|        3 |  2.10 | 2010-02-23 23:00:00 |
|        1 |  1.00 | 2010-02-23 19:00:00 |
|        2 |  1.00 | 2010-02-23 19:00:00 | 
|        3 |  1.00 | 2010-02-23 19:00:00 | 
+----------+-------+---------------------+

Every time a share price is updated, a new row is inserted into the table.

With this structure, how can I return a query that shows the price change in the last 24 hours?

The desired result would be:

+----------+------+------+------------+
| stock_id | then | now  | difference |
+----------+------+------+------------+    
|        3 | 1.00 | 3.30 |       2.30 |
|        2 | 1.00 | 3.25 |       2.25 |
|        1 | 1.00 | 0.05 |      -0.95 |
+----------+------+------+------------+

What's the best way to go about this? Some kind of join? A sub-query?

What I think I'm aiming for is to essentially query once to get then, query again to get now and then somehow glue it all together at the end.

Edit: I need to account for negative changes too.

Philip Morton
  • 129,733
  • 38
  • 88
  • 97

1 Answers1

7

Ok, got home, and was able to figure this out.

SELECT stock_id, t1.price AS `then`, t2.price AS `now`, ROUND(t2.price - t1.price, 2) AS `difference`
FROM (
        SELECT stock_id, price, date FROM share_prices sp
        WHERE  date = (SELECT MIN(date) FROM share_prices sp2
                       WHERE date BETWEEN '2010/02/23 10:00:00'
                       AND '2010/02/24 10:00:00'
                       AND sp2.stock_id = sp.stock_id)
    ) t1
    JOIN
    (
        SELECT stock_id, price, date FROM share_prices sp
        WHERE  date = (SELECT MAX(date) FROM share_prices sp2
                       WHERE date BETWEEN '2010/02/23 10:00:00'
                       AND '2010/02/24 10:00:00'
                       AND sp2.stock_id = sp.stock_id)
    ) t2 USING(stock_id)
ORDER BY `difference` DESC

Uses the results from 2 subqueries, each with their own subquery to the first and last, respectively, record for that range.

I was using integer for stock_id, float for price and timestamp for date, since there may be issues (notably with the MIN and MAX) with other data types.

Tarka
  • 4,041
  • 2
  • 22
  • 33
  • I don't think this is porting well (I don't know Oracle enough). Your subqueries are returning the most recent record and the oldest record (with a time period). But this therefore only returns two records (which don't tie up). This could just be the way I've adapted it though ... – Amadiere Feb 24 '10 at 15:43
  • I'm pretty sure that this is a correct solution, but on my 20,000+ row table, the query never completes before it times out. That's not really your problem though, it's mine. On a smaller table, it should work fine. – Philip Morton Feb 25 '10 at 09:40