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

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

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

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

サブクエリ

上のコード①の、この部分がサブクエリです。

f:id:yuka_nakayama:20170314140216p:plain

サブクエリ内で一度まとめたデータをもとに、全体でさらにデータをまとめていきます。

コード①では、
サブクエリ内でユーザー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

と出てくる。

(備忘録おわり)