Redmineチケットを作成日毎にカウントするAccessの集計クエリを紹介します。
Redmineデータベースには、チケットを格納した「issues」テーブルがあります。
このテーブルのリンクテーブルをAccessに作成して、集計クエリで使用します。
リンクテーブルの作成方法については次の記事が参考になります。
集計クエリのSQL文
集計クエリのSQL文は次の通りです。
SELECT CDate(Int([issues].[created_on])) AS 年月日, Count(issues.id) AS 作成数
FROM (issues INNER JOIN projects ON issues.project_id = projects.id) INNER JOIN trackers ON issues.tracker_id = trackers.id
WHERE (((trackers.name)="集計したいトラッカー名") AND ((projects.name)="集計したいプロジェクト名"))
GROUP BY CDate(Int([issues].[created_on]));MySQL(Redmine)のリンクテーブルがあるAccessファイルに、新たに選択クエリを作成して、[ファイル]リボンの[表示] ＞ [SQLビュー] から貼り付けて使用できます。
[ファイル]リボンの[表示] ＞ [デザインビュー]に切り替えると次のように表示されます。
”集計したいトラッカー名”と”集計したいプロジェクト名”は、ご使用のRedmineにあわせて変更してください。
SQL文のポイント
簡単に、このSQL文のポイントを解説したいと思います。
チケットの作成日時は「issues」テーブルの「created_on」フィールドに日付/時刻型で保存されています。日付/時刻型では、データを倍精度浮動小数点(小数点15位まで)として保存していて、整数部分が日付、小数点部分が時刻を表しています。
CDate(Int([issues].[created_on]))では、先ず、Int関数で小数点以下を切り捨て、整数部分をCdate関数で日付型(Date)に変換しています。この値をGROUP BYに指定することで、日付別の集計としています。
この集計クエリで使用しているテーブルの各フィールドは、(ぼくの知る範囲ですが)次のようになっています。
「issues」テーブルのフィールド一覧
Redmineのチケットは、プロジェクトやトラッカーの違いに関係なく、すべてこのテーブルにあります。テーブルの中身はこんな感じです。
| 項目名 | 説明 |
| id | チケット番号 |
| tracker_id | トラッカーのid、設定内容は「trackers」テーブルにあります |
| project_id | プロジェクトのid、設定内容は「projects」テーブルにあります |
| subject | 題名 |
| description | 説明 |
| due_date | 期日 |
| category_id | カテゴリのid、設定内容は「issue_categories」テーブルにあります |
| status_id | ステータスのid、設定内容は「issue_statuses」テーブルにあります |
| assigned_to_id | 担当者のid、設定内容は「users」テーブルにあります |
| priority_id | 優先度のid、設定内容は「enumerations」テーブルにあります |
| fixed_version_id | 対象バージョンのid、設定内容は「versions」テーブルにあります |
| author_id | 作成者のid、設定内容は「users」テーブルにあります |
| lock_version | ？ |
| created_on | チケットの作成日時 |
| updated_on | チケットの更新日時 |
| start_date | 開始日 |
| done_ratio | 進捗率 |
| estimated_hours | 見積り時間 |
| parent_id | 親チケットの番号 |
| lft | チケットの階層を木構造で表した左(left)側の値 |
| rgt | チケットの階層を木構造で表した右(right)側の値 |
| is_private | １：プライベートチケット ０：通常のチケット |
| closed_on | チケットの完了日時、但し再オープン時にクリアされません |
このテーブルにはチケットの標準フィールドしかありません。各チケットのカスタムフィールドの値は、すべてcustom_valuesテーブルに保存されています。
木構造によるチケットの階層表現については、また別の記事で紹介したいと思います。
「trackers」テーブルのフィールド一覧
このテーブルには、トラッカーに関する設定内容があります。
| 項目名 | 説明 |
| id | トラッカーのid |
| name | トラッカー名 |
| is_in_chlog | ？ |
| position | トラッカーの表示順 |
| is_in_roadmap | 1：このトラッカーをロードマップに表示する 0：このトラッカーをロードマップに表示しない |
| fields_bits | 使用するフィールドをビット演算で表現した値 |
| default_status_id | デフォルトのステータスのid |
「projects」テーブルのフィールド一覧
このテーブルには、プロジェクトに関する設定内容があります。
| 項目名 | 説明 |
| id | プロジェクトのid |
| name | プロジェクト名 |
| description | プロジェクトの説明 |
| homepage | ホームページ |
| is_public | 1：このプロジェクトを公開する 0：このプロジェクトを公開しない |
| parent_id | 親プロジェクトのid |
| created_on | プロジェクトの作成日時 |
| updated_on | プロジェクトの更新日時 |
| identifier | プロジェクトの識別子 |
| status | 1：有効なプロジェクト 5：終了したプロジェクト 9：アーカイブしたプロジェクト |
| lft | プロジェクトの階層を木構造で表した左(left)側の値 |
| rgt | プロジェクトの階層を木構造で表した右(right)側の値 |
| inherit_members | 1：親プロジェクトからメンバーを継承する 0：親プロジェクトからメンバーを継承しない |
| default_version_id | ？ |
この記事のまとめ
今回は「チケットの作成数を日別にカウントする」集計クエリを紹介しました。
このようにAccessファイルにMySQL(Redmine)のリンクテーブルを作成すれば、面倒なプログラムを組むことなく、データベース言語(SQL)だけで色々集計できます。
また、Accessで集計した結果は、Excelと連携して簡単にグラフ化することもできます。
この方法についても、別の記事で紹介したいと思います。
