JIRA數(shù)據(jù)庫及常用SQL

1 JIRA數(shù)據(jù)庫能實(shí)現(xiàn)的功能？

1 做效能數(shù)據(jù)分析阱持，定義好規(guī)則后恳不，可以通過帆軟報(bào)表展示出來

參考JIRA之質(zhì)量效能指標(biāo)? 和?JIRA效能指標(biāo)統(tǒng)計(jì)與展示

2 項(xiàng)目日常管理卖宠，數(shù)據(jù)收集

大部分情況下旁理，我們都可以通過篩選器進(jìn)行項(xiàng)目過程中數(shù)據(jù)的分析躏精。但是少數(shù)情況卫枝，篩選器無法過濾出來的惩系，我們就可以考慮直接讀取數(shù)據(jù)庫信息來分析。

3 項(xiàng)目成員考核指標(biāo)數(shù)據(jù)分析

參考軟件企業(yè)各角色績效考核指標(biāo)設(shè)計(jì)防楷，比如考核測試人員的測試用例產(chǎn)出率牺丙，測試階段有效bug數(shù)等，就可以通過讀取數(shù)據(jù)庫等方式來分析

4 項(xiàng)目管理運(yùn)行標(biāo)準(zhǔn)化分析

當(dāng)公司產(chǎn)研規(guī)模比較大的時(shí)候复局，管理制度的統(tǒng)一就顯得尤為重要冲簿。那么制定了項(xiàng)目管理規(guī)則后，如果來檢查過程中的執(zhí)行規(guī)范性呢肖揣？一方面就可以通過周期性讀取項(xiàng)目空間數(shù)據(jù)庫? 另一方面可以通過JIRA api 實(shí)現(xiàn)的腳本來定期檢查分析

2 JIRA數(shù)據(jù)庫常用SQL民假？

我寫過兩個(gè)腳本 1 是分析項(xiàng)目效能數(shù)據(jù)的? 2 是定期檢查項(xiàng)目運(yùn)行情況的，這里我列舉了一些查詢的案例龙优，供參考。

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 按項(xiàng)目查詢所有 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 查詢進(jìn)行中的沖刺的故事詳情

select

a.id,

a.issuenum, -- issue key

a.assignee, -- 經(jīng)辦人

a.summary, -- 概要

a.RESOLUTIONDATE, -- 解決結(jié)果

sp.`NAME` as sprint, --沖刺名稱

(select pname from issuestatus c where c.id=a.issuestatus) as issuestatus, --問題狀態(tài)

(select TRUNCATE(b.numbervalue,1) from customfieldvalue b where b.issue=a.id and

b.customfield ='10006') storypoint –故事點(diǎn)數(shù)

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? 查詢指定沖刺下所有的故事和任務(wù)

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') --? 任務(wù)類型

AND customfieldvalue.CUSTOMFIELD = 10004 -- 代表sprint的customfield

AND sprint.id = {0} -- 沖刺號

AND customfieldvalue.stringvalue = cast(sprint.id as VARCHAR) -- customfield是1004 而且對應(yīng)的sprint號是 1735的

AND customfieldvalue.issue = jiraissue.id --符合以上條件的兩張表里的issue 相同的數(shù)據(jù)

AND jiraissue.log_time =

(

? ? select max(log_time)

? ? FROM incr_z_table_jiraissue AS jiraissue

)

2.6? 查詢指定故事下所有的子任務(wù)

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' -- 子類型是子任務(wù)

AND issuelink.LINKTYPE = 10100? -- 鏈接類型是父子鏈接

AND jiraissue1.id = issuelink.destination -- 鏈接目標(biāo)對象

AND issuelink.source = jiraissue2.id

AND jiraissue2.id = {0}? -- 父故事or任務(wù)的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? 查詢指定項(xiàng)目所有的沖刺

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} -- 輸入項(xiàng)目參數(shù)

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新建數(shù)

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 任務(wù)按時(shí)完成率

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? 各狀態(tài)任務(wù)統(tǒng)計(jì)

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?各項(xiàng)目按時(shí)完成率

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?任務(wù)和故事周轉(zhuǎn)時(shí)長

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?人員未按時(shí)完成任務(wù)排名

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 項(xiàng)目缺陷密度

?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數(shù)據(jù)庫地圖

不同版本可能會存在差異事秀，僅供參考

百度網(wǎng)盤地址貼上來彤断，文章就被禁用了，所以需要excel版本的數(shù)據(jù)庫地圖可以留言郵箱給我易迹。

如果失效了宰衙，就參考如下截圖

最后編輯于：2023.03.15 14:00:11

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者

人面猴
序言：七十年代末，一起剝皮案震驚了整個(gè)濱河市睹欲，隨后出現(xiàn)的幾起案子供炼，更是在濱河造成了極大的恐慌一屋，老刑警劉巖，帶你破解...
沈念sama閱讀 218,546評論 6贊 507
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件袋哼，死亡現(xiàn)場離奇詭異冀墨，居然都是意外死亡，警方通過查閱死者的電腦和手機(jī)涛贯，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 93,224評論 3贊 395
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進(jìn)店門诽嘉，熙熙樓的掌柜王于貴愁眉苦臉地迎上來，“玉大人弟翘，你說我怎么就攤上這事虫腋。” “怎么了稀余？”我有些...
開封第一講書人閱讀 164,911評論 0贊 354
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵悦冀，是天一觀的道長。經(jīng)常有香客問我睛琳，道長盒蟆，這世上最難降的妖魔是什么？我笑而不...
開封第一講書人閱讀 58,737評論 1贊 294
?港島之戀（遺憾婚禮）
正文為了忘掉前任掸掏，我火速辦了婚禮茁影，結(jié)果婚禮上，老公的妹妹穿的比我還像新娘丧凤。我一直安慰自己募闲，他們只是感情好，可當(dāng)我...
茶點(diǎn)故事閱讀 67,753評論 6贊 392
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布愿待。她就那樣靜靜地躺著浩螺，像睡著了一般。火紅的嫁衣襯著肌膚如雪仍侥。梳的紋絲不亂的頭發(fā)上要出，一...
開封第一講書人閱讀 51,598評論 1贊 305
城市分裂傳說
那天，我揣著相機(jī)與錄音农渊，去河邊找鬼患蹂。笑死，一個(gè)胖子當(dāng)著我的面吹牛砸紊，可吹牛的內(nèi)容都是我干的传于。我是一名探鬼主播，決...
沈念sama閱讀 40,338評論 3贊 418
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼醉顽，長吁一口氣：“原來是場噩夢啊……” “哼沼溜！你這毒婦竟也來了？” 一聲冷哼從身側(cè)響起游添，我...
開封第一講書人閱讀 39,249評論 0贊 276
萬榮殺人案實(shí)錄
序言：老撾萬榮一對情侶失蹤系草，失蹤者是張志新（化名）和其女友劉穎通熄，沒想到半個(gè)月后，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體找都，經(jīng)...
沈念sama閱讀 45,696評論 1贊 314
?護(hù)林員之死
正文獨(dú)居荒郊野嶺守林人離奇死亡唇辨，尸身上長有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點(diǎn)故事閱讀 37,888評論 3贊 336
?白月光啟示錄
正文我和宋清朗相戀三年，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了檐嚣。大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片助泽。...
茶點(diǎn)故事閱讀 40,013評論 1贊 348
活死人
序言：一個(gè)原本活蹦亂跳的男人離奇死亡，死狀恐怖嚎京，靈堂內(nèi)的尸體忽然破棺而出嗡贺，到底是詐尸還是另有隱情，我是刑警寧澤鞍帝，帶...
沈念sama閱讀 35,731評論 5贊 346
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布诫睬，位于F島的核電站，受9級特大地震影響帕涌，放射性物質(zhì)發(fā)生泄漏摄凡。R本人自食惡果不足惜，卻給世界環(huán)境...
茶點(diǎn)故事閱讀 41,348評論 3贊 330
男人毒藥：我在死后第九天來索命
文/蒙蒙一蚓曼、第九天我趴在偏房一處隱蔽的房頂上張望亲澡。院中可真熱鬧，春花似錦纫版、人聲如沸床绪。這莊子的主人今日做“春日...
開封第一講書人閱讀 31,929評論 0贊 22
一樁弒父案其弊，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽癞己。三九已至，卻和暖如春梭伐，著一層夾襖步出監(jiān)牢的瞬間痹雅，已是汗流浹背。一陣腳步聲響...
開封第一講書人閱讀 33,048評論 1贊 270
情欲美人皮
我被黑心中介騙來泰國打工糊识，沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留绩社，地道東北人。一個(gè)月前我還...
沈念sama閱讀 48,203評論 3贊 370
代替公主和親
正文我出身青樓赂苗，卻偏偏與公主長得像铃将，于是被迫代替她去往敵國和親。傳聞我的和親對象是個(gè)殘疾皇子哑梳，可洞房花燭夜當(dāng)晚...
茶點(diǎn)故事閱讀 44,960評論 2贊 355

JIRA數(shù)據(jù)庫及常用SQL

1 JIRA數(shù)據(jù)庫能實(shí)現(xiàn)的功能？

2 JIRA數(shù)據(jù)庫常用SQL民假？

2.1 查詢問題所屬 epic

2.2 按項(xiàng)目查詢所有 backlog

2.3 查詢進(jìn)行中的沖刺的故事詳情

2.4?查詢一月未登錄用戶

2.5? 查詢指定沖刺下所有的故事和任務(wù)

2.6? 查詢指定故事下所有的子任務(wù)

2.7? 查詢指定項(xiàng)目所有的沖刺

2.8 本周bug新建數(shù)

2.9 任務(wù)按時(shí)完成率

2.10? 各狀態(tài)任務(wù)統(tǒng)計(jì)

2.11?各項(xiàng)目按時(shí)完成率

2.12?任務(wù)和故事周轉(zhuǎn)時(shí)長

2.13?人員未按時(shí)完成任務(wù)排名

2.14 項(xiàng)目缺陷密度

3 JIRA數(shù)據(jù)庫地圖

推薦閱讀更多精彩內(nèi)容