Access「作成されたRedmineのチケット数を日別にカウントする」集計クエリ

「Redmineのチケット作成数を日別にカウントする」Microsoft Access(以下 Access)の集計クエリを紹介します。Accessファイルには、MySQL(Redmine)のリンクテーブルが作成されているものとします。

リンクテーブルの作成方法については次の記事が参考になります。

AccessをMySQL(Redmine)に接続する方法
ODBCドライバーを使用して、Microsoft Access(以下 Access)をMySQL(Redmine)に接続する方法を紹介します。Accessに、MySQL(Redmine)の各テーブルをデータごと取り込んだり、リンクテーブルを作成してクエリーで集計することができるようになります。

スポンサーリンク

集計クエリのSQL文

集計クエリのSQL文は次の通りです。

MySQL(Redmine)のリンクテーブルがあるAccessファイルに、新たに選択クエリを作成して、[ファイル]リボンの[表示] > [SQLビュー] から貼り付けて使用できます。

img_56ab5bd1b4dff

[ファイル]リボンの[表示] > [デザインビュー]に切り替えると次のように表示されます。

img_56aa280aba715

”集計したいトラッカー名”と”集計したいプロジェクト名”は、ご使用の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 1:プライベートチケット
0:通常のチケット
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と連携して簡単にグラフ化することもできます。

この方法についても、別の記事で紹介したいと思います。