CODEWITHSUNDEEP
sqloracle-database
engkok
engkok

Reputation: 25

How to relate not consecutive data in a sorted list?

I have a table and data like below. In that table, 'TIME' column shows the time that the data inserted. NODE_ENTER indicates that the node starts or ends. When it equals to 1 it means that node started and the start time is app_date and app_time. When the NODE_ENTER is 0 it means that node is ended and now, app_date and app_time shows the end time.

For this table, I want to get total time on each node(for start node, region_node, somewhere and sub_region node). But how can I get that correctly? 

ID     TIME         REFID   NODE      NODE_ENTER    APP_DATE    APP_TIME
12345   0           10150   start       1           20130605    94601
23456   3,7093E+11  10150   start       0           20130611    90115
56789   3,7093E+11  10150   region      1           20130611    90116
67891   3,72167E+11 10150   sub-region  1           20130625    162419
23432   3,72171E+11 10150   sub-region  0           20130625    173226
87656   3,72171E+11 10150   region      0           20130625    173227
34599   3,72171E+11 10150   somewhere   1           20130625    173227
87654   3,72237E+11 10150   somewhere   0           20130626    120121
66789   3,72237E+11 10150   region      1           20130626    120122
99891   3,72167E+11 10150   sub-region  1           20130627    104537
21132   3,72346E+11 10150   sub-region  0           20130627    181136
82356   3,79515E+11 10150   region      0           20130628    162656

I used LAG function like below, but I couldn't get time difference between two consecutive and same named rows. For example, region nodes are not consecutive because sub-regions must be between two region 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 region nodes? When I tried to use LAG function over node name it is not working again as I can have same node names. Region node can start after somewhere node and end again.

LAG(P.NODE, 1, '-') OVER(ORDER BY P.REFID,p.time) AS PREV_NODE,
LAG(P.APP_DATE, 1, '-') OVER(ORDER BY P.REFID,p.time) AS START_DATE,
LAG(P.APP_TIME, 1, '000000') OVER(ORDER BY P.REFID, p.time) AS START_TIME

Upvotes: 1

Views: 25

Answers (1)

David Faber
David Faber

Reputation: 12485

I think you want to partition on REFID and NODE while ordering on APP_DATE and APP_TIME:

SELECT ref_id, node, node_enter, app_date, app_time
     , last_node_enter, last_app_date, last_app_time
  FROM (
    SELECT p.ref_id, p.node, p.node_enter, p.app_date, p.app_time
         , LAG(p.node_enter) OVER ( PARTITION BY p.ref_id, p.node ORDER BY p.app_date, p.app_time) AS last_node_enter
         , LAG(p.app_date) OVER ( PARTITION BY p.ref_id, p.node ORDER BY p.app_date, p.app_time) AS last_app_date
         , LAG(p.app_time) OVER ( PARTITION BY p.ref_id, p.node ORDER BY p.app_date, p.app_time) AS last_app_time
      FROM mytable p
) WHERE node_enter = 1 AND last_node_enter = 0;

I would recommend you store your dates and times as DATEs or TIMESTAMPs instead of NUMBERs or VARCHAR2s.

Upvotes: 1

Related Questions