1 JIRA数据库能实现的功能？
1 做效能数据分析，定义好规则后，可以通过帆软报表展示出来
2 项目日常管理，数据收集
大部分情况下，我们都可以通过筛选器进行项目过程中数据的分析。 但是少数情况，筛选器无法过滤出来的，我们就可以考虑直接读取数据库信息来分析。
3 项目成员考核指标数据分析
参考软件企业各角色绩效考核指标设计，比如 考核测试人员的测试用例产出率，测试阶段有效bug数等，就可以通过读取数据库等方式来分析
4 项目管理运行标准化分析
当公司产研规模比较大的时候，管理制度的统一就显得尤为重要。 那么制定了项目管理规则后，如果来检查过程中的执行规范性呢？ 一方面就可以通过周期性读取项目空间数据库 另一方面可以通过JIRA api 实现的脚本来定期检查分析
2 JIRA数据库常用SQL？
我写过两个脚本 1 是分析项目效能数据的 2 是定期检查项目运行情况的，这里我列举了一些查询的案例，供参考。
2.1 查询问题所属 epic
select d.Stringvalue
from jiraissue a ,issuelink b, customfieldvalue d
where
b.LINKTYPE='10200'
and b.SOURCE=a.ID
and b.DESTINATION=c.id
and d.issue=a.id
and d.customfield='10004'
2.2 按项目查询所有 backlog
select
p.pname as `project`,
c.SUMMARY,
c.description,
(select iss.pname from issuestatus iss where c.issuestatus=iss.id) as `status`,
(select cfv.TEXTVALUE from customfieldvalue cfv where cfv.CUSTOMFIELD='12000' and
cfv.ISSUE=c.id limit 1) as `AC`,
(select d.Stringvalue from jiraissue a ,issuelink b, customfieldvalue d where
b.LINKTYPE='10200' and b.SOURCE=a.ID and b.DESTINATION=c.id and d.issue=a.id and
d.customfield='10004') as `EPIC`,
(select format(cfv.NUMBERVALUE,1) from customfieldvalue cfv where
cfv.CUSTOMFIELD='10006' and cfv.ISSUE=c.id limit 1) as `storypoint`,
(select sp.`name` from customfieldvalue cfv, AO_60DB71_SPRINT sp where
cfv.CUSTOMFIELD='10001' and cfv.ISSUE=c.id and cfv.STRINGVALUE=sp.id order by sp.id
desc limit 1 ) as `sprint`
from
jiraissue c, project p
where
p.id in ('12900','12901','12902','12903','12904')
and c.PROJECT=p.id
and c.issuetype='10001'
2.3 查询进行中的冲刺的故事详情
select
a.id,
a.issuenum, -- issue key
a.assignee, -- 经办人
a.summary, -- 概要
a.RESOLUTIONDATE, -- 解决结果
sp.`NAME` as sprint, --冲刺名称
(select pname from issuestatus c where c.id=a.issuestatus) as issuestatus, --问题状态
(select TRUNCATE(b.numbervalue,1) from customfieldvalue b where b.issue=a.id and
b.customfield ='10006') storypoint –故事点数
from
jiraissue a,
customfieldvalue cf,
AO_60DB71_SPRINT sp
where
a.project = '12203'
and a.issuetype='10001 -- 问题类型=Story
and a.ID = cf.ISSUE
and cf.STRINGVALUE = sp.ID
and cf.CUSTOMFIELD='10001'
and sp.id in (
select sp.id
from AO_60DB71_SPRINT sp
where FROM_UNIXTIME(sp.START_DATE/1000) < NOW() AND
FROM_UNIXTIME(sp.END_DATE/1000) >now()
)
2.4 查询一月未登录用户
Select
a.user_key,c.display_name,
FROM_UNIXTIME(attr.attribute_value/1000,'%Y-%m-%d'),
m.parent_name
from
app_user a , cwd_user c, cwd_user_attributes attr, cwd_membership m
where
a.lower_user_name=c.lower_user_name
AND c.id=attr.user_id
and a.id=m.child_id
and attr.attribute_name ='login.lastLoginMillis'
and DATEDIFF(CURDATE(),FROM_UNIXTIME(attr.attribute_value/1000))>=30
and m.parent_name in ('jira-software-users')
2.5 查询指定冲刺下所有的故事和任务
SELECT DISTINCT jiraissue.id, jiraissue.issuenum,jiraissue.assignee, jiraissue.summary
From incr_z_table_jiraissue AS jiraissue,
incr_z_table_customfieldvalue AS customfieldvalue,
incr_z_table_ao_60db71_sprint AS sprint
WHERE (jiraissue.issuetype = '10001' --故事类型
Or jiraissue.issuetype = '10002') -- 任务类型
AND customfieldvalue.CUSTOMFIELD = 10004 -- 代表sprint的customfield
AND sprint.id = {0} -- 冲刺号
AND customfieldvalue.stringvalue = cast(sprint.id as VARCHAR) -- customfield是1004 而且 对应的sprint号是 1735的
AND customfieldvalue.issue = jiraissue.id --符合以上条件的两张表里的issue 相同的数据
AND jiraissue.log_time =
(
select max(log_time)
FROM incr_z_table_jiraissue AS jiraissue
)
2.6 查询指定故事下所有的子任务
SELECT DISTINCT jiraissue1.id, jiraissue1.issuenum,jiraissue1.assignee,jiraissue1.timeoriginalestimate
From incr_z_table_jiraissue AS jiraissue1,
incr_z_table_issuelink AS issuelink, incr_z_table_jiraissue AS jiraissue2
where jiraissue1.issuetype = '10003' -- 子类型是子任务
AND issuelink.LINKTYPE = 10100 -- 链接类型是父子链接
AND jiraissue1.id = issuelink.destination -- 链接目标对象
AND issuelink.source = jiraissue2.id
AND jiraissue2.id = {0} -- 父故事or任务的issue id
and jiraissue1.log_time =
(
select max(jiraissue.log_time)
FROM incr_z_table_jiraissue as jiraissue
)
and jiraissue2.log_time =
(
select max(jiraissue.log_time)
FROM incr_z_table_jiraissue as jiraissue
)
2.7 查询指定项目所有的冲刺
SELECT DISTINCT sprint.id, sprint.name, FROM_UNIXTIME(sprint.START_DATE/1000), FROM_UNIXTIME(sprint.END_DATE/1000),FROM_UNIXTIME(sprint.complete_date/1000), sprint.closed, sprint.started
From incr_z_table_jiraissue AS jiraissue,
incr_z_table_customfieldvalue AS customfieldvalue,
incr_z_table_ao_60db71_sprint AS sprint
where jiraissue.project = {0} -- 输入项目参数
AND customfieldvalue.CUSTOMFIELD = 10004 --代表sprint的customfield
AND customfieldvalue.stringvalue = cast(sprint.id as VARCHAR)
AND customfieldvalue.issue = jiraissue.id
AND jiraissue.issuetype = '10001'
AND sprint.id in (
select sprint.id
From incr_z_table_ao_60db71_sprint AS sprint
where FROM_UNIXTIME(sprint.START_DATE/1000) <NOW() AND sprint.closed = FALSE and sprint.started = true
)
AND sprint.closed = FALSE and sprint.started = true
AND FROM_UNIXTIME(sprint.START_DATE/1000) <NOW()
2.8 本周bug新建数
SELECT 'Bug Created' AS `Catalog`, count(1) AS `NUM` FROM jiraissue WHERE issuetype = '10401' AND project in ('12203','12100','12703','12802','12800','12936','12944') AND CREATED BETWEEN @startdate AND @enddate;
2.9 任务按时完成率
SELECT b.total,b.num,TRUNCATE (b.num / b.total, 2) AS `ratio`
FROM
(
SELECT count(DISTINCT a.id) AS `total`, count((((DATEDIFF(duedate, resolutiondate) >=- 1 or DUEDATE is null) and resolutiondate<=@enddate) ) OR NULL ) AS `num`
FROM jiraissue a , issuelink b , customfieldvalue cf , AO_60DB71_SPRINT sp
WHERE a.project in ('12203','12100','12703','12802','12800','12936','12944') and a.issuetype='10202'
AND a.ID=b.DESTINATION and b.LINKTYPE='10100'
AND b.SOURCE = cf.ISSUE AND cf.CUSTOMFIELD = '10001' AND cf.STRINGVALUE = sp.ID
AND sp.id IN
(
SELECT sp.id FROM AO_60DB71_SPRINT sp WHERE FROM_UNIXTIME(sp.START_DATE / 1000) < @curdate AND FROM_UNIXTIME(sp.END_DATE / 1000) > @curdate
)
) b;
2.10 各状态任务统计
select c.pname as `issuestatus`, count(a.id) as `NUM`
FROM jiraissue a , issuelink b , customfieldvalue cf , AO_60DB71_SPRINT sp,issuestatus c
WHERE a.project in ('12203','12100','12703','12802','12800','12936','12944') and a.issuetype='10202'
AND a.ID=b.DESTINATION and b.LINKTYPE='10100'
AND b.SOURCE = cf.ISSUE AND cf.CUSTOMFIELD = '10001' AND cf.STRINGVALUE = sp.ID
and a.issuestatus=c.ID
AND sp.id IN
(
SELECT sp.id FROM AO_60DB71_SPRINT sp WHERE FROM_UNIXTIME(sp.START_DATE / 1000) < @curdate AND FROM_UNIXTIME(sp.END_DATE / 1000) > @curdate
)
group BY c.pname;
2.11 各项目按时完成率
SELECT b.project,b.Sprint, b.total,TRUNCATE (b.num / b.total, 2) AS `ratio`
FROM
(
SELECT p.pname as `Project`,sp.`NAME` as `Sprint`,count(DISTINCT a.id) AS `total`, count((((DATEDIFF(duedate, resolutiondate) >=- 1 and resolutiondate<=@enddate)or DUEDATE is null) ) OR NULL ) AS `num`
FROM jiraissue a , issuelink b , customfieldvalue cf , AO_60DB71_SPRINT sp, project p
WHERE a.project in ('12203','12100','12703','12802','12800','12936','12944') and a.issuetype='10202'
AND a.ID=b.DESTINATION and b.LINKTYPE='10100'
AND b.SOURCE = cf.ISSUE AND cf.CUSTOMFIELD = '10001' AND cf.STRINGVALUE = sp.ID
AND a.PROJECT=p.ID
AND sp.id IN
(
SELECT sp.id FROM AO_60DB71_SPRINT sp WHERE FROM_UNIXTIME(sp.START_DATE / 1000) < @curdate AND FROM_UNIXTIME(sp.END_DATE / 1000) > @curdate
)
group by a.project,sp.`NAME`
) b
order BY ratio desc;
2.12 任务和故事周转时长
SELECT j.project, j.task_cycle, b.story_cycle
FROM
(
SELECT p.pname AS `project`, FORMAT( avg( TIMESTAMPDIFF(DAY, CREATED, RESOLUTIONDATE) ),1 ) AS `task_cycle`
FROM jiraissue a, project p
WHERE
issuetype in ('10202') AND a.PROJECT = p.id and a.project in ('12203','12100','12703','12802','12800','12936','12944') AND RESOLUTIONDATE BETWEEN @startdate AND @enddate
GROUP BY p.pname
) j,
(
SELECT p.pname as `project`, FORMAT( avg( TIMESTAMPDIFF(DAY, a.startdate, a.RESOLUTIONDATE) ), 1 ) AS `story_cycle`
FROM
(
select a.id,a.project, FROM_UNIXTIME(min(sp.START_DATE)/1000) as `startdate`, a.RESOLUTIONDATE
from jiraissue a, customfieldvalue cf, AO_60DB71_SPRINT sp
where a.ID = cf.ISSUE AND cf.STRINGVALUE = sp.ID AND cf.CUSTOMFIELD = '10001' and a.project in ('12203','12100','12703','12802','12800','12936','12944') AND RESOLUTIONDATE BETWEEN @startdate AND @enddate
group by a.id,a.project, a.RESOLUTIONDATE
) a, project p
where p.ID=a.project
group BY a.project
) b
WHERE j.project = b.project;
2.13 人员未按时完成任务排名
SELECT (SELECT u.display_name FROM cwd_user u, app_user b WHERE a.ASSIGNEE = b.user_key AND b.lower_user_name = u.lower_user_name ORDER BY u.directory_id DESC LIMIT 1 ) AS `ASSIGNEE`
,COUNT(a.id) as `total`,count( DATEDIFF(duedate, resolutiondate) <- 1 or null) as `num`
,TRUNCATE(count( DATEDIFF(duedate, resolutiondate) <- 1 or null) /COUNT(a.id),2) as `ratio`
FROM jiraissue a , issuelink b , customfieldvalue cf , AO_60DB71_SPRINT sp
WHERE a.project in ('12203','12100','12703','12802','12800','12936','12944') and a.issuetype='10202'
AND a.ID=b.DESTINATION and b.LINKTYPE='10100'
AND b.SOURCE = cf.ISSUE AND cf.CUSTOMFIELD = '10001' AND cf.STRINGVALUE = sp.ID
AND sp.id IN
(
SELECT sp.id FROM AO_60DB71_SPRINT sp WHERE FROM_UNIXTIME(sp.START_DATE / 1000) < @curdate AND FROM_UNIXTIME(sp.END_DATE / 1000) > @curdate
)
group by ASSIGNEE
having num >0
order by ratio desc;
2.14 项目缺陷密度
SELECT project.pname AS `project`, sp2. NAME AS `sprint`, x.bugs / x.storypoint AS `bug/storypoint`
FROM
(
SELECT sp1.project, sp1.SprintId AS `SprintId`,
(SELECT sum( TRUNCATE (storypoint.numbervalue, 1) ) storypoint
FROM jiraissue a, customfieldvalue storypoint, customfieldvalue cf
WHERE a.issuetype in ('10001') AND a.RESOLUTIONDATE<=@enddate AND a.id = storypoint.issue
AND storypoint.customfield = '10006' AND a.ID = cf.ISSUE AND cf.STRINGVALUE = sp1.SprintId AND cf.CUSTOMFIELD = '10001'
) AS `storypoint`,
(SELECT count(1) FROM jiraissue WHERE jiraissue.project = sp1.project AND jiraissue.CREATED BETWEEN sp1.start_Date AND if(sp1.end_Date<@curdate,sp1.end_date,@curdate) AND jiraissue.issuetype = '10401' ) AS `bugs`
FROM
(
SELECT DISTINCT a.PROJECT, sp.id AS `SprintId`, FROM_UNIXTIME( sp.START_DATE / 1000, '%Y/%m/%d') AS `start_Date`,FROM_UNIXTIME( sp.END_DATE / 1000,'%Y/%m/%d') AS `end_date`
FROM jiraissue a, customfieldvalue cf, AO_60DB71_SPRINT sp
WHERE a.ID = cf.ISSUE
AND cf.STRINGVALUE = sp.ID AND cf.CUSTOMFIELD = '10001' AND FROM_UNIXTIME(sp.START_DATE / 1000) <= @curdate AND FROM_UNIXTIME(sp.END_DATE / 1000) >= @curdate
) sp1
) x, project, AO_60DB71_SPRINT sp2
WHERE x.project = project.id AND sp2.id = x.sprintid and project.id in ('12203','12100','12703','12802','12800','12936','12944')
ORDER BY `bug/storypoint` DESC;
3 JIRA数据库地图
不同版本可能会存在差异，仅供参考
百度网盘地址贴上来，文章就被禁用了，所以需要excel版本的数据库地图 可以留言邮箱给我。
如果失效了，就参考如下截图
