Re: Query performance

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