Sort by number of views in the last hour [MySQL]
I have a table that contains all views from the last 24 hours. I want to pull all pages sorted by rank. The rank should be calculated something like this:
rank = (0.3 * viewsInCurrentHour) * (0.7 * viewsInPreviousHour)
I want reverend in one request. Is this possible, or do I need to make 2 requests (one for the current hour and one for the last hour and then just fill them in)?
Here are the DESCRIBE
tables accesslog
:
+-----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+----------------+
| aid | int(11) | NO | PRI | NULL | auto_increment |
| sid | varchar(128) | NO | | | |
| title | varchar(255) | YES | | NULL | |
| path | varchar(255) | YES | | NULL | |
| url | text | YES | | NULL | |
| hostname | varchar(128) | YES | | NULL | |
| uid | int(10) unsigned | YES | MUL | 0 | |
| timer | int(10) unsigned | NO | | 0 | |
| timestamp | int(10) unsigned | NO | MUL | 0 | |
+-----------+------------------+------+-----+---------+----------------+
select
url,
sum(timestamp between subdate(now(), interval 2 hour) and subdate(now(), interval 1 hour)) * .3 +
sum(timestamp between subdate(now(), interval 1 hour) and now()) * .7 as rank
from whatever_your_table_name_is_which_you_have_kept_secret
where timestamp > subdate(now(), interval 2 hour)
group by url
order by rank desc;
sum(condition)
works, because mysql trye
has 1
, and false
is 0
, therefore, the conditions summation coincides with the fact that some noobs recorded assum(case when condition then 1 else 0 end)
Edit:
Note the addition where timestamp > subdate(now(), interval 2 hour)
to improve performance, as only the results of these records contribute to the result.