agora inbox for pgsql-performance@postgresql.org  

help / color / mirror / Atom feed

From: Richard Huxton <dev@archonet.com>
To: Bill <bill@math.uchicago.edu>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query performance
Date: Tue, 29 Jun 2004 20:03:25 +0100
Message-ID: <40E1BCFD.9090401@archonet.com> (raw)
In-Reply-To: <200406291733.i5THXkfL031311@math.uchicago.edu>
References: <200406291733.i5THXkfL031311@math.uchicago.edu>

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

view thread (48+ messages)  latest in thread

Message-ID: <40E1BCFD.9090401@archonet.com>
Permalink:  ../40E1BCFD.9090401@archonet.com/
Also on:    postgresql.org/message-id/40E1BCFD.9090401@archonet.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: dev@archonet.com, bill@math.uchicago.edu
  Subject: Re: Query performance
  In-Reply-To: <40E1BCFD.9090401@archonet.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox