时间戳

首页 > 解决方案 > oracle sql查询时间段集合并集

sql - oracle sql查询时间段集合并集

问题描述

我有一个包含以下表格的 oracle 数据库。如何计算每个房间的使用时间？每个房间的时间段可能会重叠，表格结构如下。</p>

t_room_electricity

+------------+--------------------+--------------------+
|  roomcode  |     starttime      |        endtime     |
+------------+--------------------+--------------------+
|        123 | 2019/5/10 10:00:00 | 2019/5/10 11:30:00 |
|        123 | 2019/5/10 10:30:00 | 2019/5/10 11:00:00 |
|        456 | 2019/5/10 11:00:00 | 2019/5/10 12:00:00 |
|        456 | 2019/5/10 13:00:00 | 2019/5/10 14:00:00 |
|        456 | 2019/5/10 13:30:00 | 2019/5/10 15:00:00 |
|        789 | 2019/6/10 14:22:00 | 2019/6/10 14:26:00 |
|        789 | 2019/6/10 14:31:00 | 2019/6/10 14:36:00 |
|        886 | 2019/6/10 14:32:00 | 2019/6/10 14:35:00 |
+------------+--------------------+--------------------+

标签： sqloracle

解决方案

更新答案以迎合 OP 提到的情况。

使用 MATCH_RECOGNIZE

  with data
    as (
    select *
      from t_room_electricity
      match_recognize(
      partition by roomcode
      order by starttime
      measures
        first(starttime) f_starttime
        ,last(starttime) l_starttime
        ,first(endtime)  f_endtime
        ,last(endtime)   l_endtime
        ,min(starttime) as min_starttime
        ,max(endtime) as max_endtime
        ,match_number() as mn
        ,classifier() as cls
      pattern(strt group1*)
      define group1
          as starttime<first(endtime)
      )
    )
select roomcode
      ,round(sum((max_endtime-min_starttime)*24*60)) as diff_in_minutes
 from data
 group by roomcode

请参阅输出 https://dbfiddle.uk/?rdbms=oracle_18&fiddle=783140ede5dfbf8088a6ce77232ecef7

使用 SQL

select x.roomcode
      ,count(distinct x.minutes_worked)
 from (
select a.roomcode
       ,a.starttime
       ,a.endtime
       ,a.starttime + lvl/24/60 as minutes_worked
  from t_room_electricity a
  join (select level as lvl
          from dual
          connect by level<=24*60 
        )b
    on b.lvl<=to_number((a.endtime-a.starttime)*24*60)
)x
group by x.roomcode

见输出.. https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=2add9344f1335376b2fe32df21f769d6

更新了答案以迎合重叠，（基于新记录集）

456 | 2019/5/10 11:00:00 | 2019/5/10 12:00:00 |
456 | 2019/5/10 13:00:00 | 2019/5/10 14:00:00 |
456 | 2019/5/10 13:30:00 | 2019/5/10 15:00:00 |
456 | 2019/5/10 13:45:00 | 2019/5/10 15:05:00 |

考虑重叠以下查询可以达到预期的结果

select roomcode
      ,sum(round(time_in_minutes))
from (
select roomcode
       ,eventdate
       ,case when start_flag=1 
              and (lag(start_flag) over(partition by roomcode order by eventdate asc) = 0 
               or  lag(start_flag) over(partition by roomcode order by eventdate asc) is null)
             then null
            else (eventdate
                 -
                 lag(eventdate) over(partition by roomcode order by eventdate asc)
                 )*24*60
        end as time_in_minutes
from (        
     select roomcode,starttime as eventdate,1 as start_flag from t_room_electricity
      union all
     select roomcode,endtime as eventdate,0 as start_flag from t_room_electricity
     )x
   )y
group by roomcode   


+----------+-----------------------------+
| ROOMCODE | SUM(ROUND(TIME_IN_MINUTES)) |
+----------+-----------------------------+
|      123 |                          90 |
|      456 |                         185 |
|      789 |                           9 |
|      886 |                           3 |
+----------+-----------------------------+

这首先创建了一个内部块，它将每个 eventdatetime 存储到一个名为 EventDate 的列中。然后步骤是检查前一个事件是否是封闭边界（即 start_flag = 1），如果是，则开始计数，否则继续计算前一个事件之间的差异（以分钟为单位）

在此之后，结果按房间代码分组，并总结了 time_in_minutes

https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=650b4f0ad7304d7f44e7fabbca160a90

旧答案 您可以通过使用 group by 表达式来实现这一点，如下所示

select roomcode,sum((endtime-starttime)*24*60) as diff_in_minutes
 from t_room_electricity
 group by roomcode

+----------+-----------------+
| ROOMCODE | DIFF_IN_MINUTES |
+----------+-----------------+
|      123 |             120 |
|      789 |               9 |
|      456 |             210 |
|      886 |               3 |
+----------+-----------------+

请参阅 dbfiddle 链接。 https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=16fd05220157fd274cf0fab4e61c8802

推荐阅读