lead函数

1.

select  name ,process_operation_name, track_in_time , receiver , track_in_quantity,track_out_time , passer,track_out_quantity , 
       processflow_name,
       processflow_version,
       product_spec_name 
      from (            
       select  distinct timekey,
       name, 
       product_quantity as track_in_quantity , 
       last_event_time as track_in_time, 
       last_event_name as Track_in, 
       last_event_user as receiver,
       lead(last_event_name) over (partition by name,process_operation_name ORDER BY timekey) track_out,
       lead(last_event_time) over (partition by name,process_operation_name ORDER BY timekey) track_out_time,
       lead(last_event_user) over (partition by name,process_operation_name ORDER BY timekey) passer,
       lead(product_quantity) over (partition by name,process_operation_name ORDER BY timekey) track_out_quantity,
    process_operation_name,
       processflow_name,
       processflow_version,
       product_spec_name 
       from job_lot_history 
           where last_event_name = 'TrackIn' 
               or last_event_name = 'TrackOut'
               or last_event_name = 'CancelTrackIn') a where a.Track_in = 'TrackIn' and a.track_in_time != track_out_time
               and name =@lotname ;

例子

1.Lot 的TrackIn ，TrackOut ,CancelTrackIn事件

SELECT 
    name,
    timekey,
    process_operation_name,
    processflow_name,
    processflow_version,
    product_spec_name,
    last_event_name,
    last_event_time
FROM
    job_lot_history
WHERE
    (last_event_name = 'TrackIn'
        OR last_event_name = 'TrackOut'
        OR last_event_name = 'CancelTrackIn')
        AND name = 'GGLA598190409019';

 2.将每一行和下一行的数据合并。

SELECT 
    name,
    timekey,
    process_operation_name,
    processflow_name,
    processflow_version,
    product_spec_name,
    last_event_name as track_in,
    last_event_time as track_out,
    last_event_user as receiver,
    lead(last_event_name) over (partition by name,process_operation_name ORDER BY timekey) track_out,
    lead(last_event_time) over (partition by name,process_operation_name ORDER BY timekey) track_out_time,
   	lead(last_event_user) over (partition by name,process_operation_name ORDER BY timekey) passer
FROM
    job_lot_history
WHERE
    (last_event_name = 'TrackIn'
        OR last_event_name = 'TrackOut'
        OR last_event_name = 'CancelTrackIn')
        AND name = 'GGLA598190409019';

　　

 

3.筛选数据

 

select  name ,process_operation_name, track_in_time , receiver , track_in_quantity,track_out_time , passer,track_out_quantity , 
       processflow_name,
       processflow_version,
       product_spec_name 
      from (            
       select  distinct timekey,
       name, 
       product_quantity as track_in_quantity , 
       last_event_time as track_in_time, 
       last_event_name as Track_in, 
       last_event_user as receiver,
       lead(last_event_name) over (partition by name,process_operation_name ORDER BY timekey) track_out,
       lead(last_event_time) over (partition by name,process_operation_name ORDER BY timekey) track_out_time,
       lead(last_event_user) over (partition by name,process_operation_name ORDER BY timekey) passer,
       lead(product_quantity) over (partition by name,process_operation_name ORDER BY timekey) track_out_quantity,
    process_operation_name,
       processflow_name,
       processflow_version,
       product_spec_name 
       from job_lot_history 
           where last_event_name = 'TrackIn' 
               or last_event_name = 'TrackOut'
               or last_event_name = 'CancelTrackIn') a where a.Track_in = 'TrackIn' and a.track_in_time != track_out_time
               and name ='GGLA598190409019' ;