SQL-关于 Jira 中线上问题首次响应时间的统计

对于IT服务业，线上问题的首次响应时间(Time to Frist Response)指标可以用来评估技术人员对客户问题的响应速度。线上问题JIRA工单由客户满意团队提交，然后分配给技术团队解决。

JIRA图形插件JIRA Charting Plugin将第一次备注的时间定义为Date of First Response，如果某JIRA工单无备注或在解决时才有备注，该Date of First Response将统计不出来或不准确。我们将“首次响应时间”定义为：技术第一次操作JIRA的时间，JIRA操作包括改动记录与备注。

显然，JIRA Charting Plugin插件无法满足我们的统计需求。下面介绍一个SQL脚本(MySQL DB)来统计各个开发小组的平均首次响应时间和平均处理时间：

DECLARE @NumberOfDays INT;
SET @NumberOfDays = 7;
#汇总
SELECT T.bizTeam 'Business Team', COUNT(T.bizTeam) 'Number Of Issues',
SEC_TO_TIME(AVG(TIMESTAMPDIFF(SECOND, T.created, T.firstResponse))) AS 'Average First Response Time',
SEC_TO_TIME(AVG(TIMESTAMPDIFF(SECOND, T.created, T.resolutiondate))) AS 'Average Resoultion Time'
FROM (
SELECT DISTINCT CONCAT(p.pkey, '-', ji.issuenum) AS '工单号', ji.created,
IF(ja.created <= cg.created, ja.created, cg.created) AS 'firstResponse'
, ji.resolutiondate, IF(ja.created <= cg.created, 'Comment', 'Transition') AS '首次响应类型',
cwm.parent_name 'bizTeam', ji.assignee '处理人', ji.summary '描述'
FROM jiraissue ji
JOIN project p on p.id = ji.project
JOIN cwd_user cwd ON cwd.user_name = ji.assignee
JOIN cwd_membership cwm ON cwm.child_name = cwd.user_name
JOIN cwd_directory ON cwd_directory.id = cwd.directory_id
#Join JIRA transition
JOIN changegroup cg ON cg.issueid = ji.id
JOIN changeitem ci ON ci.groupid = cg.id
#Join JIRA comments
LEFT JOIN jiraaction ja ON ja.issueid = ji.id
    AND ja.created =
   (SELECT MIN(jiraaction.created) FROM jiraaction
    JOIN cwd_user ON cwd_user.user_name = author
    JOIN cwd_membership ON cwd_membership.child_name = cwd_user.user_name
    JOIN cwd_directory ON cwd_directory.id = cwd_user.directory_id
    WHERE jiraaction.issueid = ja.issueid
        AND cwd_directory.directory_position =1
        AND cwd_membership.parent_name IN ('Tech Team 1', 'Tech Team 2', 'Tech Team 3'))
WHERE cwd_directory.directory_position =1
AND p.pkey = 'ONLINE'
AND ji.resolutiondate >= DATE_SUB(NOW(), INTERVAL @NumberOfDays DAY)
AND cwm.parent_name IN ('Tech Team 1', 'Tech Team 2', 'Tech Team 3')
#AND ji.issuenum = 5490
AND ji.resolution = 1
AND ji.created >= DATE_SUB(NOW(), INTERVAL 30 DAY)
AND cg.created =
   (SELECT MIN(changegroup.created) FROM changegroup
    JOIN cwd_user ON cwd_user.user_name = changegroup.author
    JOIN cwd_membership ON cwd_membership.child_name = cwd_user.user_name
    JOIN cwd_directory ON cwd_directory.id = cwd_user.directory_id
    WHERE changegroup.issueid = cg.issueid
        AND cwd_directory.directory_position =1
        AND cwd_membership.parent_name IN ('Tech Team 1', 'Tech Team 2', 'Tech Team 3'))
) T
GROUP BY T.bizTeam
ORDER BY SEC_TO_TIME(AVG(TIMESTAMPDIFF(SECOND, T.created, T.firstResponse)));

#详情
SELECT DISTINCT CONCAT(p.pkey, '-', ji.issuenum) AS '工单号', ji.created '创建日期',
IF(ja.created <= cg.created, ja.created, cg.created) AS '首次响应日期'
, ji.resolutiondate '解决日期', IF(ja.created <= cg.created, 'Comment', 'Transition') AS '首次响应类型',
cwm.parent_name '业务组', ji.assignee '处理人', ji.summary '描述'
FROM jiraissue ji
JOIN project p on p.id = ji.project
JOIN cwd_user cwd ON cwd.user_name = ji.assignee
JOIN cwd_membership cwm ON cwm.child_name = cwd.user_name
JOIN cwd_directory ON cwd_directory.id = cwd.directory_id
#Join JIRA transition
JOIN changegroup cg ON cg.issueid = ji.id
JOIN changeitem ci ON ci.groupid = cg.id
#Join JIRA comments
LEFT JOIN jiraaction ja ON ja.issueid = ji.id
    AND ja.created =
   (SELECT MIN(jiraaction.created) FROM jiraaction
    JOIN cwd_user ON cwd_user.user_name = author
    JOIN cwd_membership ON cwd_membership.child_name = cwd_user.user_name
    JOIN cwd_directory ON cwd_directory.id = cwd_user.directory_id
    WHERE jiraaction.issueid = ja.issueid
        AND cwd_directory.directory_position =1
        AND cwd_membership.parent_name IN ('Tech Team 1', 'Tech Team 2', 'Tech Team 3'))
WHERE cwd_directory.directory_position =1
AND p.pkey = 'ONLINE'
AND ji.resolutiondate >= DATE_SUB(NOW(), INTERVAL @NumberOfDays DAY)
AND cwm.parent_name IN ('Tech Team 1', 'Tech Team 2', 'Tech Team 3')
#AND ji.issuenum = 5490
AND ji.resolution = 1
AND ji.created >= DATE_SUB(NOW(), INTERVAL 30 DAY)
AND cg.created = 
   (SELECT MIN(changegroup.created) FROM changegroup
    JOIN cwd_user ON cwd_user.user_name = changegroup.author
    JOIN cwd_membership ON cwd_membership.child_name = cwd_user.user_name
    JOIN cwd_directory ON cwd_directory.id = cwd_user.directory_id
    WHERE changegroup.issueid = cg.issueid
        AND cwd_directory.directory_position =1
        AND cwd_membership.parent_name IN ('Tech Team 1', 'Tech Team 2', 'Tech Team 3'))
ORDER BY cwm.parent_name; 

欢迎加我个人微信交流 craigyoung1985，一起学习和交流。请备注：姓名/城市/工作岗位，我可以拉到Jira和Confluence中国社区群。