Mysql：计算访问之间的平均时间 [英] Mysql: Calculate averge time between visits

问题描述

这与我的其他问题有关。

我有这个表

CREATE OR REPLACE TABLE hits (ip bigint, page VARCHAR(256), agent VARCHAR(1000), 
                              date datetime)

我想计算每个页面的googlebot访问之间的平均时间。

and I want to calculate average time between googlebot visit for every page.

... WHERE agent like '%Googlebot%' group by page order by date

像

select datediff('2010-09-18 04:20:47', '2010-09-16 05:23:04')

但表中的每个日期

如果没有mysql方式，我该如何在PHP中执行此操作？

but for every date in table
If there is no mysql way, how can I do this in php?

推荐答案

SELECT page, TIMESTAMPDIFF(SECOND, MIN(date), MAX(date)) / (COUNT(*)-1) FROM hits 
WHERE agent like '%Googlebot%' GROUP BY page;

TIMESTAMPDIFF（SECOND，a，b）返回日期表达式a和b之间的秒差。对于每个页面，查询查找第一次和最后一次访问的日期以及总访问次数，并计算算术平均值。

TIMESTAMPDIFF(SECOND, a, b) returns the difference in seconds between the date expressions a and b. For each page, the query finds the date of the first and last visit and the total count of visits, and calculates the arithmetic average.

这篇关于Mysql：计算访问之间的平均时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！