"Ding Xiangguang" <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org