List:General Discussion« Previous MessageNext Message »
From:Chris W Date:March 18 2010 6:27pm
Subject:Re: 7-day average
View as plain text  
I changed the names slightly when I tested it but here is the query that 
gives the result you want...

SELECT a.`AccountID`, a.`company`, sum(h.hits), count(h.AccountID), 
sum(h.hits)/count(h.AccountID) AS AvgHits
FROM account a
JOIN hitsperday h using (AccountID)
WHERE `date` < DATE(now()) AND `date` >= DATE(DATE_SUB(now(), INTERVAL 7 
DAY))
GROUP BY a.`AccountID`, a.`company`
ORDER BY sum(h.hits)/count(h.AccountID) DESC

I think I would change the math.  Since there are several days in there 
where there are no hits, that should in my opinion count against the 
average.  So instead of dividing by count(h.AccountID),  I would divide 
by 7 which would change the values to about...

11.4
8.6
5.7

still in the same order though.

Brian Dunning wrote:
> My brain just squirted out my ear trying to figure this out, so maybe one of you can
> help me scoop it back up.
>
> I have a table of accounts, and a table of hits-per-day per account. I'm trying to
> sort my accounts by the most hits-per-day, averaged over the preceding 7 days (8 days ago
> through yesterday).
>
> Table: accounts
> +------------+---------+
> | account_id | company |
> +------------+---------+
> |          1 | ABC     |
> |          2 | DEF     |
> |          3 | GHI     |
> +------------+---------+
>
> Table: hits_per_day
> +------------+------------+------+
> | account_id |    date    | hits |
> +------------+------------+------+
> |          1 | 2010-03-01 |   10 |
> |          1 | 2010-03-12 |   10 |
> |          2 | 2010-03-12 |   10 |
> |          3 | 2010-03-12 |   10 |
> |          1 | 2010-03-15 |   30 |
> |          2 | 2010-03-15 |   70 |
> |          3 | 2010-03-15 |   50 |
> |          3 | 2010-03-18 |   10 |
> +------------+------------+------+
> * Notice the first and last rows should be excluded because they are not between 8
> days ago and yesterday.
>
> So the results should look like this:
> +------------+---------+----------+
> | account_id | company | avg_hits |
> +------------+---------+----------+
> |          2 | DEF     |       40 |
> |          3 | GHI     |       30 |
> |          1 | ABC     |       20 |
> +------------+---------+----------+
>
> I hope that makes sense! I've driven myself crazy with this to the point that I can
> no longer think about it clearly. Thanks for any help.
>
>
>
>   
Thread
7-day averageBrian Dunning18 Mar
  • Re: 7-day averageJigal van Hemert18 Mar
  • Re: 7-day averageChris W18 Mar
    • Re: 7-day averageBrian Dunning18 Mar