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

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 each time unit (year, month, day)

4) Review the time units (year, month, day) left by 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 of these can be used to guide testing, development efficiency

2. Relationship with time and role:

1) Examine the amount of bugs found per month per test

2) Examine the amount of bugs resolved per month for each development

3) Review each Test since the development of the submitted version test, to find the delay of each new bug

4) Investigate each new Bug's latency after each development self-test submission bug

This 1234 can be used to guide performance assessment

3. Other factors that can be considered in relation to the bug:

1) based on project division

2) based on modules (hardware, firmware, underlying software, upper-level applications (front end, backstage), etc., depending on the project 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) Determine if a bug is a difficult bug and find it: Based on resolution of delay, number of repeated reopen, test and developer labeling

2) define each project sub-module to solve this project sub-module bug The person who is the most responsible for the project sub-module, query the number of project sub-modules per person, etc.

4, Case:

Using Bugfree, you will find a problem, all the bug information 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 research 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 ' gr OUP by Modulepath have count (*) >= 1; #select * from Bf_buginfo where ProjectName like ' XXXX ' and Modulepath = '/'; #se Lect 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 per month #select count (*), date_format (openeddate, '%y-%m ' ) from Bf_buginfo GROUP by Date_format (Openeddate, '%y%m '); #--query the number of bugs generated per month #select count (*), date_format ( Openeddate, '%y-%m ') from the bf_buginfo where ProjectName like ' XXXX ' GROUP by Date_format (Openeddate, '%y%m '); #-- Find out more about 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 (opene  Ddate, '%y%m ') = ' 201406 '), #--query xxxxbug status Condition #select count (*), bugstatus from Bf_buginfo where ProjectName like ' XXXX ' group By bugstatus#--query full item bug status #select COUNT (*), bugstatus from Bf_buginfo Group by bugstatus#-- Query heavy difficulty BUG: Primary filtering method: Analysis from resolved bugs: 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: Primary 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/927/1248 Datediff>=2-fixed/closed/all#resolveddate is used to compare SVN records for easy viewing of #select Bugid,resolveddate,projectname,modulepath, Bugtitle,datediff (resolveddate,openeddate) from Bf_buginfo where ProjectName like ' XXXX ' and bugstatus = ' closed ' and date diff (Resolveddate,openeddate) >= 2 and Resolution = ' Fixed ' ORDER by Modulepath Desc,bugid desc#--based on resolution man query Bug#select Cou NT (*), resolvedby from bf_buginfo where Resolution = ' fixed ' and bugstatus = ' closed ' GROUP by Resolvedby ORDER by COUNT (*) #--define each project sub-module to solve this project sub-module bug The person who is the most responsible for the project sub-module, query the number of project sub-modules per person select COUNT (*), Resolvedby from (select b.* from (select Modulepath, Resolvedby, COUNT (*) as num from bf_buginfo where Resolution = ' fixed ' and bugstatus = ' closed ' GROUP by Modul Epath, 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

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