3. スプレッドシートのVLOOKUPをSQLではどうやるか?

Updated
2021/8/8 9:07
Author
Jumpei IkegamiJumpei Ikegami
BigQueryのサンドボックス環境を使って、SQL(SELECT文)の基礎を学んでいきましょう。この一連のチュートリアルでは、まず慣れ親しんだ(?)スプレッドシートの機能と比較しながら、対応するSQLの機能を1つずつ体験していきます。
今回は、「複数のテーブルを結合する」機能を学びます。

前提知識

次のチュートリアルの続きです。
2. スプレッドシートのCOUNT()を、SQLではどうやるか?2021/6/20 5:552021/8/8 9:02
 

作るもの

サンプルデータを使って、複数テーブルの結合をスプレッドシートとSQLの両方でやってみます。
具体的には、スプレッドシートでいうVLOOKUPのように、Webサイトのアクセスログデータに対して、アクセス時の端末情報やブラウザ情報を結合してみましょう。
notion image
↑2つのテーブルを結合

体験すること

このチュートリアルによって、以下を体験します。
  • スプレッドシートでVLOOKUPをしてみる
  • INNER JOINで2つのテーブルを結合する
  • SELECT文で扱うテーブルに一時的な別名を付ける

チュートリアルスタート!

ここからは、実際に手を動かしながら読み進めていきましょう。ただ読むだけよりも、実際に体験した方が内容を深く理解することができます。

デバイス情報データをスプレッドシートにアップロードする

前回の記事では、pageviewsという名前のアクセスログデータをBigQueryから抽出してスプレッドシートにアップロードしました。今回は、端末情報とブラウザ情報が格納されたdevicesというデータを同様の手順でスプレッドシートにサンプルデータとしてアップロードします。
前回の記事で用意したBigQueryのサンドボックス環境を開きましょう。
クエリエディタに次のSELECT文をコピペし、実行します。このクエリは、2017年1月1日のGoogle Merchandise Storeのアクセスログについて、各セッションのアクセス時の端末情報とブラウザ情報を抽出します。(まだクエリ内容を理解する必要はありません。)
SQL
クエリ結果が下に表示されたら、[結果の保存 > CSV(ローカルファイル)]をクリックしCSVファイルをダウンロードします。
notion image
↑devicesデータをCSVダウンロード
次に、スプレッドシートにCSVファイルをインポートします。前回のスプレッドシートと同じものを使いましょう。
[ファイル > インポート > アップロード > デバイスのファイルを選択]からCSVファイルを選択します。前回同様、「新しいシートを挿入する」を選択し、[データをインポート]をクリックします。
notion image
↑新しいシートを挿入
問題なくインポートに成功すると、サンプルデータが格納されたシートが新規に追加されます。わかりやすいように、シート名を「devices」に変更しておきましょう。
notion image
↑シート名の変更

アクセスログデータに利用ブラウザ情報をVLOOKUPで紐付ける

pageviewsdevicesには、共通のカラムがあります。それは、セッションのIDを示すvisitIdです。このvisitIdを手がかりにすることで、pageviewsのそれぞれのレコードについて、アクセス時の端末情報やブラウザ情報を紐付けることができます。ここでは、ブラウザ情報をpageviewsに紐付けて表示してみましょう。
それをスプレッドシートの世界で実現するには、VLOOKUPという関数を使います。「pageviews」シートを開き、E列に「devices」シートのbrowser情報を表示してみましょう。できそうな人は、この先を見ずにやってみてください。
notion image
↑browser情報を追加
さて、VLOOKUP関数を使うには次の指定が必要になります。
  • 検索に使うキー
  • 検索する範囲
  • 範囲内の何列目を出力するか
  • 範囲内のデータの検索キーは並び替え済みか
今回の場合、たとえばE2に書くべき関数呼び出し式は次の通りです。
Plain Text
要は「devices」シートにある表の範囲とその中の出力すべき列を指定し、指定したキーに合致するレコードを検索させればいいというわけです。
なお本来はブラウザ毎のアクセス割合を計算したくなるところですが、今回はまだ扱いません。次々回くらいで説明します。

SQLのJOINでテーブルを結合する

同じことを、SQLでやってみます。SQLの場合、VLOOKUPという関数はありません。変わりに、JOINと呼ばれる方法を使います。
BigQueryのコンソールを開き、次のSELECT文を実行してみましょう。
SQL
前回説明したように、WITH pageviews AS (...)という記述でpageviewsテーブルを用意しています。同様に、devices AS (...)という記述で、先ほどスプレッドシートにアップロードしたのと同じ端末・ブラウザ情報を含むdevicesテーブルを準備しています。今は、この部分は気にせず「pageviewsテーブルとdevicesテーブルの2つがあるんだな」と思っておいてください。
現時点で、最後のSELECT文は単純にpageviewsテーブルをそのまま出力しているだけです。試しに実行してみても、browserというカラムは含まれていません。
notion image
↑ここにbrowserを追加する
複数のテーブルを結合するには、最後のSELECT文を次のように書き換えて実行します。
SQL
すると、browserという列を追加で出力することができます。
notion image
↑devicesテーブルを結合しbrowser情報を含めて出力
INNER JOINは、FROMの後で記述します。INNER JOINの直後で、結合するもう1つのテーブル名を指定します。また、ONの後に2つテーブルのそれぞれどのカラムを紐付けのキーにするのかを等式で指定します。
SQL
「テーブルAの〇〇カラム」という指定をしたい場合は、 テーブルA.〇〇 のようにドットでつなぎます。pageviewsテーブルのvisitIdであれば、pageviews.visitIdといった具合です。スプレッドシートのVLOOKUPでは列の番号で検索キーを指定しましたが、SQLではカラム名で指定していることに注意してください。(カラム名で指定できた方が読みやすいですよね。)
2つのテーブルを結合した後は、それらを1つにまとめた仮想的なテーブルが一時的に生成されます。SELECTの直後の出力列の指定では、この仮想的なテーブルの各列を選ぶことができます。列の名前の先頭に テーブル名. が付与されていることに注意してください。列名にテーブル名が入ることで、visitIdのように両テーブルで同じ名前のカラムがあっても区別できるようになります。
notion image
↑JOINした後の仮想的なテーブルのイメージ
わかりやすく言えば、FROMINNER JOINの組み合わせで用意した1つの仮想的なテーブルに対して、通常通りSELECT文を実行すればいいわけです。先ほど実行したSELECT文を再掲するので、改めて中身を理解してみてください。
SQL
なお、ここで説明した仮想的なテーブルは文字通り「仮想的」なもので、SELECT文が実行された後はきれいさっぱり無くなってしまいます。SELECT文はSQLの中でも「データベースの中身に影響を与えない」という特徴があったことを思い出してください。
ちなみに、テーブル名が長くて指定が煩雑な場合は、FROMINNER JOINのテーブル名指定の後にASを使うことで、それぞれのテーブルに別名を一時的に付けることもできます。
SQL
ご覧のように、ONSELECTの列名の指定に登場するテーブル名にも、別名を使うことができます。長い名前のテーブルを扱うときはSQLが読みにくくなるので、ASを使って積極的に別名を付けましょう。

演習問題

今回学んだことを定着させるために、演習問題を用意しました。自分の理解を確かめるために、ぜひやってみてください。
pageviewsテーブルとdevicesテーブルを結合し次の列だけを全件出力するSQL文を書いてください。(前述したWITH句を最初に書くことを忘れないでください。)
  1. deviceCategory
  1. visitTime
以上で、今回のチュートリアルは終了です。次回は、スプレッドシートで複数のデータを結合するのに使われるVLOOKUPについて、同じ処理をSQLで実行する方法について学びます。
 

次のチュートリアル

4. スプレッドシートのフィルタ機能をSQLではどうやるか?2021/6/20 5:552021/8/8 9:11