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()
少しはコーディングに慣れてきた気もしますが
まだまだ覚えることがたくさんあるので
めげずにやっていきたいと思います。
vol.3 サブクエリ、正規表現、JOIN句
こんにちは
2月20日のやったことを書いていきます。
この日は、Google Cloud DataLabを使ってデータ分析の方法を教わりました。
書くコードは前回に引き続きSQLです。
前回はBigQueryのQuery Tableにコードを書いていましたが、
複数の文章を書くことができないため、
Google Cloud DataLabを使うことになりました。
今日は
サブクエリ
正規表現
JOIN句
をやりました。
まず実際のコードです。
コード①
DL_COUNT = """
(SELECT
product_id,
count(uid) AS count
FROM
(SELECT
uid,
integer(
REGEXP_EXTRACT(url, r'/doc/download/complete/([0-9]+)/')
) AS product_id,
FROM
[project-id:oceanus_prod.oceanus_v3_bizocean_20170217]
WHERE
url CONTAINS "https://www.bizocean.jp/doc/download/complete/"
GROUP BY
uid,
product_id)
GROUP BY
product_id
ORDER BY
count DESC)
"""
bq.Query(DL_COUNT).results()コード②
bq.Query(
"""
SELECT
product.product_id AS product_id,
product.name,
dl_count.count AS count
FROM
{DL_COUNT} AS dl_count
JOIN [project-id:bizocean.product] AS product
ON dl_count.product_id = product.product_id
ORDER BY
count DESC
""".format(DL_COUNT = DL_COUNT)).results()
サブクエリ
上のコード①の、この部分がサブクエリです。
サブクエリ内で一度まとめたデータをもとに、全体でさらにデータをまとめていきます。
コード①では、
サブクエリ内でユーザーID(uid)と
そのユーザーがダウンロードした書式のID(product_id)をグループ化しました。
正規表現
正規表現もコード①で使っていて、サブクエリ内の
REGEXP_EXTRACT(url, r'/doc/download/complete/([0-9]+)/')
この部分です。
書式ダウンロード完了時のURLには必ず「doc/download/complete/[書式ID(数字)]」が含まれるので、
「doc/download/complete/[書式ID(数字)]が含まれるURLの
書式IDの部分を表示しました。
コード①では、
サブクエリと正規表現を使って
ダウンロードされた書式のIDとそのダウンロード数を多い順に表示しました。
多い順に10個並べるとこうなります。
product_id count
1 100,532 53
2 104,566 48
3 102,415 45
4 102,372 43
5 100,115 41
6 102,334 38
7 104,478 33
8 103,104 32
9 519,931 31
10 518,138 31
JOIN句
これはコード②で使っています。
簡単にいうとデータテーブルとデータテーブルをくっつけるもので、
上のコードでは
JOIN [project-id:bizocean.product] AS product
ON dl_count.product_id = product.product_id
別々のデータテーブルにある書式ID(product_id)(本質的に同じもの)を
JOIN句を使ってくっつけました。
dl_countというデータテーブルでは
ダウンロードされた書式のIDと そのダウンロード数、
productというデータテーブルでは
ダウンロードされた書式のID その書式の名前 が表示されているので、
共通項の書式IDでくっつけて
書式ID、書式の名前、ダウンロード数
を出しました。
product_id product_name count
1 100,532 請求書003 シンプルな請求書 53
2 104,566 （契約書雛形）業務委託契約書 48
3 102,415 御見積書 45
4 102,372 金銭貸借契約書（無利子・一括弁済・無担保）（借用書） 43
5 100,115 委任状01 41
6 102,334 工事請負契約書 38
7 104,478 事業計画書 33
8 103,104 書類送付のご案内-シンプル書類送付状 32
9 519,931 職務経歴書006 31
10 518,138 勤怠管理表001 31
まだごく簡単なことしかできませんが、
今日で行数がかなり増えた感じがします。
行数が増えるとカンマを忘れてしまったり
スペルミスをしてしまったりします。
そういったことに気を付けるのはもちろんですが、
エラーが出たときに間違いを見つけやすいように
きれいに整頓して書いたり、
こまめに動かしてみることが大切だと知りました。
＊備忘録
SELECTやWHEREなどの句の使い方にはある程度慣れてきましたが
GROUP BYがまだイマイチ分からなかったので
ここで備忘のために復習します。
「GROUP BY」句
…同じ値を持つ行をグループ化する。
たとえば、
ユーザーID:123456の人がAという書式を5回ダウンロードした
ユーザーID:234567の人がBという書式を1回ダウンロードした
ユーザーID:345678の人がCという書式を2回ダウンロードした
このとき、グループ化しないと
123456,A
123456,A
123456,A
234567,B
345678,C
345678,C
となるが、
重複する行を1つのグループとして、
123456,A
234567,B
345678,C
と出てくる。
（備忘録おわり）
vol.2 SQL初歩的なデータ抽出とグラフ作成
こんにちは
2月14日（インターン２日目）は、
Google BigQueryにある実際のデータを使ってSQLをぽちぽちしてみました。
SQLは、データの管理や操作などができるデータベース言語として広く普及しています。
現時点ではまだすごく簡単なコードですが、とりあえずどこに何を書けばいいのかがよく分からず、何度もエラーを出してました…。
うーーん、、どこに何を書けばいいのか、というのが本当に難しかったです。
この日は「SELECT」、「FROM」、「WHERE」、「GROUP BY」、「ORDER BY」、「LIMIT」の句だけを使ったのですが、句を並べるのにも順番があって、
句の中も、カンマで区切るとかカッコで括るとか、ややこしいな～という。
それで数時間奮闘していたのですが、なんとなく、なんとなく出来てきた気がします。
エラーの頻度はかなり減りました。
できたのは、
①男女別の年齢ごとの会員数
コード…
SELECT gender, age, count(gender) as count FROM [project-id:bizocean.member] WHERE gender ="male" GROUP BY gender, age ORDER BY count desc LIMIT 1000
女性の年齢別会員数はmaleをfemaleにしました。
②業種別会員数
コード…
SELECT industry , count( industry ) as count FROM [project-id:bizocean.member] GROUP BY industry ORDER BY count desc LIMIT 1000
③一番会員が多い業種の職種別会員数
コード…
SELECT industry, job_spec1, count( job_spec1) as count FROM [project-id:bizocean.member] WHERE industry CONTAINS "小売" GROUP BY industry, job_spec1 ORDER BY count desc LIMIT 1000
でした。
（グラフタイトルなどがちゃんとしていないのは目をつぶってください…）
②で小売・卸売・商社の会員が一番多いと分かったので、
③でその会員の職種を見てみました。
なぜ小売・卸売・商社が多いのか、なぜその中でも営業職や経営者が多いのか、
その日はもう時間がなくできませんでしたが
次回のインターンで、その会員の方たちがどういう書式をダウンロードしているのかなどを見て分析を進めていきたいと思いました。
