プロジェクトの運用を行っている際に、ユーザーのサービス利用傾向を知るためにデータの統計を依頼されることがあります。
例として、ゲームの場合ですとユーザーがどのアイテムを何個所持しているかを調べて欲しいと依頼された場合、一人ひとりのユーザーの持ち物を調べるとなると、結局DBのダンプになってしまいます。
このような場合は、「何個もっているユーザーが何人いる」ということを調べることで、ユーザーの全体的な動向を知ることができます。
ここでは、0～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;
ここで、例えば0～9個までを持っているユーザーは、更に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)
オンラインゲーム等では、アイテムの保持数に上限などを設けている場合がほとんどです、例えば、アイテムは最大100個しか持てないという制限があるとします。
ここでは、アイテムの所持数を0～9、10～19、・・・、90～100のように区切って、段階ごとに範囲を決めて抽出するとすると、以下のSQLになります。
mysqlz.item_id, z.name, IFNULL(t1.cnt, 0) as "0=IFNULL(t2.cnt, 0) as "10=IFNULL(t3.cnt, 0) as "20=IFNULL(t4.cnt, 0) as "30=IFNULL(t5.cnt, 0) as "40=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) ) 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) ) 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) ) 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) ) 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) ) t5 ON t5.item_id = z.item_id; +---------+--------------------------+-------+--------+--------+--------+--------+ | item_id | name | 0=+---------+--------------------------+-------+--------+--------+--------+--------+ | 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 | +---------+--------------------------+-------+--------+--------+--------+--------+
少し冗長になってしまいましたが、もっと他にスマートなやり方があるのかも知れません。
他にいい方法も浮かばなかったのですが、とりあえず抽出することができました。
こちらの記事はなかの人（memorycraft）監修のもと掲載しています。
元記事は、こちら