GoogleSQL 基礎 — 分析クエリの型

bigquery-public-data.usa_names.usa_1910_current（州別・年別の出生時の名前集計。5 列・約631万行・約186 MiB）を使い、SELECT の基本形を一通り試します。サンドボックスで完走可・無料枠内（各クエリとも 110〜180 MB 程度の見込み。テーブル全体は 5 列で 186 MiB なので、読む列数に応じて全体の 6 割〜ほぼ全量になります）。

1. 2020 年・カリフォルニア州の人気の名前トップ 10 を、性別と件数付きで取得します。

-- 処理: 180 MB 程度（WHERE の year, state も含め実質 5 列すべてを参照）
SELECT name, gender, number
FROM `bigquery-public-data.usa_names.usa_1910_current`
WHERE year = 2020 AND state = 'CA'
ORDER BY number DESC
LIMIT 10;

期待される出力: name / gender / number の 3 列が number 降順で 10 行。上位には当時人気だった男女の名前が並びます（具体的な名前と件数は年によって変わるため、実行して確認してください）。

2. SELECT * EXCEPT と SELECT * REPLACE を試します。

-- 処理: 150 MB 程度
SELECT * EXCEPT (state)
FROM `bigquery-public-data.usa_names.usa_1910_current`
WHERE year = 2020 AND gender = 'F'
ORDER BY number DESC
LIMIT 5;

-- 処理: 180 MB 程度（REPLACE は列を除外しないため実質 5 列すべてを参照）。name の値だけを大文字化して返す
SELECT * REPLACE (UPPER(name) AS name)
FROM `bigquery-public-data.usa_names.usa_1910_current`
WHERE year = 2020 AND gender = 'F'
ORDER BY number DESC
LIMIT 5;

期待される出力: 1 つ目は state 列が消えた 4 列 5 行。2 つ目は列構成は同じまま name だけがすべて大文字になった 5 行です。

3. 全期間・全州を対象に、女児の名前として合計 50 万件を超えたことがある名前を HAVING で絞り込みます。

-- 処理: 110 MB 程度（name, gender, number の 3 列。5 列中 3 列なので約 6 割）
SELECT name, SUM(number) AS total_babies
FROM `bigquery-public-data.usa_names.usa_1910_current`
WHERE gender = 'F'
GROUP BY name
HAVING total_babies > 500000
ORDER BY total_babies DESC;

期待される出力: 累計 50 万件を超えた女児名だけが total_babies 降順で並びます。HAVING は SUM で作った集計後の値（エイリアス total_babies）に対する条件なので、集計前の生の行には使えないことを確認してください。

後片付け: すべて宛先テーブルを指定しない SELECT のみなので、削除すべきリソースはありません。クエリ結果は一時テーブルとして扱われ、自動的に期限切れになります。

austin_bikeshare.bikeshare_trips（227万行・10 列・約252 MiB）と、駅マスタ austin_bikeshare.bikeshare_stations（101 行・16 列・15 KB とごく小さい）を JOIN します。サンドボックスで完走可・無料枠内（駅マスタは無視できるほど小さく、費用は trips 側の参照列数でほぼ決まります）。

1. まず JOIN キーの型を確認せずに、終了駅（end_station_id）で JOIN してみます。

-- このクエリは型不一致でエラーになります（実行不要、エラー内容の確認が目的）
SELECT t.trip_id, s.name
FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips` AS t
JOIN `bigquery-public-data.austin_bikeshare.bikeshare_stations` AS s
  ON t.end_station_id = s.station_id
LIMIT 10;

期待される出力: end_station_id は STRING、駅マスタ側の station_id は INTEGER のため、比較演算子に一致するシグネチャがない旨のエラーになります（例: No matching signature for operator = for argument types: STRING, INT64。文言はバージョンにより多少異なります）。GoogleSQL は STRING と INT64 を自動変換しません。

2. CAST で型を揃えて修正し、続けて WITH 句で「出発駅ごとの平均利用時間」を求めます。

-- 処理: 60〜90 MB 程度（start_station_id, duration_minutes の 2 列は数値型で軽い）
WITH station_trips AS (
  SELECT start_station_id, duration_minutes
  FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
  WHERE duration_minutes BETWEEN 1 AND 180
)
SELECT
  s.name AS station_name,
  COUNT(*) AS trip_count,
  ROUND(AVG(t.duration_minutes), 1) AS avg_minutes
FROM station_trips AS t
JOIN `bigquery-public-data.austin_bikeshare.bikeshare_stations` AS s
  ON t.start_station_id = s.station_id
GROUP BY station_name
ORDER BY trip_count DESC
LIMIT 10;

期待される出力: station_name / trip_count / avg_minutes の 3 列で 10 行。利用回数が多い駅ほど上位に来ます。start_station_id（trips 側）と station_id（stations 側）はどちらも INTEGER なので、CAST なしでそのまま JOIN できることも確認してください。

後片付け: すべて宛先テーブルを指定しない SELECT のみなので、削除すべきリソースはありません。

再び usa_names.usa_1910_current を使い、RANK と QUALIFY、そして LAG で前年比を計算します。サンドボックスで完走可・無料枠内（150〜190 MB 程度の見込み。2 つ目のクエリは WHERE で name / state / gender も参照するため実質 5 列すべてが対象になります）。

1. 2015〜2020 年について、年・性別ごとの人気トップ 3 を QUALIFY で取得します。

-- 処理: 150 MB 程度
SELECT
  year, gender, name, number,
  RANK() OVER (PARTITION BY year, gender ORDER BY number DESC) AS rank
FROM `bigquery-public-data.usa_names.usa_1910_current`
WHERE year BETWEEN 2015 AND 2020
QUALIFY rank <= 3
ORDER BY year, gender, rank;

期待される出力: year / gender / name / number / rank の 5 列。年×性別の組ごとに rank が 1〜3 の行が並び、合計で 6 年 × 2 性別 × 3 位 = 36 行になります。

2. 特定の名前（例: カリフォルニア州の女児名 Emma）について、前年からの増減を LAG で計算します。

-- 処理: 180 MB 程度（WHERE の name / state / gender も含め実質 5 列すべてを参照）
SELECT
  year,
  number,
  number - LAG(number) OVER (ORDER BY year) AS yoy_change
FROM `bigquery-public-data.usa_names.usa_1910_current`
WHERE name = 'Emma' AND state = 'CA' AND gender = 'F'
ORDER BY year;

期待される出力: year / number / yoy_change の 3 列。最初の年は比較対象の前年が無いため yoy_change が NULL になり、以降は前年との差分（増減）が入ります。

後片付け: すべて宛先テーブルを指定しない SELECT のみなので、削除すべきリソースはありません。

ハンズオン 4-1 の HAVING クエリと 4-3 の QUALIFY クエリをパイプ構文で書き直し、最後に CREATE TABLE ... AS で結果を演習用データセット handson_ch04 に保存します。サンドボックスで完走可・無料枠内（スキャン量はハンズオン 4-1・4-3 と同程度。テーブル作成自体は課金対象外）。

1. 演習用データセットを US ロケーションに作成します。

bq mk \
  --dataset \
  --location=US \
  --description="第4章 GoogleSQL 基礎の演習用" \
  --default_table_expiration=86400 \
  --label=purpose:handson \
  PROJECT_ID:handson_ch04

2. 4-1 の HAVING クエリをパイプ構文で書き直します。GROUP BY は独立した演算子ではなく AGGREGATE に統合され、HAVING 相当の絞り込みは WHERE で行います。

-- 処理: 110 MB 程度。結果はハンズオン 4-1 の HAVING クエリと同じ
FROM `bigquery-public-data.usa_names.usa_1910_current`
|> WHERE gender = 'F'
|> AGGREGATE SUM(number) AS total_babies GROUP BY name
|> WHERE total_babies > 500000
|> ORDER BY total_babies DESC;

3. 4-3 の QUALIFY クエリをパイプ構文で書き直しつつ、結果を handson_ch04.top_names_2015_2020 に保存します。EXTEND でランキング列を追加し、QUALIFY 相当は WHERE で行います。EXTEND は既存の列を残したまま列を追加するだけなので、最後に SELECT で出力列を 4-3 と同じ 5 列に絞ります。

-- 処理: 150 MB 程度
CREATE TABLE `PROJECT_ID.handson_ch04.top_names_2015_2020` AS (
  FROM `bigquery-public-data.usa_names.usa_1910_current`
  |> WHERE year BETWEEN 2015 AND 2020
  |> EXTEND RANK() OVER (PARTITION BY year, gender ORDER BY number DESC) AS rank
  |> WHERE rank <= 3
  |> SELECT year, gender, name, number, rank
  |> ORDER BY year, gender, rank
);

4. 保存されたテーブルを確認します。

-- 処理: 数十 KB 程度（保存した数十行を読むだけ）
SELECT COUNT(*) AS row_count
FROM `PROJECT_ID.handson_ch04.top_names_2015_2020`;

期待される出力: 4-3 のハンズオンと同じ 36 行が保存されていることが row_count = 36 で確認できます。

後片付け: この章で作成したリソースをすべて削除します。

bq rm -r -f -d PROJECT_ID:handson_ch04