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」で値の小さいものを先に置き、「値1<値2」の順で指定します。

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

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

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

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


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

この記事を書いた人

池田 志穂

池田 志穂

メンバーズデータアドベンチャーのデータアナリスト。大手企業のWebデザイナー/コーダー/Webディレクターを経て、システム会社にてコンサル業務とPMに従事し、2019年メンバーズ再入社(リメンバーズ)。

おすすめ記事

タグ

2020新卒バトンAdobe IllustratorBIツールCCDLab.CSSCSVDockerDXECExcelExcel関数GAGitGoogleAnalyticsGoogleデータポータルKubernetesLT会MAMembersDinerOJTPhotoshopPythonRubySDGsSEOSimilarWebSlackSNSSocial Art JapanプロジェクトSQLUIUXUXライティングUXリサーチVitePressVSCodeWeb3WebディレクションWebディレクターWebマーケティングWeb解析Well-beingWordPressアクセシビリティアナリティクスウェビナーエシカルエシカルファッションエンジニアオウンドメディアオンラインオンラインイベントお悩み相談室キャリアクライアントワークコーディングコミュニケーションコンテンツマーケティングコンペサービスサイト構造サステイナブルスウェーデンスキルアップセミナーソーシャルアーティストソーシャルクリエイターチームビルディングツールデータデータアナリストデータサイエンティストディレクションディレクターデザイナーデザインデンマークトンマナナレッジハックブームの裏側プランニングフレームワークプレゼンプログラミングプログラミング教育ブロックチェーンフロントエンドマーケターマーケティングマシンラーニングマネジメントスキルミーティングメタバースメンタルハックメンバーズメディカルマーケティングカンパニーメンバーズルーツカンパニーユーザーテストライティングラボ活動リサーチリモートワークショップワークスタイル事例仕事術仙台再生可能エネルギー分析効率化勉強会動画北欧医療業界品質管理営業地方金融企業学生向け広告運用提案数学新卒研修新規構築機械学習気候変動海洋プラスチック問題生産性生産性向上産学連携研修社会課題社会課題調査競技プログラミング脱炭素自動化ツール色彩検定製薬業界資格開発環境障がい者雇用