😸

BigQuery超入門①

2024/12/22に公開

SQL

Google Cloud

BigQuery

tech

はじめに

業務で BigQuery を触る機会が増えてきたのですがまだまだ慣れないので、超基本を手を動かしながらインプットして、それを言語化してアウトプットしてみようと思います（複数回に分ける予定）。
SQL は手を動かさないと身につかないものだと思うので、手を動かすことを基本にしながら頑張ります。
基本的には BigQuery ではじめる SQL データ分析 GA4 & Search Console & Google フォーム対応を中心に読みながら手を動かして学んだことをまとめていきます。
（とても読みやすく、サンプルデータも用意されているので BigQuery を初めて触る方におすすめです）

学ぶこと

SQL の基本構文
グループ化
SQL の実行順序

データを抽出するための SQL 基本構文

まずは SQL の基本構文から見ていきます。

用語の定義
カラム・・・テーブルの構造としての列を指す
フィールド・・・カラムに格納されている値を指す
レコード・・・テーブルの構造としての行を指す

SELECT でデータを抽出する

SELECT を使うと指定したフィールドを持つレコードを取得できる。

例）全フィールドを持つレコードを取得

SELECT
  *
FROM
  `project_id.dataset.products`

例）ID, name フィールドを持つレコードを取得

SELECT
  id, name
FROM
  `project_id.dataset.products`

例）EXCEPTで ID を除いたフィールドを持つレコードを取得
EXCEPT を使って不要なフィールドを取り除くことが可能。

SELECT
  * EXCEPT(id)
FROM
  `project_id.dataset.products`

例）DISTINCTでユニークな ID フィールドを持つレコードを取得
DISTINCT を使うことで重複を削除したフィールドの値を取得できる。

SELECT
  DISTINCT id,
  name
FROM
  `project_id.dataset.products`

SELECT が持つ特性として新しいフィールド追加することも可能。
例）全レコードに新しく result フィールドが追加され「合格」という値が格納される。

SELECT
  id,
  name,
  "合格" AS result
FROM
  `project_id.dataset.products`

例）既存のフィールド値を組み合わせて新しいフィールドを作成する（新しく合計金額のフィールドを追加）

SELECT
  total_price,
  subtotal_price,
  (total_price + subtotal_price) AS total
FROM
  `dataset_id.project_id.orders`

レコードの並び替え

テーブルから抽出したレコードには規則性がないため ORDER BY によって並び順の基準を作ったり、順序を指定したりしてレコードに規則性を持たせることができる。

例）並び順の基準を order_id に指定する
デフォルトだと順序は昇順なので、この場合は order_id を基準に昇順でレコードを並べる。

SELECT
  order_id,
  user_id,
  quantity
FROM
  `project_id.test.sales`
ORDER BY
  order_id

例）order_id, quantity を基準に昇順でレコードを並び替える

SELECT
  order_id,
  user_id,
  quantity
FROM
  `project_id.test.sales`
ORDER BY
  order_id, quantity

レコードの絞り込み

レコードの取得件数を制限したり、条件に一致したレコードのみを取得したりする方法について確認していきます。

COUNT

COUNT を使うことで取得するレコード数を制限できる。

例）20 件だけレコードを取得

SELECT
  order_id,
  user_id,
  quantity
FROM
  `project_id.dataset.sales`
ORDER BY
  order_id,
  quantity
LIMIT 20

OFFSET

OFFSET を使うことで取得する位置をずらしてレコードを取得する。
（ページネーションってこうやって実現するのかと思った）

例）15 行目以降のレコードから 20 件だけ取得

SELECT
  order_id,
  user_id,
  quantity
FROM
  `project_id.dataset.sales`
ORDER BY
  order_id,
  quantity
LIMIT 100
OFFSET 15

WHERE

WHEREでは条件式を使って、条件に一致したレコードのみを抽出できる。

例）quantity=4 のレコードのみを抽出

SELECT
  order_id,
  user_id,
  quantity
FROM
  `project_id.dataset.sales`
WHERE
  quantity = 4

WHERE で AND と OR の組み合わせを使うことができる。
注意点として優先する条件をカッコで囲むようにしないと意図しない結果になる可能性がある。
例）「性別が男性もしくは出身地が長崎県」かつ「誕生日が 2000 年 1 月 1 日以降」に当てはまる顧客を抽出する

SELECT
  customer_id,
  customer_name,
  birthday,
  prefecture
FROM
  `project_id.dataset.customers`
WHERE
  (gender = 1 OR prefecture = "長崎") AND birthday > "2000-01-01"

カッコの場所が違うと「性別が男性」もしくは「出身地が長崎県で誕生日が 2000 年 1 月 1 日以降」と言う条件になる。

SELECT
  customer_id,
  customer_name,
  birthday,
  prefecture
FROM
  `project_id.dataset.customers`
WHERE
  gender = 1 OR (prefecture = "長崎" AND birthday > "2000-01-01")

条件式で演算子を利用する

WHERE で記述する条件式では色んな演算子を組み合わせて条件を指定できる。
ここではいくつかピックアップする。

IN

IN を使って「対象の要素のいずれかに当てはまるかどうか」で一致の確認をできる。

例）prefecture が「京都」「兵庫」「大阪」のいずれかである場合に一致する

SELECT
  *
FROM
  `project_id.dataset.customers`
WHERE
  prefecture IN ("京都","兵庫","大阪")

例えば IN を使わない場合、次のように OR の組み合わせを書くことになるため条件が増えても OR を毎回記述する必要がなくなる。

SELECT
  *
FROM
  `project_id.dataset.customers`
WHERE
  prefecture = "京都" OR prefecture = "兵庫" OR prefecture = "大阪"

最近だと IN (配列) のような配列内のいずれかの要素に含まれているかを確認する絞り込みも行った。
例えば ["京都","兵庫","大阪"] のような配列の場合に UNNEST を使って配列を展開し要素のいずれかに含まれているかを確認できる。

SELECT
  *
FROM `project_id.dataset.customers`
WHERE
  prefecture IN UNNEST(["京都","兵庫","大阪"])

BETWEEN

BETWEENを使うと値の範囲指定できるため、指定された範囲内の値をもつフィールドのレコードのみを取得できる。
特に期間の範囲指定で使うことが多い。

例）誕生日が「1990 年 1 月 1 日」から「2000 年 1 月 1 日生まれ」の顧客のみ抽出する

SELECT
  *
FROM
  `project_id.dataset.customers`
WHERE
  birthday BETWEEN "1990-01-01" AND "2000-01-01"
ORDER BY
  birthday

BETWEEN では文字列の範囲指定もできアルファベットの場合は a が小さく z が一番大きいものとして扱われる。
BETWEEN を使う際の注意点は次の点に気をつけた方が良さそう。

（まだ使っているところを見たことないが）日本語文字列は結果が定かではないようなので避ける
BETWEEN の AND 条件の順番を間違えないこと。BETWEEN A AND B で A > B の関係にならないようにする

NG 例
誕生日が「2000 年 1 月 1 日生まれ」から「1990 年 3 月 20 日」の顧客のみ抽出するとなっておりデータを取得できない。

SELECT
  *
FROM
  `project_id.dataset.customers`
WHERE
  birthday BETWEEN "2000-01-01" AND "1990-03-20"
ORDER BY
  birthday

TRUE / FALSE

本来の WHERE では条件を加えて「条件に一致したデータのみ抽出」するが、シンプルに TRUE / FALSE だけを指定できる。
TRUE であれば全レコードを返し、FALSE の場合は 0 件にである（レコードがゼロ）。
これは「ある条件に一致した時だけテーブルのレコードを返すようにしたい」といった SQL を動的に使う際に使えたりもする。

例えば Go の template を使うと次のように動的にできる。

-- SelectCustomers = true の場合のみレコードを返す
{{ if .SelectCustomers }}
SELECT
  *
FROM
  `project_id.dataset.customers`
WHERE
  TRUE
{{ end }}

-- SelectCustomers = false の場合はレコードは0件
{{ if ne .SelectCustomers }}
SELECT
  *
FROM
  `project_id.dataset.sales`
WHERE
  FALSE
{{ end }}

WHERE 1=1

上記に関連して SQL を動的に扱う際は WHERE 1=1 を使うことで動的に扱うことができる。
WHERE 1=1 は常に TRUE なので「条件に一致しない場合は全レコードを返す」「条件に一致した場合は一致したレコードを返す」という処理を実現できる。
例えば Go の template を使うと次のようになる。

-- FilterCustomers = true の場合のみ条件を加えて一致したレコードのみ返す
-- 一致しない場合は全レコードを返す
SELECT
  *
FROM
  `project_id.dataset.customers`
WHERE
  1=1
  {{ if .FilterCustomers }}
  {{ .FilterCustomers }}
  {{ end }}

-- {{ .FilterCustomers }} の中身
AND name = "田中太朗"

グループ化

グループ化とは、指定したフィールドで同じ値を持つ複数のレコードを一つのレコードにまとめてグループ単位で分類すること。
GROUP BYを使うことでグループ単位で分類できる。
例えば出身地ごとに分類したい場合は次の SQL を実行すると分類ができる。

user_id	prefecture
1	東京
2	大阪
3	東京
4	大阪
5	東京

SELECT
  prefecture
FROM
  users
GROUP BY
  prefecture

実行結果

prefecture
東京
大阪

後述する集計関数を使って「出身地ごとのユーザー数」を取得できたりもする。

user_id	prefecture
1	東京
2	大阪
3	東京
4	大阪
5	東京

次の SQL で実現できる。

SELECT
  prefecture,
  count(user_id) AS count
FROM
  users
GROUP BY
  prefecture

実行結果

prefecture	count
東京	3
大阪	2

また複数レコードをグループ化することも可能。
例えば「出身地の性別ごと」に分類したい場合。

user_id	prefecture	gender
1	東京	男
2	大阪	女
3	東京	女
4	大阪	男
5	東京	男

次の SQL で「東京の男性」「東京の女性」「大阪の男性」「大阪の女性」のグループで分類できる。

SELECT
  prefecture,
  gender,
  count(user_id) AS count
FROM
  users
GROUP BY
  prefecture,
  gender

実行結果

prefecture	gender	count
東京	男	2
東京	女	1
大阪	男	1
大阪	女	1

GROUP BY を使う際の注意点としてSELECT に含めることができるのはグループ化したフィールド or 集計関数でなければいけないという点がある。

集計関数

GROUP BY を使うにあたり集計関数が使えるようになることは必須である。
（ただし GROUP BY と集計関数が必ずセットである必要はない）

COUNT でレコード数を取得する

COUNT 関数はレコード数を取得するものだが引数によって意味合いが違う。
フィールドを指定しない場合はシンプルにレコードの数を返す。
例）order_id ごとのレコード数を取得

SELECT
  order_id,
  COUNT(*) AS order_id_count
FROM
  `project_id.dataset.sales`
GROUP BY
  order_id

例）フィールドを指定すると指定したフィールドの値の個数を返す

SELECT
  order_id,
  COUNT(product_id) AS product_id_count
FROM
  `project_id.dataset.sales`
GROUP BY
  order_id
ORDER BY
  product_id_count DESC

COUNT(*) は単にレコードの数を返すので値が null でも含むが、 COUNT(product_id) の場合は指定したフィールドの値の個数になるので null は含まない数が返ってくる。

DISTINCTを使うと重複しているフィールドの値は削除してユニークな値のみを取得できる。
COUNT と組み合わせることでユニークな値のフィールドの個数を取得できる。
例）都道府県ごとのユーザー数を取得する

SELECT
  prefecture,
  COUNT(DISTINCT customer_id) AS users
FROM
  `project_id.dataset.customers`
GROUP BY
  prefecture

HAVING で集計結果から取得するレコードを絞り込む

前提として SQL には実行の順序があり、グループ化される前に WHERE でレコードの絞り込みが行われる。
そのためグループ化した上で集計した結果を WHERE で絞り込むことができない。
集計結果を絞り込むにはHAVINGを使う。

エラー例）都道府県でグループ化したユーザー数が 20 以上のレコードだけを取得する

SELECT
  prefecture,
  COUNT(DISTINCT customer_id) AS users
FROM
  `project_id.dataset.customers`
GROUP BY
  prefecture
WHERE
  users >= 20

当然グループ化してから集計関数が適用されるので、その前に実行される WHERE で集計関数を使うことはできない。

SELECT
  prefecture,
  COUNT(DISTINCT customer_id) AS users
FROM
  `project_id.dataset.customers`
GROUP BY
  prefecture
WHERE
  COUNT(DISTINCT customer_id) >= 20

HAVING を使って集計結果を絞り込む。

SELECT
  prefecture,
  COUNT(DISTINCT customer_id) AS users
FROM
  `project_id.dataset.customers`
GROUP BY
  prefecture
HAVING
  users >= 20

その他の集計関数

その他にも SUM,AVG,MAX,MIN などの集計関数が存在する。

例）SUM でグループ化したフィールドの値の合計値を算出する
「ユーザーの注文ごとの合計購入数」を取得。

SELECT
  user_id,
  order_id,
  SUM(quantity) AS total_quantity
FROM
  `project_id.dataset.sales`
GROUP BY
  user_id,
  order_id
ORsDER BY
  total_quantity DESC

例）MAX でグループ化したフィールドの値の最大値を算出する
「ユーザーの注文で一番購入数が多かった値」を取得。

SELECT
  user_id,
  order_id,
  MAX(quantity) AS total_quantity
FROM
  `project_id.dataset.sales`
GROUP BY
  user_id,
  order_id
ORDER BY
  total_quantity DESC

例）MIN で値の最小値を取得する
「ユーザーの注文で一番購入数が少なかった値」を取得。

SELECT
  user_id,
  order_id,
  MIN(quantity) AS max_quantity
FROM
  `project_id.dataset.sales`
GROUP BY
  user_id, order_id
ORDER BY
  max_quantity DESC

IF を使った柔軟なグループ化

GROUP BY では指定したフィールドの値そのものをグループ化して分類できるようになったが、実務ではもっと柔軟に分類できるようにしたい場面がある。
それを実現するための一つの手段としてIFがある。
IF(条件式,TRUEの場合の値,FALSEの場合の値)

例えば誕生日を「平成生まれ」「昭和生まれ」で分類したい時は次の SQL を実行することで実現できる。

SELECT
  IF(birthday >= "1989-01-08","平成生まれ","昭和生まれ") AS era,
  COUNT(DISTINCT customer_id) AS count
FROM
  `project_id.dataset.customers`
GROUP BY
  era

他にも都道府県別ではなく「一都三県」「一都三県以外」のような分類も可能。

SELECT
  IF((prefecture IN ("東京","神奈川","千葉","埼玉")) IS TRUE, "一都三県","一都三県以外") AS prefecture,
  COUNT(DISTINCT customer_id) AS count
FROM
  `project_id.dataset.customers`
GROUP BY
  prefecture

CASE

柔軟なグループ化を実現するもう一つの手段がCASEである。
IF では条件に一致するかどうかの二択で判断していたが CASE ではより多くの条件を追加できる。
例）prefecture から「関東主要県」「関西主要県」「その他」に分けてグループ化する

SELECT
  CASE prefecture
    WHEN "東京" THEN "関東主要県"
    WHEN "神奈川" THEN "関東主要県"
    WHEN "大阪" THEN "関西主要県"
    WHEN "兵庫" THEN "関西主要県"
    WHEN "京都" THEN "関西主要県"
  ELSE "その他"
  END AS prefecture_group,
  COUNT(DISTINCT customer_id)
FROM
  `project_id.dataset.customers`
GROUP BY
  prefecture_group
ORDER BY
  prefecture_group

対象のフィールドと等しいかどうかを見るのであれば上記の記述で問題ないが値の大小を比較したり、BETWEEN での範囲指定をしたりしたい場合は次のような記述にする必要がある。

例）グループ化するフィールドの記述場所を変更

SELECT
  CASE
    WHEN birthday >= "2000-01-01" THEN "2000年代生まれ"
    WHEN birthday >= "1990-01-01" THEN "1990年代生まれ"
    WHEN birthday >= "1980-01-01" THEN "1980年代生まれ"
  ELSE "1970年代以前生まれ"
  END AS era_group,
  COUNT(DISTINCT customer_id)
FROM
  `project_id.dataset.customers`
GROUP BY
  era_group

また CASE は条件式の順序も重要で順番を間違えると意図しないデータが返ってくる。
「新しい順」or「古い順」/「大きい順」or「小さい順」などの規則の順番に従って書いていく。

例）条件式の順番を間違えると「1990 年代生まれ」がヒットしなくなる

SELECT
  CASE
    WHEN birthday >= "2000-01-01" THEN "2000年代生まれ"
    WHEN birthday >= "1980-01-01" THEN "1980年代生まれ"
    WHEN birthday >= "1990-01-01" THEN "1990年代生まれ"
  ELSE "1970年代以前生まれ"
  END AS era_group,
  COUNT(DISTINCT customer_id)
FROM
  `project_id.dataset.customers`
GROUP BY
  era_group

補足: SQL の実行順序について理解する

FROM 句
↓
JOIN 句（WHERE句と一緒にされている場合もある）
↓
WHERE 句
↓
GROUP BY 句
↓
HAVING 句
↓
SELECT 句
↓
ORDER BY 句
↓
LIMIT 句

すでに述べた通りグループ化したら集計関数を実施できるが WHERE 句で集計関数を使えない
LIMIT でレコード数を絞り込んでも先に全データを取得して最終的に絞り込むので処理する際のデータ量は変わらない。
例外で BigQuery では GROUP BY でも SELECT のエイリアスを指定できる。

参考記事・書籍

SQL 記述者全員が理解すべき SELECT 文の論理的な処理順序のお話
 BigQuery ではじめる SQL データ分析 GA4 & Search Console & Google フォーム対応

GitHubで編集を提案

はじめに

学ぶこと

データを抽出するための SQL 基本構文

SELECT でデータを抽出する

レコードの並び替え

レコードの絞り込み

COUNT

OFFSET

WHERE

条件式で演算子を利用する

IN

BETWEEN

TRUE / FALSE

WHERE 1=1

グループ化

集計関数

COUNT でレコード数を取得する

HAVING で集計結果から取得するレコードを絞り込む

その他の集計関数

IF を使った柔軟なグループ化

CASE

補足: SQL の実行順序について理解する

参考記事・書籍

Discussion