按 60 分钟滑动窗口分组

Question

我有一个表，其中包含有关作业的信息：作业类型、开始时间和结束时间。

我想要一份报告，显示每小时运行了多少个作业 - 但不是时钟时间，而是从该组中第一个作业的时间起 60 分钟的偏移时间（它们是一批作业，所以我确定工作类型不会在时间上重叠）。

像这样的事情：

num_of_jobs | job_type | hour
-----------------------------
1254        | B        | 2011-08-22 13:47
9983        | B        | 2011-08-22 14:47
9072        | B        | 2011-08-22 15:47
20309       | B        | TOTAL
79          | C        | 2011-08-22 16:02
105         | C        | 2011-08-22 17:02
184         | C        | TOTAL
10234       | D        | 2011-08-22 17:29
9882        | D        | 2011-08-22 18:29
20116       | D        | TOTAL

如果可以按小时分组（例如 12:00、13:00、14:00），我可以像这样轻松地完成此操作（实际查询的非常简化的版本）：

select count(job_id) number_of_jobs, job_type,
    case when to_char(end_date,'YYYY-MM-DD HH24') is not null
         then to_char(end_date,'YYYY-MM-DD HH24')||':00'
         else 'TOTAL'
    end Date_and_hour
from my_jobs
where end_date is not null
group by rollup (to_char(end_date,'YYYY-MM-DD HH24')) , job_type
order by to_char(end_date,'YYYY-MM-DD HH24') asc, job_type asc;

除了按小时分组之外，而不是基于 job_type 的批次中的最小开始时间。

我真的不太确定如何让它发挥作用。

（使用Oracle 10g）

Answer 1

在我看来，真正的问题是找到每一行属于哪个小时组。一旦获得，您可以将其插入您提供的查询中以获得最终答案。要获取小时组，我们所要做的就是计算出自该组中最早的时间以来已经过去了多少小时：

select floor((end_date - min(end_date) 
                         over (partition by job_type))*24)+1 as hour_group
from my_jobs
where end_date is not null

我使用 trunc 因为我们只关心整个小时并添加一个小时这样我们就从一开始计数，而不是从零开始。由于此解决方案使用分析函数，因此您需要在分组之前将其放入子查询中。

---

稍微玩了一下，我最终得到以下（未经测试的）查询：

SELECT   COUNT(job_id) number_of_jobs, 
         job_type, 
         min_time + FLOOR((end_date - min_time) * 24) / 24 AS date_and_hour
FROM     (SELECT job_id, 
                 job_type, 
                 end_date, 
                 MIN(end_date) OVER (PARTITION BY job_type) AS min_time
          FROM   my_jobs
          WHERE  end_date IS NOT NULL)
GROUP BY ROLLUP(min_time + FLOOR((end_date - min_time) * 24) / 24), job_type
ORDER BY hour_group ASC, job_type ASC;

原理与我最初的答案相同，我只是稍微移动了数学。