如何使用开始和结束时间列制作时间存储库？

Question

我有3列，员工_id，start_time和end_time，我想赚1小时的钱，向我展示每小时有多少员工工作。例如，员工A从下午12点到下午3点工作，雇员B从下午2点到下午4点工作，因此，下午12点（1名员工在工作）下午1点（1名员工）下午2点（2名员工在工作）3 pm（ 2个员工）和下午4点（1名员工），我该如何在SQL中进行？让我向您展示开始和结束时间列的图片。

样本输入将为：

预期的结果将是

我想创建一个水桶，以了解一天中的每个小时都在工作。

选择

  Employee_id,
  TIME(shift_start_at,timezone) AS shift_start,
  TIME(shift_end_at,timezone) AS shift_end,
  
FROM
  `employee_shifts` AS shifts
 
WHERE
  DATE(shifts.shift_start_at_local) >= "2022-05-01"
  
GROUP BY
  1,
  2,
  3

Answer 1

假设您在MySQL版本8或更高版本上生成了所有存储桶，请加入开始在启动时间范围内移动到填充时间，请过滤出那些不适用的时间，然后计算例如： -

DROP TABLE IF EXISTS t;

create table t (id int, startts datetime, endts datetime);

insert into t values
(1,'2022-06-19 08:30:00','2022-06-19 10:00:00'),
(2,'2022-06-19 08:30:00','2022-06-19 08:45:00'),
(3,'2022-06-19 07:00:00','2022-06-19 07:59:00');

 with cte as 
(select 7 as bucket union select 8 union select 9 union select 10 union select 11),
cte1 as
(select bucket,t.*,
             floor(hour(startts)) starthour, floor(hour(endts)) endhour
from cte
left join t on cte.bucket between floor(hour(startts)) and floor(hour(endts)) 
) 
select bucket,count(id) nof from cte1 group by bucket
;
+--------+-----+
| bucket | nof |
+--------+-----+
|      7 |   1 |
|      8 |   2 |
|      9 |   1 |
|     10 |   1 |
|     11 |   0 |
+--------+-----+
5 rows in set (0.001 sec)

Answer 2

如果您的时间桶数量有限，也许您可

WITH CTE AS
    (SELECT 
         COUNTRY,
         MONTH,
         TIMESTAMP_DIFF(time_b, time_a, MINUTE) dt,
         METRIC_a,
         METRIC_b
     FROM
         TABLE_NAME)
SELECT
      CASE 
          WHEN dt BETWEEN 0 AND 10 THEN "0-10"
          WHEN dt BETWEEN 10 AND 20 THEN "11-20"
          WHEN dt BETWEEN 20 AND 30 THEN "21-30"
          WHEN dt BETWEEN 30 AND 40 THEN "31-40"
          WHEN dt > 40 THEN ">40"
      END as time_bucket,
      AVG(METRIC_a),
      SUM(METRIC_b)
FROM CTE

​​以以这种方式使用它，我应该强调，如果您有有限的存储桶。如果您有很多存储桶，则可以使用存储桶创建一个基础表，然后左JOIN它以获取结果。

Answer 3

只需在每列中使用一个子查询，即提及两者之间所需的时间戳，还请确保您的start_time和end_time列是时间戳类型。有关更多信息，请共享表结构，示例数据和预期输出

Answer 4

如果我很好地了解，这将是

SELECT HOUR, (SELECT COUNT(*)
  FROM employee
 WHERE start_time <= HOUR
   AND end_time   >= HOUR) AS  working
  FROM schedule HOUR

日程安排是一张具有员工时间表的表。