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 09:37:49 +0100
Message-ID: <40E12A5D.70007@archonet.com> (raw)
In-Reply-To: <200406281702.i5SH2YfL013269@math.uchicago.edu>
References: <200406281702.i5SH2YfL013269@math.uchicago.edu>

Bill wrote:
> Ok....so here lies the output of oclh (i.e "\d oclh")  
> 
>                     Table "public.oclh"
>  Column |         Type          |           Modifiers           
> --------+-----------------------+-------------------------------
>  symbol | character varying(10) | not null default ''
>  date   | date                  | not null default '0001-01-01'
>  open   | numeric(12,2)         | not null default '0.00'
>  close  | numeric(12,2)         | not null default '0.00'
>  low    | numeric(12,2)         | not null default '0.00'
>  high   | numeric(12,2)         | not null default '0.00'
> Indexes: symbol_2_oclh_index btree (symbol, date),
>          symbol_oclh_index btree (symbol, date)

Well, I'm not sure why the two indexes on the same columns, and I'm not 
sure it makes sense to have defaults for _any_ of the columns there.

So - you want:
1. ratio = abs(closing-opening)/opening
2. average = all the ratios of each day of each stock
3. Highest average

Well, I don't know what you mean by #2, but #1 is just:

SELECT
   symbol,
   "date",
   abs(close - open)/open AS ratio
FROM
   oclh
GROUP BY
   symbol, date;

I'd probably fill in a summary table with this and use that as the basis 
for your further queries. Presumably from "yesterday" back, the 
ratios/averages won't change.

-- 
   Richard Huxton
   Archonet Ltd

view thread (48+ messages)  latest in thread

Message-ID: <40E12A5D.70007@archonet.com>
Permalink:  ../40E12A5D.70007@archonet.com/
Also on:    postgresql.org/message-id/40E12A5D.70007@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: <40E12A5D.70007@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