jira数据查询(按周统计线上bug,所有bug,bug占比)

本文提供了一个复杂的SQL查询案例,用于统计不同周次的线上和所有项目的数量,并计算漏出占比。通过对具体表字段的筛选与汇总,实现了多维度的数据分析。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

转载自:https://blog.youkuaiyun.com/baidu_21982563/article/details/40077191

SELECT IFNULL(c1,'total') AS 项目名称,
SUM(IF(c2='20',C3,0)) AS 第20周线上,
SUM(IF(c2='20',C4,0)) AS 第20周所有,
CONCAT(LEFT(IF(SUM(IF(c2='20',C3,0))=0,0,SUM(IF(c2='20',C3,0)) /SUM(IF(c2='20',C4,0)) )*100 ,6),'%') AS 漏出占比,
SUM(IF(c2='21',C3,0)) AS 第21周线上,
SUM(IF(c2='21',C4,0)) AS 第21周所有,
CONCAT(LEFT(IF(SUM(IF(c2='21',C3,0))=0,0,SUM(IF(c2='21',C3,0)) /SUM(IF(c2='21',C4,0)) )*100 ,6),'%') AS 漏出占比,
SUM(IF(c2='22',C3,0)) AS 第22周线上,
SUM(IF(c2='22',C4,0)) AS 第22周所有,
CONCAT(LEFT(IF(SUM(IF(c2='22',C3,0))=0,0,SUM(IF(c2='22',C3,0)) /SUM(IF(c2='22',C4,0)) )*100 ,6),'%') AS 漏出占比,
SUM(IF(c2='23',C3,0)) AS 第23周线上,
SUM(IF(c2='23',C4,0)) AS 第23周所有,
CONCAT(LEFT(IF(SUM(IF(c2='23',C3,0))=0,0,SUM(IF(c2='23',C3,0)) /SUM(IF(c2='23',C4,0)) )*100 ,6),'%') AS 漏出占比,
SUM(IF(c2='24',C3,0)) AS 第24周线上,
SUM(IF(c2='24',C4,0)) AS 第24周所有,
CONCAT(LEFT(IF(SUM(IF(c2='24',C3,0))=0,0,SUM(IF(c2='24',C3,0)) /SUM(IF(c2='24',C4,0)) )*100 ,6),'%') AS 漏出占比
#concat(left(if(SUM(IF(c2='2',C3,0))=0,0,SUM(IF(c2='2',C3,0)) /SUM(IF(c2='2',C4,0)) )*100 ,6),'%') AS 漏出占比
#SUM(C3) AS 漏出总数,
#SUM(C4) AS 总数
 FROM ( SELECT t1.c1,t1.c2,t1.c3,t2.c4 FROM
 (SELECT b.pname AS c1,WEEK(a.created)+1 AS c2,COUNT(a.id) AS c3 FROM jiraissue a
 LEFT JOIN project b ON a.project=b.id
 LEFT JOIN issuetype c ON a.issuetype=c.id
 WHERE a.created>='2014-1-1 00:00:00' AND a.issuetype=27 AND issuestatus NOT IN (10028,10029)
 GROUP BY c1,c2) t1
LEFT JOIN
(SELECT b.pname AS c1,WEEK(a.created)+1 AS c2,COUNT(a.id) AS c4 FROM jiraissue a
 LEFT JOIN project b ON a.project=b.id
 LEFT JOIN issuetype c ON a.issuetype=c.id
 WHERE a.created>='2014-1-1 00:00:00' AND a.issuetype IN(27,28) AND issuestatus NOT IN (10028,10029)
 GROUP BY c1,c2)t2 ON t1.c1=t2.c1 AND t1.c2=t2.c2
GROUP BY t1.c1,t1.c2
)tx
 GROUP BY C1 WITH ROLLUP ;

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值