【SQL】BigQueryでWEBドメイン集計したらLEFTJOINでつまづいたってばよ！！

	


## 概要
**某データ分析会社による一流のデータサイエンティストを養成するのためのSQL研修BigQuery編**

**目的：web視聴ログからモニター別にwebサイト「TBS」、「twitter」それぞれ視聴した回数を集計
　　　BigQueryでのJOINについて**

## 使用テーブル
　私のネットの閲覧履歴をもとに作ってみました。

- (i) **Dataset.monitor_web_log**　：サンプルモニターのweb視聴ログ

|monitor|koudou_time|url|domain|web_title|
|------|----|----|----|----|
|A|1809|www\.yaoo.co.jp/|yahoo.co.jp|YAHOO!　JAPAN|
|A|1908|news.yahoo.co.jp/pickup/6245951|yahoo.co.jp|香取降板なら仮装大賞に危機?|
|A|1910|headlines.yahoo.co.jp/hl?a=20170706-00000008-tospoweb-ent|yahoo.co.jp|日テレ「仮装大賞」打ち切り危機　香取慎吾降板なら欽ちゃん出ない？|
|A|2008|ja.wikipedia.org/wiki/%E9%80%83%E3%81%92%E3%82%8B%E3%81%AF%E6%81%A5%E3%81%A0%E3%81%8C%E5%BD%B9%E3%81%AB%E7%AB%8B%E3%81%A4|wikipedia.org|Wikipedia　逃げるは恥だが役に立つ|
|B|0520|www\.tbs.co.jp/NIGEHAJI_tbs/|tbs.co.jp|TBS　逃げるは恥だが役に立つ|
|B|0812|www\.tbs.co.jp/NIGEHAJI_tbs/intro/|tbs.co.jp|TBS　逃げるは恥だが役に立つ　はじめに|
|B|1012|ja.wikipedia.org/wiki/Kiss_(%E9%9B%91%E8%AA%8C)|wikipedia.org|Wikipedia　Kiss (雑誌)|
|B|1212|www\.tbs.co.jp/drama/|tbs.co.jp|TBS　ドラマ・映画|
|C|1435|twitter.com/|twitter.com|twitter|
|C|2222|twitter.com/i/moments/882828976158322688|twitter.com|漢字クイズ！漢字好きにしか読めない?? |
|C|2309|www\.tbs.co.jp/mini_bangumi/|tbs.co.jp|TBS　ミニ番組|
|D|2313|www\.tbs.co.jp/yuukinosirusi/|tbs.co.jp|TBS　勇気のシルシ|
|D|2315|twitter.com/i/moments/882807311760322560|twitter.com|浴衣に似合う！短めの髪でもできる大人のヘアアレンジ?|
|D|2320|www\.tbs.co.jp/oni/|tbs.co.jp|TBS　渡る世間は鬼ばかり|
|E|0644|www\.tbs.co.jp/weather/|tbs.co.jp|TBS　お天気ガイド|
|E|0710|twitter.com/i/streams/category/686639687419596819|twitter.com|twitter　ニュース|
↓
カラム名
monitor: **サンプルモニター**
koudou_time: **行動時刻**
url: **URL**
domain: **ドメイン名**
web_title: **WEBのページタイトル名**


※データの見方
monitor：Aの場合、
koudou_timeが
**1809** となっているが、これは18:09にURL **www\.yahoo.co.jp/**を視聴し、
**1908** では、19:08にURL **news.yahoo.co.jp/pickup/6245951** を視聴したことを表しています。

- (ii) **Dataset.m_web_master**　：webサイトマスター

|web_num|url|web_name|
|------|----|----|
|01|www\.tbs.co.jp/|TBS |
|02|twitter.com/|twitter |
↓
カラム名
web_num: **URL番号**
url: **集計対象URL**
web_name: **webサイト名**

※データの見方
集計対象URLが2つ **www\.tbs.co.jp/** , **twitter.com/** あり、web視聴ログのURLと前方一致で集計する
例)
web視聴ログから、Eの場合
urlが **www\.tbs.co.jp/weather/** で、
**www\.tbs.co.jp/** ～と前方一致なので、webサイト「TBS」に視聴した数えて集計する。

##問題 １

- **全モニターのwebサイト「TBS」と「twitter」にそれぞれ視聴した回数を集計**

このwebサイトだけでなく、他の番組にも適用できるSQLを書くことを目標とします

>**OUTPUT形式**

|monitor|TBS|twitter|
|------|----|----|
|A|??|??|
|B|??|??|
|C|??|??|
|D|??|??|
|E|??|??|
↓
↓
↓
↓
↓




##解答例 シンプルに書くとこうなる
```sql:解答例※我ながら美しいクエリである
SELECT
  a1.monitor,
--web_nameがTBSの場合１、それ以外は０とし、その合計（視聴数）をカラムTBSに表示
  SUM (
    CASE
      WHEN b1.web_name = 'TBS' THEN 1
      ELSE 0
    END ) AS TBS,
--web_nameがtwitterの場合１、それ以外は０とし、その合計（視聴数）をカラムtwitterに表示
  SUM (
    CASE
      WHEN b1.web_name = 'twitter' THEN 1
      ELSE 0
    END ) AS twitter
FROM
--monitor_web_logのurlにweb_masterのurlを前方一致で結合
  Dataset.monitor_web_log AS a1
LEFT JOIN
  Dataset.web_master AS b1
ON
  (a1.url LIKE CONCAT(b1.url, '%'))
GROUP BY
  1
ORDER BY
  1;
```
##想定したOUTPUT
monitor|TBS|twitter|
|------|----|----|
|A|**0**|**0**|
|B|**3**|**0**|
|C|**1**|**2**|
|D|**2**|**1**|
|E|**1**|**1**|

##実際のOUTPUT　まさかのエラー

```
LEFT OUTER JOIN cannot be used without a condition that is an equality of fields from both sides of the join.
```
###BigQuery ではLIKEでのLEFT JOIN が出来ないってばよ！！
###「＝」でないと結合しないってばよ！！

**今回のNG箇所　（LIKEを使ったLEFT JOIN）**

```sql:PostgreSQLなどでは使えるLEFT　JOIN　LIKE構文

LEFT JOIN
  Dataset.web_master AS b1
ON
  (a1.url LIKE CONCAT(b1.url, '%'))
```


##INNER JOIN で試してみる
```sql:INNER　JOINでテスト
SELECT
  a1.monitor,
  SUM (
    CASE
      WHEN b1.web_name = 'TBS' THEN 1
      ELSE 0
    END ) AS TBS,
  SUM (
    CASE
      WHEN b1.web_name = 'twitter' THEN 1
      ELSE 0
    END ) AS twitter
FROM
  Dataset.monitor_web_log AS a1
INNER JOIN  --LEFT から INNERに変えてみた
  Dataset.web_master AS b1
ON
  (a1.url LIKE CONCAT(b1.url, '%'))
GROUP BY
  1
ORDER BY
  1;
```



##OUTPUT（これではNG）
monitor|TBS|twitter|
|------|----|----|
|B|**3**|**0**|
|C|**1**|**2**|
|D|**2**|**1**|
|E|**1**|**1**|
###INNER JOINではLIKE結合が出来た！しかしmonitor 'A' が表示されていない。。

##上記プログラムをサブクエリにして、monitor_logのmonitorと「＝」でLEFT JOINするってばよ！
```sql:解答例
SELECT
  DISTINCT(b1.monitor),
--INNER JOINで集計から漏れたmonitorは視聴数がNULLなので、それを0に置換
  COALESCE(c1.tbs,
    0)AS TBS,
  COALESCE(c1.twitter,
    0 ) AS twitter
FROM
  Dataset.monitor_web_log AS b1
LEFT JOIN (
  SELECT
    a1.monitor,
    SUM (
      CASE
        WHEN b1.web_name = 'TBS' THEN 1
        ELSE 0
      END ) AS TBS,
    SUM (
      CASE
        WHEN b1.web_name = 'twitter' THEN 1
        ELSE 0
      END ) AS twitter
  FROM
    Dataset.monitor_web_log AS a1
  INNER JOIN
    Dataset.web_master AS b1
  ON
    (a1.url LIKE CONCAT(b1.url, '%'))
  GROUP BY
    1
    )AS c1
USING
  (monitor)
ORDER BY
  1;
```
##OUTPUT（最終出力結果）
monitor|TBS|twitter|
|------|----|----|
|A|**0**|**0**|
|B|**3**|**0**|
|C|**1**|**2**|
|D|**2**|**1**|
|E|**1**|**1**|

###BigQueryでのLEFT JOINは「＝」でないと結合しないので注意が必要！データの下処理か、他のJOINを併用すべし！

###BigQueryでのJOINは
###LEFT JOIN「＝」のみ結合可
###INNER JOIN「＝」、「LIKE」どちらも結合可

#この記事がイイと思ったらイイね、シェアよろしくだってばよ！！

##☆今回使用した主な関数☆
- **SUM CASE**　  カラムごとに非 NULL 値の合計を返します。
- **CONCAT**　1 つ以上の value を 1 つに連結します。
- **COALESCE**　NULLを任意のデータに置換します。（BigQueryの公式HPに記載は無いが正常に作動した。）
- **LEFT JOIN**　2つのテーブルに対する LEFT OUTER JOIN の結果（または単に LEFT JOIN）は、右側のテーブルに結合のキーを満たす行がない場合でも、JOIN 句の左側のテーブルのすべての行を保持します。左外部結合を意味します。**結合条件は「＝」のみ**
- **INNER JOIN**　?INNER JOIN または単に JOIN と書き、内部結合を意味します。**結合条件は「＝」以外も可**

**【参考URL】**
https://cloud.google.com/bigquery/docs/reference/standard-sql/?hl=ja