CODEWITHSUNDEEP
mysqlsql-order-by
Eduard Luca
Eduard Luca

Reputation: 6612

Order by number of views in last hour [MySQL]

I have a table which holds all views for the last 24 hours. I want to pull all pages ordered by a rank. The rank should be calculated something like this:

rank = (0.3 * viewsInCurrentHour) * (0.7 * viewsInPreviousHour)

I want the prefferably in one single query. Is this possible, or do I need to make 2 queries (one for the current hour and one for the last hour and then just aggregate them)?

Here is the DESCRIBE of the table 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       |                |
+-----------+------------------+------+-----+---------+----------------+

Upvotes: 3

Views: 149

Answers (1)

Bohemian
Bohemian

Reputation: 425238

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;

The sum(condition) works because in mysql trye is 1 and false is 0, so summing a condition is the same as what some noobs write as sum(case when condition then 1 else 0 end)

Edit:

Note the addition of where timestamp > subdate(now(), interval 2 hour) to improve performance, because only these records contribute to the result.

Upvotes: 2

Related Questions