From: "Rhino" <rhino1@stripped>
> > I need to extract a list which the following values from this table, for
> > each hour (in date_time field):
> > - symbol
> > - min(price)
> > - max(price)
> > - price where date_time is the earliest for that certain hour.
> > - price where the date_time is the last from that hour.
> > - The sum of volume from that hour.
> > I have tried to get the list of symbols, then get each hourly period and
> > calculate those 6 values for each period, but there are many symbols and
> > very many periods, and it takes very very much time.
> > Is there a more intelligent way of getting those values in another way
> > than
> > symbol by symbol and period by period?
> It's hard to answer your question since you haven't given us any examples
> the SQL you've already tried. You haven't told us which version of MySQL
> are using, either. That makes a big difference since newer versions offer
> many more SQL capabilities like views and subqueries that could really
> You certainly shouldn't have to write separate queries for each different
> symbol that you are using!
Here is the table definition. The table is simple, but what I want is
CREATE TABLE `tickers` (
`symbol` varchar(20) NOT NULL,
`last_volume` bigint(20) unsigned default NULL,
`last_price` decimal(20,4) unsigned default NULL,
`last_update` datetime default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
I want to get a list of values for more periods of time, 5 minutes, 15
minutes, and hourly.
I need to get:
date_format(last_update, '%Y-%m-%d') as date
date_format(last_update, '%H:%i:%s') as time
min(last_price) as low (The min value of last_price for that period)
max(last_price) as high (the max price from that period)
last_price as open (where last_update=min(last_update) from that period)
last_price as close (where last_update=max(last_update) from that period)
The result data should look something like:
... then here follow the rest of records for simb1 and for other symbols.
You may see that the first "time" is 10:15:00, the next time is 10:30:00,
the next is 10:45, so the period of time is 15 minutes.
The first "low" is the lowest price between 10:15:00 and 10:30:00 and the
"high" is the highest price in that period.
The first "open" value is the "last_price" of the first trade from that
period and the "close" price is the last_price of the latest trade from that
I don't know if MySQL can create a query that can get those values fast
Thank you very much.