Skip to content

Instantly share code, notes, and snippets.

@tagomoris

tagomoris/BigQuery_Analytics_reading_03_tagomoris.md

Last active August 29, 2015 14:06
Show Gist options
  • Save tagomoris/65a19a2767f2988e6ce2 to your computer and use it in GitHub Desktop.
Save tagomoris/65a19a2767f2988e6ce2 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
BigQuery_Analytics_reading_03_tagomoris.md

Recipes

ざっと見るよ！

Pivot

テーブルをピボットあるいは転置するのはレポーティングや可視化で頻出なのでやっとこうね。普通SQLでは複数フィールドでのgroupingで実現する。 が、2次元のみを扱い、特にそのうちの1次元がごく少数の候補のみによって構成される場合、その候補すべてをカラムに展開してしまうのが便利な場合があるよ。

たとえば長い順に100単語をシェイクスピアの戯曲から見付けたい場合。各々の作品中で何度ずつ出てきたかもいっしょにカウントする。

SELECT word, corpus, corpus_total
FROM(
  SELECT
    word,
    LENGTH(word) AS word_len,
    corpus,
    SUM(word_count) AS corpus_total
  FROM [publicdata:samples.shakespeare]
  WHERE LENGTH(word) > 10
  GROUP BY word, word_len, corpus
) -- あれ、ここに alias いらないの？
ORDER BY word_len DESC
LIMIT 100

これはまだpivotしてない。これからやる。

もし特定の作品にだけ注目したい場合、それらのカウントを取るためにそれぞれ専用のカラムを作って出力すればよい。

SELECT
  word,
  SUM(IF(corpus = 'kinglear', corpus_total, 0)) AS kinglear,
  SUM(IF(corpus = 'tempest', corpus_total, 0)) AS tempest,
  SUM(IF(corpus = 'macbeth', corpus_total, 0)) AS macbeth,
  SUM(corpus_total) AS [total] -- このエスケープ、標準SQL？
FROM (
  SELECT
    word,
    LENGTH(word) AS word_len,
    corpus,
    SUM(word_count) AS corpus_total
  FROM [publicdata:samples.shakespeare]
  WHERE LENGTH(word) > 10
  GROUP BY word, word_len, corpus
)
GROUP BY word, word_len
ORDER BY word_len DESC
LIMIT 100

まあ長い単語ってだいたい作品別にユニークなやつだしあんまり使われないから作品別カラムはいっぱい 0 になるんだけどね :-)

多くのケースでは、ピボットすべき値というのは前もっては分からない。さっきの例はテキトーにやったけど、現実的にはもうちょいいい方法がある。たとえば、こんな感じでカラムに使うべき上位3件を特定できる。これはBQのクエリに差し込むためのprojection句生成クエリ。

-- "SUM(IF(c = '" + corpus + "', corpus_total, 0)" ')' がひとつ足りない
SELECT CONCAT(
         CONCAT(
           "SUM(IF(corpus = '",
           corpus
         ),
         "', corpus_total, 0)"
       )
FROM (
  SELECT corpus, SUM(word_count) total -- ASがない？
  FROM [publicdata:samples.shakespeare]
  GROUP BY corpus
  ORDER BY total DESC
  LIMIT 3
)

結果

Row	f0_	 
1	SUM(IF(corpus = 'hamlet', corpus_total, 0)	 
2	SUM(IF(corpus = 'kingrichardiii', corpus_total, 0)	 
3	SUM(IF(corpus = 'coriolanus', corpus_total, 0)

カラム数が多くなると書くのにもアレげだけど、どうせみんな自動生成するからいいんじゃないかな。まあ実際にはBigQueryのクエリでカラム名のフォーマッティングまでやらせないだろうけど、まあこれは最終的なクエリがどうなるかを示す例ってことで。

Cohort Analysis

Cohortってなに？

Cohort analysis is a subset of behavioral analytics that takes the data from a given eCommerce platform, web application, or online game and rather than looking at all users as one unit, it breaks them into related groups for analysis.
http://en.wikipedia.org/wiki/Cohort_Analysis

コーホート（cohort）とは、共通した因子を持ち、観察対象となる集団のこと。人口学においては同年（または同期間）に出生した集団を意味する。疫学においてはコホート研究において用いられる母集団を指し、コホートと呼ばれることが多い。
http://ja.wikipedia.org/wiki/%E3%82%B3%E3%83%BC%E3%83%9B%E3%83%BC%E3%83%88

本にもどって

ビッグデータから何か意味のある情報を取り出そうとしたとき、コーホートに注目するのは割と普通。コーホートってのは特定の因子をもつ層のことで、その特定の因子ってのはたとえばこういうやつ:

  • phone(スマホ？)で特定のアプリケーションにアクセスしたユーザ
  • 特定の項目を編集したことのあるWikipediaコントリビュータ
  • ひとつ以上の郵便番号に該当する都市

ほとんどのケースでは、あるエンティティ(ユーザなど？)が特定のコーホートに属しているかはひとつのレコードを調べただけではわからない。のでどうやるかの例を出してみるよ。

TIP

この章ではexplicitなフィールド参照を GROUP BY および ORDER BY で使ってる。そのほうがわかりやすいし。でも古いSQL標準では番号も使えて、BQでもサポートしている。以下言い訳。

なぜした。

もどって

集合に属するかどうかみたいなのの検定には SOME() と EVERY() という集合関数が使えるよ! SOME って要するに array.any?(&:func) で EVERY は array.all?(&:func) ですね。

SELECT
  bush_all, bush_some, obama_all, obama_some,
  COUNT(1) AS num,
  AVG(edits) AS avg_edits
FROM (
  SELECT
    contributor_id,
    EVERY(bush_edit) AS bush_all,
    SOME(bush_edit) AS bush_some,
    EVERY(obama_edit) AS obama_all,
    SOME(obama_edit) AS obama_some,
    COUNT(1) AS edits
  FROM (
    SELECT
      contributor_id,
      (LOWER(title) = 'george w. bush') AS bush_edit,
      (LOWER(title) = 'barrack obama') AS bush_edit
    FROM [publicdata:samples.wikipedia]
  )
  GROUP EACH BY 1 -- GROUP BY contributor_id
  HAVING bush_all OR bush_some OR obama_all OR obama_some
)
GROUP BY 1, 2, 3, 4
ORDER BY 1, 2, 3, 4

出力イメージ(ORDER BY を無視している、本当は f,t,f,t が先頭に来るはず、末尾は f,f,f,t)

bush_all, bush_some, obama_all, obama_some, num, avg_edits
    TRUE,      TRUE,     FALSE,      FALSE,   2,    100000
   FALSE,      TRUE,     FALSE,      FALSE,  10,        50
   FALSE,      TRUE,     FALSE,       TRUE,  30,         1
   FALSE,     FALSE,     FALSE,       TRUE,   5,        30
   FALSE,     FALSE,      TRUE,       TRUE,   2,      3000

私見: これ一番内側のクエリで WHERE LOWER(title) IN ('george w. bush', 'barrack obama') すれば 外の謎のHAVINGいらないんじゃね？

GROUP BY の中に GROUP EACH BY があるのが典型的なコーホート分析(だそうです)。オバマについての記事「も」編集したことがあるか、オバマについての記事「しか」編集したことがないから EVERY と SOME から分かる。このクエリは1回で複数の数値を出せる。たとえばユニークユーザ数とか、一人あたりの編集回数の平均値とか。

もっとヤバい例、同じクエリを書く方法で、初めて編集した記事にもとづいてコーホートを算出してみる。

SELECT
  IF(bush_some AND obama_some, first_bush < first_obama, NULL) AS bush_earlier,
  bush_some,
  obama_some,
  COUNT(1) AS num,
  AVG(edits) AS avg_edits
FROM (
  SELECT
    contributor_id,
    MIN(IF(bush_edit, ts, 9999999999999)) AS first_bush,
    SOME(bush_edit) AS bush_some,
    MIN(IF(obama_edit, ts, 9999999999999)) AS first_obama,
    SOME(obama_edit) AS obama_some,
    COUNT(1) AS edits
  FROM (
    SELECT
      contributor_id,
      timestamp AS ts,
      (LOWER(title) = 'george w. bush') AS bush_edit,
      (LOWER(title) = 'barrack obama') AS bush_edit
    FROM [publicdata:samples.wikipedia]
  )
  WHERE bush_edit OR obama_edit
  GROUP EACH BY 1
)
GROUP BY 1, 2, 3
ORDER BY 1, 2, 3

キモはネストの中の GROUP EACH BY だ!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment