pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

view of weekly data
2+ messages / 2 participants
[nested] [flat]

* view of weekly data
@ 2006-02-15 03:39  Ding Xiangguang <Xiangguang@singtel.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Ding Xiangguang @ 2006-02-15 03:39 UTC (permalink / raw)
  To: pgsql-sql

Hi, friend,

Suppose there is table of daily transaction data with 5 fields,

time(date), open(float8), high(float8), low(float8), close(float8)

Is it possible to create a view of weekly data, i.e. open is the first day'open, high is the highest of the week, low is the lowest of the week, close is the last day's close.

Thank you.



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: view of weekly data
@ 2006-02-18 07:36  Greg Stark <gsstark@mit.edu>
  parent: Ding Xiangguang <Xiangguang@singtel.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Greg Stark @ 2006-02-18 07:36 UTC (permalink / raw)
  To: Ding Xiangguang <Xiangguang@singtel.com>; +Cc: pgsql-sql

"Ding Xiangguang" <Xiangguang@singtel.com> writes:

> Hi, friend,
> 
> Suppose there is table of daily transaction data with 5 fields,
> 
> time(date), open(float8), high(float8), low(float8), close(float8)
> 
> Is it possible to create a view of weekly data, i.e. open is the first
> day'open, high is the highest of the week, low is the lowest of the week, close
> is the last day's close.

low and high are easy, they're just min() and max(). so you would get
something like:

select date_trunc('week', time) as startofweek, 
       min(low) as weeklylow, 
       max(high) as weeklyhigh
  from dailydata
 group by date_trunc('week', time)


Unfortunately showing the open and close is much much harder. To get them
efficiently requires a feature set called OLAP that Postgres doesn't have and
isn't likely to get soon.

In Postgres 8.1 (and 8.0?) you could actually write some custom aggregate
functions using RECORD data type to store the earliest and latest time found
so far and the corresponding open and close to get them efficiently. Maybe
someone else would be able to show how to do that, I haven't tried it yet.

The only way to do it in standardish SQL would be with terribly inefficient
subqueries:

select date_trunc('week', time) as startofweek, 
       min(low) as weeklylow, 
       max(high) as weeklyhigh,
       (select open 
          from dailydata as d 
         where date_trunc('week',time)=date_trunc('week',dailydata.time)
         order by time asc
         limit 1
       ) as weeklyopen,
       (select close
          from dailydata as d 
         where date_trunc('week',time)=date_trunc('week',dailydata.time)
         order by time desc
         limit 1
       ) as weeklyclose
  from dailydata
 group by date_trunc('week', time)


-- 
greg




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2006-02-18 07:36 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-02-15 03:39 view of weekly data Ding Xiangguang <Xiangguang@singtel.com>
2006-02-18 07:36 ` Greg Stark <gsstark@mit.edu>

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