mysql a query to show trend in views

I have a cron job that runs hourly.

The cron job adds a record to table 'update'

table - 'update'
update_id (primary)
timestamp

and

adds a record for each page of my website into table views

table - 'views'
page_id
update_id
view_count

What I am looking to achieve is a query which can compare the most recent update (highest update_id) to the update before for each page_id and return a percentage increase in views. can't for the life of me get my head around it.

Here is some example data.

page_id = 1
update_id = 10
view_count = 1251

page_id = 1
update开发者_JAVA技巧_id = 11
view_count = 1349

You may want to try the joining the views table on update_id - 1, as in the following query:

SELECT    v1.page_id,
          v1.view_count latest_count,
          v2.view_count previous_count,
          (1 - v2.view_count / v1.view_count) * 100 trend_perc
FROM      views v1
JOIN      views v2 ON (v2.update_id = v1.update_id - 1 AND 
                       v2.page_id = v1.page_id)
WHERE     v1.update_id = (SELECT MAX(update_id) FROM `views`)
GROUP BY  v1.page_id
ORDER BY  page_id;

Test case:

CREATE TABLE `updates` (update_id int, timestamp datetime);
CREATE TABLE `views` (page_id int, update_id int, view_count int);

INSERT INTO `updates` VALUES (1, '2010-07-08 12:00:00');
INSERT INTO `updates` VALUES (2, '2010-07-08 13:00:00');
INSERT INTO `updates` VALUES (3, '2010-07-08 14:00:00');

INSERT INTO `views` VALUES (1, 1, 100);
INSERT INTO `views` VALUES (2, 1, 50);
INSERT INTO `views` VALUES (3, 1, 75);
INSERT INTO `views` VALUES (1, 2, 150);
INSERT INTO `views` VALUES (2, 2, 90);
INSERT INTO `views` VALUES (3, 2, 80);
INSERT INTO `views` VALUES (1, 3, 175);
INSERT INTO `views` VALUES (2, 3, 115);
INSERT INTO `views` VALUES (3, 3, 120);

Result:

+---------+--------------+----------------+------------+
| page_id | latest_count | previous_count | trend_perc |
+---------+--------------+----------------+------------+
|       1 |          175 |            150 |    14.2857 |
|       2 |          115 |             90 |    21.7391 |
|       3 |          120 |             80 |    33.3333 |
+---------+--------------+----------------+------------+
3 rows in set (0.00 sec)

Note that trend_perc will be NULL if the previous_count happens to be 0.

I guess you need to aggregate the update table first, so,

SELECT COUNT(*) AS cnt FROM `update` WHERE update_id = (SELECT MAX(update_id) FROM `update`) GROUP BY page_id;

Then you want a join with views

SELECT * FROM views AS v1 ...

... uh, wait. I think you're missing one column in the update table, aren't you? The page_id.

CREATE TEMPORARY TABLE vTemp 
SELECT page_id, COUNT(*) AS cnt FROM `update` WHERE update_id = (SELECT MAX(update_id) FROM `update`) GROUP BY page_id;

Then you want a join with views:

SELECT 1 - (v1.view_count / vTemp.cnt) AS difference FROM views AS v1 
  LEFT OUTER JOIN vTemp AS v2 USING(page_id)
WHERE v1.update_id = (SELECT MAX(update_id)-1 FROM `update`) GROUP BY page_id

Or such...