Select top ranked overlapping SQL segment

Question

I am looking for a way to select top ranked overlapping segments. The table will look something like this:

CODE   START                STOP                 RANK
shift  2016-07-20 05:00 AM  2016-07-20 08:00 AM  5 
late   2016-07-20 05:00 AM  2016-07-20 05:08 AM  1
break  2016-07-20 06:00 AM  2016-07-20 06:30 AM  2

This is what I would like my output to be:

CODE   START                STOP                 
late   2016-07-20 05:00 AM  2016-07-20 05:08 AM   
shift  2016-07-20 05:08 AM  2016-07-20 06:00 AM  
break  2016-07-20 06:00 AM  2016-07-20 06:30 AM
shift  2016-07-20 06:30 AM  2016-07-20 08:00 AM  

In general, I would only like to see what the top ranking segment says about this human condition, but if they had no condition other than the standard “shift” segment, just show that they are on shift.

It makes sense? Please resolve any issues or suggested solutions. It seems I can not imagine anything at this moment. I can choose segments with the highest rating, but not when they overlap.

: , , ( , ) 05:00 05:08, 05:08 , , , 05:08 06:00.

06:00 06:30 , . 06:30 08:00, .

, .

Answer 1

, SQL, !: D

. SQLServer ( :), SQL :

create or replace table ranges(
        code varchar,
        beg timestamp_ntz,
        end timestamp_ntz,
        rank integer);
insert into ranges values
        ('shift', '2016-07-20 05:00:00', '2016-07-20 08:00:00', 5),
        ('late',  '2016-07-20 05:00:00', '2016-07-20 05:00:08', 1),
        ('break', '2016-07-20 06:00:00', '2016-07-20 06:30:00', 2);

WITH PERIODS AS (
  select beg, lead(beg, 1) over (order by beg) AS end
  from (select beg from ranges union select end from ranges)
),
MATCHING_RANGES AS (
  select periods.beg, periods.end, ranges.code, ranges.rank
  from periods
  join ranges on (periods.beg >= ranges.beg and periods.end <= ranges.end)
  where periods.end is not null 
),
RANKED_RANGES AS ( 
  select beg, end, code, row_number() over (partition by beg order by rank) in_period_rank 
  from matching_ranges 
)
select code, beg, end from ranked_ranges
where in_period_rank = 1
order by beg;

-------+---------------------+---------------------+
 CODE  |         BEG         |         END         |
-------+---------------------+---------------------+
 late  | 2016-07-20 05:00:00 | 2016-07-20 05:00:08 |
 shift | 2016-07-20 05:00:08 | 2016-07-20 06:00:00 |
 break | 2016-07-20 06:00:00 | 2016-07-20 06:30:00 |
 shift | 2016-07-20 06:30:00 | 2016-07-20 08:00:00 |
-------+---------------------+---------------------+

( "" "" , , ):

• LAG . :

  ---------------------+---------------------+
           BEG         |         END         |
  ---------------------+---------------------+
   2016-07-20 05:00:00 | 2016-07-20 05:00:08 |
   2016-07-20 05:00:08 | 2016-07-20 06:00:00 |
   2016-07-20 06:00:00 | 2016-07-20 06:30:00 |
   2016-07-20 06:30:00 | 2016-07-20 08:00:00 |
   2016-07-20 08:00:00 | [NULL]              |
  ---------------------+---------------------+
  

• MATCHING_RANGES "" ( , NULL), :

  ---------------------+---------------------+-------+------+
           BEG         |         END         | CODE  | RANK |
  ---------------------+---------------------+-------+------+
   2016-07-20 05:00:00 | 2016-07-20 05:00:08 | shift | 5    |
   2016-07-20 05:00:00 | 2016-07-20 05:00:08 | late  | 1    |
   2016-07-20 05:00:08 | 2016-07-20 06:00:00 | shift | 5    |
   2016-07-20 06:00:00 | 2016-07-20 06:30:00 | shift | 5    |
   2016-07-20 06:00:00 | 2016-07-20 06:30:00 | break | 2    |
   2016-07-20 06:30:00 | 2016-07-20 08:00:00 | shift | 5    |
  ---------------------+---------------------+-------+------+
  

  , ,

• RANKED_RANGES :

  ---------------------+---------------------+-------+----------------+
           BEG         |         END         | CODE  | IN_PERIOD_RANK |
  ---------------------+---------------------+-------+----------------+
   2016-07-20 05:00:00 | 2016-07-20 05:00:08 | late  | 1              |
   2016-07-20 05:00:00 | 2016-07-20 05:00:08 | shift | 2              |
   2016-07-20 05:00:08 | 2016-07-20 06:00:00 | shift | 1              |
   2016-07-20 06:00:00 | 2016-07-20 06:30:00 | break | 1              |
   2016-07-20 06:00:00 | 2016-07-20 06:30:00 | shift | 2              |
   2016-07-20 06:30:00 | 2016-07-20 08:00:00 | shift | 1              |
  ---------------------+---------------------+-------+----------------+
  

• 1:)