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:content-transfer-encoding;
	b=KXrOQ/HjPRY+BIVRP0c7AVM4SoKoq6MEhGueMXVq15fT0NGCe50+6RPkcb4cBrWrNk
	OwEwU3gDKRXscD/eImE1B+njm/ORnUHlfm7a9VhI2Un36yw40FceqDSe/hkuVeVATSrD
	+GnzcHvkEWEkIBakerX//9EF7pt5LP2QqvFgs=
MIME-Version: 1.0
In-Reply-To: <20100414154958.GA16847@apartia.fr>
References: <20100414145434.GA13682@apartia.fr>
	<r2k396486431004140846y4579154erc604d6286ac2cb89@mail.gmail.com>
	<20100414154958.GA16847@apartia.fr>
Date: Wed, 14 Apr 2010 18:06:59 +0200
Message-ID: <n2i162867791004140906l3cf1ac73q39343c54bb5facb1@mail.gmail.com>
Subject: Re: graphing time series data
From: Pavel Stehule <pavel.stehule@gmail.com>
To: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

2010/4/14 Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>:
> 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. =C2=A0Only I was
>> calculating the counts per day. =C2=A0this query could be crafted to wor=
k
>> for you.
>>
>> CREATE OR REPLACE VIEW opendiscrepencydailycounts AS
>> =C2=A0WITH opendays(day) AS (
>> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0SELECT gs.day::date AS day
>> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0FROM generate_series((( SELECT
>> min(discrepencylist.discstartdt) AS min
>> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0FRO=
M discrepencylist))::timestamp without time
>> zone, 'now'::text::date::timestamp without time zone, '1
>> day'::interval) gs(day)
>> =C2=A0 =C2=A0 =C2=A0 =C2=A0 )
>> =C2=A0SELECT opendays.day, ds.resolvingparty, count(opendays.day) AS
>> opendiscrepancies
>> =C2=A0 =C2=A0FROM discrepencylist ds, opendays
>> =C2=A0 WHERE opendays.day >=3D ds.discstartdt AND opendays.day <=3D
>> LEAST('now'::text::date, ds.resolutiondate)
>> =C2=A0 GROUP BY opendays.day, ds.resolvingparty
>> =C2=A0 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
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0(select generate_s=
eries(min(p.created_on),max(p.modified_on),'1
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0week') as week fro=
m price p) as w join price p on (p.created_on <
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0w.week + '7 days' =
and p.modified_on > w.week + '7 days') group by
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0w.week order by w.=
week
>
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0week =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=
| count
> ------------------------+--------
> =C2=A02010-02-10 15:32:18+01 | 125369
> =C2=A02010-02-17 15:32:18+01 | 126882
> =C2=A02010-02-24 15:32:18+01 | 128307
> =C2=A02010-03-03 15:32:18+01 | 126742
> =C2=A02010-03-10 15:32:18+01 | 133596
> =C2=A02010-03-17 15:32:18+01 | 149019
> =C2=A02010-03-24 15:32:18+01 | 149908
> =C2=A02010-03-31 15:32:18+02 | 147617
>
> The rest should be easy from there!

I don't understand well. Why you don't use a function date_trunc(),

select date_trunc('week', created), count(*)
from price
group by date_trunc('week', created)

Regards
Pavel Stehuke

>
> Thanks for your input,
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>