Аналитика Jira без API: считаем Lead Time, CFD и метрики релизов прямо в SQL по базе Postgres

TL;DR. Jira Data Center хранит всё в обычной Postgres, включая полный лог переходов по статусам. Если подключить эту базу как источник данных в Grafana, можно построить инженерную аналитику (Lead/Cycle Time, предсказуемость, CFD, метрики релизов) без REST API, без выгрузок в BI и без плагинов из маркетплейса. В статье — разбор трёх самых нетривиальных запросов: предсказуемости через перцентили, восстановления времени в статусах из changelog и рекурсивного обхода дерева релиза.

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

Зачем вообще лезть в базу, если есть API

У Jira есть REST API, есть JQL, есть маркетплейс с дашбордами. Но как только метрики становятся чуть сложнее, чем «сколько задач закрыто за спринт», всё это упирается в потолок:

• JQL не умеет считать время между переходами. Он фильтрует задачи, но не отвечает на вопрос «сколько задача пролежала в статусе review». А именно это и есть основа потоковых метрик.

• API отдаёт changelog поштучно, по одной задаче. Чтобы посчитать перцентиль Lead Time по тысяче задач, нужно выкачать changelog каждой и агрегировать на своей стороне. Это медленно и хрупко.

• Готовые плагины — чёрный ящик. Они считают «что-то похожее на Cycle Time», но их определение статусов не совпадает с вашим реальным процессом.

При этом Jira Data Center / Server работает поверх обычной реляционной СУБД — как правило PostgreSQL. Вся история задач лежит в таблицах, к которым можно обратиться напрямую. Grafana умеет подключать Postgres как источник данных и рендерить результат SELECT в графики, таблицы и bar gauge. Дальше — вопрос знания схемы и умения писать SQL.

Важная оговорка про доступ. Читать боевую базу Jira напрямую — плохая идея: тяжёлые запросы с оконными функциями и рекурсией нагружают СУБД, а это тот же инстанс, на котором работают люди. Правильный вариант — read-only реплика или отдельный аналитический слепок. Дайте Grafana пользователя строго с SELECT и только на нужные таблицы.

Схема Jira: три таблицы, на которых всё держится

Прежде чем смотреть запросы, нужно понять четыре сущности. Их достаточно, чтобы восстановить почти любую потоковую метрику.

Таблица	Что хранит	Ключевые поля
jiraissue	Сами задачи	id, project, issuetype, issuestatus, issuenum, summary, created
changegroup	«Событие изменения» задачи — с временной меткой	id, issueid, created
changeitem	Что именно поменялось внутри события	groupid, field, oldstring, newstring
project	Проекты	id, pkey, pname

Логика простая. Каждый раз, когда в задаче что-то меняется, Jira пишет строку в changegroup (когда) и одну или несколько строк в changeitem (что). Смена статуса — это changeitem с field = 'status', где oldstring — статус «откуда», newstring — «куда».

То есть полная история жизни задачи по статусам — это:

SELECT cg.issueid,
       cg.created      AS change_time,
       ci.oldstring    AS from_status,
       ci.newstring    AS to_status
FROM changegroup cg
JOIN changeitem ci ON ci.groupid = cg.id
WHERE ci.field = 'status'
ORDER BY cg.issueid, cg.created;

Это — «первичный лог событий». Всё остальное в статье построено поверх него.

Ещё несколько таблиц, которые понадобятся: issuetype (типы: Bug, Feature, Sub-task…), issuestatus (справочник статусов), component + nodeassociation (компоненты, связаны с задачей через таблицу связей, а не напрямую), label (метки), worklog (списанное время), issuelink + issuelinktype (связи между задачами), customfieldvalue + customfieldoption (кастомные поля и их значения-списки).

Теперь — три запроса, ради которых всё затевалось.

Запрос 1. Предсказуемость потока: перцентили Lead Time

Что считаем. Не среднее время выполнения задачи, а два перцентиля — медиану (P50) и P85 — и их отношение. Это гораздо честнее среднего: среднее Lead Time легко «раздувает» пара застрявших задач, а перцентиль устойчив к выбросам. Отношение P85/P50 — это и есть мера предсказуемости: если оно близко к 1, поток стабилен и срокам можно верить; если 3–4, то «обычная» задача и «неудачная» отличаются в разы, и любые обещания по срокам — рулетка.

Идея запроса. Lead Time считаем как время от входа в статус «взяли в работу» (в примере — In Progress) до входа в финальный Done. Оба момента достаём из лога переходов, вычитаем, переводим в дни, а дальше — percentile_cont.

Названия статусов во всех примерах — обобщённые, под условный workflow. В вашей Jira цепочка будет своя; смысл запросов от этого не меняется, но списки статусов при переносе надо переписать.

Ключевые фрагменты

Сначала выделяем все переходы по статусам в один CTE — это наш «источник событий»:

WITH status_changes AS (
  SELECT cg.issueid,
         ci.newstring AS to_status,
         cg.created   AS change_time
  FROM changegroup cg
  JOIN changeitem ci ON cg.id = ci.groupid
  WHERE ci.field = 'status'
),

Дальше — важный момент, из-за которого наивный расчёт врёт. У задачи статус «взяли в работу» может встречаться несколько раз: задачу вернули из ревью, потом опять взяли в работу. Нам нужен первый вход в работу и первый переход в Done, поэтому берём MIN(change_time):

start_times AS (
  SELECT issueid, MIN(change_time) AS start_time
  FROM status_changes
  WHERE to_status = 'In Progress'
  GROUP BY issueid
),
end_times AS (
  SELECT issueid, MIN(change_time) AS end_time
  FROM status_changes
  WHERE to_status = 'Done'
  GROUP BY issueid
),

Соединяем начало с концом и переводим разницу в дни. Здесь же навешиваются фильтры дашборда:

lead_times AS (
  SELECT st.issueid,
         EXTRACT(EPOCH FROM (et.end_time - st.start_time)) / 86400.0 AS lead_time_days
  FROM start_times st
  JOIN end_times  et ON st.issueid = et.issueid
  JOIN jiraissue  ji ON ji.id = st.issueid
  JOIN project    p  ON ji.project = p.id
  WHERE et.end_time > st.start_time              -- отсекаем «Done раньше старта»
    AND $__timeFilter(et.end_time)               -- макрос временного диапазона Grafana
    AND p.pname IN ($project)                    -- переменная-мультиселект дашборда
    AND ji.issuetype NOT IN (
          SELECT id FROM issuetype WHERE pname = 'Sub-task'  -- подзадачи не считаем
    )
),

Обратите внимание на три вещи:

1. EXTRACT(EPOCH FROM interval) / 86400.0 — канонический способ перевести interval в дни: EPOCH даёт секунды, делим на число секунд в сутках. 86400.0 именно с точкой, чтобы деление было дробным, а не целочисленным.

2. $__timeFilter(...) и $project — это макросы Grafana, а не валидный SQL сам по себе. Grafana подставляет вместо $__timeFilter(et.end_time) условие вида et.end_time BETWEEN ... AND ... по выбранному в шапке диапазону, а вместо $project — список выбранных проектов. Так один запрос обслуживает любые срезы без переписывания.

3. et.end_time > st.start_time — защита от грязных данных: если из-за ручных правок в Jira Done оказался раньше входа в работу, такая задача даёт отрицательный Lead Time и её надо выкинуть.

Финал — сами перцентили:

percentiles AS (
  SELECT
    percentile_cont(0.50) WITHIN GROUP (ORDER BY lead_time_days) AS p50,
    percentile_cont(0.85) WITHIN GROUP (ORDER BY lead_time_days) AS p85
  FROM lead_times
)
SELECT
  ROUND(p50::numeric, 2)                        AS lead_time_p50_days,
  ROUND(p85::numeric, 2)                        AS lead_time_p85_days,
  ROUND((p85 / NULLIF(p50, 0))::numeric, 2)     AS p85_to_p50_ratio
FROM percentiles;

percentile_cont — это упорядоченно-множественная агрегатная функция (ordered-set aggregate). Синтаксис WITHIN GROUP (ORDER BY ...) обязателен: функции нужно знать, по какому полю строить распределение. percentile_cont (continuous) интерполирует между соседними значениями — для непрерывных величин вроде времени это то, что нужно; есть ещё percentile_disc, который возвращает всегда реально существующее значение из набора.

NULLIF(p50, 0) защищает от деления на ноль: если по выбранному фильтру задач не нашлось, P50 будет NULL, и отношение честно станет NULL, а не уронит запрос.

Запрос 2. Восстанавливаем время в каждом статусе: CFD из changelog

Что считаем. Для каждого статуса пайплайна — сколько времени задачи в нём в среднем проводят (медиана, минимум, максимум по типу задач). Это основа кумулятивной диаграммы потока (CFD) и главный инструмент поиска бутылочных горлышек: если медиана времени в review втрое больше, чем в in progress, узкое место найдено.

Почему это сложно. В Jira нет таблицы «задача X провела в статусе Y столько-то часов». Есть только точки переходов. Значит, интервалы нахождения в статусе надо реконструировать: из точек A→B в момент t1, B→C в момент t2 вывести, что «в статусе B задача была от t1 до t2». И аккуратно обработать три краевых случая:

• интервал до первого перехода (от создания задачи до первой смены статуса);

• интервал после последнего перехода (от последней смены до NOW() — задача всё ещё висит в статусе);

• задачи без единого перехода статуса (созданы и лежат как есть).

Ключевые фрагменты

Список интересующих статусов задаём явно, через unnest массива в набор строк — так его удобно переиспользовать в фильтрах:

WITH all_statuses AS (
  SELECT unnest(ARRAY[
    'to do','in progress','review',
    'testing','ready for release','done'
  ]) AS status
),

Здесь набор статусов — условный, для примера. В реальном дашборде это ваш кастомный workflow, зашитый в запрос явно, а не угаданный. Это осознанный компромисс: запрос знает про ваш процесс ровно то, что вы ему сказали. При переносе первым делом перепишите этот массив под свои статусы.

Собираем события переходов. Здесь же — важный трюк с компонентами: в Jira задача связана с компонентом не напрямую, а через таблицу связей nodeassociation (полиморфную — она хранит связи любых сущностей), поэтому джойн двухступенчатый и обязательно LEFT, чтобы не потерять задачи без компонента:

change_events AS (
  SELECT i.id AS issue_id,
         cg.created AS change_date,
         LOWER(ci.oldstring) AS old_status,
         LOWER(ci.newstring) AS new_status,
         c.cname AS component_name
  FROM jiraissue i
  JOIN project   p  ON i.project = p.id
  JOIN issuetype it ON it.id = i.issuetype
  JOIN changegroup cg ON cg.issueid = i.id
  JOIN changeitem  ci ON ci.groupid = cg.id
  LEFT JOIN nodeassociation na
         ON na.source_node_id = i.id
        AND na.sink_node_entity = 'Component'
        AND na.association_type  = 'IssueComponent'
  LEFT JOIN component c ON c.id = na.sink_node_id
  WHERE ci.field = 'status'
    AND it.pname = 'Bug'
    AND p.pname IN ($project)
    AND (c.cname IS NULL OR c.cname IN ($component))
    AND (LOWER(ci.oldstring) IN (SELECT status FROM all_statuses)
      OR LOWER(ci.newstring) IN (SELECT status FROM all_statuses))
),

Дальше — сердце запроса. Оконными функциями LAG/LEAD мы для каждого события видим соседние по времени. Именно это позволяет превратить точки в интервалы:

event_window AS (
  SELECT ce.*,
         ROW_NUMBER() OVER (PARTITION BY ce.issue_id ORDER BY ce.change_date) AS rn,
         LAG(ce.change_date)  OVER (PARTITION BY ce.issue_id ORDER BY ce.change_date) AS prev_change_date,
         LEAD(ce.change_date) OVER (PARTITION BY ce.issue_id ORDER BY ce.change_date) AS next_change_date
  FROM change_events ce
),

Теперь три источника интервалов, каждый — отдельным CTE, объединяются через UNION ALL:

-- (3) интервал от создания до первого перехода
initial_intervals AS (
  SELECT ew.issue_id, ew.old_status AS status,
         i.created AS start_time, ew.change_date AS end_time
  FROM event_window ew
  JOIN jiraissue i ON i.id = ew.issue_id
  WHERE ew.rn = 1 AND ew.old_status IS NOT NULL
    AND ew.old_status IN (SELECT status FROM all_statuses)
),
-- (4) интервал каждого нового статуса: до следующего перехода или, если его нет, до сейчас
new_status_intervals AS (
  SELECT ew.issue_id, ew.new_status AS status,
         ew.change_date AS start_time,
         COALESCE(ew.next_change_date, NOW()) AS end_time
  FROM event_window ew
  WHERE ew.new_status IS NOT NULL
    AND ew.new_status IN (SELECT status FROM all_statuses)
),
-- (5) задачи вообще без переходов статуса
no_change_issues AS (
  SELECT i.id, LOWER(s.pname), i.created, NOW()
  FROM jiraissue i
  JOIN issuestatus s ON s.id = i.issuestatus
  /* ... фильтры по типу/проекту/компоненту ... */
  WHERE NOT EXISTS (
    SELECT 1 FROM changeitem ci
    JOIN changegroup cg ON ci.groupid = cg.id
    WHERE cg.issueid = i.id AND ci.field = 'status'
  )
),
all_intervals AS (
  SELECT * FROM initial_intervals
  UNION ALL SELECT * FROM new_status_intervals
  UNION ALL SELECT * FROM no_change_issues
),

COALESCE(next_change_date, NOW()) — это и есть обработка «хвоста»: у последнего события следующего перехода нет, LEAD вернёт NULL, и мы честно считаем интервал до текущего момента.

Предпоследний шаг — обрезка интервалов по границам временного окна Grafana. Задача могла войти в статус до начала отчётного периода и выйти после его конца; в отчёт должна попасть только пересекающаяся часть. За это отвечают GREATEST/LEAST:

final_intervals AS (
  SELECT ai.issue_id, ai.status,
         GREATEST(ai.start_time, i.created) AS start_time,
         LEAST(ai.end_time, NOW())          AS end_time,
         EXTRACT(EPOCH FROM (LEAST(ai.end_time, NOW())
                           - GREATEST(ai.start_time, i.created))) / 3600.0 AS hours_in_status
  FROM all_intervals ai
  JOIN jiraissue i ON i.id = ai.issue_id
  WHERE GREATEST(ai.start_time, i.created) <= $__timeTo()
    AND LEAST(ai.end_time, NOW())          >= $__timeFrom()
    AND GREATEST(ai.start_time, i.created)  < LEAST(ai.end_time, NOW())
),

И финал — сумма фрагментов одной задачи в одном статусе (если задача входила в статус несколько раз), затем медиана и границы по всем задачам:

aggregated AS (
  SELECT issue_id, status, SUM(hours_in_status) AS total_hours
  FROM final_intervals
  GROUP BY issue_id, status
)
SELECT status,
       COUNT(*) AS issues_count,
       ROUND(MIN(total_hours)::numeric, 2) AS min_hours,
       ROUND(MAX(total_hours)::numeric, 2) AS max_hours,
       ROUND(percentile_cont(0.5) WITHIN GROUP (ORDER BY total_hours)::numeric, 2) AS median_hours
FROM aggregated
GROUP BY status
ORDER BY CASE LOWER(status)
           WHEN 'to do' THEN 1  WHEN 'in progress' THEN 2  /* ... */
           WHEN 'done' THEN 6  ELSE 999
         END;

Последняя деталь, которая часто удивляет: сортировка через CASE. Статусы нужно показать в порядке пайплайна (to do → … → done), а не по алфавиту и не по частоте. Алфавит здесь бессмысленен, поэтому порядок задаётся вручную: каждому статусу — свой номер. Некрасиво, зато читаемо и не требует отдельной таблицы-справочника с порядком.

Этот запрос — хороший шаблон на будущее. Паттерн «точки событий → LAG/LEAD → интервалы → обрезка по окну → агрегат» переиспользуется для любой метрики, где надо восстановить длительность из журнала переходов.

Запрос 3. Рекурсия по дереву релиза

Что считаем. Метрики релиза: соотношение багов к задачам, разбивка по командам, время от создания релизного тикета до его закрытия. Всё это — по одному релизу, заданному корневым тикетом.

Почему рекурсия. Релиз в Jira устроен как дерево: есть корневой релизный тикет, к нему привязаны задачи, к задачам — подзадачи и связанные баги, и так на несколько уровней вглубь. Заранее неизвестно, насколько глубоко. Обычный джойн фиксированной глубины тут не годится — нужен обход дерева произвольной вложенности, а это классический случай для WITH RECURSIVE.

Ключевой фрагмент

WITH RECURSIVE epic_subtasks AS (
    -- базовый уровень: задачи, напрямую связанные с релизным тикетом
    SELECT ji.id,
           ji.id AS root_id,
           ji.summary AS root_summary,
           it.pname AS issuetype
    FROM jiraissue ji
    JOIN project   p  ON ji.project = p.id
    JOIN issuelink il ON il.destination = ji.id
    JOIN issuetype it ON ji.issuetype = it.id
    WHERE il.source = CASE
                         WHEN $platform = 'A' THEN <ID релизного тикета, платформа A>
                         WHEN $platform = 'B' THEN <ID релизного тикета, платформа B>
                      END
      AND ( ($platform = 'A' AND p.pkey = 'PROJ_A')
         OR ($platform = 'B' AND p.pkey = 'PROJ_B') )

    UNION ALL

    -- рекурсивный шаг: дети найденных задач, root_id тащим за собой
    SELECT ji.id,
           es.root_id,
           es.root_summary,
           it.pname
    FROM jiraissue ji
    JOIN project   p  ON ji.project = p.id
    JOIN issuelink il ON il.destination = ji.id
    JOIN epic_subtasks es ON il.source = es.id   -- ← связь с уже найденным уровнем
    JOIN issuetype it ON ji.issuetype = it.id
    WHERE ( ($platform = 'A' AND p.pkey = 'PROJ_A')
         OR ($platform = 'B' AND p.pkey = 'PROJ_B') )
)

Как читать рекурсивный CTE:

• Якорь (anchor) — часть до UNION ALL. Стартовый набор: задачи, у которых в issuelink источник — релизный тикет. il.destination = ji.id при il.source = <релиз> означает «задача, на которую ссылается релиз».

• Рекурсивный член — после UNION ALL. Он джойнится на сам CTE (JOIN epic_subtasks es ...): берёт уже найденные задачи и ищет их детей. Postgres повторяет этот шаг, пока очередная итерация не перестанет добавлять строки.

• root_id протаскивается через все уровни. В якоре ji.id AS root_id, в рекурсивном члене — es.root_id (наследуем от родителя, а не берём свой). Благодаря этому на любой глубине известно, к какому релизу принадлежит задача. Без этого дерево «схлопнулось» бы и мы бы не смогли сгруппировать метрики по релизу.

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

subbug_counts AS (
  SELECT root_summary,
         COUNT(*) FILTER (WHERE issuetype = 'Sub-bug') AS sub_bug_count
  FROM epic_subtasks
  GROUP BY root_summary
)

COUNT(*) FILTER (WHERE ...) — компактный способ условного подсчёта без CASE WHEN ... THEN 1. Читается как «сколько строк, удовлетворяющих условию».

Осторожно: циклы и производительность

Два подводных камня рекурсии по реальным данным Jira:

1. Циклы. Если связи задач образуют петлю (A relates to B, B relates to A), наивный WITH RECURSIVE зациклится. В Postgres 14+ для этого есть CYCLE ... SET ... USING, в более старых версиях — руками тащить массив уже посещённых id и проверять NOT (ji.id = ANY(path)). В боевом коде это обязательно.

2. Стоимость. Рекурсия по issuelink на большой инстанс — тяжёлая операция. Это ещё один аргумент за read-only реплику и за то, чтобы не ставить такие панели на авто-обновление раз в 30 секунд.

Отдельно стоит отметить работу с кастомными полями (в соседних панелях того же дашборда). Значение кастомного поля-выпадашки хранится в customfieldvalue.stringvalue как ID варианта, а человекочитаемый текст — в customfieldoption.customvalue. Чтобы получить «что сломалось» словами, нужен джойн customfieldvalue → customfieldoption по этому ID, да ещё с приведением типов (cfo.id::text = cfv.stringvalue), потому что одно поле — число, другое — строка. Мелочь, но без неё в отчёте будут голые числовые ID вместо названий.

Как это собирается в Grafana

Несколько практических вещей, которые превращают набор запросов в рабочий дашборд:

Переменные (Dashboard settings → Variables). $project, $assignee, $component, $platform и т.д. — это Query-переменные, которые сами тянут список значений из той же базы (например, SELECT pname FROM project ORDER BY pname). В мультиселект-режиме $project подставляется как список для IN (...). Так один дашборд обслуживает все команды без копипасты.

Макросы вместо хардкода дат. $__timeFilter(col), $__timeFrom(), $__timeTo() привязывают запрос к выбранному в шапке диапазону. Никогда не зашивайте даты в SQL — потеряете главное удобство Grafana.

Тип панели под форму данных. barchart для распределений, table для перечней задач со ссылками, bargauge для одиночных KPI вроде «время на релиз». Запрос должен возвращать данные ровно в той форме, которую ждёт панель (для time series — колонка времени + значения; для таблицы — просто строки).

Импорт готового дашборда. Grafana экспортирует дашборд в JSON. Чтобы перенести к себе: Dashboards → New → Import, загрузить report.json, выбрать папку и, главное, указать свой источник данных Postgres. После импорта панели покажут No data, пока не подставите свою базу и не выберете значения переменных — это нормально.

Что дальше и где грабли

Пара честных предупреждений, если решите повторить:

• Схема Jira недокументирована и может меняться между версиями. То, что работает на вашей Data Center сегодня, может поехать после мажорного апгрейда. Держите запросы под контролем версий.

• Названия статусов — ваши, а не универсальные. Все запросы завязаны на конкретный workflow (в статье — условные to do, review, done…). При переносе первым делом перепишите списки статусов под свой процесс.

• Data Center, не Cloud. В Jira Cloud прямого доступа к базе нет — там только API. Всё описанное применимо к self-hosted Server/Data Center.

• Права — только чтение. Пользователь Grafana не должен иметь ничего, кроме SELECT. И, повторюсь, лучше на реплике.

Основная мысль простая: потоковые метрики живут в логе переходов, а не в текущем состоянии задач. Как только эта таблица (changegroup + changeitem) становится вашим источником истины, а SQL — инструментом, вы перестаёте зависеть от того, что придумали авторы плагинов, и считаете ровно те метрики, которые нужны вашему процессу.

Все идентификаторы проектов, ключи, ID тикетов и полей, метки команд и названия статусов в примерах — условные, приведены только для иллюстрации приёмов. Реальные значения в вашей инсталляции будут другими; при переносе запросов их нужно подставить самостоятельно.