CODEWITHSUNDEEP
mysqlsql
zing
zing

Reputation: 61

MYSQL Query : How to get values per category?

I have huge table with millions of records that store stock values by timestamp. Structure is as below:

Stock, timestamp, value

goog,1112345,200.4

goog,112346,220.4

Apple,112343,505

Apple,112346,550

I would like to query this table by timestamp. If the timestamp matches,all corresponding stock records should be returned, if there is no record for a stock for that timestamp, the immediate previous one should be returned. In the above ex, if I query by timestamp=1112345 then the query should return 2 records: 

  goog,1112345,200.4

  Apple,112343,505 (immediate previous record)

I have tried several different ways to write this query but no success & Im sure I'm missing something. Can someone help please.

Upvotes: 0

Views: 152

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I don't think there is an easy way to do this query. Here is one approach:

select tprev.*
from (select t.stock,
             (select timestamp from t.stock = s.stock and timestamp <= <whatever> order by timestamp limit 1
             ) as prevtimestamp
      from (select distinct stock
            from t
           ) s
    ) s join
    t tprev
    on s.prevtimestamp = tprev.prevtimestamp and s.stock = t.stock

This is getting the previous or equal timestamp for the record and then joining it back in. If you have indexes on (stock, timestamp) then this may be rather fast.

Another phrasing of it uses group by:

select tprev.*
from (select t.stock,
             max(timestamp) as prevtimestamp
      from t
      where timestamp <= YOURTIMESTAMP
      group by t.stock
    ) s join
    t tprev
    on s.prevtimestamp = tprev.prevtimestamp and s.stock = t.stock

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562328

SELECT `Stock`, `timestamp`, `value`
FROM `myTable`
WHERE `timestamp` = 1112345
  UNION ALL
SELECT `Stock`, `timestamp`, `value`
FROM `myTable`
WHERE `timestamp` < 1112345
ORDER BY `timestamp` DESC
LIMIT 1

Upvotes: 1

hd1
hd1

Reputation: 34657

select Stock, timestamp, value from thisTbl where timestamp = ? and fill in timestamp to whatever it should be? Your demo query is available on this fiddle

Upvotes: 0

Related Questions