From: John Summerfield <postgres@herakles.homelinux.org>
To: pgsql-sql@postgresql.org
Subject: How to aggregates this data
Date: Thu, 11 Jan 2007 07:50:19 +0900
Message-ID: <45A56DAB.1030709@herakles.homelinux.org> (raw)
<wince> Now I've figured why I don't get replies, I'm sending again.
Intro:
I have a table that contains much data like this:
SELECT distinct on (date) asx_code,date,open,high,low,close from
sales_summaries where asx_code = 'TLSCA' and date >= '2006-12-01' order
by date, time desc limit 10;
SELECT distinct on (date) asx_code,date,open,high,low,close,volume from
sales_summaries where asx_code = 'TLSCA' and date >= '2006-12-01' order
by date, time desc limit 10;
asx_code | date | open | high | low | close | volume
----------+------------+-------+-------+-------+-------+----------
TLSCA | 2006-12-01 | 2.330 | 2.340 | 2.300 | 2.300 | 29165222
TLSCA | 2006-12-04 | 2.310 | 2.320 | 2.280 | 2.300 | 14973667
TLSCA | 2006-12-05 | 2.310 | 2.320 | 2.300 | 2.320 | 23238175
TLSCA | 2006-12-06 | 2.320 | 2.400 | 2.320 | 2.380 | 58357650
TLSCA | 2006-12-07 | 2.380 | 2.450 | 2.380 | 2.450 | 64005594
TLSCA | 2006-12-08 | 2.450 | 2.520 | 2.450 | 2.510 | 82831560
TLSCA | 2006-12-11 | 2.500 | 2.550 | 2.500 | 2.540 | 38711749
TLSCA | 2006-12-12 | 2.560 | 2.620 | 2.560 | 2.610 | 61709325
TLSCA | 2006-12-13 | 2.600 | 2.600 | 2.540 | 2.590 | 39455386
TLSCA | 2006-12-14 | 2.600 | 2.650 | 2.600 | 2.620 | 30675340
(10 rows)
Some may guess (correctly) that this is price information from a stock
exchange. What I want to do is consolidate the data into weekly (for
starters) periods. Writing a Java (or other procedural language) program
to read row by row is fairly straightforward. What I want to do is to do
it entirely in SQL.
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?"
May the pedants ignore that these weeks don't run Monday to Friday:-)
fwiw I'm working on Linux; thus far everything's done in bash scripts
aided by friends such as lynx, grep awk and (Of course) psql.
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: postgres@herakles.homelinux.org
Subject: Re: How to aggregates this data
In-Reply-To: <45A56DAB.1030709@herakles.homelinux.org>
* 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