oracle查询按小时分组日期差异 [英] oracle query to group date difference by hour

问题描述

我希望能得到一些帮助，以帮助我编写一些我自己编写的SQL程序.

I am hoping to get some help to write some SQL that I have had no success writing myself.

我有一个包含数据的表:

I have a table with the data:

ID    StartDate             EndDate
1     01/01/2000 04:30 AM   01/02/2000 06:15 AM
2     01/03/2000 06:10 AM   01/03/2000 07:00 AM

我需要获得以下信息:

Hour24     Minutes
04         30
05         60
06         65
07         60

换句话说，按小时划分日期范围.然后针对每个范围按小时进行汇总. 我的数据库是Oracle 11G R2，由于某些情况，我担心我不能使用PL/SQL.

In other words, split up date ranges by hour. Then aggregate by hour for each range. My database is Oracle 11G R2 and I am afraid due to circumstances I cannot use PL/SQL.

非常感谢您的帮助！

推荐答案

如果如示例数据所示，日期时间范围可以跨越一天以上，则它们可能会花费一个小时的60分钟以上.例如，我们有一个荒谬的过程，需要48个小时才能完成，因此，在进行检查时(仅说6个小时)，基于24小时计数器的查询将返回60，而实际上可能需要返回120.

If your datetime ranges can span more than a day, as your sample data shows, then it is possible they could take up more than 60 minutes of one hour. For example, we have a ridiculous process that takes over 48 hours to complete, so when checking for, lets just say hour 6, the queries based on a 24 hour counter will return 60, when they might actually need to return 120.

with hours as (
  select trunc(sysdate, 'hh') - ((rownum - 1) / 24) as testhour
  from dual connect by rownum <= (trunc(sysdate, 'hh') - (trunc(sysdate, 'mm') - (1 / 24))) * 24
)
select
  to_char(h.testhour, 'hh24') as Hour24,
  round(sum((least(t.stopdatetime, h.testhour + (1/24)) - greatest(t.startdatetime, h.testhour)) * 1440)) Minutes
from
  datetimes t,
  hours h
where
  t.stopdatetime >= trunc(sysdate, 'mm')
  and (
    h.testhour between t.startdatetime and t.stopdatetime
    or h.testhour = trunc(t.startdatetime, 'hh')
  )
group by
  to_char(h.testhour, 'hh24')
order by 1
;

此查询应处理这些情况，但以当前形式运行需要更长的时间；从本月的第一小时到当前时间，它将返回每小时的日期时间.

This query should handle those situations, but it takes much longer to run in it's current form; it will return a datetime for every hour, from the first hour this month to the current hour.

这篇关于oracle查询按小时分组日期差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！