-- 原始数据
select c.system as `系统名`,a.srv_name as `服务`, DATE_FORMAT(a.created_at, '%Y %m') as `发布月份`, a.version as `制品版本` , pc.deploy_cluster_name as `发布集群` ,  b.`project_name` as `coding项目名称`, b.attribute as `发布特性`, a.created_at as `发布时间`, 
a.updated_at as `最新时间`, a.creator as `发布人`, b.reviewer as `代码审查人`, case a.status 
when 1 then '准备发布' when 2 then '发布中' when 3 then '成功' when 4 then '发布失败' when 5 then '发布终止' else a.status end as `状态`, case a.release_type
when 1 then '正常发布' when 2 then '测试发布' when 3 then '特殊发布' when 4 then '回滚发布' when 5 then '扩容发布' else a.release_type end as `发布类型`
from (select * from deploy_tasks WHERE srv_name != 'coding' ) a 
left join (select id, reviewer, attribute,project_name from deploy_plans ) b on a.deploy_plan_id=b.id
left join (select SUBSTRING_INDEX(SUBSTRING_INDEX(srv_dn,'/',3),'/',-1) as `system`,id from `eff_service`.basec_srv) c on a.srv_id=c.id 
LEFT JOIN  (
SELECT deploy_task_id, GROUP_CONCAT(deploy_cluster_name SEPARATOR '\n') as deploy_cluster_name FROM ( SELECT deploy_task_id, deploy_cluster_name  FROM deploy_machine_records GROUP BY deploy_task_id,deploy_cluster_name ) pc1  GROUP BY deploy_task_id
) pc ON pc.deploy_task_id  = a.id order by c.system,a.srv_name,a.created_at desc

-- 发布次数,成功次数
SELECT t.system as `系统名`, t.srv_name as '服务名',t.project_name as 'coding项目名',t.coun as '发布次数', coalesce(t1.coun1,0) as '发布完成次数', coalesce(CONCAT(ROUND(t1.coun1 /t.coun * 100,2),'','%') ,'0%')  as '发布成功率' 

,coalesce(t2.coun2,0) as '回滚发布次数', CONCAT(coalesce(ROUND(t3.pub_time,2),0),'分钟') as '(已完成)平均发布时长' FROM (
select c.system, a.srv_name,b.project_name,COUNT(*) as coun
from (select * from deploy_tasks WHERE srv_name != 'coding' ) a
left join (select id, reviewer, attribute,project_name from deploy_plans) b on a.deploy_plan_id=b.id
left join (select SUBSTRING_INDEX(SUBSTRING_INDEX(srv_dn,'/',3),'/',-1) as `system`,id from `eff_service`.basec_srv) c on a.srv_id=c.id 
GROUP BY a.srv_name,b.project_name,c.system
) t LEFT JOIN (
select a.srv_name,COUNT(*) as coun1 from (
select * from deploy_tasks WHERE srv_name != 'coding' AND `status` = 3 ) a
left join (select id, reviewer, attribute,project_name from deploy_plans ) b on a.deploy_plan_id=b.id GROUP BY a.srv_name
) t1 on t.srv_name = t1.srv_name LEFT JOIN (

select a.srv_name,COUNT(*) as coun2 from (
select * from deploy_tasks WHERE srv_name != 'coding' AND `release_type` = 4  ) a
left join (select id, reviewer, attribute,project_name from deploy_plans ) b on a.deploy_plan_id=b.id GROUP BY a.srv_name

) t2 on t.srv_name = t2.srv_name LEFT JOIN (

select a.srv_name,AVG(TIMESTAMPDIFF(MINUTE,a.created_at,a.updated_at)) as pub_time from (
select * from deploy_tasks WHERE srv_name != 'coding' AND `status` = 3 ) a
left join (select id, reviewer, attribute,project_name from deploy_plans ) b on a.deploy_plan_id=b.id GROUP BY a.srv_name

) t3 on t.srv_name = t3.srv_name ORDER BY t.system,t.srv_name,t.coun DESC


select a.srv_name as '服务名',COUNT(*)  as '回滚发布次数' from (
select * from deploy_tasks WHERE srv_name != 'coding' AND `release_type` = 4  ) a
left join (select id, reviewer, attribute,project_name from deploy_plans ) b on a.deploy_plan_id=b.id GROUP BY a.srv_name


select a.srv_name,AVG(TIMESTAMPDIFF(MINUTE,a.created_at,a.updated_at)) as pub_time from (
select * from deploy_tasks WHERE srv_name != 'coding' AND `status` = 3 ) a
left join (select id, reviewer, attribute,project_name from deploy_plans ) b on a.deploy_plan_id=b.id GROUP BY a.srv_name


select DATE_FORMAT(a.created_at, '%Y%m') as `发布月份`, a.srv_name as `服务`, b.`project_name` as `coding项目名称`, a.version as `制品版本`, b.attribute as `发布特性`, a.created_at as `发布时间`, 
a.updated_at as `最新时间`, a.creator as `发布人`, b.reviewer as `代码审查人`, case a.status 
when 1 then '准备发布' when 2 then '发布中' when 3 then '成功' when 4 then '发布失败' when 5 then '发布终止' else a.status end as `状态`, case a.release_type
when 1 then '正常发布' when 2 then '测试发布' when 3 then '特殊发布' when 4 then '回滚发布' when 5 then '扩容发布' else a.release_type end as `发布类型`
from (select * from deploy_tasks WHERE srv_name = 'webrtc-center-svr-l3' AND `status` = 3 ) a
left join (select id, reviewer, attribute,project_name from deploy_plans ) b on a.deploy_plan_id=b.id order by a.id desc