将进程与任务数据隔离的查询

Question

在某些过程中，我有以下任务表：

ID  | PREV_ID | NEXT_ID | TASK_TS
----+---------+---------+--------------------
100 |         |   101   | 2013-01-10 10:22:00
101 |   100   |   102   | 2013-01-10 10:25:12
102 |   101   |         | 2013-01-10 10:27:00
103 |         |   104   | 2013-01-10 10:31:00
104 |   103   |         | 2013-01-10 10:35:00
105 |         |         | 2013-01-10 10:38:22

ID是任务ID，PREV_ID是进程链中先前任务的ID，NEXT_ID是进程链中下一个任务的ID，TASK_TS是任务(事件)发生时的时间戳。

我需要用启动任务ID表示的将进程与此表隔离的查询，以mins/小时/天计算进程中的任务数和进程持续时间(流程任务链中的最后任务和第一个任务之间的差异)(无关紧要)。

ID  |  TASKS | DURATION
----+--------+---------
100 |    3   |    5      <-- this process has 3 tasks and lasted 5 mins
103 |    2   |    4      <-- this process has 2 tasks and lasted 4 mins
105 |    1   |    0      <-- this process has only 1 task and lasted 0 mins

Answer 1

您可以使用分层查询为每个链查找根id (即第一个任务)：

select t.*, connect_by_root id as root_id
from t42 t
connect by id = prior next_id
start with prev_id is null;

        ID    PREV_ID    NEXT_ID TASK_TS                         ROOT_ID
---------- ---------- ---------- ---------------------------- ----------
       100                   101 10-JAN-13 10.22.00.000000000        100 
       101        100        102 10-JAN-13 10.25.12.000000000        100 
       102        101            10-JAN-13 10.27.00.000000000        100 
       103                   104 10-JAN-13 10.31.00.000000000        103 
       104        103            10-JAN-13 10.35.00.000000000        103 
       105                       10-JAN-13 10.38.22.000000000        105 

这使用了运算符

CONNECT_BY_ROOT是一个一元运算符，仅在分层查询中有效。使用此运算符限定列时，Oracle将使用根行中的数据返回列值。该操作符扩展了分层查询的CONNECT BY [PRIOR]条件的功能。

因此，不管一行的层次结构有多低，您仍然可以看到它与哪个根相关。例如，在本例中，前三行都与根ID 100相关；对于ID 102，如果不遍历层次结构，这一点并不明显。

然后，可以将该分层查询用作基于根ID分组的子查询：

select root_id as id, count(*) as tasks,
  max(task_ts) - min(task_ts) as duration
from (
  select t.*, connect_by_root id as root_id
  from t42 t
  connect by id = prior next_id
  start with prev_id is null
)
group by root_id
order by root_id;

        ID      TASKS DURATION  
---------- ---------- -----------
       100          3 0 0:5:0.0   
       103          2 0 0:4:0.0   
       105          1 0 0:0:0.0   

这里的持续时间是一个间隔(日到秒)，因为我将task_ts列设置为时间戳。如果你的约会实际上是一个日期，那么你会看到一天中的一小部分。无论哪种方式，您都可以将其转换为所需的格式。

带有时间戳的SQL Fiddle或有日期.