Message-ID: <40E1BCFD.9090401@archonet.com>
Date: Tue, 29 Jun 2004 20:03:25 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 0.7 (X11/20040615)
MIME-Version: 1.0
To: Bill <bill@math.uchicago.edu>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query performance
References: <200406291733.i5THXkfL031311@math.uchicago.edu>
In-Reply-To: <200406291733.i5THXkfL031311@math.uchicago.edu>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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