用Oracle分析函数计算HandleTime
用Oracle分析函数计算HandleTime
提问于 2013-11-27 14:56:36
我的桌子在下面。
create table AuditInfo
(AuditID number(5),
TaskID number(5),
Task_Status varchar2(15),
UserID number(5),
SegmentTime number(5));
insert into AuditInfo values(1,101,'ASSIGNED',1,0);
insert into AuditInfo values(2,101,'HOLD',1,5);
insert into AuditInfo values(3,101,'RESERVED',1,0);
insert into AuditInfo values(4,101,'ASSIGNED',1,0);
insert into AuditInfo values(5,101,'COMPLETED',1,5);
insert into AuditInfo values(6,102,'ASSIGNED',1,0);
insert into AuditInfo values(7,102,'HOLD',1,5);
insert into AuditInfo values(8,102,'TRANSFER',2,0);
insert into AuditInfo values(9,102,'ASSIGNED',2,0);
insert into AuditInfo values(10,102,'HOLD',2,5);
insert into AuditInfo values(11,102,'COMPLETED',2,0);在每个任务生命周期结束时，我需要一个额外的列Task_Handle_Time。
1)状态转移或完成的任务被视为生命周期的终结。
2)如果任务同时包含传输(用户传输)或已完成，则需要分别计算每个生命周期的处理时间。TaskID 102表示此场景。
3)总处理时间是从分配状态开始到任务生命周期状态(转移、完成)的任务段的总和。
我的输出应该像
AuditID TaskID Task_Status UserID SegmentTime HandleTime
1 101 ASSIGNED 1 0 0
2 101 HOLD 1 5 0
3 101 RESERVED 1 0 0
4 101 ASSIGNED 1 0 0
5 101 COMPLETED 1 5 10
6 102 ASSIGNED 1 0 0
7 102 HOLD 1 5 0
8 102 TRANSFER 2 0 5
9 102 ASSIGNED 2 0 0
10 102 HOLD 2 5 0
11 102 COMPLETED 2 0 5在上面的输出中，任务101没有用户传输，处理时间用所有段时间之和计算一次，任务102有从UserID 1到2的用户传输，因此对于每个生命周期，我们应该计算两次HandleTime。
以下是我的查询，它不能按需要工作
select ai.*, case when row_number() over(partition by TaskID,Task_Status in(TRANSFER,COMPLETED) order by TaskID)=count(*) over(partition by TaskID,Task_Status in(TRANSFER,COMPLETED) order by TaskID)
then sum(SegmentTime) over(partition by TaskID,Task_Status in(TRANSFER,COMPLETED) order by TaskID) else 0 end as "Handle Time" from AuditInfo ai order by AuditID;有办法做到这一点吗？
这里是我尝试过的链接。http://www.sqlfiddle.com/#!4/8d96d/7
回答 2
Stack Overflow用户
回答已采纳
发布于 2013-11-27 15:40:35
我认为这样做的关键是将Oracle的sum()分析函数用作普通行的运行总数超过0的值，对于转换行使用"1“。这使您可以划分适合两个转换之间的行。只要稍微清理一下，就可以处理该方法中的一个错误，而且您是金人：
SELECT AUDITID, TASKID, TASK_STATUS, USERID,
DECODE(RUNTOT,GRP,0,SUM(SEGMENTTIME) OVER (PARTITION BY TASKID, GRP)) HANDLING
FROM (
SELECT A.*,
NVL(LAG(RUNTOT) OVER (PARTITION BY TASKID ORDER BY AUDITID) , RUNTOT) GRP
FROM (
SELECT A.*,
SUM(CASE WHEN TASK_STATUS IN ('TRANSFER', 'COMPLETED') THEN 1 ELSE 0 END)
OVER (PARTITION BY TASKID ORDER BY AUDITID) RUNTOT
FROM AUDITINFO A) A)
ORDER BY AUDITID ASC
AUDITID TASKID TASK_STATUS USERID HANDLING
1 101 ASSIGNED 1 0
2 101 HOLD 1 0
3 101 RESERVED 1 0
4 101 ASSIGNED 1 0
5 101 COMPLETED 1 10
6 102 ASSIGNED 1 0
7 102 HOLD 1 0
8 102 TRANSFER 2 5
9 102 ASSIGNED 2 0
10 102 HOLD 2 0
11 102 COMPLETED 2 5 Stack Overflow用户
发布于 2013-11-27 15:37:24
它可以只是一个简单的和()，而不是一个复杂的嵌套分析函数来实现一个适当的数据模型。这是你可以走的方向。
create table Task (
TaskID number(5) primary key
);
create table TaskCycle (
CycleID number(5) primary key,
TaskId number(5) not null references Task (TaskID)
);
create table CycleSegment (
SegmentID number(5) primary key,
CycleID number(5) not null references TaskCycle (CycleID),
Task_Status varchar2(15) not null,
UserID number(5) not null,
SegmentTime number(5) not null
);
insert into Task values (101);
insert into Task values (102);
insert into TaskCycle values ( 1, 101 );
insert into TaskCycle values ( 2, 102 );
insert into TaskCycle values ( 3, 102 );
insert into CycleSegment values ( 1, 1, 'ASSIGNED', 1, 0 );
insert into CycleSegment values ( 2, 1, 'HOLD', 1, 5);
insert into CycleSegment values ( 3, 1, 'RESERVED', 1, 0);
insert into CycleSegment values ( 4, 1, 'ASSIGNED', 1, 0);
insert into CycleSegment values ( 5, 1, 'COMPLETED', 1, 5);
insert into CycleSegment values ( 6, 2, 'ASSIGNED', 1, 0);
insert into CycleSegment values ( 7, 2, 'HOLD', 1, 5);
insert into CycleSegment values ( 8, 2, 'TRANSFER', 2, 0);
insert into CycleSegment values ( 9, 3, 'ASSIGNED', 2, 0);
insert into CycleSegment values ( 10, 3, 'HOLD', 2, 5);
insert into CycleSegment values ( 11, 3, 'COMPLETED', 2, 0);
select s.SegmentID, c.TaskID, s.Task_Status, s.UserID, s.SegmentTime
, case
when s.Task_Status in ('TRANSFER', 'COMPLETED')
then
( select sum(s2.SegmentTime)
from CycleSegment s2
where s2.CycleID = c.CycleID )
else 0
end as Handle_Time
from TaskCycle c
join CycleSegment s on (s.CycleID = c.CycleID )
order by c.TaskID, s.SegmentID
;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/20245707
复制相关文章
相似问题
腾讯云开发者
