【MySQL】 caseとlikeでgroup byする


例えば下記のようなアクセスするたびにuser_idとuser_agentを記録するaccessテーブルがあったとする

access

+------+---------+------------------+
| id   | user_id | user_agent       |
+------+---------+------------------+
|    1 |       1 | android4.3_hogeA |
|    2 |       2 | android4.3_hogeB |
|    3 |       1 | android4.3_hogeA |
|    4 |       3 | android4.2_hogeA |
|    5 |       2 | android4.3_hogeB |
|    6 |       1 | android4.3_hogeA |
|    7 |       3 | android4.2_hogeA |
+------+---------+------------------+

ここからuser_idとuser_agentの重複を省いたアクセス数をselectしようと思います

まずはuser_idでグループ化して重複を省いてcountを取るなら

mysql> select user_id, count(distinct user_id) count from access group by user_id;

+---------+-------+
| user_id | count |
+---------+-------+
|       1 |     1 |
|       2 |     1 |
|       3 |     1 |
+---------+-------+

続いてuser_agentでグループ化してcountを取るなら

mysql> select user_agent, count(*) count from access group by user_agent;

+------------------+-------+
| user_agent       | count |
+------------------+-------+
| android4.2_hogeA |     2 |
| android4.3_hogeA |     3 |
| android4.3_hogeB |     2 |
+------------------+-------+

ここでandroidのOSバージョンが違うだけの「android4.2_hogeA」「android4.3_hogeA」は
同じ機種として判別したいと思い試行錯誤した結果こんな感じ

mysql>
select user_agent, count(*) count from access
group by
 case
  when user_agent like “%hogeA%” then “hogeA”
  when user_agent like “%hogeB%” then “hogeB”
 end

+------------------+-------+
| user_agent       | count |
+------------------+-------+
| android4.3_hogeA |     5 |
| android4.3_hogeB |     2 |
+------------------+-------+

これだとandroid4.2が含まれていないように見えるのでuser_agentもcaseで変更するとこんな感じ

mysql>
select
 case
  when user_agent like “%hogeA%” then “hogeA”
  when user_agent like “%hogeB%” then “hogeB”
 end user_agent, count(*) count from access
group by
 case
  when user_agent like “%hogeA%” then “hogeA”
  when user_agent like “%hogeB%” then “hogeB”
 end

+------------+-------+
| user_agent | count |
+------------+-------+
| hogeA      |     5 |
| hogeB      |     2 |
+------------+-------+

んで、さらにcount数をuser_idで絞ってみる。
mysql>
select
 case
  when user_agent like “%hogeA%” then “hogeA”
  when user_agent like “%hogeB%” then “hogeB”
 end user_agent, count(distinct user_id) count from access
group by
 case
  when user_agent like “%hogeA%” then “hogeA”
  when user_agent like “%hogeB%” then “hogeB”
 end

+------------+-------+
| user_agent | count |
+------------+-------+
| hogeA      |     2 |
| hogeB      |     1 |
+------------+-------+

「hogeA」や「hogeB」は固定文字列になってしまうけど
一応思い描いていた結果を出力する事ができました。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です