2. スプレッドシートのCOUNT()を、SQLではどうやるか？
Updated
2021/8/8 9:02
Author
Jumpei Ikegami前回用意したBigQueryの環境を使って、SQL（SELECT文）の基礎を学んでいきましょう。この一連のチュートリアルでは、まず慣れ親しんだ（？）スプレッドシートの機能と比較しながら、対応するSQLの機能を1つずつ体験していきます。
今回は、「数を数える」機能を学びます。Google Analyticsのデータを元にしたWebサイトのアクセスログデータに対して、ページ閲覧数、セッション数、ユニークユーザー数を集計してみましょう。
前提知識
前回のチュートリアルの続きです。
1. 無料のBigQueryサンドボックスでSELECT文を実行できる環境を立ち上げよう2021/6/20 5:422021/8/8 8:52
作るもの
サンプルデータを使って、Webのアクセス解析でよく使われる集計値を、スプレッドシートとSQLの両方で算出します。
↑スプレッドシートによる集計
↑SQLによる集計
体験すること
このチュートリアルによって、以下を体験します。
- サンプルデータをスプレッドシートにインポートする
- PV数、セッション数、UU数について知る
- スプレッドシートで数のカウントをしてみる
- FROMでテーブルを指定する
- SELECTで出力する列を指定する
- COUNT()で個数をカウントする
- COUNT(DISTINCT 列名)でユニーク数をカウントする
チュートリアルスタート！
ここからは、実際に手を動かしながら読み進めていきましょう。ただ読むだけよりも、実際に体験した方が内容を深く理解することができます。
サンプルデータをCSVファイルとしてダウンロードする
このチュートリアルでは、同じサンプルデータに対して、スプレッドシートとSQLの両方を使って同じ処理を実行します。そのために、まずはBigQueryにあるサンプルデータをスプレッドシートにインポートします。
前回の記事で用意したBigQueryのサンドボックス環境を開きましょう。
クエリエディタに次のSELECT文をコピペし、実行します。このクエリは、2017年1月1日のGoogle Merchandise Storeのアクセスログを元にしたサンプルデータの一部を抽出します。（まだクエリ内容を理解する必要はありません。）
SQL
クエリ結果が下に表示されたら、[結果の保存]をクリックします。
↑結果の保存
保存先を選ぶモーダルが開くので、「CSV（ローカルファイル）」を選択します。
↑CSVファイルとしてダウンロード
これで、サンプルデータをCSVファイルとしてPCに保存することができました。
CSVファイルをスプレッドシートにインポートする
次に、スプレッドシートにCSVファイルをインポートします。
まずGoogleスプレッドシートを開きます。利用にはBigQueryと同様、Googleアカウントが必要になります。
スプレッドシートを新規作成します。適当な名前を付けたら、[ファイル > インポート]を選択します。
↑ファイル > インポート
[アップロード > デバイスのファイルを選択]をクリックします。
↑デバイスのファイルを選択
「新しいシートを挿入する」を選択し、[データをインポート]をクリックします。
↑新しいシートを挿入する
問題なくインポートに成功すると、サンプルデータが格納されたシートが新規に追加されます。わかりやすいように、シート名を「pageviews」に変更しておきましょう。
↑シート名の変更
Webのアクセスログでよく使われる集計値を知る
ここで、今までよく見ていなかったサンプルデータの中身を見ていきましょう。真剣に理解する必要はないですが、自分がどんなデータを扱っているのかわかった方が記述するSQLの理解も進みます。
前述したように、このサンプルデータはGoogle Analyticsで収集された2017年1月1日のGoogle Merchandise Storeのアクセスログを元にしたものです。本来はかなり複雑なデータになっていますが、抽出時に記述した謎のSELECT文によって、以下のカラム（列）だけが抽出されています。
visitTime（閲覧日時）
fullVisitorId（ユーザーID）
visitId（セッションID）
url（閲覧ページURL）
これらの項目名や値はGoogle Analyticsの仕様が元になっていますが、Webサイトのアクセスログはだいたい似たようなデータを持っています。
fullVisitorIdは、アクセスしたユーザーを特定するためのIDです。具体的には、Google AnalyticsによってWebブラウザのCookieに保存されたIDを元にしています。visitIdは、あるユーザーの一回の来訪を特定するためのセッションIDです。ある人が店の入り口を入ってから出るまでを、Webの世界ではセッションと呼びます。そのセッションを一意に特定するためのIDが、セッションIDです。サンプルデータの1レコード（行）は、「あるユーザーがブラウザでGoogle Merchandise Storeのあるページを開いた」というデータを表します。1人のユーザーが3ページを見たら3レコード、2人のユーザーがそれぞれ1ページずつ見たら2レコード、といった具合です。
これらデータを使うことで、Webのアクセスログ集計で使われる次のような集計値を算出することができます。
- ページビュー（PV）数
- セッション数
- ユニークユーザー（UU）数
これらを、ある期間のWebサイト全体について集計してみます。
PV数は、全てのページ閲覧を数えた値です。ユーザーの区別なくカウントされます。今回のサンプルデータでは1レコードが1PVに対応しているので、レコード数を数えればPV数になります。
セッション数は、全てのセッションを数えた値です。これもユーザーの区別なくカウントされます。1回のセッションで複数ページを見ている場合であっても、セッション数のカウントは1になります。
UU数は、期間中にアクセスしたユーザーの数を数えた値です。1人のユーザーが期間中に複数回来訪した場合であっても、UU数のカウントは1になります。
スプレッドシートでPV数、セッション数、UU数を集計してみる
サンプルデータの用意と集計すべき値の理解ができたら、まずはSQLではなくスプレッドシートで集計をしてみましょう。
シートを新しく追加し、「全体集計」という名前を付けます。
A列に、集計すべき項目名を入力します。
- PV数
- セッション数
- UU数
- 1セッション当たり平均PV数
- 1UU当たり平均セッション数
↑集計用シートを追加
「pageviews」シートにあるデータを使って、これらの集計値を計算してみます。自信がある方は、先を読まずに自分で考えて答えを出してみましょう。
↑pageviewsのデータ
「PV数」を集計するには、単に「pageviews」のレコード数をカウントします。行番号を見ればわかりますが、関数を使ってやるのであれば次のように
visitTimeの値の個数をカウントします。値の個数はどのカラムも同じなので、visitTimeではなくfullVisitorIdやvisitIdやurlをCOUNTAしても同じです。Plain Text
「セッション数」を集計するには、セッションIDであるvisitIdのユニーク数をカウントします。ユニーク数とは、重複を削除した後の数のことです。スプレッドシートには、
COUNTUNIQUE()という関数を使ってユニーク数をカウントできます。Plain Text
「UU数」を集計するには、ユーザーIDである
fullVisitorIdのユニーク数をカウントします。Plain Text
「1セッション当たり平均PV数」と「1UU当たり平均セッション数」は、集計した3つの値を組み合わせれば出すことができます。「1セッション当たり平均PV数」は「PV数 / セッション数」です。
Plain Text
「1UU当たり平均セッション数」は「セッション数 / UU数」です。
Plain Text
結果は、次の通りです。
↑集計結果
これを見ると、一度Google Merchandise Storeに来た人は1回の来訪で平均約4PVしていたことがわかります。1ページだけ見て帰る人はそこまで多くないのかもしれません。また、1UU当たりの平均セッション数は約1回です。今回のサンプルデータでは1日分のデータしか扱っていないので、「1日に2回以上以上来る人はかなり少ない」ということがわかります。
SQLでテーブルの中身をそのまま出してみる
ここからは、同じ集計値をBigQueryでSQLを使って算出してみましょう。しかしその前に、SELECT文の基本を学びます。
まず、BigQueryのクエリエディタ画面を開き、サンプルデータを抽出したときに使ったSQLを再度実行してみます。
SQL
このSQLの冒頭には、
WITH pageviews AS (...)と書かれた部分があります。ここは、今は学習に関係が無いので無視してください。この部分は、サンプルデータが格納されたga_sessions_20170101というテーブルを一時加工して、今回のチュートリアル用にpageviewsというテーブルを用意しています。皆さんは、pageviewsというテーブルだけを意識すればまずはOKです。なお、これ以降のテキストではWITH句の部分は飛ばして記述します。皆さんがBigQueryでクエリを実行するときには、上記のWITH句を付けた状態で実行してください。
WITH句を無視すると、このSQLは次の部分だけになります。
SQL
SELECT文では、FROMの後にテーブル名を指定します。データベースにはたくさんのテーブルがあるので、ここでテーブル名を指定しないとどのデータを扱っていいのかわからないというわけです。ここでは、WITH句で生成した
pageviewsテーブルを指定しています。ここで、最後のSELECT文を次のように変更して実行してみてください。
SQL
すると、出力結果に
urlが表示されなくなります。このように、SELECTの後には結果に出力する列の名前を指定します。なお、最初のように*（アスタリスク）を指定すると、元のテーブルの全ての列が出力されます。これで、テーブルと列を指定して結果を出力することができるようになりました。
SQLでPV数、セッション数、UU数を集計してみる
いよいよ、スプレッドシートで実行したのと同じ集計をSQLでやってみましょう。
- PV数
- セッション数
- UU数
- 1セッション当たり平均PV数
- 1UU当たり平均セッション数
最後のSELECT文を次のように書き換えます。
SQL
実行すると、次のようにクエリ結果が表示されます。スプレッドシートで集計した値と見比べて、同じ結果になっていることを確認してください。
↑集計結果
SELECTの後には、元のテーブルの列をそのまま指定するだけではなく、元データを集計するための式を記述したりすることができます。出力結果を表示するときの列の名前は、ASの後で指定します。SQL
テーブルにあるレコードの数を単純にカウントするには、
COUNT(*)を使います。これでPV数を出すことができます。SQL
セッション数やUU数のように、ある列のユニーク数をカウントしたい場合は、
COUNT(DISTINCT 列名)を使います。これはスプレッドシート関数のCOUNTUNIQUE()に対応します。SQL
SQLでは、関数の結果をさらに加減乗除することもできます。（スプレッドシートの式でも同様のことができますよね。）
SQL
これで、SQLでテーブルの中のデータをカウントする方法を学ぶことができました。スプレッドシートと対応させながら、SQL文をもう一度読み直してみてください。
ちなみに、BigQueryのSQLでは個数をカウントする
COUNT()だけではなく、集計で使える次のような関数が用意されています。個数ではなく数値の集計をする場合は、これらの関数を使います。SUM(): 合計値
AVG(): 平均値
MAX(): 最大値
MIN(): 最小値
演習問題
今回学んだことを定着させるために、演習問題を用意しました。自分の理解を確かめるために、ぜひやってみてください。
pageviewsテーブルを使って、次の値を集計するSQL文を書いてください。（前述したWITH句を最初に書くことを忘れないでください。）- この日に見られたURLの種類数
- 1つのURLが、平均で何PV見られているか
以上で、今回のチュートリアルは終了です。次回は、スプレッドシートで複数のデータを結合するのに使われるVLOOKUPについて、同じ処理をSQLで実行する方法について学びます。