pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to aggregates this data
4+ messages / 3 participants
[nested] [flat]
* How to aggregates this data
@ 2007-01-10 22:50 John Summerfield <postgres@herakles.homelinux.org>
0 siblings, 2 replies; 4+ messages in thread
From: John Summerfield @ 2007-01-10 22:50 UTC (permalink / raw)
To: pgsql-sql
<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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to aggregates this data
@ 2007-01-11 00:29 Chad Wagner <chad.wagner@gmail.com>
parent: John Summerfield <postgres@herakles.homelinux.org>
1 sibling, 1 reply; 4+ messages in thread
From: Chad Wagner @ 2007-01-11 00:29 UTC (permalink / raw)
To: John Summerfield <postgres@herakles.homelinux.org>; +Cc: 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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to aggregates this data
@ 2007-01-11 00:38 Chad Wagner <chad.wagner@gmail.com>
parent: Chad Wagner <chad.wagner@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Chad Wagner @ 2007-01-11 00:38 UTC (permalink / raw)
To: John Summerfield <postgres@herakles.homelinux.org>; +Cc: pgsql-sql
By the way, if you want to include that "incomplete" week before 12/1
(incomplete because it doesn't have a data point for Monday) then you would
do this:
select s1.asx_code, s1.wdate AS date, s1.low, s1.high, s2.open, s3.close,
s1.volume
from (select asx_code, date_trunc('week', date) AS wdate, min(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;
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to aggregates this data
@ 2007-01-11 19:51 Bruno Wolff III <bruno@wolff.to>
parent: John Summerfield <postgres@herakles.homelinux.org>
1 sibling, 0 replies; 4+ messages in thread
From: Bruno Wolff III @ 2007-01-11 19:51 UTC (permalink / raw)
To: John Summerfield <postgres@herakles.homelinux.org>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-01-11 19:51 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-01-10 22:50 How to aggregates this data John Summerfield <postgres@herakles.homelinux.org>
2007-01-11 00:29 ` Chad Wagner <chad.wagner@gmail.com>
2007-01-11 00:38 ` Chad Wagner <chad.wagner@gmail.com>
2007-01-11 19:51 ` Bruno Wolff III <bruno@wolff.to>
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