----- Original Message -----
From: "Octavian Rasnita" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
