使用Oracle 12c和更高版本...

select case_id,last_assigned_group as assigned_group,last_assigned_person as assigned_person,nvl(last_row_added,systimestamp) - first_row_added as time_owned
from help_desk_case_assignment_history
    match_recognize (
        partition by case_id
        order by seq#
        measures
            first(row_added) as first_row_added,last(row_added) as last_row_added,last(assigned_group) as last_assigned_group,last(assigned_person) as last_assigned_person
        one row per match
        after match skip past last row
        pattern (
            assignment_run* case_end
        )
        define
            assignment_run as (assigned_group = next(assigned_group)),case_end as (elapsed_time is null or next(assigned_group) is null)
    )
;

用人类的话来说：每个帮助台案例ID都会找到同一组中最后一次不间断的“运行”。对于作业的最后一次“运行”，请确定其开始时间，结束时间和结束人员。并显示找到的值。

使用Oracle 11g及更低版本...

with xyz as (
    select X.*,case when lnnvl(assigned_group = lag(assigned_group) over (partition by case_id order by seq#)) then seq# end as assignment_run_start
    from help_desk_case_assignment_history X
),xyz2 as (
    select X.*,last_value(assignment_run_start) ignore nulls over (partition by case_id order by seq#) as assignment_run_id
    from xyz X
),xyz3 as (
    select case_id,assigned_group,assignment_run_id,max(assigned_person) keep (dense_rank last order by seq#) as last_assigned_person,nvl(max(row_added) keep (dense_rank last order by seq#),systimestamp)
            - min(row_added) keep (dense_rank first order by seq#)
            as time_owned,row_number() over (partition by case_id order by assignment_run_id desc) as last_group_ind
    from xyz2 X
    group by case_id,assignment_run_id
)
select case_id,time_owned
from xyz3
where last_group_ind = 1
;

也许很丑，但是很简单而且很有效。

用人类的话来说：

1. 将分配运行的边界（起点）标识为递增的数字ID。
2. 将找到的任务运行扩展到整个任务运行。
3. 计算作业的运行时间和最后分配的人员。
4. 仅将上一次计算限制为最后一次运行（按其ID）。