MySql 查询时间间隔数据的直方图

Question

我有一个这种类型的事件输入

event user
event start
event end
event type

插入到 MySql 表中，每行都以 user+start 作为主键。

我需要按时间间隔(比如分钟)计算每个时间间隔发生的事件来查询直方图类型。像这样的东西:

SELECT count(*) as hits FROM events 
WHERE type="browsing" 
GROUP BY time_diff("2015-1-1" AND "2015-1-2") / 60 * second

但是除了编写代码之外，我找不到在 SQL 中执行此操作的任何方法，知道吗？

示例数据

user, start, end, type
1, 2015-1-1 12:00:00, 2015-1-1 12:03:59, browsing
2, 2015-1-1 12:03:00, 2015-1-1 12:06:00, browsing
2, 2015-1-1 12:03:00, 2015-1-1 12:06:00, eating
3, 2015-1-1 12:03:00, 2015-1-1 12:08:00, browsing

结果应该是这样的:

         ^
count    |
browsing |
users    |       *
         |       *  *  *  *
         | *  *  *  *  *  *  *  *
         --|--|--|--|--|--|--|--|--|--> minute
         0  1  2  3  4  5  6  7  8  9 

Answer 1

您可以使用具有所需级别的分组依据来执行此操作。以下是使用您提供的数据的示例:

首先是创建表并填充它的 SQL。这里的 ID 列不是“必需的”，但如果表很大或上面有索引，则建议使用。

CREATE TABLE `test`.`events` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user` INT NULL,
  `start` DATETIME NULL,
  `end` DATETIME NULL,
  `type` VARCHAR(45) NULL,
  PRIMARY KEY (`id`));

INSERT INTO events (user, start, end, type) VALUES 
(1, '2015-1-1 12:00:00', '2015-1-1 12:03:59', 'browsing'),
(2, '2015-1-1 12:03:00', '2015-1-1 12:06:00', 'browsing'),
(2, '2015-1-1 12:03:00', '2015-1-1 12:06:00', 'eating'),
(3, '2015-1-1 12:03:00', '2015-1-1 12:08:00', 'browsing');

获取持续时间分钟数与事件数的有序对列表:

然后可以使用 timestampdiff 函数轻松编写查询，如下所示:

SELECT 
    TIMESTAMPDIFF(MINUTE, start, end) as minutes,
    COUNT(*) AS numEvents
FROM
    test.events
GROUP BY TIMESTAMPDIFF(MINUTE, start, end)

输出:

minutes      numEvents
3            3
5            1

选择中的第一个参数可以是 FRAC_SECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER 或 YEAR 之一。

以下是您可以执行的查询的更多示例:

按小时划分的事件(应用 floor 函数)

SELECT 
    TIMESTAMPDIFF(HOUR, start, end) as hours,
    COUNT(*) AS numEvents
FROM
    test.events
GROUP BY TIMESTAMPDIFF(HOUR, start, end)

**按小时排列的事件，格式更好**

SELECT 
    CONCAT("<", TIMESTAMPDIFF(HOUR, start, end) + 1) as hours,
    COUNT(*) AS numEvents
FROM
    test.events
GROUP BY TIMESTAMPDIFF(HOUR, start, end)

您可以按各种选项进行分组，但这绝对可以帮助您入门。大多数绘图包允许您指定任意 x y 坐标，因此您无需担心 x 轴上的缺失值。

获取特定时间事件数量的有序对列表(用于记录):请注意，这是留作引用。

现在进行查询。首先，您必须选择要用于分组的项目。例如，一项任务可能需要一分钟多的时间，因此开始和结束时间可能不同。对于所有这些示例，我都以开始时间为基础，因为那是事件实际发生的时间。

要按分钟对事件计数进行分组，您可以使用如下查询:

SELECT 
     DATE_FORMAT(start, '%M %e, %Y %h:%i %p') as minute, 
     count(*) AS numEvents 
FROM test.events 
GROUP BY YEAR(start), MONTH(start), DAYOFMONTH(start), HOUR(start), MINUTE(start);

请注意这是如何按所有项目分组的，从年份开始，一直到分钟。我还将分钟显示为标签。结果输出如下所示:

minute                      numEvents
January 1, 2015 12:00 PM    1
January 1, 2015 12:03 PM    3

这是您随后可以使用 php 获取的数据，并准备好由众多​​图形库之一显示，在 x 轴上绘制分钟列，在 y 轴上绘制 numEvents。

以下是您可以执行的查询的更多示例:

按小时统计的事件

SELECT 
     DATE_FORMAT(start, '%M %e, %Y %h %p') as hour, 
     count(*) AS numEvents 
FROM test.events 
GROUP BY YEAR(start), MONTH(start), DAYOFMONTH(start), HOUR(start);

事件按日期

SELECT 
    DATE_FORMAT(start, '%M %e, %Y') as date, 
    count(*) AS numEvents 
FROM test.events 
GROUP BY YEAR(start), MONTH(start), DAYOFMONTH(start);

每月事件

SELECT 
    DATE_FORMAT(start, '%M %Y') as date, 
    count(*) AS numEvents 
FROM test.events 
GROUP BY YEAR(start), MONTH(start);

按年分类的事件

SELECT 
    DATE_FORMAT(start, '%Y') as date, 
    count(*) AS numEvents 
FROM test.events 
GROUP BY YEAR(start);

我还应该指出，如果您在该表的起始列上有一个索引，这些查询将很快完成，即使有数亿行。

希望对您有所帮助!如果您对此有任何其他问题，请告诉我。