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

概要

某データ分析会社による一流のデータサイエンティストを養成するのためのSQL研修BigQuery編
ECポイントアプリのデータをPostgreSQLとBigQueryで集計してみた。

この記事で何を知れるか

・BigQueryでのカラム名とデータ型を数値型に変更する際の注意点
・例として、COUNT、SUM、CAST を使ったクエリの書き方

使用テーブル

・(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でやってみた。

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

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さん「おれ日本語しらねーから半角英数字使えや。クォーテーションも邪魔や。」

だそうです。

なので、カラム名変更

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ではカラム名は日本語禁止、半角英数字のみ。でした。

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

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

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でダイレクトアタック！！！！

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？なにそれ？そんな型名知りませんけど。」

なんでやねん！！
他のテーブルのスキーマ情報で確認してみる

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

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

なので、INTEGERからINT64に変更。
　　てかいきなり64ってなんやねん。
　

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注意点まとめ

１．カラム名は日本語禁止。半角英数字のみ。
２．数値型（INTEGER）にCASTする際はINT64と表記する。

☆今回使用した主な関数☆

・COUNT 指定したカラムのレコード数を表示

SELECT COUNT（カラム名）FROM　テーブル名；

・SUM　指定したカラム（数値型）の合計数を表示

SELECT SUM（カラム名（数値型））AS　新表示カラム名  FROM  テーブル名；

・CAST　指定したカラムのデータ型を変更

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 数値型一覧

型名	格納サイズ	説明	範囲
INT64	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