【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

posted @   逆火狂飙  阅读(238)  评论(0)  编辑  收藏  举报
相关博文：
· 【Oracle SQL】把某表列值借助码值表由代号转换成名称的SQL，子查询效率未必低
· 【oracle SQL】为避免oracle函数listagg隐患而进行的一次sql改进
· sql 执行过程
· SQL 执行顺序
· 图解 SQL 执行顺序，通俗易懂！
阅读排行：
· 无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
· C#/.NET/.NET Core优秀项目和框架2025年2月简报
· Manus爆火，是硬核还是营销？
· 终于写完轮子一部分：tcp代理 了，记录一下
· 【杭电多校比赛记录】2025“钉耙编程”中国大学生算法设计春季联赛（1）
历史上的今天：
2020-05-29 BasicInterpreter1.00 运行简单Basic脚本 打印变量及字符串
2020-05-29 用于测试 SqlAnalyzer1.01 的21个测试用例
2020-05-29 SqlAnalyzer1.01 源码
生当作人杰 死亦为鬼雄 至今思项羽 不肯过江东
点击右上角即可分享
微信分享提示