I have database with stock prices. I want to query for a list of prices for every symbol at a specific time. ie. What was the last price value 24 hours ago.
I can do it with individual symbols but it doesn’t make sense when I want to populate a table with 200 symbols.
[select from database where time < now() - ‘24h’ AND “symbol”=xxx ORDER BY time DESC LIMIT 1’]
How can I get a single price for every symbol with 1 query?
I can think of two ways to do this, the first is using the symbols as a tag:
stocks,ticker=APPL price=50
stocks,ticker=GOOG price=42
Then you could have a query like select last(price) from stocks where time < now() group by ticker.
The other method would be to store as fields like:
stocks appl=50,goog=42
Then you can use a query like your example:
select * from stocks where time < now() - '24h' order by time desc limit 1
If you go the second route you might need to aggregate over a short period in case not all samples are at the same time.
