List:General Discussion« Previous MessageNext Message »
From:Rhino Date:February 13 2006 10:38pm
Subject:Re: selecting min, max
View as plain text  
I've just spent the last couple of hours trying various queries to get the 
result you want. I'm afraid I didn't find an answer for you. I think I 
understand exactly what you want but I couldn't figure out how to write the 
query you need.

Unfortunately, I'm using 4.0.15 and I don't even have subquery support in 
that version. I might be able to solve the problem in DB2 SQL, which I know 
better, but that won't help you because MySQL doesn't appear to support the 
techniques I would use in DB2, like nested table expressions and common 
table expressions.

Therefore, I think you should look very carefully at the two suggestions 
that Peter Brawley gave you earlier in the day. They look promising and I 
think you might be able to solve the problem that way.

Sorry I couldn't help more.

--
Rhino

----- Original Message ----- 
From: "Octavian Rasnita" <orasnita@stripped>
To: "Octavian Rasnita" <orasnita@stripped>; "Rhino" <rhino1@stripped>; 
<mysql@stripped>
Sent: Monday, February 13, 2006 3:16 PM
Subject: Re: selecting min, max


> PS, I have forgotten to tell that I am using MySQL 5.
>
> Thank you.
>
> Teddy
>
> ----- Original Message ----- 
> From: "Octavian Rasnita" <orasnita@stripped>
> To: "Rhino" <rhino1@stripped>; <mysql@stripped>
> Sent: Monday, February 13, 2006 10:00 PM
> Subject: Re: selecting min, max
>
>
>> 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
>>
>>
>>
>> -- 
>> No virus found in this incoming message.
>> Checked by AVG Free Edition.
>> Version: 7.1.375 / Virus Database: 267.15.6/258 - Release Date: 2/13/2006
>>
>>
>
>
> -- 
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.375 / Virus Database: 267.15.6/258 - Release Date: 13/02/2006
>
> 



-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.6/258 - Release Date: 13/02/2006

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