Jira 和禪道資料庫分析，方便你寫週報、寫總結、出報告

方便你寫週報、寫總結、出報告

禪道

1、主要表

描述	描述
zt_case	測試用例表
zt_bug	測試bug表
zt_project	專案表
zt_action	流程動作表(解決、關閉、啟用...)
zt_user	使用者表

2、資料統計示例-供參考使用

測試人員發現有效bug數排行。條件：時間

SELECT
    COUNT(*) num,
    openedBy,
    realname
FROM
    zt_bug b
INNER JOIN zt_user u ON u.account = b.openedBy
WHERE
    DATE_FORMAT(b.openedDate, '%Y%m') = '202003'
AND b.resolution NOT IN (
    'bydesign',
    'duplicate',
    'external',
    'notrepro',
    'willnotfix'
)
GROUP BY
    b.openedBy
ORDER BY
    num DESC;

研發人員發生bug數排行。條件：時間

SELECT
    COUNT(*) num,
    openedBy,
    realname
FROM
    zt_bug b
INNER JOIN zt_user u ON u.account = b.resolvedBy
WHERE
    DATE_FORMAT(b.openedDate, '%Y%m') = '202304'
GROUP BY
    b.resolvedBy
ORDER BY
    num DESC;

研發Bug被啟用次數(非一次性通過)。 條件：時間

SELECT
    SUM(num) num,
    realname
FROM
    (
        SELECT
            b.id,
            COUNT(1) num,
            u.realname realname
        FROM
            zt_bug b
        INNER JOIN zt_action a ON a.objectID = b.id
        INNER JOIN zt_user u ON u.account = b.resolvedBy
        WHERE
            DATE_FORMAT(b.openedDate, '%Y%m') = '201204'
        AND a.objectType = 'bug'
        AND a.action = 'activated'
        GROUP BY
            b.id
        ORDER BY
            num DESC
    ) tem
GROUP BY
    tem.realname
ORDER BY
    num DESC;

Jira

1、主要表

表名	描述
project	專案表
jiraissue	問題表
issuestatus	問題狀態表
issuelink	問題連結表
worklog	工作日誌表
customfieldvalue	問題屬性值表
app_user	使用者表
fileattachment	附件表

2、資料統計示例-供參考使用

統計某時間段內每個人在每個專案中的工作量

SELECT
    p.pname,
    u.lower_user_name username,
    sum(l.timeworked / 28800) all_time
FROM
    jiraissue ji
INNER JOIN project p ON ji.PROJECT = p.ID
INNER JOIN worklog l ON ji.id = l.issueid
INNER JOIN app_user u ON l.UPDATEAUTHOR = u.user_key
WHERE
    l.startdate BETWEEN '2020-03-01'
AND '2020-03-31 23:59:59'
GROUP BY
    l.UPDATEAUTHOR,
    p.id

如果統計bug問題數，已jiraissue展開sql編寫即可

掃一掃，關注我