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

でした。

（グラフタイトルなどがちゃんとしていないのは目をつぶってください…）

②で小売・卸売・商社の会員が一番多いと分かったので、

③でその会員の職種を見てみました。

なぜ小売・卸売・商社が多いのか、なぜその中でも営業職や経営者が多いのか、

その日はもう時間がなくできませんでしたが

次回のインターンで、その会員の方たちがどういう書式をダウンロードしているのかなどを見て分析を進めていきたいと思いました。

vol.1 初日

はじめまして

このブログでは、

プログラミング未経験の文系大学生が

インターンシップを通して成長していく過程を伝えていきたいと思います。

自己紹介

私は都内の大学に通っている女性で、現在２年生です。

上記の通り、プログラミング経験はありません。

高校生の時から就職は衣料品メーカーを考えていて、商品開発をしてみたいけどデザインできないし、うーん、、と思いながら大学に進学しました。

大学では経営学科に属していますが、統計学系の講義をいくつか受けたり、他大学の知り合いが統計学をやっていたりして、データ分析の存在を知りました。

それで、データ分析ができれば商品開発に携われる！来年の春から始まる就活までにある程度のプログラミングスキルを身に着けたい！と思い、

ビズオーシャンのエンジニアリング部でインターン生として雇っていただくことになりました。

 

株式会社ビズオーシャンの紹介

株式会社ビズオーシャンは、もともとは株式会社ミロク情報サービスという財務や会計システムなどのサービスを提供する会社の一部署でした。

が、経営責任の明確化や投資効率の最大化などを目的として2016年４月に完全に分社化しました。

ビズオーシャンが運営している同名のサイト「bizocean」では、事業計画書、契約書、スケジュール管理表など、主にビジネスシーンで活用できるテンプレートを会員に無料で提供しています。

2017年2月現在、テンプレートは20,000点以上ありますが、約200万人の会員にメールマガジンを送信することで、無料提供を可能にしています。

ビズオーシャンのサイトでは、会員登録のときに

勤めている会社の業態、勤務地、年齢なども登録するのですが、

こういう情報から、いつ、どんな人が、どんなテンプレをダウンロードしているのか、など細かく分析できるようになれたらなー思います。

今朝初めてオフィスに来たのですが、

雰囲気がわりとラフな感じで、みなさん優しそうな方ばかりなので、

色々と教えてもらいながら頑張っていこうと思います。