Oracle calculation of the sum of the sections

Viewed 58

Welcome, my task is to calculate the employee's working time, I have individual jobs (sections A-B), work is done on multiple machines (1+), I need to calculate from such data sections of time when the employee worked on any number of machines.

Green is the expected result: enter image description here

Data:

with t as (
  select 1 as id, 1.5 as DSTART, 3 as DSTOP from dual union all
  select 2 as id, 2 as DSTART, 3.5 as DSTOP from dual union all
  select 3 as id, 4.5 as DSTART, 8 as DSTOP from dual union all
  select 4 as id, 7 as DSTART, 9 as DSTOP from dual union all
  select 5 as id, 7 as DSTART, 12 as DSTOP from dual union all
  select 6 as id, 7 as DSTART, 11.5 as DSTOP from dual
)
select * from t
order by DSTART, DSTOP;

enter image description here

I want to get this set of data in a SQL (View) query, e.g. using a tree? or other solution:

enter image description here

Platform: Oracle 11g

2 Answers

If you are using Oracle 12c (or later), have a look at MATCH_RECOGNIZE()

Test table / data

-- id 5: not in the image you've uploaded
-- id 6: dstop value taken from the uploaded image
create table wt( id, dstart, dstop )
as
  select 1 as id, 1.5 as DSTART, 3 as DSTOP from dual union all
  select 2 as id, 2 as DSTART, 3.5 as DSTOP from dual union all
  select 3 as id, 4.5 as DSTART, 8 as DSTOP from dual union all
  select 4 as id, 7 as DSTART, 9 as DSTOP from dual union all
  select 7 as id, 7 as DSTART, 12 as DSTOP from dual union all
  select 6 as id, 7 as DSTART, 11.5 as DSTOP from dual
;

Query

select *
from wt
match_recognize (
  order by id
  measures
    first( dstart )  firststart_
  , last( dstop)     laststop_
  one row per match
  pattern( A B* )
  define
    B as prev( dstop ) > dstart
) ;

-- result
+-----------+---------+
|FIRSTSTART_|LASTSTOP_|
+-----------+---------+
|1.5        |3.5      |
|4.5        |12       |
+-----------+---------+

See DBfiddle.

EDIT: for Oracle 11g

As you are using Oracle 11g (information added after the initial answer), you cannot use MATCH_RECOGNIZE(). However, you can probably use analytic functions for this. One of the problems is, that we need to {1} find the "groups" that the IDs and values belong to. Once this is achieved, we can {2} use GROUP BY, and call MIN() and MAX() to get the results.

{1} find groups

select id, value_, startstop_
, case
    when startstop_ = 1 and lag_ < value_ then 1  -- group start
    else 0                                        -- in group
  end groupstart_
from (
  select id, value_, startstop_
  , lag( value_, 1, 0 ) over ( order by id, value_ )  lag_
  from wt
  unpivot (
    value_ for startstop_ in ( dstart as 1, dstop as -1 )
  )
)
;

+--+------+----------+-----------+
|ID|VALUE_|STARTSTOP_|GROUPSTART_|
+--+------+----------+-----------+
|1 |1.5   |1         |1          |
|1 |3     |-1        |0          |
|2 |2     |1         |0          |
|2 |3.5   |-1        |0          |
|3 |4.5   |1         |1          |
|3 |8     |-1        |0          |
|4 |7     |1         |0          |
|4 |9     |-1        |0          |
|6 |7     |1         |0          |
|6 |11.5  |-1        |0          |
|7 |7     |1         |0          |
|7 |12    |-1        |0          |
+--+------+----------+-----------+

{2} Numbering of groups

create or replace view group_view as
with cte_ as (                                      -- CTE - Oracle: "subquery factoring"
  select id, value_, startstop_
  , case
      when startstop_ = 1 and lag_ < value_ then 1  -- group start
      else 0                                        -- in group
    end groupstart_
  from (
    select id, value_, startstop_
    , lag( value_, 1, 0 ) over ( order by id, value_ )  lag_
    from wt
    unpivot (
      value_ for startstop_ in ( dstart as 1, dstop as -1 )
    )
  )
)
select
  id, value_, startstop_
, sum( groupstart_ ) over ( order by id ) group_
from cte_ ;

You don't necessarily need to code a view (this has just been added for clarity here). Also, you may be able to refactor the above code, and make it more compact.

Now we can:

select * from group_view ;

+--+------+----------+------+
|ID|VALUE_|STARTSTOP_|GROUP_|
+--+------+----------+------+
|1 |1.5   |1         |1     |
|1 |3     |-1        |1     |
|2 |2     |1         |1     |
|2 |3.5   |-1        |1     |
|3 |4.5   |1         |2     |
|3 |8     |-1        |2     |
|4 |7     |1         |2     |
|4 |9     |-1        |2     |
|6 |7     |1         |2     |
|6 |11.5  |-1        |2     |
|7 |7     |1         |2     |
|7 |12    |-1        |2     |
+--+------+----------+------+

-- and
select group_, min( value_ ), max( value_ )
from group_view
group by group_
order by 1
;

+--+---------------+---------------+
|G_|MIN(STARTSTOP_)|MAX(STARTSTOP_)|
+--+---------------+---------------+
|1 |1.5            |3.5            |
|2 |4.5            |12             |
+--+---------------+---------------+

SQLfiddle here.

http://sqlfiddle.com/#!4/06c51b/7

select gr_nr, min(d1) as DSTART, max(d2) as DSTOP
from (
  select d1, d2, ISLAND, RN - RN_IN_GROUP as GR_NR
  from (
    select d1, d2, ISLAND, row_number() over (order by d1) as RN,  
    row_number() over (partition by ISLAND order by d1) RN_IN_GROUP
    from (
      select d1, d2,
        decode((select count(*) from wt 
          where                  
          d1 >= wt.DSTART and d2 <= wt.DSTOP) , 0, 0, 1) as ISLAND
      from (
        select START_STOP as d1, lead(START_STOP) over (order by START_STOP) as d2
        from (
        select * from wt
        UNPIVOT(
            start_stop
            for foo in (
                DSTART as 'A',
                DSTOP as 'B'
              )
          )
        )
        order by START_STOP    
      )
    )
    where d1 <> d2
    order by d1 
  )
  where ISLAND > 0 
  order by d1
) 
group by gr_nr
order by min(d1)

Result:

enter image description here

Related