Redmine のチケット数をグラフにする 2

前回の続きです。

Redmine のチケット数をグラフにしようと、日別チケット数を求めるところまでやりました。次は、完了日ごとのチケット数を求めて最終的なグラフを作ろうと思います。

以下の SQL で日付ごとの完了数が取得できます。単純に最終更新日を参照するのではなく、 journals などからステータスが完了になった日付を取得しています。 1 つめの select で journals に存在するチケット (終了以外のステータスから終了に変化したチケット) を処理し、 2 つめの select で journals に存在しないチケット (いきなり終了ステータスで登録されたチケット) を処理しています。

select
	strftime('%Y-%m-%d', t.last_updated) as [date]
	, count(*) as [完了数] 
from
	( 
		select
			max(j.created_on) as last_updated
			, max(i.status_id) as status_id 
		from
			issues as i 
			inner join journals as j 
				on i.id = j.journalized_id 
			inner join journal_details as d 
				on j.id = d.journal_id 
		where
			i.project_id = 1 
			and j.journalized_type = 'Issue' 
			and d.prop_key = 'status_id' 
		group by
			i.id
	) as t 
	inner join issue_statuses as s 
		on t.status_id = s.id 
where
	s.is_closed = 't' 
group by
	strftime('%Y-%m-%d', t.last_updated) 
union all 
select
	strftime('%Y-%m-%d', i.created_on) as [date]
	, count(*) as [完了数] 
from
	issues as i 
	inner join issue_statuses as s 
		on i.status_id = s.id 
where
	i.project_id = 1 
	and s.is_closed = 't' 
	and not exists( 
		select
			* 
		from
			journals as j 
			inner join journal_details as d 
				on j.id = d.journal_id 
		where
			j.journalized_id = i.id 
			and j.journalized_type = 'Issue' 
			and d.prop_key = 'status_id'
	) 
order by
	[date]

ということで、やっとデータが取得できました。 *1

後は、昨日の分とともに取得したデータを Excel に張り付け、累積を求めてグラフ出力しました。以下は、作成したグラフのサンプル *2 です。

そういえば、脱Excel！ Redmineでアジャイル開発を楽々管理なんて記事がありました。同僚に紹介する時やマニュアルページ代りに使えそうですね。

*1:使い捨ての予定なので微妙な SQL ですね。もっといい方法があると思います。お勧めの方法があればぜひ教えてください。

*2:実際のグラフはお見せできないので、チケット件数などに一部ダミー値を混合