测试工具-Jira和禅道数据库分析

方便你写周报、写总结、出报告

禅道

1、主要表

2、数据统计示例-供参考使用

测试人员发现有效bug数排行。条件：时间

SELECT
	COUNT(*) num,
	openedBy,
	realname
FROM
	zt_bug b
INNER JOIN zt_user u ON u.account = b.openedBy
WHERE
	DATE_FORMAT(b.openedDate, '%Y%m') = '202003'
AND b.resolution NOT IN (
	'bydesign',
	'duplicate',
	'external',
	'notrepro',
	'willnotfix'
)
GROUP BY
	b.openedBy
ORDER BY
	num DESC;

研发人员发生bug数排行。条件：时间

SELECT
	COUNT(*) num,
	openedBy,
	realname
FROM
	zt_bug b
INNER JOIN zt_user u ON u.account = b.resolvedBy
WHERE
	DATE_FORMAT(b.openedDate, '%Y%m') = '202304'
GROUP BY
	b.resolvedBy
ORDER BY
	num DESC;

研发Bug被激活次数(非一次性通过)。 条件：时间

SELECT
	SUM(num) num,
	realname
FROM
	(
		SELECT
			b.id,
			COUNT(1) num,
			u.realname realname
		FROM
			zt_bug b
		INNER JOIN zt_action a ON a.objectID = b.id
		INNER JOIN zt_user u ON u.account = b.resolvedBy
		WHERE
			DATE_FORMAT(b.openedDate, '%Y%m') = '201204'
		AND a.objectType = 'bug'
		AND a.action = 'activated'
		GROUP BY
			b.id
		ORDER BY
			num DESC
	) tem
GROUP BY
	tem.realname
ORDER BY
	num DESC;

Jira

1、主要表

2、数据统计示例-供参考使用

统计某时间段内每个人在每个项目中的工作量

SELECT
	p.pname,
	u.lower_user_name username,
	sum(l.timeworked / 28800) all_time
FROM
	jiraissue ji
INNER JOIN project p ON ji.PROJECT = p.ID
INNER JOIN worklog l ON ji.id = l.issueid
INNER JOIN app_user u ON l.UPDATEAUTHOR = u.user_key
WHERE
	l.startdate BETWEEN '2020-03-01'
AND '2020-03-31 23:59:59'
GROUP BY
	l.UPDATEAUTHOR,
	p.id

如果统计bug问题数，以jiraissue表展开sql编写即可

另外，欢迎加入软件测试技术交流群 313782132 ~进群可领取免费软件测试资料以及群内测试大牛解惑！

测试工程师职业发展路线图

功能测试 — 接口测试 — 自动化测试 — 测试开发 — 测试架构师

加油吧，测试人！如果你需要提升规划，那就行动吧，在路上总比在起点观望的要好。事必有法，然后有成。

资源不错就给个推荐吧~