Help us understand the problem. What is going on with this article?

MySQLでクロス集計をするクエリの実行速度検証

More than 5 years have passed since last update.

実行速度の簡単な検証をしてみました。
単価*数量を計算して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件。下記のようなデータが入っています。

id pref date price qty
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クライアントソフトで実行したときに見やすい。という利点があります。データの確認には良いと思います。

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

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

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

MySQLでクロス集計をするクエリ
http://web.loft-net.co.jp/lofttecs/mysql_crosstab/

lofttecs
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away