DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:content-type;
	b=fmMNBBGL7ArCpN6ad+ol5t1wQF5BQcnk2roqBDCNTrIh4eoK6WbcbX1eUT4oA59Uwa
	WY/VTdmfPpVMWN2t4Dy1WdwJi63xn6WxZDd5wORwYYXmium2bRM5029mQlvrvs1vY9FB
	LKzfWjeoUFHP5Qdxft/l9LQz/xtG+8CbUxzDQ=
MIME-Version: 1.0
In-Reply-To: <20100414145434.GA13682@apartia.fr>
References: <20100414145434.GA13682@apartia.fr>
Date: Wed, 14 Apr 2010 08:46:13 -0700
Message-ID: <r2k396486431004140846y4579154erc604d6286ac2cb89@mail.gmail.com>
Subject: Re: graphing time series data
From: Richard Broersma <richard.broersma@gmail.com>
To: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1

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;


-- 
Regards,
Richard Broersma Jr.

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