From: Bruno Wolff III <bruno@wolff.to>
To: John Summerfield <postgres@herakles.homelinux.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: How to aggregates this data
Date: Thu, 11 Jan 2007 13:51:44 -0600
Message-ID: <20070111195144.GA17354@wolff.to> (raw)
In-Reply-To: <45A56DAB.1030709@herakles.homelinux.org>
References: <45A56DAB.1030709@herakles.homelinux.org>
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.
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-sql@postgresql.org
Cc: bruno@wolff.to, postgres@herakles.homelinux.org
Subject: Re: How to aggregates this data
In-Reply-To: <20070111195144.GA17354@wolff.to>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox