読者です 読者をやめる 読者になる 読者になる

ビズオーシャンでのインターン日記

プログラミング未経験の文系大学生がインターンシップで頑張る日記です

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()


f:id:yuka_nakayama:20170314105011p:plain




この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を含むテーブルです。


f:id:yuka_nakayama:20170314105045p:plain


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



f:id:yuka_nakayama:20170314105126p:plain







これを応用し、ダウンロードされた書式の名前と、それをダウンロードしたユーザーの職種を示す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_DLPRODUCT_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()





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