前置き
MAなどにおいて、縦持ちのデータを横に持ち直さなければならないときってありますよね。
テーブルの名前を as で定義して、同じテーブルをいくつもJOINさせる方法もありますが、もう少し簡単な手法をご紹介します。
とっても初心者向けなので、つよつよな方・・・ごめんなさい。
データセット
- user
- ユーザーTBL
- visit
- ユーザーのウェブサイト訪問記録TBL
user
| id |
|---|
| 1 |
| 2 |
| 3 |
visit
| id | user_id | view_page | created_at |
|---|---|---|---|
| 1 | 1 | A | 2019-08-01 |
| 2 | 2 | B | 2019-08-01 |
| 3 | 2 | C | 2019-08-02 |
| 4 | 1 | B | 2019-08-03 |
| 5 | 3 | B | 2019-08-04 |
| 6 | 1 | B | 2019-08-05 |
Join
select
*
from
user
left outer join
visit
on user.id = visit.user_id
サンプル
A, B, Cページに訪れた回数を数えましょう。
期待結果
| user_id | visit_A | visit_B | visit_C |
|---|---|---|---|
| 1 | 1 | 2 | 0 |
| 2 | 0 | 1 | 1 |
| 3 | 0 | 1 | 0 |
クエリ
-- postgres
select
visit.user_id
, sum( case when view_page = 'A' then 1 else 0 end ) as visit_A
, sum( case when view_page = 'B' then 1 else 0 end ) as visit_B
, sum( case when view_page = 'C' then 1 else 0 end ) as visit_C
from
user
left outer join
visit
on user.id = visit.user_id
group by
visit.user_id
prestoではちょっと変わった書き方も可能です。
postgreと同じでも出力結果は変わりませんが、個人的にはこっちのほうが見やすくて好きです。
-- presto
select
visit.user_id
, sum( if( view_page = 'A', 1, 0 ) as visit_A
, sum( if( view_page = 'B', 1, 0 ) as visit_B
, sum( if( view_page = 'C', 1, 0 ) as visit_C
from
user
left outer join
visit
on user.id = visit.user_id
group by
visit.user_id
今回はsumを使いましたが、countを使うほうがいいかもしれません。
そこまで細かくは検証していないので、気になる方がいたら、修正してもらえると嬉しいです。
なお、フラグを立てる場合にも役立つので便利ですね。
補足
max, minなどの集約関数を用いれば、もう少し難しいこともできますよ。
私も頑張ってやっていきます。