Re: graphing time series data

On Wed, Apr 14, 2010 at 7:54 AM, Louis-David Mitterrand
<vindex+lists-pgsql-sql@apartia.org> wrote:

> Now, I'd like to make a graph of average prices per week, per
> id_product. As some prices don't vary much, distribution would not be
> ideal if I simply 'group by extract(week from p.modified)'.

I created a view for a similar problem that I had.  Only I was
calculating the counts per day.  this query could be crafted to work
for you.

CREATE OR REPLACE VIEW opendiscrepencydailycounts ASWITH opendays(day) AS (        SELECT gs.day::date AS day
FROMgenerate_series((( SELECT
 
min(discrepencylist.discstartdt) AS min                  FROM discrepencylist))::timestamp without time
zone, 'now'::text::date::timestamp without time zone, '1
day'::interval) gs(day)       )SELECT opendays.day, ds.resolvingparty, count(opendays.day) AS
opendiscrepancies  FROM discrepencylist ds, opendays WHERE opendays.day >= ds.discstartdt AND opendays.day <=
LEAST('now'::text::date, ds.resolutiondate) GROUP BY opendays.day, ds.resolvingparty ORDER BY opendays.day,
ds.resolvingparty;


-- 
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug