7-day average

--Apple-Mail-2-716303074
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
charset=us-ascii

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.



--Apple-Mail-2-716303074--

Re: 7-day average

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.

Yummy, fresh brain! ;-)

> 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).

According to your table definitions:

SELECT `account_id`, `company`, AVG(`hits`) AS avg_hits
FROM `accounts`
JOIN `hits_per_day` USING (`account_id`)
WHERE `date` BETWEEN CURRENT_DATE() - INTERVAL 8 DAY AND CURRENT_DATE()
- INTERVAL 1 DAY
GROUP BY `account_id`
ORDER BY avg_hits DESC

--
Jigal van Hemert.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: 7-day average

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.
>
>
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: 7-day average

You're exactly right, that's an important point that I neglected when =
putting together my example. Good catch.


On Mar 18, 2010, at 11:27 AM, Chris W wrote:

> 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...

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg