Em Thursday 21 February 2008 18:37:47 Justin escreveu:
> Now i could write a function to do this or do it in C++ program that
> creates query with all kinds of unions. I'm wondering if there is a way
> to create this in a single select statement??
> I can't think of a way to do it???
Why you need it in one query? Think of maintenability not on code size.
Solve the problem in parts, calculating it for one week -- or ten, you can use
the interval type -- and then moving on...
The function would look like:
WHILE start_date + '10 weeks'::interval < today:
SELECT INTO weeks_avg avg(value) FROM table WHERE date BETWEEN start_date AND
start_date+'10 weeks'::interval;
start_date:=start_date + '1 week'::interval;
END WHILE;
Start from that and you'll have it done. (Of course, above is pseudo code and
untested...)
--
Jorge Godoy <jgodoy@xxxxxxxxx>
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
