- Joined
- Jan 5, 2005
I originally posted my problem on stack exchange, but it got buried pretty quickly. I'm hoping a db person can help me out. Thanks in advance!
http://stackoverflow.com/questions/40062572/mysql-group-by-existence-between-two-datetimes
Copied here:
I have a database from a fake online marketplace. Entries have the item name, price, and a timestamp for when the item was added to the marketplace, and when it was removed (or sold) from the marketplace.
What I'd like to do is find the minimum price of a given item on the market over time.
I thought of two solutions: one is to change the database so that each item gets an entry every 10 minutes, for example, then do something like
This would substantially increase the database size, though.
Another alternative would be to do one query per time point that I'm interested in:
I'm guessing there's a mysql command for looping a query (though I don't know what it is). This option seems like it will take a very long time for large data sets.
Is there a more elegant solution?
Data would look something like:
Output:
http://stackoverflow.com/questions/40062572/mysql-group-by-existence-between-two-datetimes
Copied here:
I have a database from a fake online marketplace. Entries have the item name, price, and a timestamp for when the item was added to the marketplace, and when it was removed (or sold) from the marketplace.
What I'd like to do is find the minimum price of a given item on the market over time.
I thought of two solutions: one is to change the database so that each item gets an entry every 10 minutes, for example, then do something like
Code:
SELECT min(`price`)
FROM `market.auctions`
WHERE `name` = 'itemname'
GROUP BY `timestamp`
ORDER BY `timestamp`
This would substantially increase the database size, though.
Another alternative would be to do one query per time point that I'm interested in:
Code:
SELECT min(`price`)
FROM `market.auctions`
WHERE `name` = 'itemname' AND
`timestamp_added` <= '2016-10-15 12:15:00' AND
`timestamp_removed` > '2016-10-15 12:15:00'
I'm guessing there's a mysql command for looping a query (though I don't know what it is). This option seems like it will take a very long time for large data sets.
Is there a more elegant solution?
Data would look something like:
Code:
time_added,time_removed,price
"2016-09-27 23:05:00","2016-09-27 23:55:00",350000
"2016-09-27 23:05:00","2016-09-27 23:45:00",340000
"2016-09-27 23:10:00","2016-09-27 23:35:00",330000
"2016-09-27 23:15:00","2016-09-27 23:20:00",320000
"2016-09-27 23:15:00","2016-09-27 23:25:00",350000
"2016-09-27 23:20:00","2016-09-27 23:25:00",300000
Output:
Code:
time, min price
"2016-09-27 23:05:00",340000
"2016-09-27 23:10:00",330000
"2016-09-27 23:15:00",320000
"2016-09-27 23:20:00",300000
"2016-09-27 23:25:00",330000
"2016-09-27 23:30:00",330000
"2016-09-27 23:35:00",340000
"2016-09-27 23:40:00",340000
"2016-09-27 23:45:00",350000