上一篇为 bug统计分析初步
本篇重点讨论基于sql的bug统计分析方法。
1、与时间和状态的关系：
1）考察每个时间单位（年、月、日）产生的bug量
2）考察每个时间单位（年、月、日）解决的bug量
3）考察每个时间单位（年、月、日）遗留的bug量
4）考察每个bug遗留的时间单位（年、月、日）
5）考察平均bug遗留的时间单位（年、月、日）
6）通过结合1）、2）、3）考察分析发现、解决bug的时间段（月、日、时）峰值
其中6可以用来指导测试、开发效率
2、与时间、角色的关系：
1）考察每个测试每个月发现的bug量
2）考察每个开发每个月解决的bug量
3）考察每个测试自开发提交版本测试之后，发现每个新bug的时延
4）考察每个开发自测试提交bug之后，解决每个新bug的时延
此1234均可以用来指导绩效考核
3、其他可以考虑与bug发生关系的系数：
1）基于项目划分
2）基于模块（硬件、固件、底层软件、上层应用（前端、后台）等，根据不同项目可以不同的划分情况）
3）基于功能性质划分（非致命、一般、界面、崩溃等）
4）基于重现概率划分
等等
3、高级扩展
1）判断一个bug是否是难bug，并把它找出来：根据解决时延、重复reopen的次数、测试和开发人员的标注
2）定义每个项目子模块解决本项目子模块bug最多的人为项目子模块负责人，查询每个人所负责的项目子模块数等
4、案例：
使用bugfree，会发现一个问题，所有的bug信息都放在一张表bf_buginfo里。ModulePath字段在项目有多个子模块时，是作为整字段中间加'/'区分层级的。
下面是我用到的一些SQL统计语句（为其中一个考察点，笔者在下一篇博客里专门抽象出一个SQL面试题）：
#--查询bug总体情况#select ProjectName,ModulePath,BugTitle,BugStatus,OpenedDate from bf_buginfo order by ProjectName,ModulePath,OpenedDate;#--查询每个项目的bug数目（XXXX算一个项目）#select count(*),ProjectName from bf_buginfo group by ProjectName having count(*) >= 1;#--查询XXXX项目每个模块的bug数目#select count(*),ModulePath from bf_buginfo where ProjectName like 'XXXX' group by ModulePath having count(*) >= 1;#select * from bf_buginfo where ProjectName like 'XXXX' and ModulePath = '/';#select BugId,ProjectName,ModulePath,BugTitle,BugStatus,OpenedDate from bf_buginfo order by OpenedDate,ResolvedDate,ClosedDate group by DATE_FORMAT(OpenedDate,'%Y%m');#--查询每个月产生的bug数目#select count(*),DATE_FORMAT(OpenedDate,'%Y-%m') from bf_buginfo group by DATE_FORMAT(OpenedDate,'%Y%m');#--查询XXXX每个月产生的bug数目#select count(*),DATE_FORMAT(OpenedDate,'%Y-%m') from bf_buginfo where ProjectName like 'XXXX' group by DATE_FORMAT(OpenedDate,'%Y%m');#--查询XXXXbug高峰期的详细内容#select ModulePath,BugTitle,BugStatus,OpenedDate,DATE_FORMAT(OpenedDate,'%Y-%m') from bf_buginfo where ProjectName like 'XXXX' and ( DATE_FORMAT(OpenedDate,'%Y%m') = '201310' or DATE_FORMAT(OpenedDate,'%Y%m') = '201406' );#--查询XXXXbug状态情况#select count(*),BugStatus from bf_buginfo where ProjectName like 'XXXX' group by BugStatus#--查询全项目bug状态情况#select count(*),BugStatus from bf_buginfo group by BugStatus#--查询重难点bug：初级过滤方法：从已解决的bug中分析：reopen的 : 需要了解如何获取reopen的记录 ：bf_testaction和bf_buginfo#select count(distinct(bugId)) from Bf_testaction as taction , bf_buginfo as buginfo where ActionType = 'Activated' and taction.idvalue = buginfo.bugid #select count(distinct(bugId)) from bf_testaction as taction , bf_buginfo as buginfo where ActionType = 'Activated' and taction.idvalue = buginfo.bugid and ProjectName like 'XXXX'#activated 226#activated 138#--查询重难点bug：初级过滤方法：从已解决的bug中分析：长时间未解决#690/1695/2715 datediff>=2-fixed/closed/all#select count(*),BugID,ResolvedDate,ProjectName,ModulePath,BugTitle,datediff(ResolvedDate,OpenedDate) from bf_buginfo where bugstatus = 'closed' and datediff(ResolvedDate,OpenedDate) >= 2 and Resolution = 'Fixed' order by ModulePath DESC,BugID DESC#378/927/1248 datediff>=2-fixed/closed/all#ResolvedDate用于对比svn记录，方便查看#select BugID,ResolvedDate,ProjectName,ModulePath,BugTitle,datediff(ResolvedDate,OpenedDate) from bf_buginfo where ProjectName like 'XXXX' and bugstatus = 'closed' and datediff(ResolvedDate,OpenedDate) >= 2 and Resolution = 'Fixed' order by ModulePath DESC,BugID DESC#--根据解决人查询bug#select count(*),ResolvedBy from bf_buginfo where Resolution = 'fixed' and BugStatus = 'closed' group by ResolvedBy order by count(*)#--定义每个项目子模块解决本项目子模块bug最多的人为项目子模块负责人，查询每个人所负责的项目子模块数select count(*),ResolvedBy from (select B.* from (select ModulePath, ResolvedBy, count(*) as num from bf_buginfo where Resolution = 'fixed' and BugStatus = 'closed' group by ModulePath, ResolvedBy) B , (select A.ModulePath, MAX(A.num) as num from ( select ModulePath,ResolvedBy,count(*) as num from bf_buginfo where Resolution = 'fixed' and BugStatus = 'closed' group by ModulePath, ResolvedBy ) A group by A.ModulePath )C where B.ModulePath = C.ModulePath and B.num = C.num order by B.ResolvedBy ) D group by ResolvedBy