Re: Query performance

Bill wrote:
> Ok, thanks.  So let me explain the query number 2 as this is the more
> difficult to write.  So I have a list of stocks, this table contains the
> price of all of the stocks at the open and close date.  Ok, now we have a
> ratio from query (1) that returns at least a very rough index of the daily
> performance of a given stock, with each ratio representing the stock's
> performance in one day.  Now we need to average this with the same stock's
> ratio every day, to get a total average for each stock contained in the
> database.  Now I would simply like to find a ratio like this that represents
> the average of every stock in the table and simply find the greatest ratio.
> Sorry about the lousy explanation before, is this a bit better?
>
> Here is an example if needed.
>
> Say we have a stock by the name of YYY
>
> I know, due to query 1 that stock YYY has a abs(close-open)/open price ratio
> of for example, 1.3 on Dec 1 and (for simplicity let's say we only have two
> dates) and Dec 2 the ratio for YYY is 1.5. So the query averages and gets
> 1.4.  Now it needs to do this for all of the stocks in the table and sort by
> increasing ratio.

Well, the simplest would be something like:

CREATE VIEW my_ratios AS SELECT ...(select details we used for #1
previously)

Query #1 then becomes:
SELECT * FROM my_ratios;

Then you could do:
SELECT
   symbol,
   avg(ratio) as ratio_avg
FROM
   my_ratios
GROUP BY
   symbol
ORDER BY
   avg(ratio)
;

Now, in practice, I'd probably create a symbol_ratio table and fill that
one day at a time. Then #2,#3 would be easier.

--
   Richard Huxton
   Archonet Ltd