MySQLでクロス集計をするクエリ

  • 投稿日:
  • by
このエントリーをはてなブックマークに追加

MySQLでクロス集計のSQL文の書き方とその検証をしてみました。

単価*数量を計算して2次元のデータを取得するSQLです。

select 
pref,
sum(case when date = '2015-10-01' then price * qty else 0 end) `2015-10-01`,
sum(case when date = '2015-10-02' then price * qty else 0 end) `2015-10-02`,
sum(case when date = '2015-10-03' then price * qty else 0 end) `2015-10-03`,
sum(case when date = '2015-10-04' then price * qty else 0 end) `2015-10-04`,
sum(case when date = '2015-10-05' then price * qty else 0 end) `2015-10-05`,
sum(case when date = '2015-10-06' then price * qty else 0 end) `2015-10-06`,
sum(case when date = '2015-10-07' then price * qty else 0 end) `2015-10-07`,
sum(case when date = '2015-10-08' then price * qty else 0 end) `2015-10-08`,
sum(case when date = '2015-10-09' then price * qty else 0 end) `2015-10-09`,
sum(case when date = '2015-10-10' then price * qty else 0 end) `2015-10-10`,
sum(case when date = '2015-10-11' then price * qty else 0 end) `2015-10-11`,
sum(case when date = '2015-10-12' then price * qty else 0 end) `2015-10-12`,
sum(case when date = '2015-10-13' then price * qty else 0 end) `2015-10-13`,
sum(case when date = '2015-10-14' then price * qty else 0 end) `2015-10-14`,
sum(case when date = '2015-10-15' then price * qty else 0 end) `2015-10-15`,
sum(case when date = '2015-10-16' then price * qty else 0 end) `2015-10-16`,
sum(case when date = '2015-10-17' then price * qty else 0 end) `2015-10-17`,
sum(case when date = '2015-10-18' then price * qty else 0 end) `2015-10-18`,
sum(case when date = '2015-10-19' then price * qty else 0 end) `2015-10-19`,
sum(case when date = '2015-10-20' then price * qty else 0 end) `2015-10-20`,
sum(case when date = '2015-10-21' then price * qty else 0 end) `2015-10-21`,
sum(case when date = '2015-10-22' then price * qty else 0 end) `2015-10-22`,
sum(case when date = '2015-10-23' then price * qty else 0 end) `2015-10-23`,
sum(case when date = '2015-10-24' then price * qty else 0 end) `2015-10-24`,
sum(case when date = '2015-10-25' then price * qty else 0 end) `2015-10-25`,
sum(case when date = '2015-10-26' then price * qty else 0 end) `2015-10-26`,
sum(case when date = '2015-10-27' then price * qty else 0 end) `2015-10-27`,
sum(case when date = '2015-10-28' then price * qty else 0 end) `2015-10-28`,
sum(case when date = '2015-10-29' then price * qty else 0 end) `2015-10-29`,
sum(case when date = '2015-10-30' then price * qty else 0 end) `2015-10-30`,
sum(case when date = '2015-10-31' then price * qty else 0 end) `2015-10-31`

from test01 where
	date >= '2015-10-01' and 
	date < '2015-11-01'
group by pref
order by field(pref,'Hokkaido','Aomori','Iwate','Miyagi','Akita','Yamagata','Fukushima','Ibaraki','Tochigi','Gunma','Saitama','Chiba','Tokyo','Kanagawa','Niigata','Toyama','Ishikawa','Fukui','Yamanashi','Nagano','Gifu','Shizuoka','Aichi','Mie','Shiga','Kyoto','Osaka','Hyogo','Nara','Wakayama','Tottori','Shimane','Okayama','Hiroshima','Yamaguchi','Tokushima','Kagawa','Ehime','Kochi','Fukuoka','Saga','Nagasaki','Kumamoto','Oita','Miyazaki','Kagoshima','Okinawa')

重くなるのではないかと思い、実行速度の簡単な検証をしてみました。

テーブルのカラムは下記です。

	`id` INT(11) NULL DEFAULT NULL,
	`pref` VARCHAR(9) NULL DEFAULT NULL,
	`date` DATE NULL DEFAULT NULL,
	`price` INT(11) NULL DEFAULT NULL,
	`qty` INT(11) NULL DEFAULT NULL

データ件数は5,000件。下記のようなデータが入っています。

idprefdatepriceqty
1 Kagoshima 2015-10-30 98 10
2 Yamanashi 2015-11-17 86 15
3 Saga 2015-10-15 62 4

price:単価 qty:数量 pref:商品名?(都道府県にしてしまいましたがこういう商品だと考えてください。)

まずは上記のSQLを10回実行した結果は、平均 0.0360 sec。

下記のSQLで比較しました。

select 
pref,
date,
sum(price * qty) sum
from test01 
where
	date >= '2015-10-01' and 
	date < '2015-11-01'
group by pref,date
order by field(pref,'Hokkaido','Aomori','Iwate','Miyagi','Akita','Yamagata','Fukushima','Ibaraki','Tochigi','Gunma','Saitama','Chiba','Tokyo','Kanagawa','Niigata','Toyama','Ishikawa','Fukui','Yamanashi','Nagano','Gifu','Shizuoka','Aichi','Mie','Shiga','Kyoto','Osaka','Hyogo','Nara','Wakayama','Tottori','Shimane','Okayama','Hiroshima','Yamaguchi','Tokushima','Kagawa','Ehime','Kochi','Fukuoka','Saga','Nagasaki','Kumamoto','Oita','Miyazaki','Kagoshima','Okinawa')
,date

同じく10回実行した結果は、平均 0.0125 sec。

色々な条件によって違うかと思いますが、筆者がためした環境ではクロス集計のSQLの方がやや遅いようです。今回は5000件のデータでしたが、増えれば更に遅くなると思います。プログラム側の実行速度は検証しておりませんが、MySQL側が重くなるようなら避けたほうが良いかも知れません。

該当する日付のデータがなくても 0 として出力してくれるので、mysqlクライアントソフトで実行したときに見やすい。という利点があります。データの確認には良いと思います。

クロス集計クエリを実行すると下記のように出力されます。

pref2015-10-012015-10-022015-10-03...
Hokkaido 0 880 0 ...
Aomori 1088 696 490 ...
Iwate 0 3119 3143 ...
: : : : :

検証で使用したデータは下記で取得しました。

Mockaroo - Random Data Generator
https://www.mockaroo.com/

出力するフォーマットやカラムのデータタイプが色々選べるので重宝しました。

実践ハイパフォーマンスMySQL 第3版