Redmineチケットを作成日毎にカウントするAccess集計クエリ

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ビュー] から貼り付けて使用できます。

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_private1:プライベートチケット
0:通常のチケット
closed_onチケットの完了日時、但し再オープン時にクリアされません

このテーブルにはチケットの標準フィールドしかありません。各チケットのカスタムフィールドの値は、すべてcustom_valuesテーブルに保存されています。

木構造によるチケットの階層表現については、また別の記事で紹介したいと思います。

「trackers」テーブルのフィールド一覧

このテーブルには、トラッカーに関する設定内容があります。

項目名説明
idトラッカーのid
nameトラッカー名
is_in_chlog
positionトラッカーの表示順
is_in_roadmap1:このトラッカーをロードマップに表示する
0:このトラッカーをロードマップに表示しない
fields_bits使用するフィールドをビット演算で表現した値
default_status_idデフォルトのステータスのid

「projects」テーブルのフィールド一覧

このテーブルには、プロジェクトに関する設定内容があります。

項目名説明
idプロジェクトのid
nameプロジェクト名
descriptionプロジェクトの説明
homepageホームページ
is_public1:このプロジェクトを公開する
0:このプロジェクトを公開しない
parent_id親プロジェクトのid
created_onプロジェクトの作成日時
updated_onプロジェクトの更新日時
identifierプロジェクトの識別子
status1:有効なプロジェクト
5:終了したプロジェクト
9:アーカイブしたプロジェクト
lftプロジェクトの階層を木構造で表した左(left)側の値
rgtプロジェクトの階層を木構造で表した右(right)側の値
inherit_members1:親プロジェクトからメンバーを継承する
0:親プロジェクトからメンバーを継承しない
default_version_id

この記事のまとめ

今回は「チケットの作成数を日別にカウントする」集計クエリを紹介しました。

このようにAccessファイルにMySQL(Redmine)のリンクテーブルを作成すれば、面倒なプログラムを組むことなく、データベース言語(SQL)だけで色々集計できます。

また、Accessで集計した結果は、Excelと連携して簡単にグラフ化することもできます。

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

コメント

コメント一覧 (1件)

コメントする

目次