BigQueryではじめるSQL #04

サブクエリはWITH句。

前回は検索の基本構文であるSELECT文と、集計と正規化の基本構文であるGROUP BY句について説明しましたが、今回は非正規化の基本構文であるJOIN句を中心に見ていきましょう。

練習用データ「E-Commerce Data」をBigQueryで操作します。なお、BigQueryと練習用データの準備は「BigQueryではじめるSQL #01」を参照してください。

非正規化（Denormalization）とはデータの重複を許容することです。データが重複すると、データの追加・更新・削除における処理が冗長化するため、メンテナンス効率を重視する場合はなるべく排除しますが、処理速度を重視する場合はそれを犠牲にして、あえて非正規化を行います。

練習用データ「E-Commerce Data」は、とあるECサイトの購買履歴データで、各レコードには「CustomerID」（顧客ID）が紐づいているため、各顧客IDをグループとみなし、それぞれの購買回数に基づいた顧客ランクの集計が可能です。

その顧客IDごとの顧客ランクを、購買履歴と合わせて参照したい場合は、購買履歴の各レコードに、該当する顧客IDの顧客ランクを保持しておくと便利です。すなわち非正規化を行うということです。この非正規化を以下の手順で進めます。

1. 購買履歴データから、顧客IDごとの顧客ランクを集計する。
2. 購買履歴データの各レコードに、上記1で集計した顧客IDごとの顧客ランクの列を結合する。

このように、前のクエリの出力をもとに後のクエリを行うこと、あるいはその「前のクエリ」のことをサブクエリ（Subquery）といいます。サブクエリの記述方法には、後のクエリのFROM句に前のクエリを記述する方法もありますが、構文が視覚的に煩雑になるため、それを回避したい場合にWITH句を用います。WITH句を用いたサブクエリの構文は以下のようになります。

WITH ビュー名 AS ( サブクエリ ) クエリ

ちなみに、サブクエリの出力は実体を持たない仮想的なテーブルですが、それをビュー（View）といいます。また、構文中のASはAS句といい、ここではサブクエリが出力したビューに名前をつける役割を担います。

前回の「BigQueryではじめるSQL #03」で行った顧客IDごとの顧客ランクの集計をサブクエリとして、後のクエリに利用できるようWITH句を用いて以下の例文のように記述します。

列結合による非正規化はJOIN句。

それでは、購買履歴データの各レコードに、上記で集計した顧客IDごとの顧客ランクの列を結合して、非正規化を行います。このように列方向に結合することを列結合といい、列結合による非正規化にはJOIN句を用います。構文は以下のとおりです。

SELECT 列名, 列名, … FROM テーブル名（ビュー名） AS 略称 JOIN テーブル名（ビュー名） AS 略称 ON 結合条件式

さきほどのサブクエリと組み合わせると、例文は以下のようになります。

上記の例文全体の意味は、テーブル「ECData」（略称「ED」）と顧客ランクの集計結果であるビュー「CRank」（略称「CR」）を、列「CustomerID」が一致する場合に列結合し、列「InvoiceNo」「StockCode」「UnitPrice」「CustomerID」「CustomerRank」を出力する、となります。青い［実行］ボタンをクリックして、出力結果を確認してみましょう。

なお、例文中のASはAS句といい、ここではテーブルやビューに略称をつける役割を担います。略称は必須ではありませんが、つけておくと便利です。というのも、列結合ではテーブル間で列名が重複することが多く、その場合はどのテーブルの列かを「テーブル名.列名」の形で明記する必要があるのですが、テーブル名が長いとSQL文が冗長となり、可読性が損なわれるからです。この例文では「ED.CustomerID」がそれにあたります。

また、列結合には内部結合と外部結合があります。内部結合は結合条件に合致するレコードが双方にあるものだけを残す結合方式で、INNER JOINを用います。ちなみに、単にJOINと記述するとINNER JOINと同じ意味になります。つまり上記例文のJOINは内部結合となります。

一方で、外部結合は結合条件に合致するものが双方になくても残す結合方式で、OUTER JOINを用いますが、左右どちらも残す場合はFULL OUTER JOIN、左のみ残す場合はLEFT OUTER JOIN、右のみ残す場合はRIGHT OUTER JOINと、区別して指定する必要があります。

ビッグデータ分析に便利な構文。

複数のテーブルを列方向に結合することを列結合というのに対し、行方向に結合することを行結合といい、UNION句を用います。なお行結合では、結合するテーブルの列の構成が一致していることが前提となります。構文は以下のとおりです。

SELECT 列名, 列名, … FROM テーブル名（ビュー名） UNION SELECT 列名, 列名, … FROM テーブル名（ビュー名） 

ちなみに、上記のように単にUNIONと記述すると、結合するテーブル間で重複するレコードは削除され、ユニークなものだけが残ります。重複を許容したい場合はUNION ALLと記述します。

行結合は、日時で分割して蓄積や集計したレコードを統合する場合などで利用されます。すなわち、ビッグデータ分析には便利な機能です。なお、大量のレコードを日時などで分割して管理することをパーティショニングといいます。

この他にも、ビッグデータ分析では集計関数＋OVER句をよく用います。これは、通常の集計処理をGROUP BY句を使った正規化状態で行うのに対し、非正規化状態のまま、特定のグループごとに集計処理を行う方法です。そのグループをウィンドウ（Window）といい、PARTITION BY句で指定します。なお、この場合の集計関数は、分析関数やウィンドウ関数とも呼ばれます。構文は以下のとおりです。

SELECT 集計関数 OVER( PARTITION BY 列名 ) FROM テーブル名（ビュー名）

これを利用すれば、購買履歴などの時系列データの集計処理を行う場合に、サブクエリでGROUP BY句を使って集計し、その結果をJOIN句を使って時系列データに列結合する、という煩雑な処理をする必要がなくなり、とても便利です。

なお、時系列データの分析については、別の記事にて詳しく説明する予定なので、UNION句やOVER句の具体的な使い方や例文の紹介はその機会に譲りたいと思います。

また、この「BigQueryではじめるSQL」で触れたもの以外にも、SQLには多くの便利な構文・関数・演算子があります。とりわけBigQueryで利用可能なものについては、GCPのBigQuery公式リファレンスサイトに詳しいため、こちらをご参照されることをお勧めします。