From: Richard Huxton <dev@archonet.com>
To: Matthew Smith <mps@utas.edu.au>
Cc: pgsql-sql@postgresql.org
Subject: Re: Help on a complex query (avg data for day of the week)
Date: Wed, 21 Dec 2005 10:04:50 +0000
Message-ID: <43A928C2.8060901@archonet.com> (raw)
In-Reply-To: <200512191657.48139.mps@utas.edu.au>
References: <200512191657.48139.mps@utas.edu.au>
Matthew Smith wrote:
> I want to form a query that returns the average total usage for each day of
> the week, eg:
[snip]
> To get this info, I am using the following query:
>
> select dow as day, sum(sum_data)/count(dow) as avg_usage from
> (select extract('dow' from date_trunc('day', time)) as dow, sum(data) as
> sum_data
> from datalog where time >= '2005-09-11' and time <= '2005-09-25' group by dow)
> as avg_data_per_day group by day;
>
> This works well, assuming that there is at least one entry in the table for
> each day in the time period.
>
> The problem comes when there are days where no data is logged.
1. Calculate how many data-points each day represents
2. Sum the days you do have data for
3. Left-join #1 to #2 so you can calculate the average.
I'm guessing for #1 you'd rather not have 7 UNIONs, so you might find
generate_series() a useful function. See Ch 9.18. Set Returning Functions.
HTH
--
Richard Huxton
Archonet Ltd
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, mps@utas.edu.au
Subject: Re: Help on a complex query (avg data for day of the week)
In-Reply-To: <43A928C2.8060901@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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