Bug Statistic analysis continued (i) SQL-based Bug statistic method

This article consists of @lonelyrains produced. Reprint please indicate the source.

article Links: http://blog.csdn.net/lonelyrains/article/details/44225533

Previous article for the bug statistical analysis of the preliminary

This article focuses on the SQL-based Bug statistical analysis method.

1. Relationship with time and state:

1) Examine the amount of bugs produced per time unit (year, month, day)

2) Examine the number of bugs resolved per time unit (year, month, day)

3) Examine the amount of bugs left over from each time unit (year, month, day)

4) Examine the time units (year, month, and day) left over each bug.

5) Examine the time unit (year, month, day) of the average bug left behind

6) Peak of the time period (month, day, hour) of finding and resolving bugs by combining 1), 2), 3)

6 can be used to guide testing, development efficiency

2. Relationship with time and role:

1) Examine the amount of bugs found every one months in each test

2) Examine the amount of bugs that each development solves every one months

3) Examine each test since the development submitted version number test, found that each new bug delay

4) Examine each of the developed self-test submission bugs. Resolve latency for each new bug

This 1234 can be used to guide performance assessment

3, other factors to consider the relationship with the bug:

1) based on project division

2) based on modules (hardware, firmware, underlying software, upper-level applications (front end, backstage), etc., according to different projects can be divided into different situations)

3) Based on functional nature (non-fatal, general, interface, crash, etc.)

4) based on recurrence probability division

Wait a minute

3. Advanced Extension

1) Infer If a bug is a hard bug and find it out: Based on the number of time-delay, repeated reopen, test and developer annotations

2) define each project sub-module to solve the sub-module of this project the most bugs of the human Project sub-module owner. Query the number of project sub-modules per person, etc.

4, Case:

Use Bugfree. Will find a problem, all the bug information is placed in a table bf_buginfo.

The Modulepath field is added '/' to the middle of the whole field when there are multiple sub-modules in the project.

Here are some of the SQL statistics I used (for one of the survey points, the author of the next blog dedicated to abstract a SQL interview):

#--query Bug Overall situation #select projectname,modulepath,bugtitle,bugstatus,openeddate from Bf_buginfo order by ProjectName, modulepath,openeddate;#--Query the number of bugs per item (XXXX counts an item) #select count (*), ProjectName from Bf_buginfo Group by ProjectName Having Count (*) >= 1;#--Query the number of bugs per module in the XXXX Project #select count (*), Modulepath from Bf_buginfo where ProjectName like ' XXXX ' g Roup by Modulepath have count (*) >= 1; #select * from Bf_buginfo where ProjectName like ' XXXX ' and Modulepath = '/'; #s Elect bugid,projectname,modulepath,bugtitle,bugstatus,openeddate from Bf_buginfo order by Openeddate,resolveddate, Closeddate GROUP BY Date_format (Openeddate, '%y%m '); #--query the number of bugs generated every one months #select count (*), date_format (Openeddate, '%y-%m ') from Bf_buginfo Group by Date_format (Openeddate, '%y%m '); #--query the number of bugs generated per one months #select count (*), date_format ( Openeddate, '%y-%m ') from the bf_buginfo where ProjectName like ' XXXX ' GROUP by Date_format (Openeddate, '%y%m '); #-- Check the details of Xxxxbug peak #select Modulepath,bugtitle,bugstatus,openeddate,date_format (OpeneddaTe, '%y-%m ') from the bf_buginfo where ProjectName like ' XXXX ' and (Date_format (openeddate, '%y%m ') = ' 201310 ' or date_format (O Peneddate, '%y%m ') = ' 201406 '), #--query xxxxbug status Condition #select count (*), bugstatus from Bf_buginfo where ProjectName like ' XXXX ' g Roup by bugstatus#--query full item bug status #select COUNT (*), bugstatus from Bf_buginfo Group by bugstatus#-- Query heavy difficulty bug:0 Basic filtering method: Analyze from resolved bug: Reopen: Need to know how to get reopen records: Bf_testaction and 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 TAC Tion.idvalue = Buginfo.bugid and ProjectName like ' XXXX ' #activated 226#activated 138#-- Query heavy difficulty bug:0 Basic filtering method: Analysis from resolved bug: Long time unresolved #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/92 7/1248 datediff>=2-fixed/closed/all#resolveddate is used to control SVN records. Easy access to #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 = ' Fix  Ed ' ORDER by Modulepath Desc,bugid desc#--based on resolution man query Bug#select count (*), resolvedby from bf_buginfo where Resolution = ' fixed ' and bugstatus = ' closed ' GROUP by Resolvedby the ORDER by Count (*) #--define each project sub-module The person responsible for the most bugs in the sub-module of this project, Query the number of project sub-modules owned by each person select COUNT (*), Resolvedby from (select b.* from (select Modulepath, Resolvedby, COUNT (*) as Num from Bf_b Uginfo 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 ' andBugstatus = ' 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

Bug Statistic analysis continued (i) SQL-based Bug statistic method