たとえば、ゲームを例にすると、ユーザーがどのアイテムを何個持っているか、と聞かれた場合、一人ひとりのユーザーの持ち物をしらべるとなると、結局DBのダンプになってしまいます。
そういった場合、「何個もっているユーザーが何人いる」ということを調べることで、ユーザーの全体的な動向を知ることができます。
ここでは、０〜9個の人は何人、１0〜19個の人は何人、、、というように段階的に範囲を絞って、アイテムごとにMySQLから統計を採ってみたいと思います。このような統計の採り方を度数分布といいます。
対象となるテーブルは
●ユーザー
mysql> select id, name, age, created_at from user limit 5; +----+------------+------+---------------------+ | id | name | age | created_at | +----+------------+------+---------------------+ | 1 | 598d056309 | 896 | 2011-11-30 03:00:14 | | 2 | 6ab7bf0029 | 487 | 2011-11-30 03:00:14 | | 3 | c62df8dab5 | 464 | 2011-11-30 03:00:14 | | 4 | 63a9d01e41 | 609 | 2011-11-30 03:00:14 | | 5 | e5abb35d2d | 677 | 2011-11-30 03:00:15 | +----+------------+------+---------------------+ 5 rows in set (0.00 sec)
●アイテム
mysql> select id,name from item; +----+--------------------------+ | id | name | +----+--------------------------+ | 1 | やくそう | | 2 | どくけしそう | | 3 | ばんそうこう | | 4 | １００円ライター | | 5 | ビー玉 | | 6 | りんご | | 7 | けしごむ | | 8 | わゴム | +----+--------------------------+ 8 rows in set (0.00 sec)
●ユーザーの持ち物
mysql> select * from my_item limit 5; +------------+---------+---------+---------------------+ | my_item_id | item_id | user_id | created_at | +------------+---------+---------+---------------------+ | 1 | 7 | 330 | 2011-11-30 01:33:17 | | 2 | 6 | 68 | 2011-11-30 01:33:17 | | 3 | 7 | 477 | 2011-11-30 01:33:17 | | 4 | 5 | 554 | 2011-11-30 01:33:17 | | 5 | 4 | 153 | 2011-11-30 01:33:17 | +------------+---------+---------+---------------------+ 5 rows in set (0.00 sec)
だとします。
アイテムごとに各ユーザーが持っている個数を取得するには以下のようにとれます。
select item_id, user_id, count(my_item_id) from my_item group by item_id, user_id;
ここで、例えば０〜９個までを持っているユーザーは、更にhaving句を使って以下のように絞られます。
select item_id, user_id, count(my_item_id) from my_item group by item_id, user_id having count(my_item_id) >= 0 AND count(my_item_id) < 10
オンラインゲームなどは保持数の上限などを設けている場合がほとんどです、例えばアイテムは最大100個しか
持てないという制限があるとします。
ここでは、アイテムの所持数を0〜９、10〜19、、、90〜100のように区切って、段階ごとに範囲を決めて抽出するとすると、以下のSQLになります。
mysql< select z.item_id, z.name, IFNULL(t1.cnt, 0) as "0=<10", IFNULL(t2.cnt, 0) as "10=<20", IFNULL(t3.cnt, 0) as "20=<30", IFNULL(t4.cnt, 0) as "30=<40", IFNULL(t5.cnt, 0) as "40=<50" from (select id as item_id, name from item ) z LEFT OUTER JOIN ( select a.item_id,count(a.user_id) as cnt from (select item_id, user_id, count(my_item_id) from my_item group by item_id, user_id having count(my_item_id) >= 0 AND count(my_item_id) < 10) a group by a.item_id ) t1 ON t1.item_id = z.item_id LEFT OUTER JOIN( select a.item_id,count(a.user_id) as cnt from (select item_id, user_id, count(my_item_id) from my_item group by item_id, user_id having count(item_id) >= 10 AND count(item_id) < 20) a group by a.item_id ) t2 ON t2.item_id = z.item_id LEFT OUTER JOIN( select a.item_id,count(a.user_id) as cnt from (select item_id, user_id, count(my_item_id) from my_item group by item_id, user_id having count(item_id) >= 20 AND count(item_id) < 30) a group by a.item_id ) t3 ON t3.item_id = z.item_id LEFT OUTER JOIN( select a.item_id,count(a.user_id) as cnt from (select item_id, user_id, count(my_item_id) from my_item group by item_id, user_id having count(item_id) >= 30 AND count(item_id) < 40) a group by a.item_id ) t4 ON t4.item_id = z.item_id LEFT OUTER JOIN( select a.item_id, count(a.user_id) as cnt from (select item_id, user_id, count(my_item_id) from my_item group by item_id, user_id having count(item_id) >= 40 AND count(item_id) < 50) a group by a.item_id ) t5 ON t5.item_id = z.item_id; +---------+--------------------------+-------+--------+--------+--------+--------+ | item_id | name | 0=<10 | 10=<20 | 20=<30 | 30=<40 | 40=<50 | +---------+--------------------------+-------+--------+--------+--------+--------+ | 1 | やくそう | 1336 | 0 | 16 | 52 | 21 | | 2 | どくけしそう | 1346 | 0 | 13 | 60 | 16 | | 3 | ばんそうこう | 1327 | 1 | 14 | 47 | 27 | | 4 | １００円ライター | 1355 | 0 | 18 | 47 | 24 | | 5 | ビー玉 | 1368 | 0 | 14 | 52 | 24 | | 6 | りんご | 1334 | 1 | 21 | 49 | 19 | | 7 | けしごむ | 1354 | 1 | 18 | 54 | 17 | | 8 | わゴム | 1329 | 1 | 23 | 42 | 23 | +---------+--------------------------+-------+--------+--------+--------+--------+
少し冗長になってしまいましたが、もっと他にスマートなやり方があるのかも知れません。 他にいい方法も浮かばなかったのですが、とりあえず抽出することができました。
本日は以上です。
