MeterSphere结合DataEase实现测试度量

MeterSphere
1

一、在使用 MeteSphere 测试平台做日常测试工作的时候，可以结合 DataEase 做一些统计数据，实现测试情况数据大屏汇总，需求如下：

1、总览视图

展示 MeterSphere 平台中功能用例总数、接口总数、接口用例/接口自动化用例总数、缺陷总数。

2、组织级视图

展示 MeterSphere 功能用例总数、接口总数、接口用例/接口自动化用例总数、缺陷处理率已经未关闭的缺陷分布。

3、项目级视图

展示各项目各类型用例数量、项目人员数量分布、API调用总次数、各项目最后一次执行计划的成功率，接口自动化的通过率。

4、责任人级视图

展示各责任人各类型用例的数量

二、下载安装DataEase

DataEase 在GitHub 的地址：GitHub - dataease/dataease: 人人可用的开源数据可视化分析工具。

DataEase 官方文档 Redirecting

安装部署可到 官方文档中 Redirecting 查看具体安装部署步骤。

三、配置数据源

使用MeterSphere平台的mysql数据库作为数据源，数据库名称为metersphere，端口3306，数据库用户名、密码可到/opt/metersphere/.env 查看。

image
image1923×854 30.2 KB

image
image1897×615 18.7 KB

四、创建数据集和视图

1.添加SQL数据集

image
image1112×689 31.8 KB

image
image1910×737 27.3 KB

创建视图，选择数据集

image
image1906×868 86.4 KB

五、最终效果

6fd1cc02d25d954210dbd6799751e27
6fd1cc02d25d954210dbd6799751e271917×1075 144 KB

六、源码 sql 如下：

sql 类型 sql语句
统计所有功能用例总数 SELECT COUNT(id) AS ‘功能用例总数’ FROM test_case WHERE status != ‘Trash’ orstatus IS NULL;
统计接口定义_所有API总数 SELECT COUNT(id) AS ‘接口api总数’ FROM api_definition WHERE status != ‘Trash’ orstatus IS NULL;
统计接口定义_所有case总数 SELECT COUNT(id) AS ‘接口case总数’ FROM api_test_case WHERE status != ‘Trash’ orstatus IS NULL;
统计场景列表总数 SELECT COUNT( id ) AS ‘接口自动化场景用例总数’ FROM api_scenario WHERE status != ‘Trash’ orstatus IS NULL;
统计缺陷总数 SELECT COUNT(id) FROM issues;
统计各用例总数 SELECT
COUNT( c.id ) AS ‘总数’,
‘功能用例’
FROM
test_case c
WHERE c.status != ‘Trash’ or c.status IS NULL
GROUP BY c.project_id
UNION ALL
SELECT
COUNT( d.id ) AS ‘总数’,
‘接口API’
FROM
api_definition d
WHERE (d.status != ‘Trash’ or d.status IS NULL)
AND protocol = ‘HTTP’
GROUP BY d.project_id
UNION ALL
SELECT
COUNT( c.id ) AS ‘总数’,
‘接口CASE’
FROM
api_test_case c
LEFT JOIN api_definition d ON d.id = c.api_definition_id
WHERE c.status != ‘Trash’ or c.status IS NULL
AND d.protocol = ‘HTTP’
GROUP BY c.project_id
UNION ALL
SELECT
COUNT( s.id ) AS ‘总数’,
‘接口自动化场景’
FROM
api_scenario s
WHERE s.status != ‘Trash’ or s.status IS NULL
GROUP BY s.project_id
测试跟踪统计每个项目API调用总次数 SELECT
p.name AS ‘项目名’,
w.name AS ‘部门’ ,
COUNT(r.trigger_mode) AS ‘API调用总次数’
FROM
test_plan_report r
LEFT JOIN test_plan tp ON tp.id = r.test_plan_id
LEFT JOIN project p ON p.id = tp.project_id
LEFT JOIN workspace w ON w.id = p.workspace_id
WHERE r.trigger_mode = ‘API’
GROUP BY tp.project_id
统计各项目人员数量 SELECT
COUNT( u.id ) AS ‘人员总数’,
p.name
FROM
user u
LEFT JOIN project p ON p.id = u.last_project_id
GROUP BY u.last_project_id
统计缺陷处理率 SELECT 1-SUM(IF(t.status=‘new’,1,0))/count(t.id) 缺陷处理率 from issues t;
统计各类型用例数量按项目维度 SELECT
COUNT( c.id ) AS ‘总数’,
p.name AS ‘项目名’,
‘功能用例’
FROM
test_case c
LEFT JOIN project p ON p.id = c.project_id
WHERE c.status != ‘Trash’ or c.status IS NULL
GROUP BY c.project_id
UNION ALL
SELECT
COUNT( d.id ) AS ‘总数’,
p.name AS ‘项目名’,
‘接口API’
FROM
api_definition d
LEFT JOIN project p ON p.id = d.project_id
WHERE (d.status != ‘Trash’ or d.status IS NULL)
AND protocol = ‘HTTP’
GROUP BY d.project_id
UNION ALL
SELECT
COUNT( c.id ) AS ‘总数’,
p.name AS ‘项目名’,
‘接口CASE’
FROM
api_test_case c
LEFT JOIN project p ON p.id = c.project_id
LEFT JOIN api_definition d ON d.id = c.api_definition_id
WHERE (c.status != ‘Trash’ or c.status IS NULL)
AND d.protocol = ‘HTTP’
GROUP BY c.project_id
UNION ALL
SELECT
COUNT( s.id ) AS ‘总数’,
p.name AS ‘项目名’,
‘接口自动化场景’
FROM
api_scenario s
LEFT JOIN project p ON p.id = s.project_id
WHERE s.status != ‘Trash’ or s.status IS NULL
GROUP BY s.project_id
统计各类型用例数量按责任人维度 SELECT
COUNT( c.id ) AS ‘总数’,
p.name AS ‘项目名’,
w.name AS ‘部门’,
u.name AS ‘责任人’,
‘功能用例’
FROM
test_case c
LEFT JOIN project p ON p.id = c.project_id
LEFT JOIN workspace w ON w.id = p.workspace_id
LEFT JOIN user u ON u.id = c.create_user
WHERE c.status != ‘Trash’ or c.status IS NULL
GROUP BY c.create_user
UNION ALL
SELECT
COUNT( d.id ) AS ‘总数’,
p.name AS ‘项目名’,
w.name AS ‘部门’,
u.name AS ‘责任人’,
‘接口API’
FROM
api_definition d
LEFT JOIN project p ON p.id = d.project_id
LEFT JOIN workspace w ON w.id = p.workspace_id
LEFT JOIN user u ON u.id = d.user_id
WHERE (d.status != ‘Trash’ or d.status IS NULL)
AND protocol = ‘HTTP’
GROUP BY d.user_id
UNION ALL
SELECT
COUNT( c.id ) AS ‘总数’,
p.name AS ‘项目名’,
w.name AS ‘部门’,
u.name AS ‘责任人’,
‘接口CASE’
FROM
api_test_case c
LEFT JOIN project p ON p.id = c.project_id
LEFT JOIN workspace w ON w.id = p.workspace_id
LEFT JOIN user u ON u.id = c.create_user_id
LEFT JOIN api_definition d ON d.id = c.api_definition_id
WHERE (c.status != ‘Trash’ or c.status IS NULL)
AND d.protocol = ‘HTTP’
GROUP BY c.create_user_id
UNION ALL
SELECT
COUNT( s.id ) AS ‘总数’,
p.name AS ‘项目名’,
w.name AS ‘部门’,
u.name AS ‘创建人’,
‘接口自动化场景’
FROM
api_scenario s
LEFT JOIN project p ON p.id = s.project_id
LEFT JOIN workspace w ON w.id = p.workspace_id
LEFT JOIN user u ON u.id = s.user_id
WHERE s.status != ‘Trash’ or s.status IS NULL
GROUP BY s.user_id
统计每个项目最后一次执行计划的成功率 SELECT trigger_mode AS ‘调用方式’,
pass_rate AS ‘成功率’,
CONCAT(CAST((end_time - start_time) / 1000 AS DECIMAL ( 10, 2 )), ‘s’) AS ‘执行时间’,
tp_name AS ‘测试计划名称’,
DATE_FORMAT( FROM_UNIXTIME(create_time / 1000 ), ‘%Y-%m-%d %H:%i:%s’ ) AS ‘测试计划创建时间’,
p_name AS ‘项目名’,
w_name AS ‘部门’ from (
select *, row_number() over(partition by p_name order by r.create_time desc) ranking from (
SELECT
r.trigger_mode,
rc.pass_rate,
r.end_time,
r.start_time,
tp.name AS tp_name,
r.create_time,
p.name AS p_name,
w.name AS w_name
FROM
test_plan_report r
LEFT JOIN test_plan_report_content rc ON rc.test_plan_report_id = r.id
LEFT JOIN test_plan tp ON tp.id = r.test_plan_id
LEFT JOIN project p ON p.id = tp.project_id
LEFT JOIN workspace w ON w.id = p.workspace_id
) base
) rt
WHERE ranking=1;
接口自动化通过率按项目维度 SELECT
sum( CASE WHEN s.pass_rate = ‘100%’ THEN 1 ELSE 0 END )/ count( s.id ) AS ‘通过率’,
p.name AS ‘项目名’,
w.name AS ‘部门’
FROM
api_scenario s
LEFT JOIN project p ON p.id = s.project_id
LEFT JOIN workspace w ON w.id = p.workspace_id
WHERE s.status != ‘Trash’ or s.status IS NULL
GROUP BY s.project_id;
2

挺好的东西，MeterSphere 和 DataEase 分别对应什么版本呢？

3

MeterSphere: v2.10.1-lts
DataEase: v1.18.7