sql - 如何查询连续数据的时间段值？

Question

我有记录表明工作何时开始和何时结束。直到作业结束才记录结束时间，但开始时间从作业开始时就存在。我想知道在给定时间段内有多少作业在运行。

declare @data table (
    JobId INT IDENTITY(1,1),
    StartedAt DATETIME NOT NULL,
    EndedAt DATETIME NULL
)
insert into @data (StartedAt, EndedAt) 
select '1 Jan 2010 8:00', '1 Jan 2010 8:30'
union select '1 Jan 2010 8:00', '1 Jan 2010 9:00'
union select '1 Jan 2010 8:00', '1 Jan 2010 9:20'
union select '1 Jan 2010 9:00', '1 Jan 2010 9:20'
union select '1 Jan 2010 9:10', NULL

鉴于上述情况，我将如何查询每小时运行的作业数？我希望结果表明在 8:00 到 8:59 的时间段内有 3 个作业在运行，在 9:00 到 9:59 的时间段内有 4 个作业在运行，如下所示:

Time period     Jobs08:00..08:59    309:00..09:59    4

我可以修改架构，并且我可以一些影响数据的记录方式，如果这会使查询更简单或更高效的话。Blockquote

Answer 1

在不花费大量时间解决问题的情况下，我建议最简单的解决方案是创建一个天数表和一个小时表(然后通过创建笛卡尔连接得到一个小时数和天数表)例如

天(ondate 不为空)插槽(开始时间不为空，结束时间不为空)

然后确定在这些时段内具有开始或结束时间的作业的数量:

SELECT days.ondate, slots.start_time, slots.end_time, (
   SELECT COUNT(*) FROM @data
   WHERE (@data.start_time BETWEEN
       STR_TO_DATE(
          CONCATENATE(DATE_FORMAT(days.ondate, 'YYYYMMDD'),
             DATE_FORMAT(slots.start_time,'HH24MISS')), 
          'YYYYMMDDHH25MISS')
     AND
       STR_TO_DATE(
          CONCATENATE(DATE_FORMAT(days.ondate, 'YYYYMMDD'),
             DATE_FORMAT(slots.end_time,'HH24MISS')), 
          'YYYYMMDDHH25MISS')
  ) OR (@data.start_time BETWEEN
          CONCATENATE(DATE_FORMAT(days.ondate, 'YYYYMMDD'),
             DATE_FORMAT(slots.start_time,'HH24MISS')), 
          'YYYYMMDDHH25MISS')
     AND
       STR_TO_DATE(
          CONCATENATE(DATE_FORMAT(days.ondate, 'YYYYMMDD'),
             DATE_FORMAT(slots.end_time,'HH24MISS')), 
          'YYYYMMDDHH25MISS')
  ) AS jobs_running
  FROM days, slots
  ;

当然，这很快就会变得非常低效 - 所以您可能需要考虑预聚合。