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 <[EMAIL PROTECTED]>: > > Hi, > > From: "Rhino" <[EMAIL PROTECTED]> > ... > > > 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/[EMAIL PROTECTED] > >
