每5分钟内最受欢迎的页面的MySQL记录

Question

我将翻译以下内容：

我有一个MySQL表格“page”，其中包含“page_name”和“page_timestamp”字段。该表格存储了网站上的页面和时间请求。我想编写一个查询，以便在每5分钟的时间段内给出最多点击的页面。

输出结果应如下：

Time    Page           Hits 
12:00  index.html     34
12:05  page1.html     11
12:10  index.html     44

这是一次尝试，但没有输出。

select pages_timestamp,
    (select  count( pages_name)
     from pages t2
     where UNIX_TIMESTAMP(t2.pages_timestamp) DIV 300 =
         UNIX_TIMESTAMP(pages_timestamp)
     group by pages_name
     order by count(pages_name) desc
     limit 1 )  
from pages
where ...
group by UNIX_TIMESTAMP(pages_timestamp ) DIV 300

我相信有更好的方法。

- pineoclean

Answer 1

假设page_timestamp是一个DATETIME类型。

这里有一个我创建的带有一些数据的示例，它有助于可视化输出结果。

首先，让我们创建一个时间舍入函数，这将简化我们的查询：

drop function if exists rtime;
create function rtime (time DATETIME)
returns DATETIME

BEGIN
DECLARE newtime DATETIME;
set newtime = from_unixtime(floor(unix_timestamp(time)/300)*300);
return newtime;
END;

接下来让我们稍微探索一下数据。我想要提取所有的Time、Page和Count(Page_Name)，并按照时间间隔和页面进行分组。

SELECT
Time, Page, Hits 
from (
    select rtime(pages_timestamp) as time
    , page_name as page
    ,  count(page_name) as hits 
    from pages 
    group by page_name, rtime(pages_timestamp) 
    order by rtime(pages_timestamp), hits desc) g ;

这将按时间间隔和点击次数对我们的聚合表进行排序。由于我们的组按最多点击次数降序排列，因此我们可以提取第一行。在MySQL中，我们可以非聚合非分组列（src）。这为我们提供了每个组的第一行，即点击次数最多的行。我们只需通过时间进行分组从上述表中选择SELECT *：

select * from 
(select time, page, hits 
from (
     select rtime(pages_timestamp) as time
     , page_name as page
     ,  count(page_name) as hits 
     from pages 
     group by page_name, rtime(pages_timestamp) 
     order by rtime(pages_timestamp), hits desc
     ) g 
) h group by time;

注意：如果有并列，即两个页面都有最多的点击量，则只会提取一条记录。

- EoinS

---

有没有一个函数可以用于 floor 5 min TIMESTAMP 并将其用作速记？欢迎提供建议。 - EoinS

`select SEC_TO_TIME( (ROUND(TIME_TO_SEC(user_log_pages_timestamp)/300)) * 300) , ( select count(user_log_pages_basename) from user_log_pages p where
SEC_TO_TIME( (ROUND(TIME_TO_SEC(user_log_pages_timestamp)/300)) * 300) = < this part not working SEC_TO_TIME( (ROUND(TIME_TO_SEC(p.user_log_pages_timestamp)/300)) * 300) group by p.user_log_pages_basename order by count(user_log_pages_basename) desc limit 1 ) From user_log_pages
GROUP BY SEC_TO_TIME( (ROUND(TIME_TO_SEC(user_log_pages_timestamp)/300)) * 300) Out 07:55:00 66 08:00:00 66` - pineoclean

正在进行中。快速问题，你的pages_timestamp是什么类型？它是datetime类型吗？还是像12:01一样以字符串形式存储？ - EoinS

你的示例完美地运行了。非常感谢。是的，它是一个日期时间字段。 - pineoclean