黄昏より暗きもの、血の流れより赤きもの

読者です 読者をやめる 読者になる 読者になる

黄昏より暗きもの、血の流れより赤きもの

自分の好きな事を好きなように書いて行きます。

1ヶ月おきの累計売上高をMySQLで集計し、Excelでグラフ化するまで

MySQL Excel

はじめに

お店全体の売上高、家計簿の支出合計、営業成績を報告するレポートを作成する際に「○月1日から1ヶ月毎の全体的な売上高を知りたい」「○月1日から1ヶ月毎の営業件数を知りたい」と言う時がある。

株主総会の説明資料のように1ヶ月事に累計を計算し、「弊社の事業はこれだけ成長している」と言う事を説明するためのグラフを作成したいときがある。Excelの場合だと毎月毎のデータをSUMIF関数を使って集計し、加工後のデータをExcelのグラフしていく。

そしてこれがまた時間がかかって面倒。しかしながら会社のメインのデータベース(SQL)があって、会社から「SQLのSELECT文というものを使って資料作成していいよ♪」と許可されている場合のみ、こうしたSUMIF地獄から脱する事ができるだろう。以下その方法を紹介する。

尚本記事はMySQL専用の機能を使っている。OracleMicrosoft SQL Serverなどをお使いの場合、日付に関する関数、date_formatの部分をそれぞれのミドルウェアに合わせて書き換えて欲しい。

準備

データベースは各自で準備するとして、準備した後に使うファイルは以下となる。コマンドからSQLを叩いて使用するか、MySQLの場合ならばphpmyadmin上で実行しつつ使って欲しい。以下phpmyadminを使う場合のみ説明していく。練習の際phpmyadminに入り、データベースを選択又は新規作成。最後に以下のSQLを実行してデータを入れ込む。

練習用のMySQLテーブル

CREATE TABLE IF NOT EXISTS `tbl` (
  `id` int(10) NOT NULL COMMENT '主キー',
  `time` datetime NOT NULL COMMENT '集計日',
  `sales` int(100) NOT NULL COMMENT 'その日の売上高'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

練習用のMySQLデータ

INSERT INTO `tbl` (`id`, `time`, `sales`) 
VALUES 
(NULL, '2016-01-04 12:00:00', '1000000'), 
(NULL, '2016-02-10 13:00:00', '1400000'), 
(NULL, '2016-02-25 15:00:00', '600000'), 
(NULL, '2016-03-04 16:00:00', '800000'), 
(NULL, '2016-03-25 18:00:00', '800000'), 
(NULL, '2016-04-20 09:00:00', '500000'), 
(NULL, '2016-04-29 14:00:00', '400000'), 
(NULL, '2016-05-11 00:00:00', '700000'), 
(NULL, '2016-05-30 15:00:00', '300000'), 
(NULL, '2016-06-22 17:00:00', '1200000');

実践

今度はphpmyadminからデータベースを選択して、sqlを選択する。選択後以下のクエリを入力して「実行」を押す。以下のSQL文は2016年1月1日から2016年12月31日まで、1月毎に使用額[sum(a.salesの部分)]、累計金額[(SELECT...以下)]をそれぞれ集計するクエリである。累計の金額はtotalと定義している。

売上の集計期間を変えたい場合、例えば2015年から集計したい場合は2016の箇所を2015に、6月までで良い場合は2016-12-31の部分を2016-06-30とすれば良い。

1月毎に使用額、累計金額を集計するクエリ

SELECT 
	date_format(a.time,'%Y-%m') as dt,
	sum(a.sales),
	(
	select 
		sum(b.sales) 
	from 
		tbl b 
	where
		date_format(b.time, '%Y-%m') <= date_format(a.time,'%Y-%m') 
		and 
		date_format(b.time,'%Y-%m') >= '2016-01'
	) 
	as total 
from 
	tbl a 
where 
	a.time between '2016-01-01 00:00:00' and '2016-12-31 23:59:59' 
group by dt

筆者はこのSQL文を思い付くのに1時間はかかった。「select (調べたいもの) from (テーブル名) where (条件)」と言う基本形をいじくりまわし、最終的に上手く行ったのが正直な話。

漢字、英単語英文法、計算問題と同じようにこの手の事は手が動くまでに時間がかかる事が多い。しかしながら主業務で無い人も多い訳で、そういう場合は「こういうときにこういうSQL文を使う」をメモ帳(txtファイル)や、evernoteonenoteにネタ帳として保存しておくと良い

出力結果をExcel向けにエクスポートする

上手く行くと下のように日付、各月の売上[下のsum(a.sales)]、各月の累計の売上[下のtotal]が表示される。

f:id:program_study:20161223183202p:plain

各月の売上高と売上の累計が合っているかの確認が済んだら、下の「エクスポート」で、出力結果をExcelで編集していく。エクスポート画面の詳細は「データベースのエクスポート:DBOnline」に任せるとして、ここでは必要な設定を記す。まずエクスポートを「詳細」にチェックし、その後は

  1. フォーマットを「csv
  2. その他必要に応じてチェックを付ける
  3. エンコーディングへの変換を「SJIS」に指定

f:id:program_study:20161223183333p:plain

ExcelSJIS形式でないと文字化けをしてしまうので、3番目は特に注意したい。必要に応じて「1行目にカラム名を追加する」をしてみたりして、Excelで作業しやすいようにしよう。読み込んだファイルをExcelで見るとこのようになる。

f:id:program_study:20161223183352p:plain

Excelでグラフ作成

エクスポートしたファイルは時折日付が変な形式となっているので、「セルの書式設定」などで調節してグラフを作成して行こう。他のページや他の名目と金額の単位や見栄えを合わせる作業は、どうしてもExcelの方がやりやすい。そんな訳でグラフの書式をいじっていくと、累計売上のグラフの完成。

f:id:program_study:20161223183420p:plain

おわりに

今回はSQL文を紹介したいと思い書いてみた。初めての試みも多く、とくにこれから始める人にとって分かりにくい部分が数多くあったように思う。お手数だが不明な点はここのコメント欄でも良いし、各書籍や分かる人に聞きながら解決頂けると嬉しい。

参考

7-1 累計を取得