From: | Richard Broersma <richard(dot)broersma(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: graphing time series data |
Date: | 2010-04-14 15:46:13 |
Message-ID: | r2k396486431004140846y4579154erc604d6286ac2cb89@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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;
--
Regards,
Richard Broersma Jr.
Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug
From | Date | Subject | |
---|---|---|---|
Next Message | Louis-David Mitterrand | 2010-04-14 15:49:58 | Re: graphing time series data |
Previous Message | Louis-David Mitterrand | 2010-04-14 14:54:34 | graphing time series data |