Date: Wed, 14 Apr 2010 17:49:58 +0200
From: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
To: pgsql-sql@postgresql.org
Subject: Re: graphing time series data
Message-ID: <20100414154958.GA16847@apartia.fr>
Mail-Followup-To: pgsql-sql@postgresql.org
References: <20100414145434.GA13682@apartia.fr>
	<r2k396486431004140846y4579154erc604d6286ac2cb89@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <r2k396486431004140846y4579154erc604d6286ac2cb89@mail.gmail.com>
User-Agent: Mutt/1.5.20 (2009-06-14)

On Wed, Apr 14, 2010 at 08:46:13AM -0700, Richard Broersma wrote:
> 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 AS
>  WITH opendays(day) AS (
>          SELECT gs.day::date AS day
>            FROM generate_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;

You confirm my modus operandi. I tried the following which seems to give
me optimal price distribution:

select w.week,count( p.id_price) from 
		(select generate_series(min(p.created_on),max(p.modified_on),'1 
		week') as week from price p) as w join price p on (p.created_on < 
		w.week + '7 days' and p.modified_on > w.week + '7 days') group by 
		w.week order by w.week

          week          | count  
------------------------+--------
 2010-02-10 15:32:18+01 | 125369
 2010-02-17 15:32:18+01 | 126882
 2010-02-24 15:32:18+01 | 128307
 2010-03-03 15:32:18+01 | 126742
 2010-03-10 15:32:18+01 | 133596
 2010-03-17 15:32:18+01 | 149019
 2010-03-24 15:32:18+01 | 149908
 2010-03-31 15:32:18+02 | 147617

The rest should be easy from there!

Thanks for your input,