List:General Discussion« Previous MessageNext Message »
From:Rhino Date:February 13 2006 2:43pm
Subject:Re: selecting min, max
View as plain text  
----- Original Message ----- 
From: "Octavian Rasnita" <orasnita@stripped>
To: <mysql@stripped>
Sent: Monday, February 13, 2006 9:07 AM
Subject: selecting min, max


> Hi,
>
> I have a table with the following fields:
>
> symbol, date_time, price, volume
>
> 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!

Have you looked at the GROUP BY clause? If you haven't, I think you'll find 
that it is _very_ helpful. A query like:

select symbol, max(price) as Maximum_Price, min(price) as Minimum_Price
from mytable
group by symbol

should show you a single row for each symbol that you have in your table. 
Each row will contain the maximum and minimum prices for that symbol.

Of course you will still need to add the time logic to that example so that 
rows for each hour are grouped together as well. But I can't do that without 
seeing a full definition of the table and a few sample rows so that I can 
really understand the data.

Something like that should get you everything you want in just one query. I 
don't know how it will perform but if it doesn't perform well, you should be 
able to improve the performance dramatically by creating appropriate indexes 
on the data. I can't advise you on the construction of indexes in MySQL - I 
don't know enough about how MySQL uses indexes - but others on this mailing 
list are very experienced in this area and should be able to guide you.

--
Rhino 



-- 
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