• Welcome to Overclockers Forums! Join us to reply in threads, receive reduced ads, and to customize your site experience!

Need some help with mysql

Overclockers is supported by our readers. When you click a link to make a purchase, we may earn a commission. Learn More.

kayson

Member
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

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
 

Leegit

Member
Joined
Apr 4, 2013
Location
Kansas
Create a new table that stores the product key along with the minimum/maximum price and their respective timestamps. Anytime that a produce is added, check its value again the minimum and maximum. If it is outside the range, then update the field with the price and the timestamp.
 

su root

Senior Member, --, I teach people how to read your
Joined
Aug 25, 2001
Location
Ontario, Canada
If you just want the info from your current dataset, you don't need the group by/order by:
Code:
    SELECT min(`price`)
    FROM `market.auctions`
    WHERE `name` = 'itemname'

If you are looking to use the database for a new site, keep all of the active information in one table, but use a second table for historical price information. Then you need the 'group by' clause when querying the historical table because you'll have multiple rows for the same product.