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

debugcn 发表于 Dev

鲁班·普拉迪普

我的表格如下所示，其中包含Event_No，Events和Date Range。

[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

谢谢

戈登·利诺夫

这是一个悬而未决的问题。

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;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。