您可以使用带有所需级别的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
中的第一个参数可以是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获取的数据,然后通过众多图表库之一准备显示它,将“minute”列绘制在x轴上,并将“numEvents”绘制在y轴上。
这里有一些其他查询示例:
按小时计算的事件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);
我还应该指出,如果您在此表的起始列上有索引,即使有数亿行,这些查询也会快速完成。希望这可以帮到您!如果您对此有任何其他问题,请告诉我。
Select count(*) and hits
:如果您想列出两个列,我认为您必须在这里使用逗号(',
')而不是 'and
'。否则,查询可能只返回一个列,该列是应用AND
布尔运算符的结果(对于其中两列的值评估为TRUE
的行,其结果为TRUE
,其他行为FALSE
)。 - das-g