【Oracle SQL】记一个级联SQL（任务Task、批次Batch、执行Execution）的实现过程
【实验数据】
create table task( id varchar2(20), name nvarchar2(20), primary key(id) ); insert into task(id,name) values('1','飞鹰行动'); insert into task(id,name) values('2','警察任务'); insert into task(id,name) values('3','锄奸行动'); create table batch( id varchar2(20), task_id varchar2(20), primary key(id) ); insert into batch(id,task_id) values('1','1'); insert into batch(id,task_id) values('2','1'); insert into batch(id,task_id) values('3','1'); insert into batch(id,task_id) values('4','1'); insert into batch(id,task_id) values('5','2'); insert into batch(id,task_id) values('6','2'); insert into batch(id,task_id) values('7','3'); create table execution( id varchar2(20), batch_id varchar2(20), status nvarchar2(20), primary key(id) ); insert into execution(id,batch_id,status) values('1','1','init'); insert into execution(id,batch_id,status) values('2','1','running'); insert into execution(id,batch_id,status) values('3','1','failed'); insert into execution(id,batch_id,status) values('4','1','succeeded'); insert into execution(id,batch_id,status) values('5','2','succeeded'); insert into execution(id,batch_id,status) values('6','2','succeeded'); insert into execution(id,batch_id,status) values('7','2','succeeded'); insert into execution(id,batch_id,status) values('8','3','failed'); insert into execution(id,batch_id,status) values('9','3','failed'); insert into execution(id,batch_id,status) values('10','4','running'); insert into execution(id,batch_id,status) values('11','5','failed'); insert into execution(id,batch_id,status) values('12','5','succeeded'); insert into execution(id,batch_id,status) values('13','6','failed'); insert into execution(id,batch_id,status) values('14','6','running'); insert into execution(id,batch_id,status) values('15','7','succeeded');
【数据说明】
任务叫Task，任务可运行多次，一次运行叫做一批batch，一批可分为多个子执行，每个执行称为Execution。
一个批次Batch里所有的执行Execution都成功，才认为batch成功，否则认为其失败。
【需求】
书写SQL，将每个任务运行了多少次，成功几次，失败几次统计出来。像下面这样：
ID NAME RUN_CNT SUCC_CNT FAIL_CNT -------------------- ---------- ---------- ---------- ---------- 1 飞鹰行动 4 1 3 2 警察任务 2 0 2 3 锄奸行动 1 1 0
【实现步骤】
1.将表征execution的状态的init、running、failed、succeeded转为数字，以方便后面的统计。
SQL：
select decode(status,'init',1,'running',2,'failed',3,'succeeded',4,0) as stat from execution
运行结果：
SQL> select id,batch_id,decode(status,'init',1,'running',2,'failed',3,'succeeded',4,0) as stat 2 from execution; ID BATCH_ID STAT -------------------- -------------------- ---------- 1 1 1 2 1 2 3 1 3 4 1 4 5 2 4 6 2 4 7 2 4 8 3 3 9 3 3 10 4 2 11 5 3 ID BATCH_ID STAT -------------------- -------------------- ---------- 12 5 4 13 6 3 14 6 2 15 7 4 已选择15行。
2.将Execution全成功标示为1，否则标示为0.
SQL：
select a.batch_id,count(*) as exe_cnt,decode(count(*)*4,sum(a.stat),1,0) as all_ok from (select id,batch_id,decode(status,'init',1,'running',2,'failed',3,'succeeded',4,0) as stat from execution) a group by a.batch_id order by a.batch_id
运行结果：
SQL> select a.batch_id,count(*) as exe_cnt,decode(count(*)*4,sum(a.stat),1,0) as all_ok from 2 (select id,batch_id,decode(status,'init',1,'running',2,'failed',3,'succeeded',4,0) as stat 3 from execution) a 4 group by a.batch_id 5 order by a.batch_id; BATCH_ID EXE_CNT ALL_OK -------------------- ---------- ---------- 1 4 0 2 3 1 3 2 0 4 1 0 5 2 0 6 2 0 7 1 1 已选择7行。
以上SQL中，exe_cnt标示batch里的执行数量，all_ok为1说明所有execution均成功，0则表示至少有一个不成功。
3.把任务ID加入进来。
SQL：
select batch.id,batch.task_id,b.all_ok from batch inner join (select a.batch_id,count(*) as exe_cnt,decode(count(*)*4,sum(a.stat),1,0) as all_ok from (select id,batch_id,decode(status,'init',1,'running',2,'failed',3,'succeeded',4,0) as stat from execution) a group by a.batch_id) b on batch.id=b.batch_id order by batch.id
执行结果：
SQL> select batch.id,batch.task_id,b.all_ok 2 from batch 3 inner join 4 (select a.batch_id,count(*) as exe_cnt,decode(count(*)*4,sum(a.stat),1,0) as all_ok from 5 (select id,batch_id,decode(status,'init',1,'running',2,'failed',3,'succeeded',4,0) as stat 6 from execution) a 7 group by a.batch_id) b 8 on batch.id=b.batch_id 9 order by batch.id; ID TASK_ID ALL_OK -------------------- -------------------- ---------- 1 1 0 2 1 1 3 1 0 4 1 0 5 2 0 6 2 0 7 3 1 已选择7行。
上面的结果集中，可以看出id为1的任务执行了4次，只有第二次是全成功的，其余三次都不是全成功，其余以此类推。
4.按任务id分组。
SQL：
select c.task_id,count(*) as run_cnt,sum(decode(c.all_ok,1,1,0)) as succ_cnt,sum(decode(c.all_ok,0,1,0)) as fail_cnt from (select batch.id,batch.task_id,b.all_ok from batch inner join (select a.batch_id,count(*) as exe_cnt,decode(count(*)*4,sum(a.stat),1,0) as all_ok from (select id,batch_id,decode(status,'init',1,'running',2,'failed',3,'succeeded',4,0) as stat from execution) a group by a.batch_id) b on batch.id=b.batch_id) c group by c.task_id
执行结果：
SQL> select c.task_id,count(*) as run_cnt,sum(decode(c.all_ok,1,1,0)) as succ_cnt,sum(decode(c.all_ok,0,1,0)) as fail_cnt 2 from 3 (select batch.id,batch.task_id,b.all_ok 4 from batch 5 inner join 6 (select a.batch_id,count(*) as exe_cnt,decode(count(*)*4,sum(a.stat),1,0) as all_ok from 7 (select id,batch_id,decode(status,'init',1,'running',2,'failed',3,'succeeded',4,0) as stat 8 from execution) a 9 group by a.batch_id) b 10 on batch.id=b.batch_id) c 11 group by c.task_id; TASK_ID RUN_CNT SUCC_CNT FAIL_CNT -------------------- ---------- ---------- ---------- 1 4 1 3 3 1 1 0 2 2 0 2
从以上结果集中，可以清晰地分辨出ID为1的任务运行了4次，1次是全成功的，3次失败。其余类推。
这个结果集加上任务名就是我们想要的结果了。
5.加上任务名
SQL：
select task.id,task.name,d.run_cnt,d.succ_cnt,d.fail_cnt
from task
inner join
(select c.task_id,count(*) as run_cnt,sum(decode(c.all_ok,1,1,0)) as succ_cnt,sum(decode(c.all_ok,0,1,0)) as fail_cnt
from
(select batch.id,batch.task_id,b.all_ok
from batch
inner join
(select a.batch_id,count(*) as exe_cnt,decode(count(*)*4,sum(a.stat),1,0) as all_ok from
(select id,batch_id,decode(status,'init',1,'running',2,'failed',3,'succeeded',4,0) as stat
from execution) a
group by a.batch_id) b
on batch.id=b.batch_id) c
group by c.task_id) d
on task.id=d.task_id
order by task.id
运行结果：
SQL> select task.id,task.name,d.run_cnt,d.succ_cnt,d.fail_cnt 2 from task 3 inner join 4 (select c.task_id,count(*) as run_cnt,sum(decode(c.all_ok,1,1,0)) as succ_cnt,sum(decode(c.all_ok,0,1,0)) as fail_cnt 5 from 6 (select batch.id,batch.task_id,b.all_ok 7 from batch 8 inner join 9 (select a.batch_id,count(*) as exe_cnt,decode(count(*)*4,sum(a.stat),1,0) as all_ok from 10 (select id,batch_id,decode(status,'init',1,'running',2,'failed',3,'succeeded',4,0) as stat 11 from execution) a 12 group by a.batch_id) b 13 on batch.id=b.batch_id) c 14 group by c.task_id) d 15 on task.id=d.task_id 16 order by task.id; ID NAME RUN_CNT SUCC_CNT FAIL_CNT -------------------- ---------- ---------- ---------- ---------- 1 飞鹰行动 4 1 3 2 警察任务 2 0 2 3 锄奸行动 1 1 0
这就是需求想要的结果。
PS：
如果愿意，还可以使用rpad函数给它加上ascii码风格的完成比例：
SQL：
select t.id,t.name,e.run_cnt,e.succ_cnt,e.fail_cnt,rpad('*',floor(e.succ_cnt*10/e.run_cnt),'*') as percent from task t inner join (select task_id,count(*) as run_cnt,sum(decode(status,1,1,0)) as succ_cnt,sum(decode(status,0,1,0)) as fail_cnt from (select batch.task_id,c.status from batch left join (select b.batch_id,b.exe_cnt,decode(b.expect,b.actual,1,0) as status from (select a.batch_id,count(*) as exe_cnt,count(*)*4 as expect,sum(a.stat) as actual from (select id,batch_id,decode(status,'init',1,'running',2,'failed',3,'succeeded',4,0) as stat from execution) a group by a.batch_id) b ) c on batch.id=c.batch_id) d group by d.task_id) e on t.id=e.task_id
执行：
SQL> select t.id,t.name,e.run_cnt,e.succ_cnt,e.fail_cnt,rpad('*',floor(e.succ_cnt*10/e.run_cnt),'*') as percent 2 from task t 3 inner join 4 (select task_id,count(*) as run_cnt,sum(decode(status,1,1,0)) as succ_cnt,sum(decode(status,0,1,0)) as fail_cnt from 5 (select batch.task_id,c.status 6 from batch 7 left join 8 (select b.batch_id,b.exe_cnt,decode(b.expect,b.actual,1,0) as status from 9 (select a.batch_id,count(*) as exe_cnt,count(*)*4 as expect,sum(a.stat) as actual from 10 (select id,batch_id,decode(status,'init',1,'running',2,'failed',3,'succeeded',4,0) as stat 11 from execution) a 12 group by a.batch_id) b 13 ) c 14 on batch.id=c.batch_id) d 15 group by d.task_id) e 16 on t.id=e.task_id; ID NAME RUN_CNT SUCC_CNT FAIL_CNT PERCENT ---------- ---------- ---------- ---------- ---------- -------------------- 1 飞鹰行动 4 1 3 ** 2 警察任务 2 0 2 3 锄奸行动 1 1 0 **********
或者是把它变成view。
SQL：
create view taskhisview as select t.id,t.name,e.run_cnt,e.succ_cnt,e.fail_cnt,rpad('*',floor(e.succ_cnt*10/e.run_cnt),'*') as percent from task t inner join (select task_id,count(*) as run_cnt,sum(decode(status,1,1,0)) as succ_cnt,sum(decode(status,0,1,0)) as fail_cnt from (select batch.task_id,c.status from batch left join (select b.batch_id,b.exe_cnt,decode(b.expect,b.actual,1,0) as status from (select a.batch_id,count(*) as exe_cnt,count(*)*4 as expect,sum(a.stat) as actual from (select id,batch_id,decode(status,'init',1,'running',2,'failed',3,'succeeded',4,0) as stat from execution) a group by a.batch_id) b ) c on batch.id=c.batch_id) d group by d.task_id) e on t.id=e.task_id
执行：
SQL> create view taskhisview as select t.id,t.name,e.run_cnt,e.succ_cnt,e.fail_cnt,rpad('*',floor(e.succ_cnt*10/e.run_cnt),'*') as percent 2 from task t 3 inner join 4 (select task_id,count(*) as run_cnt,sum(decode(status,1,1,0)) as succ_cnt,sum(decode(status,0,1,0)) as fail_cnt from 5 (select batch.task_id,c.status 6 from batch 7 left join 8 (select b.batch_id,b.exe_cnt,decode(b.expect,b.actual,1,0) as status from 9 (select a.batch_id,count(*) as exe_cnt,count(*)*4 as expect,sum(a.stat) as actual from 10 (select id,batch_id,decode(status,'init',1,'running',2,'failed',3,'succeeded',4,0) as stat 11 from execution) a 12 group by a.batch_id) b 13 ) c 14 on batch.id=c.batch_id) d 15 group by d.task_id) e 16 on t.id=e.task_id; 视图已创建。 SQL> select * from taskhisview; ID NAME RUN_CNT SUCC_CNT FAIL_CNT PERCENT ---------- ---------- ---------- ---------- ---------- -------------------- 1 飞鹰行动 4 1 3 ** 2 警察任务 2 0 2 3 锄奸行动 1 1 0 **********
END
