Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id E9AEA9FA4A0
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 10 Jan 2007 20:29:48 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024)
	with ESMTP id 90334-05 for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 10 Jan 2007 20:29:38 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4
Received: from nf-out-0910.google.com (nf-out-0910.google.com
 [64.233.182.190])
	by postgresql.org (Postfix) with ESMTP id 22D449FA354
	for <pgsql-sql@postgresql.org>; Wed, 10 Jan 2007 20:29:42 -0400 (AST)
Received: by nf-out-0910.google.com with SMTP id x4so686986nfb
	for <pgsql-sql@postgresql.org>; Wed, 10 Jan 2007 16:29:41 -0800 (PST)
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references;
	b=lymA3nKsYbvxV2YT1iDkhfPFjhj65WG+JYZ17TPMuLv3IzGxmzjvDuinTmNwz055KNxtklqCh+Ih11weZj8ifec4VrXUmUzU0Vpxz8LzTb6Eu4Z5DVYETIJChfptKY2HzxfiJvBCKZqPXTFw5FDm856w8ASmofiCTTs4h86xZcM=
Received: by 10.49.27.17 with SMTP id e17mr1648551nfj.1168475381382;
	Wed, 10 Jan 2007 16:29:41 -0800 (PST)
Received: by 10.48.230.1 with HTTP; Wed, 10 Jan 2007 16:29:41 -0800 (PST)
Message-ID: <81961ff50701101629o265fd555tf64672547261c44@mail.gmail.com>
Date: Wed, 10 Jan 2007 19:29:41 -0500
From: "Chad Wagner" <chad.wagner@gmail.com>
To: "John Summerfield" <postgres@herakles.homelinux.org>
Subject: Re: How to aggregates this data
Cc: pgsql-sql@postgresql.org
In-Reply-To: <45A56DAB.1030709@herakles.homelinux.org>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_25537_17217671.1168475381241"
References: <45A56DAB.1030709@herakles.homelinux.org>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200701/27
X-Sequence-Number: 27179

------=_Part_25537_17217671.1168475381241
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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.

------=_Part_25537_17217671.1168475381241
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

John,<br>&nbsp;&nbsp; Sub-selects to the rescue.&nbsp; See below.<br>
<br>
select s1.asx_code, s1.bdate AS date, s1.low, s1.high, s2.open, s3.close, s1.volume<br>
from (select asx_code, date_trunc(&#39;week&#39;, date) AS bdate, max(date) AS
edate, min(low) AS low, max(high) AS high, sum(volume) AS volume<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from sales_summaries<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; group by asx_code, date_trunc(&#39;week&#39;, date)) s1, sales_summaries s2, sales_summaries s3<br>
where s1.bdate = s2.date<br>
and s1.asx_code=s2.asx_code<br>
and s1.edate = s3.date<br>
and s1.asx_code=s3.asx_code;<br>
<br>
&nbsp; asx_code&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; date&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | low&nbsp; | high | open | close |&nbsp; volume&nbsp;&nbsp; <br>
------------+------------------------+------+------+------+-------+-----------<br>
&nbsp; TLSCA&nbsp;&nbsp;&nbsp;&nbsp; | 2006-12-04 00:00:00-05 | 2.28 | 2.52 | 2.31 |&nbsp; 2.51 | 243406646<br>
&nbsp; TLSCA&nbsp;&nbsp;&nbsp;&nbsp; | 2006-12-11 00:00:00-05 |&nbsp; 2.5 | 2.65 |&nbsp; 2.5 |&nbsp; 2.62 | 170551800<br>
<br>
The &quot;date&quot; is based on ISO-8601 (in other words the week starts on
Monday).&nbsp; Be warned, as it is questionable how this will scale.&nbsp; It may require expression (function based) indexes.<br>

<br>Oracle has a feature called analytic functions, which would
allow you to use functions such as LEAD, LAG, FIRST_VALUE, LAST_VALUE.&nbsp;
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.<br>
<br><br>

------=_Part_25537_17217671.1168475381241--