合并记录，除一列外，每列都有相同的数据

Question

我有一些像这样的记录

barcode:123, event:ASSIGN, event_datetime 9/23/2018 10am

barcode:123, event:CLOSE, event_datetime 9/23/2018 11am

barcode:123, event:ASSIGN, event_datetime 9/30/2018 10am

barcode:123, event:CLOSE, event_datetime 9/30/2018 11am

我想将这4条记录合并为2条记录，如下所示：

barcode:123, event:close, event_start_datetime 9/23/18 10am event_end_datetime 9/23/2018 11am

barcode:123, event:close, event_start_datetime 9/30/2018 10am event_end_datetime 9/30/2018 11am

我尝试过明显的MIN/MAX组合，但由于所有其他列都是相同的数据(除了一列之外，基本上它们看起来都是重复的)，所以它将使用最小/最大组合合并为一列。

我在想一些带有rank()的东西，但听起来它变得相当复杂，我需要一些帮助。

Answer 1

不是最优雅的答案，但可以试试下面的

select barcode, last_event_act, max(max_event_start), max(max_event_end) , event_datetime from 
(
select barcode, last_event_act, 
max(event_end) over (partition by to_char(event_end, 'mm/dd/YYYY')) max_event_end, 
max(event_start) over (partition by to_char(event_start, 'mm/dd/YYYY')) max_event_start, event_datetime
from 
(
select  barcode, last_value(EVENT) OVER (PARTITION BY to_char(EVENT_DATETIME, 'mm/dd/YYYY') ) as last_event_act, 
case 
    when event = 'CLOSE' then event_datetime 
    END 
 as event_end, 
 case 
     when event = 'ASSIGN' then event_datetime 
     END 
as event_start , max(to_char(event_datetime, 'mm/dd/yyyy')) over (partition by to_char(event_datetime, 'mm/dd/YYYY')) as event_datetime from barc
)
    )
    group by barcode, last_event_act, event_datetime