vol.4 JOIN句の練習とpandas dataframe

こんにちは
今日は3月2日と3月7日にやった内容を書いていきたいと思います。

まずJOIN句の使い方の練習です。

初めてJOIN句を使ったのは2月20日でしたが、
ぼんやりとしか使い方が分からなかったため練習しました。

JOINの練習

JOINでは、FROM句のあとにくっつけたいテーブルの名前と、
どの項目でくっつけるのかを書きます。

たとえば

①

USER_DL="""(
SELECT
dl.uid,
integer(
REGEXP_EXTRACT(url, r'/doc/download/complete/([0-9]+)/')
) as product_id,
job_spec1 as job_spec
FROM [project_id: oceanus_prod.oceanus_v3_bizocean_20170225] as dl
JOIN {USER_JOBSPEC} as uj
　　　 on dl.uid=uj.uid
WHERE
　　　 url CONTAINS "ht tps://www.bizocean.jp/doc/download/complete/"
GROUP BY
dl.uid,
product_id,
job_spec
)""".format(USER_JOBSPEC=USER_JOBSPEC)
bq.Query(USER_DL).results()

ここでは予め「USER_JOBSPEC」というSQLを作っています。

USER_JOBSPEC="""(
SELECT
string(member_id) as uid,
job_spec1
FROM
[project_id: bizocean.member]
GROUP BY
uid,
job_spec1

)"""
bq.Query(USER_JOBSPEC).results()

このSQL文を上記のUSER_DLのSQL文に組み込むこともできますが、

そうすると長ったらしくなって読みづらくなったり、
エラーの確認が難しくなったりするので、

とりあえずUSER_JOBSPECという文字列で{}を埋めておきます。

そして、

文字列USER_JOBSPECは前に作ったSQLUSER_JOBSPECのことだよー

ということで、format(USER_JOBSPEC=USER_JOBSPEC)で置き換えます。

format()で置き換えないと、
「USER_JOBSPECとかいう文字列は一体何なんだ？」と
エラーが返ってきます。

ちなみに、FROM句にある
[project_id: oceanus_prod.oceanus_v3_bizocean_20170225] は
ユーザーIDとダウンロードした書式のIDを含むテーブルです。

両テーブルの共通項であるuid（ユーザーID）でくっつけて、
ユーザーID、ダウンロードした書式ID、職種　　を示す表を作りました。

これを応用し、ダウンロードされた書式の名前と、それをダウンロードしたユーザーの職種を示すSQLを作りました。

②

SPEC_DL="""(
SELECT
name,
job_spec
FROM
{USER_DL} as ud
JOIN {PRODUCT_NAME} as pn
on ud.product_id=pn.product_id
GROUP BY
ud.product_id,
job_spec,
name
)""".format(USER_DL=USER_DL,PRODUCT_NAME=PRODUCT_NAME)
bq.Query(SPEC_DL).results()

USER_DLは①で作ったSQLです。
ここでも予めPRODUCT_NAMEというSQLを作っています。

USER_DLとPRODUCT_NAMEではproduct_idが共通して含まれているので、
ON ud.product_id=pn.product_idで繋げます。

JOINしてできたのが以下のものです。

dataframeについて

Pythonでデータを扱うために、
SQLで書いたものをBigQueryでテーブルとして保存し、
dataframeの形式にします。

テーブルで保存しなくてもスキャン出来なくはないですが、
データが大きくなるとスキャン料金が高く付いてしまうので、
一度データを必要なものに絞り込んでからSQL文を書いていきます。

EMPLOYEE_SCALE_NAME_DF=bq.Query(
"""
SELECT
 product_id,
 employee_scale,
 count(product_id) as count
FROM
 [prject_id: work.user_dl_log20160901_20170306]
WHERE
 product_id < 600000
GROUP BY
  employee_scale,
  product_id
ORDER BY
 product_id

 """
).to_dataframe()

少しはコーディングに慣れてきた気もしますが
まだまだ覚えることがたくさんあるので
めげずにやっていきたいと思います。