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