share facebook facebook twitter menu hatena pocket slack

2011.12.06 TUE

統計ってなんじゃ?(MySQLで度数分布)

三浦 悟

WRITTEN BY 三浦 悟

プロジェクトの運用を行っている際に、ユーザーのサービス利用傾向を知るためにデータの統計を依頼されることがあります。

例として、ゲームの場合ですとユーザーがどのアイテムを何個所持しているかを調べて欲しいと依頼された場合、一人ひとりのユーザーの持ち物を調べるとなると、結局DBのダンプになってしまいます。
このような場合は、「何個もっているユーザーが何人いる」ということを調べることで、ユーザーの全体的な動向を知ることができます。

ここでは、0~9個の人は何人、10~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 | 100円ライター         |
|  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 | 100円ライター         |  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)監修のもと掲載しています。
元記事は、こちら

三浦 悟

三浦 悟

高円寺在住のなんじゃ系男子 またの名をmemorycraftといいます。 炭水化物大好き 日々の「なんじゃ?」を記事にしてます。

cloudpack

cloudpackは、Amazon EC2やAmazon S3をはじめとするAWSの各種プロダクトを利用する際の、導入・設計から運用保守を含んだフルマネージドのサービスを提供し、バックアップや24時間365日の監視/障害対応、技術的な問い合わせに対するサポートなどを行っております。
AWS上のインフラ構築およびAWSを活用したシステム開発など、案件のご相談はcloudpack.jpよりご連絡ください。