根据最小日期时间和最大日期时间追加名称

Question

我的表格中有Event_No、事件和日期范围，如下所示。

[Event_No, Events, Date Range
       1     PR    2/6/2018 12:01:00 AM
       1     PR    2/6/2018 12:02:00 AM
       1     PR    2/6/2018 12:03:00 AM
       1     RR    2/6/2018 12:04:00 AM
       1     RR    2/6/2018 12:05:00 AM
       1     RR    2/6/2018 12:06:00 AM
       1     SR    2/6/2018 12:07:00 AM
       1     SR    2/6/2018 12:08:00 AM
       1     SR    2/6/2018 12:09:00 AM
       2     PR    2/6/2018 01:01:00 AM
       2     PR    2/6/2018 01:02:00 AM
       2     PR    2/6/2018 01:03:00 AM
       2     RR    2/6/2018 01:04:00 AM
       2     RR    2/6/2018 01:05:00 AM
       2     RR    2/6/2018 01:06:00 AM
       2     SR    2/6/2018 01:07:00 AM
       2     SR    2/6/2018 01:08:00 AM
       2     SR    2/6/2018 01:09:00 AM

我需要用‘IN’(( Event -‘IN’)的串联)显示Min datetime各自的事件名称，用Out(( Event -‘Out’)的串联)显示Max datetime各自的事件。我需要如下所示的最终输出

Event_No   Events            Date Range      EventInOut
       1     PR    2/6/2018 12:01:00 AM       PR-IN
       1     PR    2/6/2018 12:03:00 AM       PR-OUT
       1     RR    2/6/2018 12:04:00 AM       RR-IN
       1     RR    2/6/2018 12:06:00 AM       RR-OUT
       1     SR    2/6/2018 12:07:00 AM       SR-IN
       1     SR    2/6/2018 12:09:00 AM       SR-OUT
       2     PR    2/6/2018 01:01:00 AM       PR-IN
       2     PR    2/6/2018 01:03:00 AM       PR-OUT
       2     RR    2/6/2018 01:04:00 AM       RR-IN 
       2     RR    2/6/2018 01:06:00 AM       RR-OUT
       2     SR    2/6/2018 01:07:00 AM       SR-IN
       2     SR    2/6/2018 01:09:00 AM       SR-OUT

谢谢

Answer 1

这是一个差距和岛屿问题。

select event_no, date,
       min(date), max(date)
from (select t.*,
             row_number() over (partition by event_no order by date) as seqnum,
             row_number() over (partition by event_no, event order by date) as seqnum_e
      from t
     ) t
group by event_no, event;

这会将这些值放在一行上，这可能会满足您的需求。

您还可以使用lead()和lag()

select t.*,
       (event || '-' || (case when prev_event is null then 'IN' else 'OUT' end))
from (select t.*,
             lag(event) over (partition by event_no order by date) as prev_event,
             lead(event) over (partition by event_no order by date) as next_event
      from t
     ) t
where prev_event is null or next_event is null;

Answer 2

下面的代码应该会让你得到结果……

create table events(event_no int, events varchar(10),date_range timestamp)

insert into events
select 1,'PR',timestamp '2/6/2018 12:01:00 AM' union all
select 1,'PR',timestamp '2/6/2018 12:02:00 AM' union all
select 1,'PR',timestamp '2/6/2018 12:03:00 AM' union all
select 1,'RR',timestamp '2/6/2018 12:04:00 AM' union all
select 1,'RR',timestamp '2/6/2018 12:05:00 AM' union all
select 1,'RR',timestamp '2/6/2018 12:06:00 AM' union all
select 1,'SR',timestamp '2/6/2018 12:07:00 AM' union all
select 1,'SR',timestamp '2/6/2018 12:08:00 AM' union all
select 1,'SR',timestamp '2/6/2018 12:09:00 AM' union all
select 2,'PR',timestamp '2/6/2018 01:01:00 AM' union all
select 2,'PR',timestamp '2/6/2018 01:02:00 AM' union all
select 2,'PR',timestamp '2/6/2018 01:03:00 AM' union all
select 2,'RR',timestamp '2/6/2018 01:04:00 AM' union all
select 2,'RR',timestamp '2/6/2018 01:05:00 AM' union all
select 2,'RR',timestamp '2/6/2018 01:06:00 AM' union all
select 2,'SR',timestamp '2/6/2018 01:07:00 AM' union all
select 2,'SR',timestamp '2/6/2018 01:08:00 AM' union all
select 2,'SR',timestamp '2/6/2018 01:09:00 AM'

 with data
  as (
 select *
       ,row_number() over(partition by event_no,events order by date_range) as rnk
       ,date_range - (row_number() over(partition by event_no,events order by date_range)* interval '1 minute' ) as col_range
  from events
      )
     ,iterim_data
     as(
select *
       ,row_number() over(partition by col_range order by date_range asc) as rnk_asc
       ,row_number() over(partition by col_range order by date_range desc) as rnk_desc
  from data
       )
select event_no,events,date_range
       ,case when rnk_asc=1 then concat(events,'-IN')
             when rnk_desc=1 then concat(events,'-OUT')
        end             
  from iterim_data
where (rnk_asc=1 or rnk_desc=1)

db fiddle link https://dbfiddle.uk/?rdbms=postgres_12&fiddle=00304c5f260a199a99c8d43bd351c3a1