From: | "Chad Wagner" <chad(dot)wagner(at)gmail(dot)com> |
---|---|
To: | "John Summerfield" <postgres(at)herakles(dot)homelinux(dot)org> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: How to aggregates this data |
Date: | 2007-01-11 00:29:41 |
Message-ID: | 81961ff50701101629o265fd555tf64672547261c44@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
John,
Sub-selects to the rescue. See below.
select s1.asx_code, s1.bdate AS date, s1.low, s1.high, s2.open, s3.close,
s1.volume
from (select asx_code, date_trunc('week', date) AS bdate, max(date) AS
edate, min(low) AS low, max(high) AS high, sum(volume) AS volume
from sales_summaries
group by asx_code, date_trunc('week', date)) s1, sales_summaries s2,
sales_summaries s3
where s1.bdate = s2.date
and s1.asx_code=s2.asx_code
and s1.edate = s3.date
and s1.asx_code=s3.asx_code;
asx_code | date | low | high | open | close |
volume
------------+------------------------+------+------+------+-------+-----------
TLSCA | 2006-12-04 00:00:00-05 | 2.28 | 2.52 | 2.31 | 2.51 |
243406646
TLSCA | 2006-12-11 00:00:00-05 | 2.5 | 2.65 | 2.5 | 2.62 |
170551800
The "date" is based on ISO-8601 (in other words the week starts on Monday).
Be warned, as it is questionable how this will scale. It may require
expression (function based) indexes.
Oracle has a feature called analytic functions, which would allow you to use
functions such as LEAD, LAG, FIRST_VALUE, LAST_VALUE. In particular
FIRST_VALUE and LAST_VALUE would have been useful to determine the open and
close for a week, but before analytics in Oracle you would use sub-selects
or multiple joins.
From | Date | Subject | |
---|---|---|---|
Next Message | Chad Wagner | 2007-01-11 00:38:38 | Re: How to aggregates this data |
Previous Message | John Summerfield | 2007-01-10 22:50:19 | How to aggregates this data |