缘由

新加入的公司业务测试到现在，累计发现问题数接近 4000 笔问题，那么在测试完成之后，即需要对这些问题进行总结

问题

如何总结，统计哪些数据？如何做到可视化？
1，基于数据进行统计，
2，基于统计出来的数据进行绘图
这里大概罗列了一下思路：

数据统计

我们是使用的 jira，所以就看了一下 jira 的库表，基本上还是 OK

#总的bug数
SELECT 
#优先级字段
#priority.pname,
count(*)
FROM  jiraissue
INNER JOIN issuestatus ON jiraissue.issuestatus=issuestatus.ID
INNER JOIN priority ON jiraissue.PRIORITY=priority.ID
WHERE jiraissue.issuetype='10004' 
#集成测试的
#AND jiraissue.PROJECT='10500'
#前端
#AND jiraissue.ASSIGNEE IN ('x','y','z')
#后端
AND jiraissue.ASSIGNEE IN ('a','b','c')
#按照优先级
#GROUP BY jiraissue.PRIORITY

#测试提bug数
SELECT REPORTER, count(*) 
FROM jiraissue as i, priority as p 
WHERE p.SEQUENCE=i.PRIORITY  AND i.issuetype='10004' GROUP BY REPORTER

#各开发完成数量
SELECT ASSIGNEE,count(*),
#优先级字段
priority.pname
FROM  jiraissue
INNER JOIN issuestatus ON jiraissue.issuestatus=issuestatus.ID
INNER JOIN priority ON jiraissue.PRIORITY=priority.ID
#前端
#AND jiraissue.ASSIGNEE IN ('x','y','z')
#后端
AND jiraissue.ASSIGNEE IN ('a','b','c')
WHERE jiraissue.issuetype='10004' AND jiraissue.issuestatus in ('10001','6') GROUP BY ASSIGNEE,priority.pname

#各开发完成数量按照优先级
SELECT ASSIGNEE,count(*),priority.pname
FROM  jiraissue
INNER JOIN issuestatus ON jiraissue.issuestatus=issuestatus.ID
INNER JOIN priority ON jiraissue.PRIORITY=priority.ID
WHERE jiraissue.issuetype='10004' 
AND jiraissue.issuestatus in ('10001','6') 
#前端
#AND jiraissue.ASSIGNEE IN ('x','y','z')
#后端
AND jiraissue.ASSIGNEE IN ('a','b','c')
GROUP BY ASSIGNEE ,PRIORITY 

#集成reopen，10202
SELECT changeitem.ID,jiraissue.issuenum,jiraissue.ASSIGNEE,count(changeitem.groupid),jiraissue.SUMMARY
FROM  jiraissue
INNER JOIN changegroup ON changegroup.issueid = jiraissue.ID
INNER JOIN changeitem ON changeitem.groupid = changegroup.ID
WHERE changeitem.NEWVALUE in('4','10202') AND jiraissue.issuetype='10004'
AND jiraissue.PROJECT='10500'
GROUP BY jiraissue.issuenum ORDER BY count(changeitem.groupid) DESC

#各模块的reopen
SELECT changeitem.ID,jiraissue.issuenum,jiraissue.ASSIGNEE,count(changeitem.groupid),jiraissue.SUMMARY
FROM  jiraissue
INNER JOIN changegroup ON changegroup.issueid = jiraissue.ID
INNER JOIN changeitem ON changeitem.groupid = changegroup.ID
WHERE changeitem.NEWVALUE in ('4','10202') 
AND jiraissue.issuetype='10004' GROUP BY jiraissue.issuenum ORDER BY count(changeitem.groupid) DESC

#每天完成数
SELECT date_format(changegroup.CREATED,'%Y-%m-%d') as time, count(1) as count
FROM  changegroup 
INNER JOIN changeitem ON changeitem.groupid = changegroup.ID
INNER JOIN jiraissue ON changegroup.issueid = jiraissue.ID
WHERE changeitem.NEWVALUE in ('5','10200') AND jiraissue.issuetype='10004' 
#前端
#AND jiraissue.ASSIGNEE IN ('x','y','z')
#后端
AND jiraissue.ASSIGNEE IN ('a','b','c')
GROUP BY date_format(changegroup.CREATED,'%Y-%m-%d') 
ORDER BY time 

#每个bug时效'5','10200'
select 
 TIMESTAMPDIFF(HOUR,startTime,endTime) cost,endTime,startTime,issuenum
from (
SELECT 
(SELECT CG.CREATED
FROM  jiraissue jira
#left JOIN changegroup CG ON CG.issueid = jira.ID
#left JOIN changeitem ON changeitem.groupid = CG.ID
INNER JOIN changegroup CG ON CG.issueid = jira.ID
INNER JOIN changeitem ON changeitem.groupid = CG.ID
WHERE changeitem.NEWVALUE in ('5','10200')
AND jira.issuetype='10004' 
and jira.ID=jiraissue.ID
ORDER BY CG.ID DESC
limit 1
) endTime,
jiraissue.CREATED startTime,
jiraissue.ASSIGNEE,
jiraissue.issuenum
from jiraissue 
WHERE jiraissue.issuetype='10004'
#前端
#AND jiraissue.ASSIGNEE IN ('x','y','z')
#后端
AND jiraissue.ASSIGNEE IN ('a','b','c')
) a
where a.endTime is not NULL;

图表绘制

1，缺陷严重等级分布

2，Issue 总数 Top10 的模块

思考

1. 开发测试过程中存在哪些问题？如何改进？

畅想

未来可以基于更多的数据，比如自动化，性能等维度，来了解系统的质量如何

交流

你所在的测试部门是如何做数据统计或结果分析的？