On Thu, Jan 11, 2007 at 07:50:19 +0900, John Summerfield <postgres@herakles.homelinux.org> wrote:
>
> I've perused my book (Mastering SQL by Martin Gruber), the postgresql
> docs (I have versions here on RHEL (Centos) 4, FC5,6, Debian Testing -
> up to 8,1) and I don't see how to choose the entry for the first open
> column, the last close. Max, min and average will do nicely for the
> others, and converting the date to an interval then dividing by seven
> seems to work nicely for creating my groups,
>
> The results I expect from the above data are
> TLSCA 2006-12-07 2.330 2.450 2.280 2.450 mumble
> TLSCA 2006-12-14 2.450 2.650 2.450 2.620 mumble
>
> The question is, "How can I do this in SQL?"
There are date functions that return the week of the year, so that you
could do a group by week and year to get your aggregates. To get the open
and close prices, you could join back to the table (twice) on week, year and
either the first or last day of the week. As long as there is data for every
day of the week for every stock, this should work.