BigQuery-SQL超入門（後編）～SQL構文の書き方～

BigQueryで分析を行う時に必須となるSQLによるデータ加工の超入門です！前編では、GCPに登録し、データ抽出からレポート作成までの流れを紹介しました。後編はカフェの売上データをBigQueryで分析するSQL構文を紹介します。

1. カフェの売上データの分析例

カフェの店長になったつもりで、以下の項目で売上データを分析します。

• 商品名だけを表示（重複を除く）
• ユーザーIDを降順に並べて3件分を表示
• 名前に[す]が含まれる商品
• 500円以上購入した人
• 商品ごとの売上金額
• 7/10 12:00~7/12 11:00の間に購入した人
• 食品,飲料,アルコールで分類した集計
• データを結合して、顧客ごとの購入金額

2. データセットとテーブルの準備

BigQueryにデータセット[store]を作り、[sales]テーブル、[customers]テーブル、[order]テーブルを作成します。テーブルの作成方法は、下記のCSVファイルを3枚作成し、BigQueryにアップロードします。詳しくは前編をご覧ください。

3. 項目ごとのデータ加工方法

商品名だけを表示（重複を除く）

A）は全ての商品名、B）は重複を除いた商品名を表示します。

ユーザーIDを降順に並べて3件分を表示

膨大なデータを抽出する際は、「LIMIT」で表示件数を指定します。BigQueryは従量課金制のため、クエリ実行前に画面内のデータ消費量を確認すると安心です。また、並び順については、降順は「DESC」、昇順は「ASC」です。昇順はデフォルトのため省略が可能です。

名前に[す]が含まれる商品

レコードを検索するには、LIKE句で「%」を付けます。「%」の位置に任意の文字列が入るため「%すいか」の場合、「すいか」で終わる文字が検索されます。任意の1文字が含まれるものを検索する場合は「_」を入力します。

500円以上購入した人

金額の絞り込みは、500円以上は「>=500」です。500円以上1,000円未満は両方を「AND」でつなぎ、「cost >= 500 AND cost < 1000」と書きます。

商品ごとの売上金額

商品ごとのグループ化は「GROUP BY」で指定します。SELECT句は、GROUP BY句で指定した列と集計関数のみを指定することができます。

7/10 12:00~7/12 11:00の間に購入した人

期間指定は「BETWEEN」で値の小さいものを先に置き、「値１<値２」の順で指定します。

食品,飲料,アルコールで分類した集計

商品を、食品、アルコール、飲料に分類して合計金額を出します。CASE文で条件分岐を作り、WHENで条件、THENで表示内容を記入します。条件を満たさないパターンもELSEで指定します。

データを結合して、顧客ごとの購入金額

データが複数テーブルに分かれている場合、テーブル同士を結合します。顧客ごとの購入金額は、「customersテーブル」と「orderテーブル」で共通する「name」をキーとして内部結合します。さらに「SUM」で合計金額を出し降順に並べています。

ここまで超基本的なSQL文を解説しました！SQLの書き方はこの通りでなく他にも方法があります。是非BigQueryで実際にクエリを書いて、データを触ってみてください。