如何将数据库记录分组为 15 分钟的时间间隔 [英] How to group datebase records into 15-minute time intervals

问题描述

我在 MySQL 数据库中有一个表 user_access，其中包含以下列:

I have a table user_access with the following columns in MySQL database:

id      int
user    int
access_time    datetime

我正在尝试运行一个查询，该查询为我提供给定集合内 user 以 15 分钟为间隔访问系统 (access_time) 的次数时间戳.

I am trying to run a query that gives me the number of times a user accesses the system (access_time) in 15-minute intervals within a given a given set of timestamps.

我当前的查询是:

select user, count(user) as users from user_access
where (access_time between '2013-05-28 02:00:00' and '2013-05-28 10:00:00')
group by user

我想要达到的结果如下:

The results I'm trying to achieve would look like:

Time                User       No of Times
--------------------------------------------------
8:00am - 8:15am     user1          20
8:00am - 8:15am     user2          5
8:15am - 8:30am     user1          15
8:15am - 8:30am     user2          23

推荐答案

首先，您的 WHERE 子句中有一个细微的错误.您需要:

First of all, you have a subtle error in your WHERE clause. You need:

where access_time >= '2013-05-28 02:00:00' 
  and access_time < '2013-05-28 10:00:00'

因为您的四分之一小时范围是从一个特定时间到另一个特定时间之前的那一刻.在您的时间范围结束时，您需要 <，而不是 <=.

because your quarter-hour ranges run from a particular time until the moment before another particular time. You need <, not <=, for the end of your time range.

然后，您需要一个表达式，该表达式可以采用任意 DATETIME 表达式并将其转换为它发生的一刻钟开始的 DATETIME.

Then, you need an expression that can take an arbitrary DATETIME expression and convert it to the DATETIME of the beginning of the quarter-hour in which it occurs.

这样就可以了.

DATE_FORMAT(datestamp,'%Y-%m-%d %H:00:00') +
            INTERVAL (MINUTE(datestamp) -
                      MINUTE(datestamp) MOD 15) MINUTE

例如，'2014-05-07 14:53:22' 会变成 '2014-05-07 14:45:00'.

如果您愿意，您可以将其定义为这样的存储函数:

You can define it as a stored function like this if you like:

DELIMITER $$
DROP FUNCTION IF EXISTS `TRUNC_15_MINUTES`$$
CREATE  FUNCTION `TRUNC_15_MINUTES`(datestamp DATETIME) 
                 RETURNS DATETIME
    NO SQL
    DETERMINISTIC
    RETURN DATE_FORMAT(datestamp,'%Y-%m-%d %H:00:00') +
                INTERVAL (MINUTE(datestamp) -
                          MINUTE(datestamp) MOD 15) MINUTE$$
DELIMITER ;

然后您可以像这样编写查询:

You can then write your query like this:

 select TRUNC_15_MINUTES(access_time) AS period_starting,
        user, count(user) as users 
   from user_access
 where access_time >= '2013-05-28 02:00:00' 
   and access_time <  '2013-05-28 10:00:00'
 group by TRUNC_15_MINUTES(access_time), user
 order by TRUNC_15_MINUTES(access_time), user

这是写在这里.http://www.plumislandmedia.net/mysql/sql-reporting-time-间隔/

这篇关于如何将数据库记录分组为 15 分钟的时间间隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！