【BigQuery実践】SQL大全~17問の演習を解きまくる~

ゴール

題名どおり、SQL文をひたすら解けるような演習問題を用意しました。

対象者としては、

SQLにまだ不安がある
これからBigQueryでSQL業務を控えている
SQL勉強したいけど、なにからやればいいかわからない

などの初級者向けの内容になってます！

ゴールは、

演習をひたすら解き、SQLに自信を持てるようになること

とします。

BigQueryを使って、実際に手を動かしていきましょう！

SQLは実際に書いた数だけ覚えます。逆に見てるだけ、読んでるだけだと一生使えるようにはなりません！

SQLの前に

BigQueryについて

BigQueryについて簡単に説明しておきます。

BigQueryというのは、Googleが提供している大量のデータを扱えるデータベースみたいなものです。

ここで詳しくはお伝えしませんが、「大量のデータを扱うならBigQueryがいいんだな」と思っていただければここではいいかなと思います。

詳しく知りたい方は、以下を参照してください。

https://cloud-ace.jp/column/detail174/

BigQueryの料金について

この記事で実装することに料金はかかりません。

ただ、BigQueryが無料で使えるということではなく、料金はかかるサービスです。

料金体系は①ストレージ料金と②クエリ料金の2種類になります。

データの容量による料金
SQLを発行した際に取得するデータ量による料金

とはいえ、BigQueryはめちゃくちゃ安いのと、ストレージ料金は毎月10GB、クエリ料金は毎月1TB無料で使えます。また初めて使う方は３万円分のクレジットが付与されるので、お金のことを気にすることなく使えます！

なので、安心して練習してください！

BigQueryででてくる用語

簡単によくでてくる用語を記載しておきます。

テーブル：データを格納する表のこと
カラム/フィールド：テーブル内の列を「カラム」または「フィールド」と呼ぶ
ロウ/レコード：テーブル内の行を「ロウ」または「レコード」と呼ぶ

データ型：Excelなどとは異なり、データベースではカラムごとにそのカラムに格納するデータの「型」を最初に定める必要がある。この型のことを「データ型」とよぶ。最初に定めた型と異なるデータは格納できない。型の種類は以下のようなものがある。

・文字列型(STRING)・・・文字列 (キャンペーン名やグループ名)
・整数型(INT64)・・・小数点以下を持たない整数(コストや表示回数)
・浮動小数点型(FLOAT64)・・・小数点を持つ数値(インプシェア)
・数値型(NUMERIC)・・・最大で38桁の整数部分と9桁の小数点以下をもつ。あまり使わない
・ブール型(BOOL)・・・「TRUE」「FALSE」値を返す
・日付型(DATE)・・・YYYY-MM-DD形式の日付
・日時型(DATETIME)・・・YYYY-MM-DD HH:mm:ss.ddddd形式の日時型
・時刻型(TIME)・・・HH:mm:ss.ddddd形式の時刻型
・タイムスタンプ型(TIMESTAMP)・・・YYYY-MM-DD HH:mm:ss.dddddタイムゾーン形式の絶対値

スキーマ：テーブルを構成する、通常複数の列の属性をまとめたものを「スキーマ」と呼ぶ

クエリ：データベースに対する問い合わせのことを「クエリ」と呼ぶ。(SQL文のこと)
ビュー：SQLの実行結果の表を記録したもの。テーブルはデータを実際に持つ。ビューは持たない。
NULL：「値がない」ことを示す

BigQueryの始め方

はじめてBigQueryを使われる方は、以下を参照いただき、設定してください。

最初の箇所は飛ばしてもらって構いません。「GCPプロジェクトの作成」から設定スタートなので、そこから読んでいただければと思います。

はじめの一歩が踏み出せない人のためのBigQuery入門

データセットをつくる

設定完了しましたら、データセットを作っていきます。

データセット名に「practice」と記載し、「データセットを作成」をクリックしてください。

テーブルをつくる

続いて作成したデータセットにテーブルを作っていきます。

今回、私の方でデータを用意させていただきました。

仮想のデータではありますが、GoogleAnalyticsとGoogle広告、Yahoo広告の3つのCSVデータをいれてみましょう。

sample_ga ダウンロード

sample_googleads ダウンロード

sample_yahooads ダウンロード

ファイルをアップロードからダウンロードしたファイルを選択し、テーブル名は、「sample_ga」「sample_googleads」「sample_yahooads」としましょう。

またスキーマは、テキストとして編集をクリックし、テキストの箇所に以下の内容をそれぞれコピペして貼り付けてください。

sample_ga

[
  {
    "mode": "NULLABLE",
    "name": "date",
    "type": "DATE"
  },
  {
    "mode": "NULLABLE",
    "name": "channel_grouping",
    "type": "STRING"
  },
  {
    "mode": "NULLABLE",
    "name": "medium",
    "type": "STRING"
  },
  {
    "mode": "NULLABLE",
    "name": "sessions",
    "type": "INTEGER"
  },
  {
    "mode": "NULLABLE",
    "name": "pageviews",
    "type": "INTEGER"
  },
  {
    "mode": "NULLABLE",
    "name": "bounces",
    "type": "INTEGER"
  },
  {
    "mode": "NULLABLE",
    "name": "session_duration",
    "type": "INTEGER"
  },
  {
    "mode": "NULLABLE",
    "name": "exits",
    "type": "INTEGER"
  },
  {
    "mode": "NULLABLE",
    "name": "users",
    "type": "INTEGER"
  },
  {
    "mode": "NULLABLE",
    "name": "new_users",
    "type": "INTEGER"
  },
  {
    "mode": "NULLABLE",
    "name": "goal1_completions",
    "type": "INTEGER"
  }
]

sample_googleads

[
  {
    "mode": "NULLABLE",
    "name": "segments_date",
    "type": "DATE"
  },
  {
    "mode": "NULLABLE",
    "name": "campaign_id",
    "type": "STRING"
  },
  {
    "mode": "NULLABLE",
    "name": "campaign_name",
    "type": "STRING"
  },
  {
    "mode": "NULLABLE",
    "name": "metrics_cost_micros",
    "type": "INTEGER"
  },
  {
    "mode": "NULLABLE",
    "name": "metrics_impressions",
    "type": "INTEGER"
  },
  {
    "mode": "NULLABLE",
    "name": "metrics_clicks",
    "type": "INTEGER"
  },
  {
    "mode": "NULLABLE",
    "name": "metrics_conversions",
    "type": "INTEGER"
  }
]

sample_yahooads

[
  {
    "mode": "NULLABLE",
    "name": "DAY",
    "type": "DATE"
  },
  {
    "mode": "NULLABLE",
    "name": "CAMPAIGN_ID",
    "type": "STRING"
  },
  {
    "mode": "NULLABLE",
    "name": "CAMPAIGN_NAME",
    "type": "STRING"
  },
  {
    "mode": "NULLABLE",
    "name": "COST",
    "type": "INTEGER"
  },
  {
    "mode": "NULLABLE",
    "name": "IMPS",
    "type": "INTEGER"
  },
  {
    "mode": "NULLABLE",
    "name": "CLICKS",
    "type": "INTEGER"
  },
  {
    "mode": "NULLABLE",
    "name": "CONVERSIONS",
    "type": "INTEGER"
  }
]

最後に詳細オプションをクリックして、スキップするヘッダー行を0から1に変更してください。その後、「テーブルを作成」をクリックしてください。

問題なくテーブルが作成されると以下のように3つのテーブルができます。

これで準備完了です。

※もしエラーが起きてデータを入れられない場合は、どこかの設定をしていない可能性があります。再度始めからやってみる等よく読んで実装してみてください。ご連絡いただいても大丈夫です！

SQL基本構文

まずは、基本構文から勉強していきます。

列の取得と並び替え

SELECT・・・列(フィールド)の値を取得する
FROM・・・取得したいテーブルを指定する
ORDER BY・・・並び替えをする

演習①データセット名「practice」テーブル名「sample_ga」から
「channel_grouping」フィールドを指定して値を全行取得しよう
※ヒント
・SELECTで取得するフィールド名を指定し、FROMで対象となるテーブル名を指定
・FROMの後ろは「データセット名.テーブル名」ＯＲ「プロジェクト名.データセット名.テーブル名」

結果テーブル例

回答

SELECT
  channel_grouping
FROM
  practice.sample_ga

演習②データセット名「practice」テーブル名「sample_ga」から
「date」「channel_grouping」「medium」「sessions」フィールドを指定して値を全行取得しよう

結果テーブル例

回答

SELECT
  date,
  channel_grouping,
  medium,
  sessions
FROM
  practice.sample_ga

演習③データセット名「practice」テーブル名「sample_ga」から
全フィールドを指定して、dateの昇順にして取得してください

※ヒント
・並べ替えの命令文は「ORDER BY」、昇順「ASC」降順「DESC」

結果テーブル例

回答

SELECT
  *
FROM
  practice.sample_ga
ORDER BY
  date

演習④データセット名「practice」テーブル名「sample_ga」から
「date」「channel_grouping」「medium」「sessions」を指定して、dateの昇順、sessionsの降順にして取得してください

※ヒント
・並べ替えの命令文は「ORDER BY」、昇順「ASC」降順「DESC」

結果テーブル例

回答

SELECT
  date,
  channel_grouping,
  medium,
  sessions
FROM
  practice.sample_ga
ORDER BY
  date ASC,
  sessions DESC

列の作成と列名付与

SELECT “文字列”、数値・・・新しいフィールドを作成する

SELECT～AS ・・・列(フィールド)の値を取得し、AS以降の列名に変更する

演習⑤データセット名「practice」テーブル名「sample_ga」から「date」「channel_grouping」を指定し、以下を追加する。
・値「東京」カラム名「product_key」
・値「GA」カラム名「medium」

結果テーブル例

回答

SELECT
  date,
  channel_grouping,
  "東京" AS product_key,
  "GA" AS medium
FROM
  practice.sample_ga

行の絞り込みと条件指定

LIMIT・・・指定した行数分取得する

演習⑥データセット名「practice」テーブル名「sample_ga」から「date」「channel_grouping」「sessions」を指定し、「sessions」の最新5位までを取得してください

結果テーブル例

回答

SELECT
  date,
  channel_grouping,
  sessions
FROM
  practice.sample_ga
ORDER BY
  sessions DESC
LIMIT
  5

WHERE・・・列(フィールド)の値でセグメントする
条件式
=・・・等しい
！＝・・・等しくない
LIKE～%○○%・・・○○が含まれているもの
IN・・・いずれかの値に当てはまる
BETWEEN A AND B・・・AとBの間に当てはまる
AND・・・複数条件時使用。A且つB
OR・・・複数条件時使用。AORB
→ANDとORを組合わせて使用するときは、優先する条件を半角()で括る
※日付型、文字列型は“”で括る。整数型、浮動小数点型は半角で記述し、“”で括らない。

演出⑦データセット名「practice」テーブル名「sample_ga」から「date」「channel_grouping」「medium」「sessions」「pageviews」を指定
抽出条件を以下のように指定
・channel_groupingに「cpc」または「Display」含む
且つ
・sessionsが100以上
「sessions」の多い順に並び替えて表示する。

回答例

回答

SELECT
  date,
  channel_grouping,
  medium,
  sessions,
  pageviews
FROM
  practice.sample_ga
WHERE
  (channel_grouping LIKE '%cpc%'
    OR channel_grouping LIKE '%Display%')
  AND sessions >= 100
ORDER BY
  sessions DESC

グループ化

GROUP BY・・・複数のレコードをまとめて1行にする。量的なデータが格納されているフィールドであれば、合計や平均などの集計値を取り出すことが可能

演習⑧データセット名「practice」テーブル名「sample_ga」から「date」「channel_grouping」「medium」を指定し、すべての要素でグルーピング。deteの昇順で取得してください。

結果テーブル例

回答

SELECT
  date,
  channel_grouping,
  medium
FROM
  practice.sample_ga
GROUP BY
  date,
  channel_grouping,
  medium
ORDER BY
  date

演習⑨データセット名「practice」テーブル名「sample_ga」から「date」「channel_grouping」「medium」を指定し、すべての要素でグルーピング。抽出条件を「medium」が「referral」のもののみ抽出してください。

結果テーブル例

回答

SELECT
  date,
  channel_grouping,
  medium
FROM
  practice.sample_ga
WHERE
  medium = "referral"
GROUP BY
  date,
  channel_grouping,
  medium

集計関数

COUNT(*)・・・グループ内のレコード数を数える　　
COUNT(フィールド名)・・・グループ内の値の個数を数える
COUNT(DISTINCT フィールド名)・・・グループ内の固有な値の個数を数える
※「レコード」と「個数」の違いはNULLを含めるか含めないかの違い
SUM(フィールド名)・・・該当フィールドのグループ内の値を合計する
AVG(フィールド名)・・・該当フィールドのグループ内の値の平均値を返す
MAX(フィールド名)・・・該当フィールドのグループ内の最大値を返す
MIN(フィールド名)・・・該当フィールドのグループ内の最小値を返す

演習⑩データセット名「practice」テーブル名「sample_ga」から「date」「medium」でグルーピングしたレコード数をかえしてください。また、レコード数のカラム名は「cnt」とし、「date」の昇順で表示する。

結果テーブル例

回答

SELECT
  date,
  medium,
  COUNT(*) AS cnt
FROM
  practice.sample_ga
GROUP BY
  date,
  medium
ORDER BY
  date

その他関数

FORMAT_TIMESTAMP(‘%Y-%m-%d’, フィールド名)・・・タイムスタンプ型のフィールドのフォーマットを変更する
CURRENT_DATE()・・・今日の日付を取得する
DATE(フィールド名)・・・STRING型のフィールドの日付をDATE型に変更する
DATE_ADD(DATEフィールド, INTERVAL 日付の整数 DAY)・・・
「DATEフィールド」から日付の整数分足した日付が返される
DATE_SUB(DATEフィールド, INTERVAL 日付の整数 DAY)・・・
「DATEフィールド」から日付の整数分減じた日付が返される

演習⑪データセット名「practice」テーブル名「sample_ga」から「date」と「dateの7日後の日付」「レコード数」を取得してください。
「dateの7日後の日付」のカラム名は、oneweek_after、「レコード数」のカラム名は、cntにそれぞれ変更。dateの昇順にして表示する。

結果テーブル例

回答

SELECT
  date,
  DATE_ADD(date, INTERVAL 7 day) AS oneweek_after,
  COUNT(*) AS cnt
FROM
  practice.sample_ga
GROUP BY
  date,
  oneweek_after
ORDER BY
  date

演習⑫データセット名「practice」テーブル名「sample_ga」から「date」と「dateの7日前の日付」「レコード数」を取得してください。
「dateの7日前の日付」のカラム名は、oneweek_before、「レコード数」のカラム名は、cntにそれぞれ変更。dateの昇順にして表示する。

結果テーブル例

回答

SELECT
  date,
  DATE_SUB(date, INTERVAL 7 day) AS oneweek_before,
  COUNT(*) AS cnt
FROM
  practice.sample_ga
GROUP BY
  date,
  oneweek_before
ORDER BY
  date

演習⑬データセット名「practice」テーブル名「sample_ga」から「今日の日付」と「date」と「レコード数」を取得してください。
「今日の日付」のカラム名は、today、「レコード数」のカラム名は、cntにそれぞれ変更。dateの昇順にして表示する。

結果テーブル例

回答

SELECT
  CURRENT_DATE() AS today,
  date,
  COUNT(*) AS cnt
FROM
  practice.sample_ga
GROUP BY
  date
ORDER BY
  date

仮想テーブル

仮想テーブル・・・SQL文を実行した結果を「仮に」保存しておいて利用する

通常のSQL文→FROM句で「実体のあるテーブル」を指定
仮想テーブル→実体を伴ったテーブルではなく、あるSQL文を実行した結果テーブル「仮想テーブル」として、そのテーブルを対象に実行する

※仮想テーブルは「テーブル結合」や「集合演算」と組み合わせて利用する。仮想テーブルを作成する方法は、「WITH句」「ビュー」「サブクエリ」の三つ。今回は「WITH句」と「サブクエリ」について説明する(サブクエリ後述)

演習⑭演習11で作成したSQL文をWITH句を用いて、抽出してください。仮想テーブル名は「test_with」とし、すべてのカラムを表現してください。また、ORDEB BYは無くしてください。

結果テーブル例

回答

WITH
  test_with AS (
  SELECT
    date,
    DATE_ADD(date, INTERVAL 7 day) AS oneweek_after,
    COUNT(*) AS cnt
  FROM
    practice.sample_ga
  GROUP BY
    date,
    oneweek_after)
SELECT
  *
FROM
  test_with

サブクエリ

サブクエリ・・・仮想テーブルをつくる方法の一つ。概念は仮想テーブルと同じ

SQL文のFROM句の中にSELECT~FROMを記載する(副問い合わせとも呼ばれる)。SQL文の中にSELECT文が複数使用されている場合、サブクエリが使われていると考える。サブクエリはよりシンプルにSQL文を書くことができるが、使用しすぎると逆にわかりづらくなるため、WITH句との併用が好ましい。

演習⑮演習14で作成したSQL文をWITH句からサブクエリに変更して、表現してください。

結果テーブル例

回答

SELECT
  *
FROM (
  SELECT
    date,
    DATE_ADD(date, INTERVAL 7 day) AS oneweek_after,
    COUNT(*) AS cnt
  FROM
    practice.sample_ga
  GROUP BY
    date,
    oneweek_after )

テーブル結合

テーブル結合・・・複数のテーブルをキーで一つにまとめる

そもそもテーブルを複数に分ける理由は、「メンテナンスが容易になるため」
・異なる性質のデータを扱いやすい
・一定期間ごとに集計されたデータを扱いやすい

演習⑯データセット「practice」テーブル名「sample_googleads」「sample_yahooads」の２つのテーブルをWITH句でそれぞれ仮想テーブルを作成。LEFT JOINで結合し、一つのテーブルで表示してください。抽出するカラムはすべてのカラムを出してみましょう。

テーブル①「sample_googleads」
結合　テーブル②「sample_yahooads」
結合キー「date」「campaign_name」

結果テーブル例

回答

WITH
  google AS (
  SELECT
    segments_date AS day,
    campaign_name AS campaign_name,
    metrics_cost_micros AS cost,
    metrics_impressions AS impression,
    metrics_clicks AS click,
    metrics_conversions AS cv
  FROM
    practice.sample_googleads),
  yahoo AS(
  SELECT
    DAY AS day,
    CAMPAIGN_NAME AS campaign_name,
    COST AS cost,
    IMPS AS impression,
    CLICKS AS click,
    CONVERSIONS AS cv
  FROM
    practice.sample_yahooads)
SELECT
  google.day AS google_day,
  yahoo.day AS yahoo_day,
  google.campaign_name AS google_campaign_name,
  yahoo.campaign_name AS yahoo_campaign_name,
  google.cost AS google_cost,
  yahoo.cost AS yahoo_cost,
  google.impression AS google_impression,
  yahoo.impression AS yahoo_impression,
  google.click AS google_click,
  yahoo.click AS yahoo_click,
  google.cv AS google_cv,
  yahoo.cv AS yahoo_cv
FROM
  google
LEFT JOIN
  yahoo
ON
  google.day = yahoo.day
  AND google.campaign_name = yahoo.campaign_name

集合演算

集合演算・・・同じカラムを持った複数のテーブルを一つにまとめる

演習⑰データセット「practice」テーブル名「sample_googleads」「sample_yahooads」の２つのテーブルをWITH句でそれぞれ仮想テーブルを作成。テーブル名「sample_googleads」のカラムに”Google”というmediumの列を追加。テーブル名「sample_yahooads」のカラムに”Yahoo”というmediumの列を追加。UNION ALLで結合し、一つのテーブルで表示してください。抽出するカラムはすべてのカラムを出してみましょう。

テーブル①「sample_googleads」
結合　テーブル②「sample_yahooads」

結果テーブル例

回答

WITH
  google AS (
  SELECT
    "Google" AS medium,
    segments_date AS day,
    campaign_name AS campaign_name,
    metrics_cost_micros AS cost,
    metrics_impressions AS impression,
    metrics_clicks AS click,
    metrics_conversions AS cv
  FROM
    practice.sample_googleads),
  yahoo AS(
  SELECT
    "Yahoo" AS medium,
    DAY AS day,
    CAMPAIGN_NAME AS campaign_name,
    COST AS cost,
    IMPS AS impression,
    CLICKS AS click,
    CONVERSIONS AS cv
  FROM
    practice.sample_yahooads AS yahooads )
SELECT
  *
FROM
  google
UNION ALL
SELECT
  *
FROM
  yahoo

まとめ

お疲れさまでした！これで基本的なSQL文はできました！

記載のSQLができれば「基本的なSQLはかけます！」と言えるのではないでしょうか。(人によってレベル感違うのでわかりませんが、、、)

ぜひSQLに自信がない方は、実際に手を動かして実践してみましょう！

参考文献

直近でおすすめなSQL本を以下に。

【BigQuery実践】SQL大全~17問の演習を解きまくる~

ゴール

SQLの前に

BigQueryについて

BigQueryの料金について

BigQueryででてくる用語

BigQueryの始め方

データセットをつくる

テーブルをつくる

SQL基本構文

列の取得と並び替え

列の作成と列名付与

行の絞り込みと条件指定

グループ化

集計関数

その他関数

仮想テーブル

サブクエリ

テーブル結合

集合演算

まとめ

参考文献

シェア！

ABOUTこの記事をかいた人

NEW POST最新の記事

【OSSワークフローシステム】Digdagについてまとめる

【OSSワークフローシステム】Airflowについてまとめる

ECSを使ってデータのバッチ処理をしてみる

dockerでデータのバッチ処理をしてみる

新着記事

カテゴリー

ゴール

SQLの前に

BigQueryについて

BigQueryの料金について

BigQueryででてくる用語

BigQueryの始め方

データセットをつくる

テーブルをつくる

SQL基本構文

列の取得と並び替え

列の作成と列名付与

行の絞り込みと条件指定

グループ化

集計関数

その他関数

仮想テーブル

サブクエリ

テーブル結合

集合演算

まとめ

参考文献

シェア！

ABOUTこの記事をかいた人

NEW POST最新の記事

【OSSワークフローシステム】Digdagについてまとめる

【OSSワークフローシステム】Airflowについてまとめる

ECSを使ってデータのバッチ処理をしてみる

dockerでデータのバッチ処理をしてみる

Recommend関連記事

新着記事

カテゴリー

タグ