时间戳

首页 > 解决方案 > 查询以选择适当的行并计算经过的时间

sql - 查询以选择适当的行并计算经过的时间

问题描述

我需要一些帮助来提出一个查询，该查询将返回问题的答案“当前分配的组拥有帮助台票证多长时间？” 以下是包含一些示例数据的数据模型的子集：

帮助台案例

Case ID (PK)    Assigned Person    Assigned Group
123456             Robert            Hardware

帮助台案例分配历史记录

Case ID (PK)    Seq # (PK)    Assigned Group    Assigned Person    Elapsed Time    Row Added Date/Time
123456            1             Hardware                             10 
123456            2             Software                              2 
123456            3             Hardware              Sam             1 
123456            4             Software              Sophie          6 
123456            5             Hardware                              8 
123456            6             Hardware              Sam             3 
123456            7             Hardware              Robert

最近一行 (Seq #7) 的 Elapsed Time 列在写入后续行 (Seq #8) 之前不会更新，所以我认为我不能使用聚合函数。对于上面的示例数据，我需要从 Seq # 5 中获取 Row added 列并从当前日期中减去它，以获得案例最近分配给 Hardware 组的总时间（我们忽略以前的分配，例如序列号 1 和序列号 3)。

上面示例的查询输出应该是：

Case ID    Assigned Group    Assigned Person    Time Owned
123456       Hardware          Robert            Current Date - Seq #5 Row Added Date/Time

标签： sqloracleoracle11g

解决方案

使用 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, assigned_group, assignment_run_id
)
select case_id, assigned_group, last_assigned_person as assigned_person, time_owned
from xyz3
where last_group_ind = 1
;

也许丑陋，但非常简单和有效。

用人类的话来说：

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

推荐阅读