非エンジニアのためのデジタルリテラシー/チュートリアル/BigQueryで学ぶSQLデータ分析入門/4. スプレッドシートのフィルタ機能をSQLではどうやるか？
チュートリアルリファレンス 用語集
チュートリアルリファレンス 用語集

4. スプレッドシートのフィルタ機能をSQLではどうやるか？

Updated
2021/8/8 9:11
Author
Jumpei IkegamiJumpei Ikegami
BigQueryのサンドボックス環境を使って、SQL（SELECT文）の基礎を学んでいきましょう。この一連のチュートリアルでは、まず慣れ親しんだ（？）スプレッドシートの機能と比較しながら、対応するSQLの機能を1つずつ体験していきます。今回は、スプレッドシートのフィルタで実現される「絞り込み」と「並び替え」について、SQLでどうやるのかを学びます。

前提知識

ここまでの全てのチュートリアルを実施済みであることを前提としています。
3. スプレッドシートのVLOOKUPをSQLではどうやるか？2021/6/20 5:552021/8/8 9:07
‍

作るもの

サンプルデータを使って、絞り込みや並び替えをスプレッドシートとSQLの両方でやってみます。
↑絞り込みと並び替え
‍

体験すること

このチュートリアルによって、以下を体験します。
  • スプレッドシートでフィルタを使ってみる
  • WHEREで対象のレコードを絞り込む
  • ORDER BYで出力順序を指定する
‍

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

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

スプレッドシートのフィルタを有効にする

今回はpageviewsのデータから、「fullVisitorIdが '4971486503438638837' のアクセスログを、visitTimeの新しい順に表示する」という条件でデータ抽出してみましょう。
まずは、例によってスプレッドシートでやってみます。前回までの記事で使っていたスプレッドシートを開きましょう。
pageviewsシートを開き、全てのデータ領域を選択します。その状態で「フィルタを作成」をクリックします。
↑フィルタを作成
無事にフィルタが作成できたら、fullVisitorIdによる絞り込みをしてみましょう。fullVisitorIdの右のフィルタアイコンをクリックします。まずは「クリア」を押し、全ての値のチェックが外します。次に検索ボックスに「4971486503438638837」と入力します。候補が1つだけ表示されるので、それをクリックしてその値だけに結果を絞り込みます。「OK」を押すと、無事にfullVisitorIdによる絞り込みが完了します。
↑fullVisitorIdによる絞り込み
次に、visitTimeが新しい順でレコードを並び替えてみましょう。今度はvisitTimeの右のフィルターアイコンをクリックします。「日付が新しい順」とは言い換えると「日付が大きい順」です。大きい順を示す「Z→Aで並び替え」をクリックし「OK」を選びます。
↑visitTimeによる並び替え
これで、「fullVisitorIdが '4971486503438638837' のアクセスログを、visitTimeの新しい順に表示する」ということが実現できました。
ちなみに、絞り込みで使ったfullVisitorIdは、ユーザーを特定するためのIDでした。絞り込まれた結果のデータを見ると、「4971486503438638837」さんは1日の間で5回もサイトを訪れていることがわかります。また、なぜかトップページ（/home）を何度も読み込んでいたようです。
‍

WHEREでレコードの絞り込み条件を指定する

同じことを、SQLでやってみます。まず、fullVisitorIdによる絞り込みからです。
BigQueryのコンソールを開き、次のSELECT文を実行してみましょう。
SQL
例によって、WITH pageviews AS (...)という記述はpageviewsテーブルを用意しているだけです。実際に気にすべきSELECT文は、最後の一行だけです。
現時点で、最後のSELECT文は単純にpageviewsテーブルをそのまま出力しているだけです。試しに実行してみても、fullVisitorIdによる絞り込みはまだされていません。
↑ここから絞り込みをかける
SQLで絞り込みを実行するには、WHEREを使います。fullVisitorIdが '4971486503438638837' のレコードだけに絞り込むには、最後のSELECT文を次のように書き換えて実行します。
SQL
すると、無事にfullVisitorIdでレコードの絞り込みができました。
↑fullVisitorIdによる絞り込み後
WHEREの直後には、結果がTRUE（真）かFALSE（偽）になるような条件式を記述します。
SQL
数値データや日付データの場合、不等式を使って比較することもできます。
SQL
また条件式は、ANDやORで複数をつなげることもできます。
SQL
SQL
NOTでTRUEとFALSEを反転させることができます
SQL
‍

ORDER BYでレコードの出力順序を指定する

さらに、「visitTimeの新しい順に表示する」という条件を実現するために、出力結果の順序を指定してみましょう。
SQLで出力順序を指定するには、ORDER BYを使います。visitTimeの新しい順に出力するには、SELECT文の最後にORDER BY指定を次のように追加します。
SQL
実行すると、visitTimeが大きい順に出力されていることがわかります。
なお、このように大きいものから小さいものへ降りるような順番のことを「降順」と呼びます。逆に小さいものから大きいものへ昇る順番は「昇順」です。
ORDER BYでは、次のように指定します。
SQL
並び順指定で指定できるのは次の2つです
  • ASC: 昇順（ascending order）
  • DESC: 降順（descending order）
SQL
SQL
ちなみに、並び順指定を省略した場合のデフォルトはASCです。
‍

演習問題

今回学んだことを定着させるために、演習問題を用意しました。自分の理解を確かめるために、ぜひやってみてください。
‍
1 . 次のSQLを修正し、「ブラウザが 'Edge' または 'Internet Explorer' であるようなpageview情報を、urlの昇順で全て出力する」ようにしてみましょう。出力したい列は次の通りです。
  • visitTime
  • fullVisitorId
  • visitId
  • url
  • browser
SQL
（ヒント）
  • 前回学んだINNER JOINが必要です
  • 記述する順番は、INNER JOIN → WHERE → ORDER BYです
以上で、今回のチュートリアルは終了です。次回は、ユーザー毎にPV数などを集計し特徴的なユーザーを見つけるための「グループ別集計」について学びます。グループ別集計ができると、一気に価値ある情報を集計できるようになります。