ykamezの技術ブログ

日々学んだことをアウトプットしていきます。Ruby/Rails/グロース/分析/施策提案

BigQueryを用いたデータ分析を業務で行うために勉強したこと

概要

データ分析を業務でするようになって、SQLを書く機会が増えたのでメモ。 実際に業務に入るまでは、先輩方がものすごいスピードでクエリを書くのを見て、ビビりまくっていましたが、一度体系的に勉強したら、かなりクエリを書く力が上がった気がするので、その過程で学んだことをまとめておきます。

勉強する前

  • 簡単な構文ならかけるけど、group_byを使ったり、集計したりすることは自信がない。
  • 業務中データを出したいと思っても、かなりコストがかかりそうなので、少し躊躇してしまう。

といったレベル感で、知識がないために、施策の仮説を立てる上で、重要なデータを出すことに関して消極的になっていました。 業務では、Railsを使っているため、ActiveRecordばかり使っており、生SQLを書く機会がほとんどなかったため、SQLなんて書かず、全てActiveRecordですませたい、くらいに思っていました。

勉強したこと

しかし、ユーザの行動データは議論のベースになるので、非常に重要なので、一度体系的にSQLを勉強することにしました。 自分はこの『SQL実践入門 ──高速でわかりやすいクエリの書き方｜技術評論社』

gihyo.jp

を使って勉強しました。

以下の項目は、自分が今回新しく勉強したことです。各項目に関しての説明は詳しい記事がたくさんあるので、そちらに説明は譲りますが、自分のメモも一緒に載せておきます。

  1. SQLの実行順序
  2. group_byの挙動
  3. case文の使い方
  4. window関数

SQLの実行順番

クエリを見た目通りの順番に表示されるのではないため、はじめのうちは実行のイメージがつきづらかったのですが、一度実行順序を理解すると、見通しが良くなります。 実際は、from, join, where, group_by, aggregate, having, select, order_by, limitの順番で実行されます。

SELECTをクエリを書く際には、一番最初に書きますが、実際に実行される際は最後の方に実行されます。この実行順書を理解しておくと、

  • selectで指定した別名が、group_byで参照できない理由
  • group_byで指定していない(or 集計関数でもない)列名が、selectで使えない理由 などがわかるはずです。

参考にしたリンク:

group byの挙動

以前はいまいちgroup byに関して理解できていなかったのですが、 SQL実践入門によると、

group_byはピザの分割のようなもの

という文章を読んでから、追加で調べて理解が深まりました。 以下の記事がわかりやすかったので、参考にしてください。

SQLのGROUP BY句の処理を理解する - ぱと隊長日誌

group byの挙動を理解すると、初めの頃怒られがちだった、must appear in the GROUP BY clause or be used in an aggregate functionといったエラーに苦しめられることはなくなります。

CASE文

使い方は複数あります。 特に、区分ごとの集計は便利で、強力な武器になります。

  1. フラグをつける
  2. 階級に分ける
  3. 区分ごとの集計
    • group byなどと組み合わせられることもある。

参考: CASE式で条件分岐をSQL文に任せる - Qiita

window関数

Window関数は、集約機能を省いたgroup_byと考えることができます。

実際の使い方としては以下のどちらかになります(PARTITION or ORDER)

  1. 集約関数 OVER(PARTITION BY key_name) partition_byによって、集合をカットすることができ(カット集合のことをフレームと言います。)、それに対して、集約をかけることができるのが特徴です。

  2. 集約関数 OVER(ORDER BY key_name)

group_byと違って、集計は行わないので、行数が変わらないのがポイントです。

  • count, sumなどの通常の集約関数
  • ::RANK, ROW_NUMBERといった順序関数:: を使うことができます。
MAX(sales) over (partition by user order by year rows between 1 preceeding and 1 preceeding)

Memo: MAX(sale)としているのは、一行しか実質選択していないから、その中からレコードを取得するために便宜上maxとしている(where.firstみたいな)

between 1 preceeding and 1 preceedingは「カレントコードの一行前から一行前の範囲」つまり、一行だけを見る。 →このようにSQLの行間比較にも用いることができます。

参考: window関数を使いこなす 〜分析のためのSQL〜 - Qiita

勉強したことで変わったこと

このようにSQLを一度体系的に勉強する時間をとったことで、わからないまま既存のクエリを変更しながら使うようなことがなくなり、ゼロから抵抗なく自分でクエリを書くことができ、業務の効率が非常に上がりました。

  • 一度体系的に学ぶ。
  • 自分が何を理解していないのか理解する。
  • 業務でどのようなクエリが必要になるのかイメージしながら勉強する。

ことで、理解が深まります。

上のように体系的にSQLを勉強し直したことに加えて、研修としてデータ分析の機会があったのも非常にタメになりました。

speakerdeck.com

まだ、BigQuery固有の便利な関数なども色々あるので引き続き勉強していきたいです！

  • EXTRACT
  • COUNT
  • GENERATE_DATE_ARRAY