@h_y(H Y)

Redmine の進捗データをいい感じに抽出する

Redmine

Last updated at 2026-03-17Posted at 2026-03-17

目的

RedmineのチケットベースでWBSを管理している際に、結局今の進捗状況はどうかを把握したい

手段

RedmineのEVMプラグインを活用してEVMで管理
チケットデータを独自で集計してしまう

この記事では2の方法を実際に試した内容を記載します。

前提

各チケットについて、以下の項目が適切に入力されている
- 開始日
- 期日
- 予定工数
- 作業時間の記録
postgresqlで構築している
postgresqlへの接続ユーザ/パスワードを把握している

末端チケットの集計

SQL

WITH RECURSIVE issue_tree AS (
  SELECT 
    i.id, 
    i.parent_id, 
    i.subject, 
    i.assigned_to_id, 
    i.start_date, 
    i.due_date, 
    i.done_ratio, 
    i.estimated_hours, 
    i.status_id, 
    i.tracker_id, 
    i.fixed_version_id, 
    CAST(i.id || ':' || i.subject AS TEXT) AS path
  FROM issues i
  WHERE i.project_id = 1 -- 取得対象PJ-IDを指定
    AND i.status_id NOT IN (5, 6) -- 集計から除外したいトラッカーIDを指定
    AND i.parent_id IS NULL

  UNION ALL

  SELECT 
    c.id,
    c.parent_id,
    c.subject,
    c.assigned_to_id,
    c.start_date,
    c.due_date,
    c.done_ratio,
    c.estimated_hours,
    c.status_id,
    c.tracker_id,
    c.fixed_version_id,
    it.path || '>' || c.id || ':' || c.subject
  FROM issues c
  JOIN issue_tree it ON c.parent_id = it.id
  WHERE c.project_id = 1 -- 取得対象PJ-IDを指定
    AND c.status_id NOT IN (5, 6) -- 集計から除外したいトラッカーIDを指定
),

time_sum AS (
  SELECT 
    issue_id,
    SUM(hours) AS total_hours
  FROM time_entries
  GROUP BY issue_id
)

SELECT 
  'http://xx.xx.xx.xx/redmine/issues/' || it.id AS URL,    
  v.name                                AS ver, 
  it.path                               AS parent, 
  t.name                                AS tracker, 
  s.name                                AS status, 
  it.subject                            AS title, 
  CONCAT(u.firstname, ' ', u.lastname)  AS name, 
  it.start_date                         AS start, 
  it.due_date                           AS end, 
  it.done_ratio                         AS sintyoku, 
  it.estimated_hours                    AS yotei, 
  COALESCE(te.total_hours, 0)           AS jiseki, 
  CASE 
    WHEN it.start_date IS NOT NULL AND it.due_date IS NOT NULL AND CURRENT_DATE BETWEEN it.start_date AND it.due_date THEN 
      ROUND(
        CAST(it.done_ratio - (100.0 * (CURRENT_DATE - it.start_date + 1) / (it.due_date - it.start_date + 1)) AS numeric),
        1
      )
    ELSE NULL
  END AS nissuuokure,
  CASE 
    WHEN it.estimated_hours IS NOT NULL AND it.estimated_hours > 0 THEN 
      ROUND(
        CAST(it.done_ratio - (100.0 * COALESCE(te.total_hours, 0) / it.estimated_hours) AS numeric),
        1
      )
    ELSE NULL
  END AS okurekosu

FROM issue_tree it
LEFT JOIN users u ON it.assigned_to_id = u.id
LEFT JOIN trackers t ON it.tracker_id = t.id
LEFT JOIN issue_statuses s ON it.status_id = s.id
LEFT JOIN time_sum te ON it.id = te.issue_id
LEFT JOIN versions v ON it.fixed_version_id = v.id

ORDER BY 
  it.start_date, v.name NULLS LAST, it.path;

実行結果

項目説明

sintyoku：Redmineのチケット情報（進捗率）
yotei：Redmineのチケット情報（予定工数）
jiseki：Redmineのチケット情報（作業時間の記録の合計）
nissuuokure：遅れ率（日数進捗）
時間ベースの進捗判断
開始日～期日の期間を見たときに、現在日時的に進捗率がいくつであるべきで
あるかを元に、実際の進捗率を比較したときの差異を計算
納期遅延の可能性が見える。日程が迫っているのに進んでないものが最も危険
okurekosu：遅れ率（工数進捗）
予定工数と作業時間合計から、工数をこれだけ使っているなら、
これくらい進捗しているべきであるという進捗率を元に、
実際の進捗率と比較したときの差異を計算
工数の使い過ぎ、作業ログの未入力など、作業面の非効率を可視化

判断方法

遅れ率が両方マイナス：遅れている＆非効率
日数進捗のみマイナス：スケジュール遅れで、工数を使っていない
＝未着手 or 別作業により圧迫されている可能性
工数のみマイナス：スケジュールは遅れていないが、
工数を使い過ぎている＝見積が甘い or 作業効率が悪い
両方プラス：スケジュールより進んでいる（ただし進捗率の過大申告に注意）

サマリ進捗の集計

末端よりも1階層うえで紐づくチケットの集計を行う。
工程別の進捗状況を確認するイメージ。

SQL

WITH RECURSIVE issue_tree AS (
  SELECT
    i.id,
    i.parent_id,
    i.subject,
    i.assigned_to_id,
    i.start_date,
    i.due_date,
    i.done_ratio,
    i.estimated_hours,
    i.project_id,
    i.fixed_version_id,
    i.status_id,
    i.tracker_id,
    0 AS level,
    ARRAY[i.id::text || ':' || i.subject] AS ticket_path
  FROM issues i
  WHERE i.parent_id IS NULL
    AND i.status_id NOT IN (5, 6) -- 集計から除外したいトラッカーIDを指定
    AND i.project_id = 1 -- 取得対象PJ-IDを指定

  UNION ALL

  SELECT
    c.id,
    c.parent_id,
    c.subject,
    c.assigned_to_id,
    c.start_date,
    c.due_date,
    c.done_ratio,
    c.estimated_hours,
    c.project_id,
    c.fixed_version_id,
    c.status_id,
    c.tracker_id,
    it.level + 1,
    it.ticket_path || (c.id::text || ':' || c.subject)
  FROM issues c
  INNER JOIN issue_tree it ON c.parent_id = it.id
  WHERE c.status_id NOT IN (5, 6) -- 集計から除外したいトラッカーIDを指定
    AND c.project_id = 1 -- 取得対象PJ-IDを指定
),

-- 子を持つチケットだけ抽出
has_children AS (
  SELECT DISTINCT parent.id
  FROM issue_tree parent
  JOIN issue_tree child ON child.parent_id = parent.id
),

-- 子を持つチケットの集計
issue_aggregate AS (
  SELECT
    parent.id AS id,
    SUM(child.estimated_hours) AS total_estimated_hours,
    SUM(te.hours) AS total_spent_hours,
    MIN(child.start_date) AS min_start_date,
    MAX(child.due_date) AS max_due_date,
    SUM(child.done_ratio * COALESCE(child.estimated_hours, 0)) AS weighted_done_sum,
    SUM(COALESCE(child.estimated_hours, 0)) AS total_weight,
    COUNT(*) AS descendant_count
  FROM issue_tree parent
  LEFT JOIN issue_tree child 
    ON child.ticket_path @> ARRAY[parent.id::text || ':' || parent.subject] AND child.id != parent.id
  LEFT JOIN time_entries te ON te.issue_id = child.id
  WHERE parent.id IN (SELECT id FROM has_children)
  GROUP BY parent.id
)

SELECT
  v.name AS "バージョン",
  array_to_string(i.ticket_path, ' > ') AS "親階層",
  t.name AS "トラッカー",
  s.name AS "ステータス",
  i.subject AS "チケット題名",
  u.firstname || ' ' || u.lastname AS "担当者",

  agg.min_start_date AS "開始日",
  agg.max_due_date AS "期日",

  -- 加重平均進捗率
  CASE
    WHEN agg.total_weight > 0 THEN ROUND((agg.weighted_done_sum / agg.total_weight)::numeric, 1)
    ELSE NULL
  END AS "進捗率（加重平均）",

  agg.total_estimated_hours AS "予定工数(h)",
  COALESCE(agg.total_spent_hours, 0) AS "作業時間合計(h)",

  -- 遅れ率（日数進捗）
  CASE
    WHEN agg.min_start_date IS NOT NULL AND agg.max_due_date IS NOT NULL AND agg.max_due_date > agg.min_start_date THEN
      ROUND(
        (
          CASE
            WHEN agg.total_weight > 0 THEN (agg.weighted_done_sum / agg.total_weight)
            ELSE 0
          END
          - LEAST(100, 100.0 * GREATEST(0, CURRENT_DATE - agg.min_start_date) / (agg.max_due_date - agg.min_start_date))
        )::numeric,
        1
      )
    ELSE NULL
  END AS "遅れ率（日数進捗）",

  -- 遅れ率（工数進捗）
  CASE
    WHEN agg.total_estimated_hours > 0 THEN
      ROUND(
        (
          CASE
            WHEN agg.total_weight > 0 THEN (agg.weighted_done_sum / agg.total_weight)
            ELSE 0
          END
          - LEAST(100, 100.0 * COALESCE(agg.total_spent_hours, 0) / agg.total_estimated_hours)
        )::numeric,
        1
      )
    ELSE NULL
  END AS "遅れ率（工数進捗）"

FROM issue_aggregate agg
JOIN issue_tree i ON i.id = agg.id
LEFT JOIN users u ON u.id = i.assigned_to_id
LEFT JOIN trackers t ON t.id = i.tracker_id
LEFT JOIN issue_statuses s ON s.id = i.status_id
LEFT JOIN versions v ON i.fixed_version_id = v.id

WHERE i.project_id = 1 -- 取得対象PJ-IDを指定
  AND i.status_id NOT IN (5, 6) -- 集計から除外したいトラッカーIDを指定

ORDER BY v.name NULLS LAST, i.ticket_path;

実行結果

項目説明

開始日：子/孫チケットの中での最も古い開始日
期日：子/孫チケットの中で最も新しい期日
進捗率（加重平均）：子/孫チケットの進捗率を加重平均で算出
予定工数(h)：子/孫チケットの予定工数の合計
作業時間合計(h)：子/孫チケットの作業時間の合計
遅れ率（日数進捗）：
時間ベースの進捗判断
開始日～期日の期間を見たときに、現在日時的に進捗率がいくつであるべきであるかを元に、
実際の進捗率を比較したときの差異を計算
納期遅延の可能性が見える。日程が迫っているのに進んでないものが最も危険
遅れ率（工数進捗）：
予定工数と作業時間合計から、工数をこれだけ使っているなら、
これくらい進捗しているべきであるという進捗率を元に、
実際の進捗率と比較したときの差異を計算
工数の使い過ぎ、作業ログの未入力など、作業面の非効率を可視化

担当者・月別山積み

各月の各担当者の予定工数・実績工数の山積みを確認。

SQL

WITH estimated AS (
  SELECT
    assigned_to_id AS user_id,
    TO_CHAR(start_date, 'YYYY-MM') AS year_month,
    SUM(estimated_hours) AS estimated_hours
  FROM
    issues
  WHERE
    project_id = 1 -- 取得対象PJ-IDを指定
    AND assigned_to_id IS NOT NULL
    AND estimated_hours IS NOT NULL
    AND start_date IS NOT NULL
  GROUP BY
    assigned_to_id,
    TO_CHAR(start_date, 'YYYY-MM')
),
spent AS (
  SELECT
    user_id,
    TO_CHAR(spent_on, 'YYYY-MM') AS year_month,
    SUM(hours) AS spent_hours
  FROM
    time_entries
  WHERE
    project_id = 1 -- 取得対象PJ-IDを指定
  GROUP BY
    user_id,
    TO_CHAR(spent_on, 'YYYY-MM')
),
combined AS (
  SELECT
    COALESCE(e.user_id, s.user_id) AS user_id,
    COALESCE(e.year_month, s.year_month) AS year_month,
    COALESCE(e.estimated_hours, 0) AS estimated_hours,
    COALESCE(s.spent_hours, 0) AS spent_hours
  FROM
    estimated e
  FULL OUTER JOIN spent s
    ON e.user_id = s.user_id AND e.year_month = s.year_month
)
SELECT
  u.firstname || ' ' || u.lastname AS "担当者名",
  c.year_month AS "年月",
  c.estimated_hours AS "予定工数(h)",
  c.spent_hours AS "実績工数(h)",
  ROUND((c.estimated_hours / 7.5 / 20)::numeric, 2) AS "予定工数(7.5h計算での人月)",
  ROUND((c.spent_hours / 7.5 / 20)::numeric, 2) AS "実績工数(7.5h計算での人月)",
  ROUND((c.estimated_hours / 10 / 20)::numeric, 2) AS "予定工数(10h計算での人月)",
  ROUND((c.spent_hours / 10 / 20)::numeric, 2) AS "実績工数(10h計算での人月)"
FROM
  combined c
  JOIN users u ON c.user_id = u.id
where c.year_month >= 'YYYY-MM' -- いつからのデータを取得したいかを指定
ORDER BY
  "担当者名",
  "年月";

実行結果

項目説明

予定工数(h)：担当者・月別の予定工数の合計
実績工数(h)：担当者・月別の作業時間の記録の合計
予定工数(7.5h計算での人月)：残業なしで考えたときの人月予定工数
実績工数(7.5h計算での人月)：残業なしで考えたときの人月実績工数
予定工数(Xh計算での人月)：Xhペースで日々働く前提とした場合の人月予定工数
実績工数(Xh計算での人月)：Xhペースで日々働く前提とした場合の人月実績工数

Viewerをどうするか

手段1：可視化系のツールを活用する

簡易に利用できて便利なのは metabase
↓のような形でWEB公開可

手段2：Excelマクロブックを作成

利用環境においてpostgresqlのドライバインストールが必要
作成したマクロブックを配布するなら、RedmineのDB接続情報をみれないようにソースのパスワードロックなどを検討

フィルタリングが自由にできたり、ガントチャートチックなシートを作ってデータ参照するようにすれば、これはこれで優秀なViewerとなる。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up