Search Postgresql Archives

Re: Average New Users Per DOW

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 7/6/15, Robert DiFalco <robert.difalco@xxxxxxxxx> wrote:
> I'm not sure how to create a result where I get the average number of new
> users per day of the week. My issues are that days that did not have any
> new users will not be factored into the average, giving an overinflated
> result.
>
> This is what I started with:
>
> WITH userdays AS
>   (SELECT u.created::DATE AS created,
>           to_char(u.created,'Dy') AS d,
>           COUNT(*) AS total
>    FROM users u
>    GROUP BY 1,2),
> userdays_avg AS
>   (SELECT extract('dow'
>                   FROM created) AS nDay,
>           d AS "Day",
>           AVG(total) AS "New Users"
>    FROM userdays
>    GROUP BY 1,2
>    ORDER BY 1)
> SELECT "Day", "New Users"
> FROM userdays_avg
> ORDER BY nDay;
>
>
> But you can see it wont give correct results since (for example) Monday's
> with no new users will not be counted in the average as 0.

One way to handle this is to union your query with one that has a
generate_series (0,6) for the DOW column and nulls for the other
columns, then treat both that and your original query as a subquery
and do your averages, since nulls are not included in either count()
or average() aggregates:

select dow, count(*), avg(some_column) from (
select extract ('dow' from some_date) as dow, some_number from some_table
union select generate_series(0,6) as dow, null as some_number) as x
group by 1 order by 1

--
Mike Nolan
nolan@xxxxxxxx


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux