• このエントリーをはてなブックマークに追加
  • このエントリーをはてなブックマークに追加

新機能を追加した時ユーザーの動きがどう変わったか知りたいものです。しかし、データを見る管理画面をつくっていなかったり、あったとしても定点で追っていないデータを見たいことがあります。

そこでエンジニアにお願いするのですが本業があるので後回しになりますし、施策の度に見る箇所が変わることがあるので毎回毎回お願いするのも結構な時間となります。

そんなときにMySQLについてある程度知っていると改善のスピードを上げることができるのではないでしょうか。今日はアプリで使いそうな簡単なものを、写真アプリを例にまとめてみました。(長くなったので基礎的な部分は別途書きます)

※テーブル名やカラム名は適当です。まず自分のサービスのものをshow tablesやshow columns from table名で確認してみてください。

 

▶売上

select sum(money) from history where created between 
'2014-02-01 0:00:00' and '2014-02-01 23:59:59';
 
+---------------+
| sum(money)  | 
+---------------+
|      15,000 | 
+---------------+

sum関数は、数値の合計。where句は条件を指定し、条件が複数ある場合はandでつなぎます。例は2/1に課金された金額の合計です。

 

▶投稿数

select count(*) from photo where created between 
'2014-02-01 0:00:00' and '2014-02-01 23:59:59';
 
+-------------+
| count(*)  | 
+-------------+
|     1,000 | 
+-------------+

count関数は、レコード数の足し上げ。例は2/1に投稿された写真枚数です。

 

▶デイリー投稿数

select count(*),date(created) as date from photo group by date 
having date= '2014-02-01' and date ='2014-02-03';
 
+-----------+------------+
| count(*)  | date       | 
+-----------+------------+
|     1,000 | 2014-02-01 | 
|     1,500 | 2014-02-02 | 
|     1,200 | 2014-02-03 | 
+-----------+------------+

group by句は、データをまとめることができます。さらにhaving句で、group by句に対して条件を設定します。例は日にちごとの写真投稿枚数です。

 

▶イベント参加人数

select count(distinct user_id)  from photo where tag_id = 1;
 
+-------------------------+
| count(distinct user_id) |
+-------------------------+
|                  10,000 | 
+-------------------------+

count関数は、()内のレコード数の足し上げます。また、distinctは重複データを取り除きます。例は特定のイベントタグをつけて投稿したユーザー数を表しています。

 

▶各フィルタの使用数

select fliter_id,count(*) from photo where filter_id in (1,3,7) 
group by filter_id;
 
+-----------+------------+
| filter_id | count(*)   | 
+-----------+------------+
|         1 |      3,500 | 
|         3 |      2,700 | 
|         7 |      4,200 | 
+-----------+------------+

where句で条件が複数ある場合はin演算子を使います。例は、フィルタIDの1、3、7の使用数です。

 

▶投稿ランキング

select user_id,count(*) as count from photo group by user_id 
order by count desc limit 3;
 
+----------+---------+
| user_id  | count   | 
+----------+---------+
|     3100 |     500 | 
|     1200 |     300 | 
|     3500 |     200 | 
+----------+---------+

order by句は並び替え。ascで昇順、descで降順に。例は写真投稿枚数のランキングで、ヘビーユーザーを見つけることができます。

 

▶いいね数ランキング

select photo_id,count(*) as count from like group by photo_id 
order by count desc limit 3;
 
+-----------+---------+
| photo_id  | count   | 
+-----------+---------+
|       300 |     900 | 
|       200 |     800 | 
|       500 |     700 | 
+-----------+---------+

例は写真についたいいね数のランキングで、人気写真を見つけることができます。
さらにその写真の情報を知りたい場合は、

select photo_id,user_id,title,count(*) as count from like 
join photo on like.photo_id = photo.id group by photo_id order by count desc limit 3;
 
+-----------+---------+------------+---------+
| photo_id  | user_id | title     | count   |
+-----------+---------+------------+---------+
|       300 |      45 | 初投稿!    |     900 | 
|       200 |     120 | ランチ@渋谷 |     800 | 
|       500 |      30 | 映画見た    |     700 | 
+-----------+---------+------------+---------+

join句を使うことで、異なるテーブル同士を結合することができます。

 

▶SNS連携数

select count(*),date(created) as date from social 
where service = 'Twitter' group by date 
having date= '2014-02-01' and date ='2014-02-03';
 
+-----------+------------+
| count(*)  | date       | 
+-----------+------------+
|     1,000 | 2014-02-01 | 
|     1,500 | 2014-02-02 | 
|     1,200 | 2014-02-03 | 
+-----------+------------+

‘文字列’で文字列を指定することができます。例は期間内のTwitterシェア数です。

 

▶タイトル検索

select photo_id,title from photo where title like '%初投稿%';
 
+-----------+--------------+
| photo_id  | title      | 
+-----------+--------------+
|      1000 | 初投稿!    | 
|      2400 | 初投稿してみた | 
+-----------+--------------+

like ‘%文字列%’で文字列が含まれるものを検索できます。

 

施策を考える時や効果検証の際に、上記を組み合わせて色々試してみてください。