Need some help with mysql

kayson · Oct 16, 2016

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 · Oct 17, 2016

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 · Nov 6, 2016

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.

Need some help with mysql

kayson

Member

Leegit

Member

su root

Senior Member, --, I teach people how to read your

Similar threads