暂无
任务描述
需求描述
验收标准
暂无
| 所属迭代 | 系统管理部 - 系统服务优化 |
|---|---|
| 所属模块 | 技术中心-需求池 系统运维 |
| 相关需求 | OA：技术中心绩效自动统计 |
| 指派给 | Closed 于 2024-02-21 15:15:25 |
| 任务类型 | 后端 |
| 任务标签 | 初始 |
| 开发组 | |
| 任务状态 | 已关闭 |
| 进度 | 100% |
| 优先级 | P2 |
| 关键词 | |
| 抄送给 |
| 由谁创建 | 周杰 于 2024-01-22 15:07:49 |
|---|---|
| 由谁完成 | 付航 于 2024-02-21 14:01:59 |
| 由谁取消 | 暂无 |
| 由谁关闭 | 周杰 于 2024-02-21 15:15:25 |
| 关闭原因 | 已完成 |
| 最后编辑 | 周杰 于 2024-02-21 15:15:25 |
| 团队 | 最初预计 | 总计消耗 | 预计剩余 |
|---|
| 最初预计 | 8工时 |
|---|---|
| 总计消耗 | 8工时 |
| 预计剩余 | 0工时 |
| 预计开始 | 2024-01-22 |
| 实际开始 | 2024-02-21 |
| 截止日期 | 2024-02-29 |
SELECT
DATE_FORMAT(bugs.openedDate, '%Y-%m') AS 'bug创建日期',
bugs.bug_count as '生产bug',
tasks.total_estimate as '完成开发任务预计总工时',
CONCAT(ROUND((COALESCE(bugs.bug_count, 0) / COALESCE(tasks.total_estimate, 1)) * 100, 2), '%') AS '中心层面bug泄漏率'
FROM
(SELECT
openedDate,
COUNT(1) AS bug_count
FROM
zt_bug
WHERE
resolution NOT IN ('duplicate', 'feiBUG', 'external')
GROUP BY
openedDate
) AS bugs
INNER JOIN
(SELECT
openedDate,
SUM(estimate) AS total_estimate
FROM
zt_task
WHERE
type = 'devel'
GROUP BY
openedDate
) AS tasks
ON
bugs.openedDate = tasks.openedDate
-- 每月测试人员的泄漏率
SELECT
realname as '测试人员',
e.`name` as '二级部门',
d.`name` as '三级部门',
bugNum '创建的bug数量',
estimateTotal as '需求预计总工时',
openedDate as '创建bug日期',
launchTime as '上线时间',
bug_leakage_rate as 'Bug泄漏率（测试）'
from
(
SELECT
a.openedDate,
a.openedBy,
a.bugNum,
b.launchTime,
b.tester,
b.estimateTotal,
CONCAT(ROUND(a.bugNum/b.estimateTotal*100,2),'%') as bug_leakage_rate ,
u.realname,
c.`name`,
c.parent
FROM (
select DATE_FORMAT(openedDate, '%Y-%m') openedDate,openedBy,count(openedBy) bugNum from zt_bug WHERE openedBy in (SELECT account FROM zt_user WHERE role = 'qa') and resolvedBuild in (577,590,627) GROUP BY DATE_FORMAT(openedDate, '%Y-%m'),openedBy
) a
inner JOIN (
SELECT DATE_FORMAT(launchTime, '%Y-%m') launchTime, tester,sum(estimate) as estimateTotal FROM zt_story GROUP BY DATE_FORMAT(launchTime, '%Y-%m'),tester
) b on a.openedDate=b.launchTime and a.openedBy = b.tester
LEFT JOIN zt_user u ON a.openedBy = u.account
LEFT JOIN zt_dept c on u.dept=c.id and a.openedBy = u.account
) d
left join zt_dept e on d.parent=e.id
-- 统计开发泄漏率
SELECT
realname as '开发人员',
e.`name` as '二级部门',
d.`name` as '三级部门',
bugNum as 'Bug数量',
estimateTotal '任务预计总工时',
resolvedDate as 'Bug解决日期',
dev_leakage_rate as 'Bug泄漏率',
finishedMonth as '任务完成日期'
FROM
(
SELECT
a.resolvedBy,
resolvedDate,
CONCAT(ROUND(a.bugNum / b.estimateTotal * 100, 2), '%') AS dev_leakage_rate,
b.finishedMonth,
u.realname ,
a.bugNum,
b.estimateTotal,
c.id as cid,
c.`name`,
c.parent
FROM (
SELECT
resolvedBy,
DATE_FORMAT(resolvedDate, '%Y-%m') as resolvedDate,
COUNT(resolvedBy) AS bugNum
FROM zt_bug
WHERE resolvedBy IN (SELECT account FROM zt_user WHERE role = 'dev')
AND resolution NOT IN ('duplicate', 'feiBUG', 'external')
GROUP BY
resolvedBy,
DATE_FORMAT(resolvedDate, '%Y-%m')
) a
INNER JOIN (
SELECT
finishedBy,
SUM(estimate) AS estimateTotal,
DATE_FORMAT(finishedDate, '%Y-%m') AS finishedMonth
FROM zt_task
GROUP BY
DATE_FORMAT(finishedDate, '%Y-%m'),
finishedBy
) b ON a.resolvedBy = b.finishedBy and a.resolvedDate=b.finishedMonth
LEFT JOIN zt_user u ON a.resolvedBy = u.account
LEFT JOIN zt_dept c on u.dept=c.id and a.resolvedBy = u.account
) d
left join zt_dept e on d.parent=e.id