集計・レポート設計

概要

マネージャー向けダッシュボードおよび各種レポートに必要な集計ロジックを定義する。

1. マネージャー向けダッシュボード

1.1 ダッシュボード概要

マネージャーが一覧で確認できる情報:

表示項目	説明
プロジェクト名	プロジェクト一覧
予算上限	設定された予算額
消化額	confirmed + pending の合計
残予算	予算上限 - 消化額
予算消化率	消化額 / 予算上限 (%)
Issue消化率	完了Issue / 全Issue (%)
乖離度	予算消化率 - Issue消化率
ETA	全Issue完了の予測日
アラート	予算超過・乖離警告の有無

1.2 ダッシュボード集計クエリ

sql

SELECT
  p.id,
  p.name,
  p.budget_limit,
  -- 消化額
  COALESCE(r.confirmed_total, 0) + COALESCE(r.pending_total, 0) AS consumed,
  -- 残予算
  p.budget_limit
    - COALESCE(r.confirmed_total, 0)
    - COALESCE(r.pending_total, 0)
    - COALESCE(r.active_total, 0) AS remaining,
  -- 予算消化率
  ROUND(
    (COALESCE(r.confirmed_total, 0) + COALESCE(r.pending_total, 0))
    * 100.0 / NULLIF(p.budget_limit, 0), 1
  ) AS budget_rate,
  -- Issue消化率
  ROUND(
    i.completed * 100.0 / NULLIF(i.total, 0), 1
  ) AS issue_rate,
  -- 乖離度
  ROUND(
    (COALESCE(r.confirmed_total, 0) + COALESCE(r.pending_total, 0))
    * 100.0 / NULLIF(p.budget_limit, 0), 1
  ) - ROUND(
    i.completed * 100.0 / NULLIF(i.total, 0), 1
  ) AS divergence,
  -- 残Issue数
  i.total - i.completed AS remaining_issues,
  -- 直近4週の完了数
  w.weekly_avg,
  -- ETA（残り週数）
  CASE
    WHEN COALESCE(w.weekly_avg, 0) = 0 THEN NULL
    ELSE ROUND((i.total - i.completed) * 1.0 / w.weekly_avg, 1)
  END AS eta_weeks
FROM projects p
LEFT JOIN (
  SELECT
    project_id,
    SUM(CASE WHEN status IN ('confirmed', 'paid') THEN amount ELSE 0 END) AS confirmed_total,
    SUM(CASE WHEN status = 'pending' THEN amount ELSE 0 END) AS pending_total,
    SUM(CASE WHEN status = 'active' THEN amount ELSE 0 END) AS active_total
  FROM rewards
  GROUP BY project_id
) r ON r.project_id = p.id
LEFT JOIN (
  SELECT
    project_id,
    COUNT(*) AS total,
    COUNT(*) FILTER (WHERE status = 'closed') AS completed
  FROM issues
  GROUP BY project_id
) i ON i.project_id = p.id
LEFT JOIN (
  SELECT
    project_id,
    COUNT(*) * 1.0 / 4 AS weekly_avg
  FROM issues
  WHERE status = 'closed'
    AND closed_at >= CURRENT_DATE - INTERVAL '28 days'
  GROUP BY project_id
) w ON w.project_id = p.id
ORDER BY p.name;

2. 委託者別の報酬サマリー（月次）

2.1 集計仕様

対象期間内に confirmed または paid になった報酬を委託者別に集計する。

2.2 集計クエリ

sql

SELECT
  u.id AS assignee_id,
  u.name AS assignee_name,
  COUNT(r.id) AS issue_count,
  SUM(r.amount) AS total_amount,
  AVG(r.amount) AS avg_amount,
  MIN(r.amount) AS min_amount,
  MAX(r.amount) AS max_amount,
  -- 難易度別内訳
  COUNT(*) FILTER (WHERE r.difficulty = 'easy') AS easy_count,
  COUNT(*) FILTER (WHERE r.difficulty = 'medium') AS medium_count,
  COUNT(*) FILTER (WHERE r.difficulty = 'hard') AS hard_count,
  SUM(r.amount) FILTER (WHERE r.difficulty = 'easy') AS easy_total,
  SUM(r.amount) FILTER (WHERE r.difficulty = 'medium') AS medium_total,
  SUM(r.amount) FILTER (WHERE r.difficulty = 'hard') AS hard_total
FROM rewards r
JOIN users u ON u.id = r.assignee_id
WHERE r.status IN ('confirmed', 'paid')
  AND r.confirmed_at >= :period_start
  AND r.confirmed_at < :period_end
GROUP BY u.id, u.name
ORDER BY total_amount DESC;

2.3 出力フォーマット例

=== 委託者別報酬サマリー（2026年3月） ===

委託者      | 件数 | 合計       | 平均     | Easy | Medium | Hard
------------|------|-----------|---------|------|--------|------
田中太郎     | 8    | 180,000円 | 22,500円 | 3    | 4      | 1
山田花子     | 5    | 120,000円 | 24,000円 | 1    | 3      | 1
佐藤次郎     | 12   | 96,000円  | 8,000円  | 10   | 2      | 0

3. プロジェクト別の支出サマリー

3.1 集計クエリ

sql

SELECT
  p.id AS project_id,
  p.name AS project_name,
  p.budget_limit,
  -- ステータス別集計
  SUM(CASE WHEN r.status = 'paid' THEN r.amount ELSE 0 END) AS paid_total,
  SUM(CASE WHEN r.status = 'confirmed' THEN r.amount ELSE 0 END) AS confirmed_total,
  SUM(CASE WHEN r.status = 'pending' THEN r.amount ELSE 0 END) AS pending_total,
  SUM(CASE WHEN r.status = 'active' THEN r.amount ELSE 0 END) AS active_total,
  -- 月別推移（当月）
  SUM(CASE
    WHEN r.status IN ('confirmed', 'paid')
     AND r.confirmed_at >= DATE_TRUNC('month', CURRENT_DATE)
    THEN r.amount ELSE 0
  END) AS current_month_spend,
  -- Issue数
  COUNT(DISTINCT r.issue_id) AS total_reward_issues,
  COUNT(DISTINCT r.issue_id) FILTER (WHERE r.status IN ('confirmed', 'paid')) AS completed_reward_issues
FROM projects p
LEFT JOIN rewards r ON r.project_id = p.id
GROUP BY p.id, p.name, p.budget_limit
ORDER BY p.name;

3.2 月別推移クエリ

sql

SELECT
  p.name AS project_name,
  TO_CHAR(DATE_TRUNC('month', r.confirmed_at), 'YYYY-MM') AS month,
  COUNT(r.id) AS issue_count,
  SUM(r.amount) AS monthly_spend
FROM rewards r
JOIN projects p ON p.id = r.project_id
WHERE r.status IN ('confirmed', 'paid')
  AND r.confirmed_at >= :start_date
GROUP BY p.name, DATE_TRUNC('month', r.confirmed_at)
ORDER BY p.name, month;

4. ETA予測ロジック

4.1 計算式

残Issue数 = 全Issue数 - 完了Issue数
直近4週の平均完了数 = 直近28日間に完了したIssue数 / 4
残り週数 = 残Issue数 / 直近4週の平均完了数
ETA日付 = 現在日 + (残り週数 * 7日)

4.2 ETA算出クエリ

sql

WITH completion_rate AS (
  SELECT
    project_id,
    COUNT(*) AS completed_last_4weeks
  FROM issues
  WHERE status = 'closed'
    AND closed_at >= CURRENT_DATE - INTERVAL '28 days'
  GROUP BY project_id
),
project_issues AS (
  SELECT
    project_id,
    COUNT(*) AS total,
    COUNT(*) FILTER (WHERE status = 'closed') AS completed
  FROM issues
  GROUP BY project_id
)
SELECT
  p.id,
  p.name,
  pi.total AS total_issues,
  pi.completed AS completed_issues,
  pi.total - pi.completed AS remaining_issues,
  COALESCE(cr.completed_last_4weeks, 0) AS completed_last_4weeks,
  ROUND(COALESCE(cr.completed_last_4weeks, 0) / 4.0, 2) AS weekly_avg,
  CASE
    WHEN COALESCE(cr.completed_last_4weeks, 0) = 0
      THEN NULL  -- 予測不能
    ELSE ROUND((pi.total - pi.completed) * 4.0 / cr.completed_last_4weeks, 1)
  END AS eta_weeks,
  CASE
    WHEN COALESCE(cr.completed_last_4weeks, 0) = 0
      THEN NULL
    ELSE CURRENT_DATE + (
      (pi.total - pi.completed) * 28 / cr.completed_last_4weeks
    ) * INTERVAL '1 day'
  END AS eta_date
FROM projects p
JOIN project_issues pi ON pi.project_id = p.id
LEFT JOIN completion_rate cr ON cr.project_id = p.id
ORDER BY p.name;

4.3 ETA表示ルール

条件	表示
直近4週の完了数 = 0	「予測不能（直近の完了実績なし）」
残Issue数 = 0	「完了済み」
ETA日付が予算枯渇予測日より後	警告マーク付きで表示
通常	「残り約X週（YYYY/MM/DD頃）」

4.4 予算枯渇予測

ETAと合わせて、予算面からの枯渇予測も算出する。

平均Issue単価 = (confirmed_total + pending_total) / 完了Issue数
残Issue予算 = 残Issue数 * 平均Issue単価
予算充足率 = 残予算 / 残Issue予算

予算充足率	判定
>= 1.0	予算内で完了見込み
0.7 - 1.0	予算不足リスクあり
< 0.7	予算大幅不足。予算追加 or スコープ縮小を推奨

5. レポートフロー

mermaid

flowchart TD
    A[日次バッチ] --> B[予算消化率・Issue消化率を集計]
    B --> C{アラート条件に該当?}
    C -->|Yes| D[アラート通知送信]
    C -->|No| E[スキップ]

    F[月次バッチ - 月末] --> G[月次スナップショット保存]
    G --> H[委託者別報酬サマリー生成]
    H --> I[プロジェクト別支出サマリー生成]
    I --> J[マネージャーにレポート送信]

    K[ダッシュボードアクセス] --> L[リアルタイム集計クエリ実行]
    L --> M[ダッシュボード表示]

6. ダッシュボードUI仕様

6.1 プロジェクト一覧テーブル

各行に表示する情報とビジュアル表現:

項目	表示形式
予算消化率	プログレスバー + パーセント表示
Issue消化率	プログレスバー + パーセント表示
乖離度	数値 + 色（緑: 正常, 黄: 注意, 赤: 警告）
残予算	金額表示 + 色（赤: マイナス）
ETA	日付 + 残り週数
アラート	アイコン表示（注意/警告/危険）

6.2 ドリルダウン

プロジェクト行をクリックすると以下の詳細を表示:

Issue一覧（報酬額・ステータス付き）
月別消化推移グラフ
委託者別の報酬内訳
ETA予測の根拠（直近4週の完了数等）

集計・レポート設計 ​

概要 ​

1. マネージャー向けダッシュボード ​

1.1 ダッシュボード概要 ​

1.2 ダッシュボード集計クエリ ​

2. 委託者別の報酬サマリー（月次） ​

2.1 集計仕様 ​

2.2 集計クエリ ​

2.3 出力フォーマット例 ​

3. プロジェクト別の支出サマリー ​

3.1 集計クエリ ​

3.2 月別推移クエリ ​

4. ETA予測ロジック ​

4.1 計算式 ​

4.2 ETA算出クエリ ​

4.3 ETA表示ルール ​

4.4 予算枯渇予測 ​

5. レポートフロー ​

6. ダッシュボードUI仕様 ​

6.1 プロジェクト一覧テーブル ​

6.2 ドリルダウン ​

集計・レポート設計

概要

1. マネージャー向けダッシュボード

1.1 ダッシュボード概要

1.2 ダッシュボード集計クエリ

2. 委託者別の報酬サマリー（月次）

2.1 集計仕様

2.2 集計クエリ

2.3 出力フォーマット例

3. プロジェクト別の支出サマリー

3.1 集計クエリ

3.2 月別推移クエリ

4. ETA予測ロジック

4.1 計算式

4.2 ETA算出クエリ

4.3 ETA表示ルール

4.4 予算枯渇予測

5. レポートフロー

6. ダッシュボードUI仕様

6.1 プロジェクト一覧テーブル

6.2 ドリルダウン