pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedgraphing time series data
6+ messages / 3 participants
[nested] [flat]
* graphing time series data
@ 2010-04-14 14:54 Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
0 siblings, 1 reply; 6+ messages in thread
From: Louis-David Mitterrand @ 2010-04-14 14:54 UTC (permalink / raw)
To: pgsql-sql
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,
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: graphing time series data
@ 2010-04-14 15:46 Richard Broersma <richard.broersma@gmail.com>
parent: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
0 siblings, 1 reply; 6+ messages in thread
From: Richard Broersma @ 2010-04-14 15:46 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: graphing time series data
@ 2010-04-14 15:49 Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
parent: Richard Broersma <richard.broersma@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Louis-David Mitterrand @ 2010-04-14 15:49 UTC (permalink / raw)
To: 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@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,
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: graphing time series data
@ 2010-04-14 16:06 Pavel Stehule <pavel.stehule@gmail.com>
parent: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
0 siblings, 1 reply; 6+ messages in thread
From: Pavel Stehule @ 2010-04-14 16:06 UTC (permalink / raw)
To: pgsql-sql
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. 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!
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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: graphing time series data
@ 2010-04-14 16:11 Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Louis-David Mitterrand @ 2010-04-14 16:11 UTC (permalink / raw)
To: pgsql-sql
On Wed, Apr 14, 2010 at 06:06:59PM +0200, Pavel Stehule wrote:
> 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)
Because if a price doesn't change for more than a week, then some weeks
will have bad statistical distribution (i.e not including prices which
only have their 'modified' updated). So I (think I) need to (1) generate
the weeks separately and (2) average prices that are current for each
week.
But I could be missing something obvious.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: graphing time series data
@ 2010-04-14 16:14 Pavel Stehule <pavel.stehule@gmail.com>
parent: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
0 siblings, 0 replies; 6+ messages in thread
From: Pavel Stehule @ 2010-04-14 16:14 UTC (permalink / raw)
To: pgsql-sql
2010/4/14 Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>:
> On Wed, Apr 14, 2010 at 06:06:59PM +0200, Pavel Stehule wrote:
>> 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)
>
> Because if a price doesn't change for more than a week, then some weeks
> will have bad statistical distribution (i.e not including prices which
> only have their 'modified' updated). So I (think I) need to (1) generate
> the weeks separately and (2) average prices that are current for each
> week.
>
> But I could be missing something obvious.
ok
Pavel
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2010-04-14 16:14 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-04-14 14:54 graphing time series data Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-04-14 15:46 ` Richard Broersma <richard.broersma@gmail.com>
2010-04-14 15:49 ` Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-04-14 16:06 ` Pavel Stehule <pavel.stehule@gmail.com>
2010-04-14 16:11 ` Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-04-14 16:14 ` Pavel Stehule <pavel.stehule@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox