BigQuery SQL cheat sheet

1. 基本クエリ

テーブルから特定の列を選択し、条件に基づいてフィルタリングし、ソートして結果を制限

SELECT column1, column2
FROM dataset.table
WHERE condition
ORDER BY column
LIMIT 10;

2. 集計関数

集計関数を使用して列の数、合計、平均、最小値、最大値を取得

SELECT
  COUNT(column) AS count_column,  -- 列の行数をカウント
  SUM(column) AS sum_column,      -- 列の合計を計算
  AVG(column) AS avg_column,      -- 列の平均値を計算
  MIN(column) AS min_column,      -- 列の最小値を取得
  MAX(column) AS max_column       -- 列の最大値を取得
FROM dataset.table
WHERE condition;

3. GROUP BY と HAVING

列でグループ化し、グループごとに行数をカウントし、特定の条件を満たすグループをフィルタリング

SELECT
  column,
  COUNT(*) AS count
FROM dataset.table
GROUP BY column
HAVING count > 1;  -- 行数が1を超えるグループのみを表示

4. JOIN

INNER JOIN: 共通の列を基に2つのテーブルを結合
LEFT JOIN: 左側のテーブルのすべての行と、共通の列が一致する右側のテーブルの行を結合

-- INNER JOIN
SELECT
  a.column1,
  b.column2
FROM dataset.table1 AS a
INNER JOIN dataset.table2 AS b
ON a.common_column = b.common_column;

-- LEFT JOIN:
SELECT
  a.column1,
  b.column2
FROM dataset.table1 AS a
LEFT JOIN dataset.table2 AS b
ON a.common_column = b.common_column;

5. サブクエリ

サブクエリを使用してメインクエリ内で他のクエリ結果を取得

SELECT
  column1,
  (SELECT MAX(column2) FROM dataset.table2 WHERE condition) AS max_column2
FROM dataset.table1
WHERE condition;

6. ウィンドウ関数

ウィンドウ関数を使用してグループ内での行数を計算

SELECT
  column,
  COUNT(*) OVER(PARTITION BY column ORDER BY column2) AS count_column
FROM dataset.table;

7. 日付関数

日付関数を使用して現在の日付、日付の加算・減算、日付の差を取得

SELECT
  CURRENT_DATE() AS current_date,  -- 現在の日付を取得
  DATE_ADD(CURRENT_DATE(), INTERVAL 1 DAY) AS next_day,  -- 現在の日付に1日を加算
  DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) AS previous_day,  -- 現在の日付から1日を減算
  DATE_DIFF(CURRENT_DATE(), DATE '2023-01-01', DAY) AS days_diff;  -- 指定日付との日数の差を計算

8. 配列操作

配列の生成と操作

SELECT
  [1, 2, 3] AS array_example,  -- 配列を生成
  ARRAY_LENGTH([1, 2, 3]) AS array_length,  -- 配列の長さを取得
  ARRAY_TO_STRING([1, 2, 3], ', ') AS array_string;  -- 配列を文字列に変換

9. CASE文

CASE文を使用して条件に基づいて異なる結果を返す

SELECT
  column,
  CASE
    WHEN condition1 THEN 'Result1'  -- condition1が真の場合
    WHEN condition2 THEN 'Result2'  -- condition2が真の場合
    ELSE 'Result3'  -- 上記の条件に一致しない場合
  END AS case_result
FROM dataset.table;

10. 正規表現

正規表現を使用して列の内容を検索、抽出、置換

SELECT
  column,
  REGEXP_CONTAINS(column, r'pattern') AS contains_pattern,  -- パターンが含まれているかを確認
  REGEXP_EXTRACT(column, r'pattern') AS extract_pattern,  -- パターンに一致する部分を抽出
  REGEXP_REPLACE(column, r'pattern', 'replacement') AS replace_pattern  -- パターンに一致する部分を置換
FROM dataset.table;

11. CTE (Common Table Expressions)

WITH句を使用して複数のCTEを定義し、クエリ内で再利用

WITH CTE1 AS (
  SELECT column1, column2
  FROM dataset.table1
  WHERE condition
),
CTE2 AS (
  SELECT column3, column4
  FROM dataset.table2
  WHERE condition
)
SELECT
  CTE1.column1,
  CTE2.column3
FROM CTE1
JOIN CTE2 ON CTE1.column2 = CTE2.column4;

12. ウィンドウ関数の応用

複雑なウィンドウ関数を使用して、前後の値やランクを計算

SELECT
  column,
  LAG(column) OVER (PARTITION BY partition_column ORDER BY order_column) AS lag_column,  -- 前の行の値
  LEAD(column) OVER (PARTITION BY partition_column ORDER BY order_column) AS lead_column,  -- 次の行の値
  ROW_NUMBER() OVER (PARTITION BY partition_column ORDER BY order_column) AS row_number,  -- 行番号
  RANK() OVER (PARTITION BY partition_column ORDER BY order_column) AS rank  -- ランク
FROM dataset.table;

13. レコード操作

STRUCT型とREPEATEDフィールドを使用して複雑なデータを操作

SELECT
  STRUCT(column1, column2) AS struct_example,  -- STRUCT型のフィールドを作成
  ARRAY(SELECT AS STRUCT column3, column4 FROM dataset.table2) AS repeated_example  -- REPEATEDフィールドを作成
FROM dataset.table1;

14. JSON操作

JSON_EXTRACT, JSON_EXTRACT_SCALARを使用してJSONデータを操作

SELECT
  JSON_EXTRACT(json_column, '$.field') AS json_field,  -- JSONフィールドを抽出
  JSON_EXTRACT_SCALAR(json_column, '$.field') AS json_field_scalar  -- JSONフィールドをスカラー値として抽出
FROM dataset.table;

15. 窓枠集計

窓枠集計を使用して移動平均などを計算

SELECT
  column,
  SUM(column) OVER w AS sum_column,  -- 窓枠内の合計を計算
  AVG(column) OVER w AS avg_column  -- 窓枠内の平均を計算
FROM dataset.table
WINDOW w AS (PARTITION BY partition_column ORDER BY order_column ROWS BETWEEN 2 PRECEDING AND CURRENT ROW);  -- 窓枠を定義

16. 時系列分析

時系列データを操作して移動平均や前後の値を取得

SELECT
  timestamp_column,
  column,
  LAG(column) OVER (ORDER BY timestamp_column) AS previous_value,  -- 前の時点の値
  LEAD(column) OVER (ORDER BY timestamp_column) AS next_value,  -- 次の時点の値
  AVG(column) OVER (ORDER BY timestamp_column ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg  -- 移動平均
FROM dataset.table;

17. パーティショニングとクラスタリング

パーティショニングとクラスタリングを使用してテーブルを効率化

CREATE TABLE dataset.partitioned_table
PARTITION BY DATE(timestamp_column)  -- 日付でパーティション分割
CLUSTER BY column1, column2 AS  -- クラスタリングキーを指定
SELECT * FROM dataset.original_table;

-- パーティションテーブルのクエリ
SELECT *
FROM dataset.partitioned_table
WHERE _PARTITIONDATE = '2024-01-01';  -- 特定のパーティションの日付をフィルタリング

18. User-Defined Functions (UDFs)

JavaScriptを使用してカスタム関数を定義し、クエリで使用

CREATE TEMP FUNCTION custom_function(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js AS """
  return x * y;
""";

SELECT
  column1,
  custom_function(column1, column2) AS custom_result  -- カスタム関数を使用
FROM dataset.table;

19. ML (Machine Learning)

BigQuery MLを使用してモデルを作成し、予測を実行

CREATE OR REPLACE MODEL dataset.model_name
OPTIONS(model_type='linear_reg', input_label_cols=['label_column']) AS  -- 線形回帰モデルを作成
SELECT * FROM dataset.training_data;

-- 予測の実行
SELECT
  *
FROM
  ML.PREDICT(MODEL dataset.model_name, (SELECT * FROM dataset.new_data));  -- 新しいデータに対して予測を実行

20. メタデータ関数

テーブルのメタデータを取得

SELECT
  table_catalog,
  table_schema,
  table_name,
  column_name,
  data_type
FROM dataset.INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'table_name';

-- テーブルサイズの取得
SELECT
  table_id,
  row_count,
  size_bytes
FROM dataset.__TABLES__
WHERE table_id = 'table_name';