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

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

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 

您可以使用 group by 和您想要的级别来执行此操作。这是使用您提供的数据的示例：

首先使用 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

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

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

按小时划分的活动（应用下限功能）

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);

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

希望这可以帮助！如果您对此还有任何其他疑问，请告诉我。