List:General Discussion« Previous MessageNext Message »
From:leo huang Date:February 14 2006 3:49am
Subject:Re: selecting min, max
View as plain text  
hi,

I think the following link would be some help for you!
http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html

Leo Huang

2006/2/14, Octavian Rasnita <orasnita@stripped>:
>
> Hi,
>
> 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
> of
> > the SQL you've already tried. You haven't told us which version of MySQL
> you
> > are using, either. That makes a big difference since newer versions
> offer
> > many more SQL capabilities like views and subqueries that could really
> help
> > you.
> >
> > 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
> complicated:
>
> 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:
>
> symbol
> 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:
>
> Symbol,data,time,low,high,open,close
> simb1,2006-02-08,10:15:00,1000,1200,1050,1150
> simb1,2006-02-08,10:30:00,1100,1150,1150,1150
> simb1,2006-02-08,10:45:00,1000,1200,1050,1150
> simb1,2006-02-08,11:00:00,1050,1200,1050,1150
> simb1,2006-02-08,11:15:00,1000,1200,1050,1150
>
> ... 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
> period.
>
> I don't know if MySQL can create a query that can get those values fast
> enough.
>
> Thank you very much.
>
>
> Teddy
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
>
>

Thread
selecting min, maxOctavian Rasnita13 Feb
  • Re: selecting min, maxRhino13 Feb
  • Re: selecting min, maxPeter Brawley13 Feb
  • Re: selecting min, maxOctavian Rasnita13 Feb
    • Re: selecting min, maxleo huang14 Feb
  • Re: selecting min, maxOctavian Rasnita13 Feb
  • Re: selecting min, maxRhino13 Feb