BigQueryではじめるSQL #03

検索はSELECT文。

SQLによる検索の基本構文はSELECT文、集計と正規化の基本構文はGROUP BY句になりますので、こちらを中心に見ていきましょう。

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

SELECT文は検索対象のテーブル（Table）と列（Column）の名前を指定する形で記述します。BigQueryの場合、テーブル名はデータセット名と合わせて記述し、その間をドット「.」で結びます。また、列名は複数の指定が可能で、その間をカンマ「,」で区切ります。構文と例文は以下のようになります。

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

ちなみにビュー（View）とは、テーブルの一部を抽出あるいは複数のテーブルを結合するなどして作成した実体をもたない仮想的なテーブルのことで、SQL文中のサブクエリ（Subquery）の出力がそれにあたります。サブクエリについては次回説明します。

また、検索対象となる行（Record）の条件や数を指定する場合は、それぞれWHERE句とLIMIT句を用います。構文と例文は以下のとおりです。

SELECT 列名, 列名, ... FROM テーブル名（ビュー名） WHERE 条件式 LIMIT 行数

なお、WHERE句には条件式を指定しますが、検索対象となる列名と、演算子や定数などを組み合わせて表現します。この例文でいうと「UnitPrice >= 10」がそれにあたります。そして、この例文全体の意味は、テーブル「ECData」で、列「UnitPrice」が10以上の行を100行だけ抽出し、列「InvoiceNo」「StockCode」「UnitPrice」を出力する、となります。青い［実行］ボタンをクリックして、出力結果を確認してみましょう。

集計はGROUP BY句。

集計と正規化の基本構文であるGROUP BY句は、SELECT文と組み合わせて使います。内容の説明は後述するとして、構文と例文はこのようになります。

SELECT 列名, 集計関数 AS 列名, ... FROM テーブル名（ビュー名） GROUP BY 列名

集計（Aggregation）とは、データ内のあるグループ（Group）について、頻度・合計値・平均値などを計算することをいいます。グループの軸には性別や年代などさまざまあり、この軸のことをディメンション（Dimension）ともいいます。

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

上記の例文全体の意味は、テーブル「ECData」で、グループの軸「CustomerID」ごとに、列「CustomerID」と、行数の集計結果である「InvoiceCount」を出力する、となります。こちらも、青い［実行］ボタンをクリックして、出力結果を確認してみましょう。

なお、例文中のCOUNTは集計関数（Aggregate Function）と呼ばれ、行数を集計するものになります。集計関数は他に、合計値を集計するSUMや、平均値を集計するAVGなどがあります。

また、例文中のASはAS句といい、ここでは集計関数の出力列に名前をつける役割を担います。つまり「COUNT(*)」の出力列に「InvoiceCount」という名前をつけることを意味します。AS句はこの他にも、SELECT文で出力されたテーブルに名前をつける機能もあります。こちらは次回のWITH句やJOIN句のところで説明します。

ちなみに、ここでもう少し踏み込んで、各顧客IDごとの顧客ランクを集計する方法を見てみます。顧客ランクの集計条件を、購買回数が300回以上を「A」、100回以上を「B」、それ未満を「C」と定義すると、例文は以下のようになります。なお、例文中のCASEはCASE句という条件分岐の基本構文のひとつで、分岐が多い場合に使います。条件分岐には他にIF句があり、こちらは分岐がひとつの場合に使います。

上記の例文全体の意味は、テーブル「ECData」で、グループの軸「CustomerID」ごとに、列「CustomerID」と、顧客ランクの集計結果（集計条件は、購買回数が300回以上を「A」、100回以上を「B」、それ未満を「C」とする）である「CustomerRank」を出力する、となります。こちらも、青い［実行］ボタンをクリックして、出力結果を確認してみましょう。

正規化もGROUP BY句。

GROUP BY句は、集計の他に正規化の役割を持ちます。正規化（Normalization）とはデータの重複を排除することです。

練習用データ「E-Commerce Data」においては、StockCode（商品コード）とUnitPrice（商品単価）が重複しているはずです。なぜなら商品コードとは商品管理の最小単位であるため、商品単価が一意に定義されているはずだからです。すなわち、商品コードと商品単価の1対1の対応表を別テーブルで保持しておけば、購買履歴のテーブルとして保持する必要があるのは商品コードだけでよいことになります。

では、商品コードと商品単価の1対1の対応表を取り出して正規化してみましょう。例文はこのようになります。

上記の例文全体の意味は、テーブル「ECData」で、グループの軸「StockCode」ごとに、列「StockCode」と、列「UnitPrice」の任意の値を列名「UnitPrice」として出力する、となります。こちらも、青い［実行］ボタンをクリックして、出力結果を確認してみましょう。

なお、例文中のANY_VALUEも集計関数のひとつで、グループ内の指定した列の任意の値を取り出すものであり、データの重複を正規化する場合によく用います。

また、このように正規化された対応表データをマスタデータ（Master Data）といい、商品コードをキーとしたものを商品マスタ、顧客IDをキーとしたものを顧客マスタといいます。逆に、購買履歴などの発生事象の時系列データをトランザクションデータ（Transaction Data）といいます。

次回は「BigQueryではじめるSQL」最終回、非正規化の基本構文であるJOIN句を中心に見ていきます。