Аналитика багов в Jira с помощью SQL — статистика в разрезе категорий. Часть II

В прошлой статье была рассмотрена простая задача по определению доли трудозатрат, которая приходится на исправление багов в проекте.

Немного усложним задачу и зададимся вопросом «Как распределяются трудозатраты на исправление багов в разрезе категорий?». Категории — это определенный классификатор, который позволяет группировать баги по определенной характеристике. Для возможности выбора категории бага, в системе Jira необходимо создать отдельный списочный кастомный атрибут, например BugProperty.

Данная практика отлично себя зарекомендовала, поскольку позволяет выявлять наиболее проблемные участки в разрабатываемом продукте. При чем, можно получать данную статистику состоянием «на сейчас» или в динамике за определенный промежуток времени. В рамках данной статьи рассмотрим как получить такую статистику состоянием «на сейчас».

Для формирования списка категорий нет каких-либо стандартов. Вы можете определить категории произвольно, исходя из специфики проекта. Главное придерживаться нескольких правил: категории не должны быть слишком общие или слишком детальные, каждый баг должен попадать в какую-то категорию. Если найденный баг не попадает ни в одну из категорий, значит самое время задуматься над созданием новой.

Ниже пример категорий, которые служат нам верой и правдой уже очень долгое время:

  • Входные данные (блокировка полей, валидация)
  • Сохранение информации
  • Отображение информации (форматы)
  • Интерфейс
  • Сообщения (грамматика, корректность)
  • Система безопасности
  • Сортировка
  • Требования
  • Лог-файлы, конфиг-файлы
  • Инструкции
  • Инсталляция
  • Производительность
  • Отчеты

Чтобы получить интересующую нас статистику возьмем за основу запрос, который возвращает полный список идентификаторов багов в проекте DEMO:

SELECT ji.ID
from   project p
       join jiraissue ji on (ji.project = p.id)
       join issuetype it on (it.id = ji.issuetype)
where  ji.project = p.id
       and p.pkey = 'DEMO' 
       and it.pname in ('Bug')

Прежде чем появится возможность группировать баги по кастомному атрибуту BugProperty, необходимо расширить последний запрос, добавив для каждого бага значение данного атрибута. Особенность кастомных атрибутов в том, что они не хранятся в таблице jiraiisue. Чтобы не усложнять структуру основного запроса, воспользуемся небольшой хитростью — значение атрибута BugProperty для каждого бага определим с помощью встроенного подзапроса. Подзапрос структурно разместим сразу после поля ID:

SELECT ji.ID,
       (<<БУДУЩИЙ ПОДЗАПРОС>>) as 'BugProperty'
from   project p
       join jiraissue ji on (ji.project = p.id)
       join issuetype it on (it.id = ji.issuetype)
where  ji.project = p.id
       and p.pkey = 'DEMO' 
       and it.pname in ('Bug')

Задача подзапроса — вернуть значение атрибута BugProperty для одного конкретного бага. Конструкция as ‘BugProperty’ позволяет явно указать название для нового атрибута в результате запроса. Без такой конструкции СУБД присвоит по умолчанию другое имя, которое вам явно не понравится.

Теперь поэтапно сформируем запланированный подзапрос.

Словарь кастомных атрибутов Jira хранится в отдельной таблице CUSTOMFIELD. Следующий запрос получает строку с информацией про кастомный атрибут BugProperty.

SELECT cf.*
 FROM customfield cf
where cf.cfname = 'BugProperty'

Это как минимум позволит убедиться, что такой атрибут существует. Учтите, что в вашем проекте атрибут может называться совершенно по другому.

Следующим шагом, получим список всех значений, которые может принимать данный атрибут. Напомню, что категории багов — это список с определенными значениями. Значения списочных атрибутов хранятся в таблице CUSTOMFIELDOPTION. Выполняем соединение предыдущего запроса с новой таблицей с помощью оператора JOIN:

SELECT cfo.*
 FROM customfield cf
 join customfieldoption cfo on (cfo.customfield=cf.id)
where cf.cfname = 'BugProperty'

Читается данный запрос так «Получить все значения кастомного списка BugProperty». Подставив вместо BugProperty название любого другого кастомного списка, вы получите его значения.

Чтобы закончить подзапрос, остается только узнать какое значение списка было выбранного для определенного бага. Предположим, что есть баг с ID = 224912. Выбранные значения в списках хранятся в отдельной таблице CUSTOMFIELDVALUE. Да, вы все поняли верно, необходимо выполнить соединение с этой таблицей. Но есть один нюанс с условием соединения. Идентификатор выбранного значения в списке хранится в строковом поле customfieldvalue.stringvalue. Поэтому, чтобы не натолкнуться при соединении на несовместимость типов необходимо с помощью оператора CAST выполнить явное преобразование ключа customfieldoption.id  в тип nvarchar. И дополнительно в секции WHERE фильтруем значение для конкретного бага, указав его ID. Вот финальная редакция подзапроса:

SELECT cfo.customvalue
 FROM customfield cf
 join customfieldoption cfo on (cfo.customfield=cf.id)
 join customfieldvalue cfv on (cast(cfo.id as nvarchar) = cfv.stringvalue)
where cf.cfname = 'BugProperty'
 and cfv.issue = 224912

Следующий шаг — интеграция подзапроса в основной запрос. Просто вставляем подзапрос в то место, где ранее был указан текст <<БУДУЩИЙ ПОДЗАПРОС>>. А явный ID бага 224912 заменяется на поле ji.ID из основного запроса.

SELECT ji.ID,
       (SELECT cfo.customvalue
          FROM customfield cf
               join customfieldoption cfo on (cfo.customfield=cf.id)
               join customfieldvalue cfv on (cast(cfo.id as nvarchar) = cfv.stringvalue)
         where cf.cfname = 'BugProperty'
               and cfv.issue = ji.ID) as 'BugProperty'
from project p
 join jiraissue ji on (ji.project = p.id)
 join issuetype it on (it.id = ji.issuetype)
where ji.project = p.id
 and p.pkey = 'DEMO' 
 and it.pname in ('Bug')

Остался последний штрих — суммирование трудозатрат на исправление багов в разрезе полученных категорий.

select BugProperty,
       SUM(timespent) as 'timespent'
from
(
  SELECT ji.ID,
         ji.timespent/3600 as 'timespent',
        (SELECT cfo.customvalue
           FROM customfield cf
                join customfieldoption cfo on (cfo.customfield=cf.id)
                join customfieldvalue cfv on (cast(cfo.id as nvarchar) = cfv.stringvalue)
          where cf.cfname = 'BugProperty'
                and cfv.issue = ji.ID) as 'BugProperty'
  from project p
       join jiraissue ji on (ji.project = p.id)
       join issuetype it on (it.id = ji.issuetype)
  where ji.project = p.id
       and p.pkey = 'DEMO' 
       and it.pname in ('Bug')
) t
group by BugProperty
order by timespent desc

Для удобства результат отсортирован в порядке убывания трудозатрат, чтобы в начале видеть наиболее проблемные категории.

Вот результат в моем случае:

art4_res2