sql - How to relate not consecutive data in a sorted list? -

I have a table and data in the table below, the 'time' column shows the time when data is inserted Gaya. NODE_ENTER it indicates that the node starts or ends when it has begun node that means is equal to 1 and start time Ap_det and Ap_taim when NODE_ENTER 0, then finish means that node and Now, ap_date and ap_time show the end time.

For this table, I want to get the total time on each node (for start node, area_ode, elsewhere, and sub-boundary node). But how can I get it right?

  id time REFID node NODE_ENTER APP_DATE APP_TIME 12345 0 10150 starting 1 20,130,605 94601 23456 3,7093E + 11 10150 start 0 20,130,611 90,115 56789 3,7093E + 11 10150 area 1 20,130,611 90,116 67,891 3,72167E +11 10150 sub-region 1 20130625 162,419 23,432 3,72171E + 11 10150 sub-region 0 20,130,625 173,226 87,656 3,72171E + 11 10150 area 0 20,130,625 173,227 34,599 3,72171E + 11 10150 than 1 20130625 173,227 87,654 3,72237E + 11 10150 anywhere 0 20,130,626 120,121 66,789 3,72237E + 11 10150 area 1 20,130,626 120,122 99891 3,72167E + 11 10150 sub-region 1 20,130,627 104,537 21132 3,72346E + 11 10150 sub-region 0 20,130,627 181,136 82,356 3,79515E + 11 10150 area 0 20130628 162656 < / Code> 

I like the LAG function below Defense used, but I could do it consistently and do not get the time difference between the designated lines. For example, area nodes are not persistent because the sub region should be between two field nodes. Now I can get time difference between two start nodes or two sub-region nodes, but how can I get time difference between two field nodes? When I try to use the lag function in the node name, it does not work again because I can have the same node name. Area nodes can start somewhere after the node and finish again

  lag (P.NODE, 1, '-'). PREV_NODE, (as ordered by P.REFID, p.time) more than P.APP_DATE, 1, '-') (START_DATE, lag (P.APP_TIME, 1 as ordered by P.REFID, p) More than) .time ("P.REFID, p.time order"), more than (Duration of time), '000000'). 
 
 I think you  partition  on  apiid  and  noDE  while  command  the  APP_DATE  and  APP_TIME : 
 
  REF_ID, node, node_enter, app_date, app_time, last_node_enter, last_app_date, the last_app_time ( P.ref_id, p.node, p.node_enter, p.app_date, p.app_time, lag (p.node_enter) A.) than (P.IT_id, p.node ORDER BY p.app_date, p.app_time) AS Antim_anod_antr, Elaji (Pi.api_det) over (Piarfaidi, Pi.ankokdi.adiiar, Pi.api_det, P Participation by AP_time) AS Last_app_date, Lag (p.app_time) Over (Partition by nod_enter = 1 and last_node_enter = 0; node ORDER BY p.app_date, p.app_time) my_table_time;  
 
 Let me give you my date and time as  DATE  s or  TIMESTAMP  as opposed to  number  I recommend S or  VARCHAR2  s.