From: | Louis-David Mitterrand <vindex+lists-pgsql-sql(at)apartia(dot)org> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: graphing time series data |
Date: | 2010-04-14 15:49:58 |
Message-ID: | 20100414154958.GA16847@apartia.fr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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(at)apartia(dot)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,
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2010-04-14 16:06:59 | Re: graphing time series data |
Previous Message | Richard Broersma | 2010-04-14 15:46:13 | Re: graphing time series data |