MySQL查询按时间间隔对时间间隔进行分组

Question

很多员工在工作，我需要每隔1小时找出在工作的员工数量。员工的工作时间表是不同的。有些工作时间为 08:00-16:00，有些工作时间为 07:00-15:00，有些工作时间为 10:00-22:00。我需要一份报告，以显示 1 小时前工作的 no_of_employees。

Hours  08::00- 09:00       No_of_employees working 12,
Hours  09:00-10:00         No_of_employees working 13,
Hours  10:00-11:00         No_of_employees working 14,
etc

我意识到间隔越短，错误就越少。我使用了下面的代码但没有成功。

SELECT date-time_from, date-time_to, COUNT(*),
    DATE(DATE_SUB(date-time_from, INTERVAL 1 HOUR))
FROM `employee_roster`
WHERE `work_status` = 'WORK' 
GROUP BY DATE(DATE_SUB(date-time_from, INTERVAL 1 HOUR))

请根据您的要求查找表的抽象结构。

TABLE  `employee_roster` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `employee_id` int(10) NOT NULL,
  `date-time_from` datetime  NOT NULL,
  `date-time_to` datetime NOT NULL,
  `work_status` var-char(20) NOT NULL DEFAULT '',

  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;     

Date-time_from 和 Date-time_to 是构成工作时间(期间)的两个日期时间字段，即 2015/0101 06:00 - 2015/01/01 18:30

请查看下面的输出。

    DatetimeFrom        DatetimeTo         no_of_employee

    2015-01-05 01:00:00 2015-01-05 02:00:00     168
    2015-01-05 06:00:00 2015-01-05 07:00:00     84
    2015-01-05 07:00:00 2015-01-05 08:00:00     84
    2015-01-05 07:20:00 2015-01-05 08:20:00     42
    2015-01-05 19:00:00 2015-01-05 20:00:00     42
    2015-01-05 19:20:00 2015-01-05 20:20:00      84
    2015-01-06 01:00:00 2015-01-06 02:00:00     126
    2015-01-06 06:00:00 2015-01-06 07:00:00      42
    2015-01-06 07:00:00 2015-01-06 08:00:00      42
    2015-01-06 07:20:00 2015-01-06 08:20:00      42 

奇怪的是，它坚持花名册的开始时间，即 07:00、08:00，即使我的工作时间涵盖整个 24 小时。在我看来，我用 1 替换以运行查询的 I 变量使条件仅在第一条记录之前有效。如何在 sql 查询中增加变量 i？

Answer 1

有很多方法可以解决这个问题，您可能需要调整此处提供的查询。

这个答案应该能为您指明正确的方向。

首先，您需要一个包含所有可能工作持续时间的帮助表(我假设连续工作时间不超过 12 小时，但请随意调整)

create table hours (i int unsigned not null);
insert into hours(i) values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12);

然后，使用此表，您需要扩展原始表，以便为每个用户的每个工作小时获得一行。最后，您想要按每 1 小时的间隔进行分组。类似这样的事情(同样，请随意调整，特别是如果您想删除“日期”部分并只保留时间):

SELECT DATE_ADD(datetime_from, INTERVAL i HOUR) AS start, DATE_ADD(datetime_from, INTERVAL i+1 HOUR) as end, COUNT(employee_id)
FROM employee_roster
JOIN hours ON i <= TIMESTAMPDIFF(HOUR, datetime_from, datetime_to)
GROUP BY CONCAT(start,end);