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

ITの隊長のブログ

ITの隊長のブログです。いや、まだ隊長と呼べるほどには至っていないけど、日々がんばります。CakePHPとPlayFrameworkを使って仕事しています。最近はAngular2をさわりはじめたお(^ω^ = ^ω^)

【SQL】日付でGroupingして、カテゴリっぽいテーブルを紐付けようとしたけど紐付けきれないときのCrossJoin

SQL MySQL

スポンサードリンク

(タイトルがナンノコッチャ!!)

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

  • 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 その他

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

要件

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

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

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

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

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

試してみたことその1

んで、私はこのような結果を出したいため、こんな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

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

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

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

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

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

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

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

試してみたことその2

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

先ほどの問題は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

これらのデータをサブクエリで用意してあげて、あとはymcategory_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

これでおk。これで冒頭のデータを取得することができました。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

これでよし!

雑感

ちなみに、まだ不足がある。

実はその月に投稿されていない場合は、その月の日付がとれない。(例:1月に投稿がない => 201501のデータが存在しない)

この場合は解決策が見つからなかったので、その穴埋めはPHPで対応しました。

先程も書いたが、そもそもデータが存在しない場合は紐付けることもグルーピングもできないので、これはしょうがないのかなと思う。

でも、日付だけのテーブルを作成したらできないこともないんだよねー。とても面倒だけど。。。

仮想的にサブクエリでデータフォーマットを用意してそいつをCrossJoinとかできたら完璧だろうけど。んな方法あるのかね?

引き続き勉強します。φ(..)メモメモ

達人に学ぶ SQL徹底指南書 (CodeZine BOOKS)

達人に学ぶ SQL徹底指南書 (CodeZine BOOKS)