sql - mysql查询显示 View 趋势

Question

我有一个每小时运行一次的 cron 作业。

cron 作业向表 'update' 添加一条记录

table - 'update'
update_id (primary)
timestamp

和

将我网站的每个页面的记录添加到 TableView 中

table - 'views'
page_id
update_id
view_count

我想要实现的是一个查询，它可以将每个 page_id 的最新更新(最高 update_id)与之前的更新进行比较，并返回 View 增加的百分比。我这辈子都想不通。

这里是一些示例数据。

page_id = 1
update_id = 10
view_count = 1251

page_id = 1
update_id = 11
view_count = 1349

Answer 1

您可能想尝试加入 update_id - 1 上的 views 表，如以下查询所示:

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;

测试用例:

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

结果:

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

请注意，如果 previous_count 恰好为 0，则 trend_perc 将为 NULL。