WHAT'S NEW?
Loading...

MySQLでクロス集計してみた【2/2】

, , , , , , ,
先日のSQLでクロス集計した際、助言をいただけて別の方法で解決した（Dupont氏に感謝）。

俺なりに何が違うのか考えてみた結果、
  • sum()とcount()で結果が違う
  • coalesce()関数とは
の2点に絞って調査してみた。

まず前回俺が書いたSQLは以下になる。
select name as '商品名',
 count(case when color='白' then 1 else null end) as '白',
 count(case when color='黒' then 1 else null end) as '黒',
 count(case when color='赤' then 1 else null end) as '赤',
 count(case when color='青' then 1 else null end) as '青',
 count(case when color='緑' then 1 else null end) as '緑',
 case
  when name='ビックリ弁当箱' then sum(field(name, 'ビックリ弁当箱'))
  when name='最果てグローブ' then sum(field(name, '最果てグローブ'))
  when name='無駄毛ロンガー' then sum(field(name, '無駄毛ロンガー'))
  else null
 end as '合計'
from history
group by name with rollup
そしてDupont氏の助言は以下。
SELECT COALESCE(`name`, '合計') AS `商品名`,
SUM(CASE WHEN `color`='白' THEN 1 ELSE 0 END) AS '白',
SUM(CASE WHEN `color`='黒' THEN 1 ELSE 0 END) AS '黒',
SUM(CASE WHEN `color`='赤' THEN 1 ELSE 0 END) AS '赤',
SUM(CASE WHEN `color`='青' THEN 1 ELSE 0 END) AS '青',
SUM(CASE WHEN `color`='緑' THEN 1 ELSE 0 END) AS '緑',
COUNT(*) AS `合計`
FROM `history`
GROUP BY `name` WITH ROLLUP;
※COALESCE()関数とWITH ROLLUPは条件が一緒になるように追記してる

大文字小文字は抜きにして、基本的にcount()がsum()になっている点と、小計がcase式か、count()か、という点が大きく違う。



この点、今までうろ覚え&自己完結でちゃんとしらべてなかったところでもあるし、そもそもSQLはそれほど突っ込んで研究したことがなかったので、「なんだこの程度か」的なレベルかもしれないが、ちゃんと調べてみることにした。

■COALESCE()

coalesceは何て読むのかというと、「こうあれす」だ。
ALCで調べてみたところ、
【自動】
  1. 〔動植物の組織などが〕癒着する
  2. 〔複数のものが〕合体する、融合する
・The two small groups coalesce into one big one. : それら二つの小グループはまとまって一つの大きなグループになった。
だそうだ。
http://eow.alc.co.jp/coalesce%20/UTF-8/?ref=sa

ちなみに使い方だけど、MySQLの4.1のマニュアルだけど、こう書いてある。
リスト内の最初の非NULL要素を返す
http://dev.mysql.com/doc/refman/4.1/ja/comparison-operators.html

NULLかNULLでないか、ということなので、とりあえず以下のようなSQLを吐いてテストしてみる。
select coalesce(null,null,'a',null,'b');
結果は以下のようになった。

coalesce(null,null,'a',null,'b')
a

これはつまり、coalesce()内に指定したリストのうち、nullは無視され、null以外の文字で最初にヒットした要素1個だけがselectされるということが分かった。

ということは、Dupont氏の助言にあるように、
SELECT COALESCE(`name`, '合計') AS `商品名`
の場合、nameフィールドを順番に表示する際、nullだったら無視され、2番目の要素の「合計」がselectされるということだ。
つまりこの場合、フィールドがnullなら2番目が適用される、という意味でOK。

俺がやりたかったことは、

商品名白黒赤青緑合計
ビックリ弁当箱112127
最果てグローブ222107
無駄毛ロンガー102126
NULL43634NULL

この最後のNULL（2個あるけど左下のNULL）を別の文字にしたかった、ということなので、
  1. select coalesce('びっくり弁当箱', '合計') は 'びっくり弁当箱' と表示され、
  2. select coalesce('最果てグローブ', '合計') は'最果てグローブ'と表示され、
  3. select coalesce('無駄毛ロンガー', '合計') は'無駄毛ロンガー'と表示され、
  4. select coalesce(null, '合計') はnullなので次の'合計'が表示される
ということになる。
なるほど、と思った。

■sum()とcount()での結果

最初の俺のSQLだと、各集計をsum()ではなくcount()を使ってやってみたわけだ。
select
count(case when color='白' then 1 else null end) as '白',
count(case when color='黒' then 1 else null end) as '黒',
count(case when color='赤' then 1 else null end) as '赤',
count(case when color='青' then 1 else null end) as '青',
count(case when color='緑' then 1 else null end) as '緑'
from history
しかしこのやり方だと、最終行の最終列フィールドがnullになってしまっている。

この部分は特別で、行、列ともに合計を出しておかないといけない。

合計を出すには、俺のやり方でも一応出すことはできるが、正直これはスマートじゃない。
つまり、
case
  when name='ビックリ弁当箱' then sum(field(name, 'ビックリ弁当箱'))
  when name='最果てグローブ' then sum(field(name, '最果てグローブ'))
  when name='無駄毛ロンガー' then sum(field(name, '無駄毛ロンガー'))
  else null
 end as '合計'
より
COUNT(*) AS '合計'
の方が断然スマートだ。

そもそもこういった場合にはCOUNT()を使う方が正しい、と言ってしまってもよいと思う。
したがって、以下のようにSQLを書き換えてみる。
select coalesce(name, '合計') as '商品名',
 count(case when color='白' then 1 else null end) as '白',
 count(case when color='黒' then 1 else null end) as '黒',
 count(case when color='赤' then 1 else null end) as '赤',
 count(case when color='青' then 1 else null end) as '青',
 count(case when color='緑' then 1 else null end) as '緑',
 count(*) as '合計'
from history
group by name
このSQLを発行すると、以下のような結果になる。

商品名白黒赤青緑合計
ビックリ弁当箱112127
最果てグローブ222107
無駄毛ロンガー102126

なかなかどうして宜しくなってきた。

さて、次に縦方向の集計を出さなければならない。
幸いにもMySQLにはGROUP BYにWITH ROLLUPをつけると、縦方向に集計された結果が1行加えられる。
これは便利だ。

http://dev.mysql.com/doc/refman/4.1/ja/group-by-modifiers.html

というわけで、さっそくGROUP BYにWITH ROLLUPをつけてみる。
select coalesce(name, '合計') as '商品名',
 count(case when color='白' then 1 else null end) as '白',
 count(case when color='黒' then 1 else null end) as '黒',
 count(case when color='赤' then 1 else null end) as '赤',
 count(case when color='青' then 1 else null end) as '青',
 count(case when color='緑' then 1 else null end) as '緑',
 count(*) as '合計'
from history
group by name with rollup
このSQLを吐いた結果が以下になる。

商品名白黒赤青緑合計
ビックリ弁当箱112127
最果てグローブ222107
無駄毛ロンガー102126
合計4363420

これで一応は完成だ。
しかし、COUNT()とSUM()はまだ調査できていない。

今回俺は、
  • 指定した値があれば1、なければnullを指定し、その数を数えるからCOUNT()
という意味でCOUNT()を使ったが、
  • 指定した値があれば1、なければ0を指定し、その数の合計値を求めるのでSUM()
という方法と同じだ。
すでに上記でうまくいっている。

これは考え方の違いなのかもしれないけど、個人的にはどうもCOUNT()で1の数を数えるより、SUM()で集計した方がよい気がしてきた。

というのも、COUNT()で数えるなら別に1じゃなくてもいいのではないかと思ったからだ。
というわけで、ためしにSQLを以下のように変更してみた。
select coalesce(name, '合計') as '商品名',
 count(case when color='白' then 'a' else null end) as '白',
 count(case when color='黒' then 'a' else null end) as '黒',
 count(case when color='赤' then 'a' else null end) as '赤',
 count(case when color='青' then 'a' else null end) as '青',
 count(case when color='緑' then 'a' else null end) as '緑',
 count(*) as '合計'
from history
group by name with rollup
条件に一致したら1ではなく'a'という文字を指定してある。そしてCOUNT()で'a'の数を数えるのだ。
当然、結果は同じになる。

しかし、もしこのSQLを初めて見る人がいたら、たぶんこう思うだろう。

「aってなに？」

と。別に画面に'a'という文字が表示されるわけでもなく、もちろん'a'という文字はどこからも要求されていない。そして表示されない且つ要求されてもいない文字を指定するのは、なんだかナンセンスだ。

しかし、1であれば、1はあくまで1という数値だ。というわけでCOUNT()で1を数えていたんだけど、1であるのであれば、つまり数値であるのであれば、COUNT()で数えるよりSUM()で足すのが正しい、と思った。

なので、COUNT()とSUM()で結果は同じだが、俺はSUM()を使ったほうがよかったと思った次第。

教えてもらってからは感覚的にCOUNT()よりSUM()が正しいと思っていたけど、ちゃんと調べるとまた別の理由もわかってきて、SQLの理解度が上がるというものだ。

というわけで、数値は数えるのではなく足した方が寝覚めよいかも。条件で1じゃなく2とか3にして、さらに凝ったこともできそうだし。


Follow:

Created by ThemeXpose