Date: Wed, 14 Apr 2010 16:54:34 +0200
From: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
To: pgsql-sql@postgresql.org
Subject: graphing time series data
Message-ID: <20100414145434.GA13682@apartia.fr>
Mail-Followup-To: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.5.20 (2009-06-14)

Hi,

I have times series data in a 'price' table:

price(id_price, price, id_product, created, modified)

Prices are polled daily and a new 'price' row is created only if the
price of id_product changes, else modified is updated to now().

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)'.

Ideally I'd generate_series() a list of weeks between min(p.created) and
max(p.modified) and then average prices 'group by p.modified < week'.

What would be the best way to tackle this?

Thanks,