（タイトルがナンノコッチャ！！）

文字だけじゃわからないはずなので、テーブルを用意します。

posts

id	post_date	category_id
1	2015-01-01	1
2	2015-02-12	2
3	2015-01-01	1
4	2015-02-12	2
5	2015-01-01	1
6	2015-04-12	2
7	2015-05-01	1
8	2015-06-15	3

categories

id	name
1	プログラミング
2	インフラ
3	ハードウェア
4	データベース
5	その他

こんな感じで２つのテーブルがあるとする。

要件

やりたいこととして、postsを月ごとにカテゴリでグルーピングしてカウントしたい。

SQLでやるなら、２つのテーブルをJoinしたあとに、gropu by posts.post_date, categories.nameでいけそうだよね？

しかし、もうひとつとして、categoriesテーブルに登録されているデータはすべて表示したい。こういう結果にしたいのだ。

ym	category_name	count
201501	プログラミング	1
201501	インフラ	1
201501	ハードウェア	NULL
201501	データベース	NULL
201501	その他	2

見たとおり、ハードウェアとデータベースのcountがNULLになっているが、これはその月にそのカテゴリが紐付けられた投稿がないからだ。その場合はこのようにして出したい。

試してみたことその１

んで、私はこのような結果を出したいため、こんなSQLを書いてみた。

SELECT 
    DATE_FORMAT(posts.post_date, '%Y%m') AS ym,
    categories.name AS category_name,
    COUNT(posts.id) AS count
FROM
    categories
        LEFT OUTER JOIN
    posts ON (categories.id = posts.category_id)
GROUP BY
    DATE_FORMAT(posts.post_date, '%Y%m'),
    categories.name

まぁ知識浅いからこうなるよね。。。単純に２つのテーブルをJoinして、グルーピングしているだけですね。

お気づきだろうけど、これじゃダメですよね。これの結果はNULLが出てこない。

ym	category_name	count
201501	プログラミング	1
201501	インフラ	1
201501	その他	2

これだと、postsで紐付けられていないcategoriesを出すことができない。当たり前っちゃー当たり前な話で、存在していないデータではjoinすることはできない。

あれ？じゃあなんのためのleft outer join？って、思った人もいるかもしれないけど、なぜ取れていないのか俺もよくわかっていない（^ω^ゞ

恐らく、unknownだがなんだかしらない値のせいじゃないかな。

ということで、これではうまく行きません。。。さーてどうしましょうか。

試してみたことその２

最近サブクエリと、Cross Joinを覚えました。この２つを使って解決しましょう。

先ほどの問題はJoinするときに紐付けるデータがないかも知れないことが問題でした。それなら先にそのようなデータを用意しておけばいい。

イメージはこんな感じ.

CROSS_DATE

ym	category_name	category_id
201501	プログラミング	1
201501	インフラ	2
201501	ハードウェア	3
201501	データベース	4
201501	その他	5

COUNT_BY_MONTH

ym	count	category_id
201501	1	1
201501	1	2
201502	3	3
201501	2	1
201505	2	2

これらのデータをサブクエリで用意してあげて、あとはymとcategory_idでJoinしてあげれば良い。実際のSQLは下記.

SELECT 
  CROSS_DATE.ym,
  CROSS_DATE.category_name,
  COUNT_BY_MONTH.count
FROM
  (SELECT 
    DATE_FORMAT(posts.post_date, "%Y%m") as ym, 
    categories.name as category_name,
    categories.id as category_id
  FROM
    posts
  CROSS JOIN categories
  GROUP BY 
    DATE_FORMAT(posts.post_date, '%Y%m'),
    categories.name) CROSS_DATE
    LEFT OUTER JOIN
  (SELECT 
    DATE_FORMAT(posts.post_date, "%Y%m") as ym, 
    count(posts.post_date) as count,
    category_id
  FROM
    posts
  GROUP BY 
    DATE_FORMAT(posts.post_date, '%Y%m')
  ) COUNT_BY_MONTH ON (CROSS_DATE.ym = COUNT_BY_MONTH.ym
    AND CROSS_DATE.category_id = COUNT_BY_MONTH.category_id)
ORDER BY CROSS_DATE.ym

これでおｋ。これで冒頭のデータを取得することができました。cross joinとサブクエリ便利！

おまけ

先ほどの結果はSQLだといいと思うが、PHPで取得するとちと問題がありました。

<?php

array(
  'CROSS_DATE' => array(
    'ym' => '201501', 
  )
  // ~ 省略 ~
  'COUNT_BY_MONTH' => array(
      // 連想配列のkey 'count' が見つからない！
   )
);

なんと！ PHPではNULLはkeyも消してしまうらしい。。。これは困った。

なので、NULLを0に変換してもらうよう、先ほどのSQLにCASEを追加しました。

SELECT 
  CROSS.ym,
  CROSS.category_name,
  CASE
    WHEN COUNT_BY_MONTH.count IS NULL THEN 0
    ELSE COUNT_BY_MONTH.count
  END AS count
FROM
  (SELECT 
    DATE_FORMAT(posts.post_date, "%Y%m") as ym, 
    categories.name as category_name,
    categories.id as category_id
  FROM
    posts
  CROSS JOIN categories
  GROUP BY 
    DATE_FORMAT(posts.post_date, '%Y%m') as ym,
    categories.name) CROSS
    LEFT OUTER JOIN
  (SELECT 
    DATE_FORMAT(posts.post_date, "%Y%m") as ym, 
    count(posts.post_date) as count,
    category_id
  FROM
    posts
  WHERE
  GROUP BY 
    DATE_FORMAT(posts.post_date, '%Y%m') COUNT_BY_MONTH ON (CROSS.ym = COUNT_BY_MONTH.ym
    AND CROSS.category_id = COUNT_BY_MONTH.category_id)
ORDER BY CROSS.ym