【SQL】BigQueryでECポイントアプリ集計したらトラップカードが発動してた件

## 概要
**某データ分析会社による一流のデータサイエンティストを養成するのためのSQL研修BigQuery編**
**ECポイントアプリのデータをPostgreSQLとBigQueryで集計してみた。**
##この記事で何を知れるか
**・BigQueryでのカラム名とデータ型を数値型に変更する際の注意点**
**・例として、<font color="Blue">COUNT、SUM、CAST </font>を使ったクエリの書き方** 
##使用テーブル
**・(1)user_master　（データ型は全てvarchar 或いはSTRING）**
**インストールしたユーザー一覧。**
delete_flg は１はこのアプリを削除したユーザー

|user_id|install_date|delete_flg|
|-------|------|-------|--------|
|101|2058/4/19|0|
|102|2058/4/21|1|
|103|2058/4/23|0|
|104|2058/4/25|0|
|105|2058/4/2 |1|

**・(2)purchase_log（データ型は全てvarchar 或いはSTRING）**
このアプリでは「ポイント」を購入したのちアプリ内でグッズと交換ができる。
列productはアプリ内でのポイント変動の内容である。

|user_id|date|kazu|product|price_doller|
|-------|---------|------|--------|---------|
|101|2058/4/19|5|キャンペーン||
|101|2058/4/19|1|キャンペーン||
|101|2058/4/23|-5|グッズ交換||
|101|2058/4/23|-1|失効||
|102|2058/4/21|5|キャンペーン||
|102|2058/4/21|-5|グッズ交換||
|103|2058/4/23|5|キャンペーン||
|103|2058/4/23|1|キャンペーン||
|103|2058/4/23|1|キャンペーン||
|103|2058/4/23|100|購入|100000||
|103|2058/4/23|-30|グッズ交換||
|103|2058/4/23|-20|グッズ交換||


##問題１ アプリを削除してないユーザーは何人いるか出力せよ。

まずは**PostgreSQL**でやってみた。

**カラム名**は分かりやすく**日本語**にしてみた。


```SQL:PostgreSQL
SELECT
--ユーザー数をカウント、カラム名は　削除してないユーザー数
    COUNT (user_id) AS "削除してないユーザー数" 
FROM
  Dataset.user_master 
WHERE
--条件を 0 に指定
  delete_flg = '0'
```

**OUTPUT　でけた。恐ろしく分かりやすい。**

|削除してないユーザー数|
|--------------|
|**3**|
**《結果・考察》**
５人中３人が削除していないことが分かった。

同じクエリを**BigQuery**でやってみた結果。

**OUTPUT （エラーが出たの巻）**

```
Syntax error: Unexpected string literal "削除してないユーザー数" at [2:24]
```
**訳（たぶん）
BigQueryさん「おれ日本語しらねーから<font color="Red">半角英数字</font>使えや。クォーテーションも邪魔や。」**

だそうです。

なので、カラム名変更

```sql:BigQuery　カラムをnon_sakujo_cntに変えてみた！
SELECT
    COUNT (user_id) AS non_sakujo_cnt
FROM
  Dataset.user_master 
WHERE
  delete_flg = '0'
```

**OUTPUT　でけたー！**

|行|non_sakujo_cnt|
|-----|---------|
|**1**|**3**|

**そんなわけで、BigQueryではカラム名は<font color="Red">日本語禁止、半角英数字</font>のみ。でした。**


##問題２　アプリを削除していないユーザーが使ったポイントの合計数を出力せよ。

まずは**PostgreSQL**でやってみた。
検索条件は
・user_masterテーブルの「delete_flg」が「0」
　AND
・purchase_logテーブルの「kazu」が「0未満」。
（ただし、データ型が文字型（varchar）なので、数値型（INTEGER）に変更する。）


```sql:PostgreSQL
SELECT
    user_id    --ユーザーID
  , SUM (CAST(kazu AS INT)) AS purchase_sum  --合計数をpurchase_sumとして表示 
FROM
  Dataset.purchase_log 
WHERE
--user_masterテーブルのdelete_flgが'0'、且つ、kazuを文字型（varchar）から数値型（INT）に変更して、0未満で絞る。
  CAST(kazu AS INT) < 0 AND
  user_id IN (   
    SELECT
        user_id 
    FROM
      Dataset.user_master 
    WHERE
      delete_flg = '0'
  ) 
GROUP BY
  1 
ORDER BY
  1; 


```
**OUTPUT でけた！**

|**user_id**|**purchase_sum**|
|-----|----------|
|**101**|**-6**|
|**103**|**-50**|

**《結果・考察》**
ポイントを消費した人とその量が分かった。
消費量が多いほどアプリに積極なユーザーであると考えられる。


同じクエリを**BigQuery**で実行してみた。

**OUTPUT エラーがあわられた！**

```
Type not found: INT at [4:37]
```
**訳（てきとう）
BigQueryさん「INT？なにそれ？そんな型知りませんけど。」**

**くそぅ、略名じゃだめか、こうなったら、、、
INTを生贄に捧げて！！
正式名称INTEGERを召喚！！！**

**INTEGERで<font color="Purple">ダイレクトアタック！！！！</font>**

```sql:BigQuery　INTをINTEGERに変更
SELECT
  user_id,
  SUM (CAST(kazu AS INTEGER)) AS purchase_sum
FROM
  Dataset.purchase_log
WHERE
  CAST(kazu AS INTEGER) < 0
  AND user_id IN (
  SELECT
    user_id
  FROM
    Dataset.user_master
  WHERE
    delete_flg = '0' )
GROUP BY
  1
ORDER BY
  1;
```

**OUTPUT またもエラー**

```
Type not found: INTEGER at [14:24]
```
**訳（てきとう）
BigQueryさん「INTEGER？なにそれ？そんな型名知りませんけど。」**

なんでやねん！！
他のテーブルのスキーマ情報で確認してみる
![syuseiI.jpg](https://qiita-image-store.s3.amazonaws.com/0/340189/17917544-1803-69d0-6c2e-9ac00c4ec985.jpeg)

いや、、、普通に**INTEGER**ってかいてありますやん。。。

が、実はBigQuery特有のトラップカードが発動していたのである。
**「BigQueryではINT型にキャストする時「<font color="Red"> INT64</font> 」と書かないといけないーー！！！」**
（たとえフィールドにサ〇コショッカーがいても関係ありません。）

なので、**INTEGER**から<font color="Red">**INT64**</font>に変更。
　　<font color="Silver">てかいきなり64ってなんやねん。
　

```sql:BigQuery　INT64に変更
SELECT
  user_id,
  SUM (CAST(kazu AS INT64)) AS purchase_sum
FROM
  Dataset.purchase_log
WHERE
  CAST(kazu AS INT64) < 0
  AND user_id IN (
  SELECT
    user_id
  FROM
   Dataset.user_master
  WHERE
    delete_flg = '0' )
GROUP BY
  1
ORDER BY
  1;
```

**OUTPUT　で、でけた。。なんで64。。。**

|行|user_id|purchase_sum|
|---|------|----------|
|**1**|**101**|**-6**|
|**2**|**103**|**-50**|

##今回のBigQuery注意点まとめ
**１．カラム名は<font color="Red">日本語禁止。半角英数字</font>のみ。
２．数値型（INTEGER）にCASTする際は<font color="Red">INT64</font>と表記する。**

##☆今回使用した主な関数☆
**・COUNT** 指定したカラムのレコード数を表示 

```sql

SELECT COUNT（カラム名）FROM　テーブル名；
```
**・SUM**　指定したカラム（数値型）の合計数を表示

```sql
SELECT SUM（カラム名（数値型））AS　新表示カラム名  FROM  テーブル名；
```
**・CAST**　指定したカラムのデータ型を変更

```sql
SELECT CAST（カラム名  AS  変更したいデータ型名）AS 新表示カラム名  FROM  テーブル名；
```





##【参考】
##PostgreSQL 数値型一覧

|型名|格納サイズ|説明|範囲|
|----|--------|----|-----|
|smallint|2バイト|狭範囲の整数|-32768から+32767|
|integer、int|4バイト|典型的に使用する整数|-2147483648から+2147483647|
|bigint|8バイト|広範囲整数|-9223372036854775808から+9223372036854775807|
|decimal|可変長|ユーザ指定精度、正確|小数点前までは131072桁、小数点以降は16383桁|
|numeric|可変長|ユーザ指定精度、正確|小数点前までは131072桁、小数点以降は16383桁|
|real|4バイト|可変精度、不正確|6桁精度|
|double precision|8バイト|可変精度、不正確|15桁精度|
|smallserial|2バイト|狭範囲自動整数|1から32767|
|serial|4バイト|自動増分整数|1から214748364|
|bigserial|8バイト|広範囲自動増分整数|1から9223372036854775807|
|money|8バイト|貨幣金額|-92233720368547758.08 から +92233720368547758.07|

##BigQuery 数値型一覧

|型名|格納サイズ|説明|範囲|
|----|--------|----|-----|
|**<font color="Red">INT64</font>**|8バイト|広範囲整数|-9,223,372,036,854,775,808～9,223,372,036,854,775,807|
|NUMERIC|16バイト|小数点前３８ 桁、小数点以降９桁|-99999999999999999999999999999.999999999～99999999999999999999999999999.999999999|
|FLOAT64|8バイト|倍精度（近似）10 進値。||


**《PostgreSQL 10.5公式ドキュメントURL（数値データ型）》**
https://www.postgresql.jp/document/10/html/datatype-numeric.html
**《BigQuery公式ドキュメントURL（標準SQLデータ型）》**
https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types?hl=ja