Go to Qiita Advent Calendar 2024 Top
LoginSignup
8
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@74kenshiro(kenshiro ono)

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

Last updated at Posted at 2019-01-31

概要

某データ分析会社による一流のデータサイエンティストを養成するのための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？なにそれ？そんな型名知りませんけど。」

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

いや、、、普通に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

8
3
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
8
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?