php - Cron 作业识别同一 url 中的多次访问？

Question

使用 PHP cron 作业，如何输出访问同一页面超过 2 次的用户？

这是 mySQL 表

id  - user - page - timestamp
340 - 1 - page1 - 2009-05-18 22:11:11
339 - 1 - page1 - 2009-05-18 22:10:01
337 - 1 - page1 - 2009-05-18 22:06:00
336 - 2 - page3 - 2009-05-18 22:00:00
335 - 2 - page3 - 2009-05-18 21:56:00

Answer 1

这可以通过group by和count轻松完成

select
user,page, count(*) as total
from table_name
group by user,page
having total > 2

更新

how can i update another table with these results? user - page - times

这可以通过使用insert into select from以及使用on重复 key 更新来完成

考虑以下因素

mysql> select * from pages ;
+------+------+
| user | page |
+------+------+
|    1 | p1   |
|    1 | p1   |
|    1 | p1   |
|    2 | p1   |
|    2 | p1   |
|    2 | p2   |
|    3 | p2   |
|    3 | p2   |
|    3 | p2   |
+------+------+



create table page_view_log 
   (
       user int,
       page varchar(100),
       times int,
       primary key(user,page)
    );

select * from page_view_log ;

请注意，这里的日志表有复合键(user,page)

现在以下查询将完成这项工作

insert into page_view_log(user,page,times)
select
user,page,total from (
 select user,page, count(*) as total
 from pages
 group by user,page
 having total > 2
)x
on duplicate key update times = total


mysql> select * from page_view_log ;
+------+------+-------+
| user | page | times |
+------+------+-------+
|    1 | p1   |     3 |
|    3 | p2   |     3 |
+------+------+-------+
2 rows in set (0.00 sec)

现在让我们在第一个表中为 user-1 添加更多页面

insert into pages values (1,'p1'),(2,'p2'),(2,'p2');

现在，如果我们再次运行上述查询，它将更新现有数据的总计并插入新值，使用复合键(user,page) 考虑重复项

mysql>  select * from page_view_log ;
+------+------+-------+
| user | page | times |
+------+------+-------+
|    1 | p1   |     4 |
|    2 | p2   |     3 |
|    3 | p2   |     3 |
+------+------+-------+