私的BigQueryでマジで使える関数たち

はじめに

やっほー😄
このガイドは、私的BigQueryでマジで使える関数とその実践的な使用例を提供します✌️
各関数の説明は公式ドキュメントに基づいており、実際のユースケースに即した例を含んでいます🙃

特に"SUM() OVER()"は詳しめに書いてます。

※自分の学習用メモとしての意味合いが強いです🚶

日付/時間関数

TIMESTAMP_SECONDS(int64_expression)

UNIXタイムスタンプ（秒）をTIMESTAMP型に変換

-- Unix時間からタイムスタンプへの変換
SELECT 
  TIMESTAMP_SECONDS(1577836800) as timestamp_value,
  -- 結果: 2020-01-01 00:00:00 UTC
  
  -- データからの変換例
  TIMESTAMP_SECONDS(CAST(JSON_EXTRACT_SCALAR(data, '$.createdAt._seconds') AS INT64)) as created_at
FROM your_table;

DATE(timestamp_expression)

TIMESTAMP型からDATE型に変換

-- タイムスタンプから日付への変換
SELECT
  DATE(CURRENT_TIMESTAMP()) as today,
  -- 結果: 2024-03-21
  
  -- タイムスタンプフィールドからの日付抽出
  DATE(TIMESTAMP_SECONDS(1577836800)) as event_date
  -- 結果: 2020-01-01
FROM your_table;

DATE_SUB(date_expression, INTERVAL int64_expression date_part)

指定した日付から特定の期間を引いた日付を返す

-- 日付の減算
SELECT
  CURRENT_DATE() as today,
  DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) as week_ago,
  DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH) as month_ago,
  DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR) as year_ago;

-- 期間の分析での使用例
WITH date_ranges AS (
  SELECT
    DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) as start_date,
    CURRENT_DATE() as end_date
)
SELECT *
FROM your_table
WHERE DATE(event_timestamp) BETWEEN 
  (SELECT start_date FROM date_ranges) 
  AND 
  (SELECT end_date FROM date_ranges);

DATE_DIFF(date_expression1, date_expression2, date_part)

2つの日付間の差分を指定した単位で返す

-- 日付の差分計算
SELECT
  DATE_DIFF('2024-01-01', '2023-12-01', DAY) as days_difference,
  -- 結果: 31
  
  DATE_DIFF('2024-01-01', '2023-01-01', MONTH) as months_difference,
  -- 結果: 12
  
  DATE_DIFF('2024-01-01', '2023-01-01', YEAR) as years_difference;
  -- 結果: 1

-- ユーザーの登録からの経過日数計算例
SELECT
  user_id,
  DATE_DIFF(
    CURRENT_DATE(),
    DATE(TIMESTAMP_SECONDS(CAST(JSON_EXTRACT_SCALAR(data, '$.createdAt._seconds') AS INT64))),
    DAY
  ) as days_since_registration
FROM users_table;

DATE_TRUNC(date_expression, date_part)

日付を指定した精度で切り捨て

-- 日付の切り捨て
SELECT
  DATE_TRUNC('2024-03-15', MONTH) as month_start,
  -- 結果: 2024-03-01
  
  DATE_TRUNC('2024-03-15', YEAR) as year_start,
  -- 結果: 2024-01-01
  
  DATE_TRUNC('2024-03-15', WEEK) as week_start;
  -- 結果: 2024-03-10（週の開始日）

-- 月次集計での使用例
SELECT
  DATE_TRUNC(date_column, MONTH) as month,
  COUNT(*) as monthly_count
FROM your_table
GROUP BY DATE_TRUNC(date_column, MONTH)
ORDER BY month;

集計関数

COUNT(expression)

行数または非NULL値の数をカウント

-- 基本的なカウント
SELECT COUNT(*) as total_rows FROM your_table;

-- 特定カラムの非NULL値のカウント
SELECT COUNT(column_name) as non_null_count FROM your_table;

-- ユニークな値のカウント
SELECT COUNT(DISTINCT column_name) as unique_count FROM your_table;

-- グループごとのカウント
SELECT 
  category,
  COUNT(*) as category_count,
  COUNT(DISTINCT user_id) as unique_users
FROM your_table
GROUP BY category;

SUM(expression)

数値の合計を計算

-- 単純な合計
SELECT SUM(amount) as total_amount FROM your_table;

-- グループごとの合計
SELECT 
  category,
  SUM(amount) as category_total,
  SUM(CASE 
    WHEN status = 'completed' THEN amount 
    ELSE 0 
  END) as completed_amount
FROM your_table
GROUP BY category;

-- 累計計算
SELECT 
  date,
  amount,
  SUM(amount) OVER (
    ORDER BY date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) as running_total
FROM your_table;

AVG(expression)

平均値を計算

-- 平均値計算
SELECT AVG(amount) as average_amount FROM your_table;

-- グループごとの平均
SELECT 
  category,
  AVG(amount) as avg_amount,
  MIN(amount) as min_amount,
  MAX(amount) as max_amount
FROM your_table
GROUP BY category;

-- 移動平均の計算
SELECT 
  date,
  amount,
  AVG(amount) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) as moving_average_7days
FROM your_table;

ウィンドウ関数

ROW_NUMBER()

パーティション内の各行に一意の連番を割り当て

-- 単純な連番付与
SELECT 
  *,
  ROW_NUMBER() OVER (ORDER BY date) as row_num
FROM your_table;

-- グループ内での連番付与
SELECT 
  *,
  ROW_NUMBER() OVER (
    PARTITION BY category 
    ORDER BY date DESC
  ) as category_row_num
FROM your_table;

-- 最新レコードの取得
WITH numbered_rows AS (
  SELECT 
    *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id 
      ORDER BY timestamp DESC
    ) as row_num
  FROM your_table
)
SELECT * FROM numbered_rows WHERE row_num = 1;

SUM() OVER()

ウィンドウ内での累計を計算する関数です。特に時系列データの分析や累積値の計算に有用です。

基本的な使い方

-- 累計計算
SELECT 
  date,
  amount,
  SUM(amount) OVER (
    ORDER BY date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) as running_total,
  
  -- カテゴリごとの累計
  SUM(amount) OVER (
    PARTITION BY category
    ORDER BY date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) as category_running_total
FROM your_table;

-- 移動合計
SELECT 
  date,
  amount,
  SUM(amount) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) as rolling_7day_sum
FROM your_table;

動作の詳細説明

例えば、以下のような売上データがあるとします：

-- サンプルデータ
date        | category | amount
2024-01-01  | A       | 100
2024-01-01  | B       | 150
2024-01-02  | A       | 200
2024-01-02  | B       | 250
2024-01-03  | A       | 300

1. 単純な累計計算:

SELECT
  date,
  amount,
  SUM(amount) OVER (ORDER BY date) as running_total
FROM sales_data

結果:

date        | amount | running_total
2024-01-01  | 100   | 250    -- 100 + 150
2024-01-01  | 150   | 250    -- 同じ日付なので同じ累計
2024-01-02  | 200   | 700    -- 250 + 200 + 250
2024-01-02  | 250   | 700    -- 同じ日付なので同じ累計
2024-01-03  | 300   | 1000   -- 700 + 300

2. カテゴリごとの累計:

SELECT
  date,
  category,
  amount,
  SUM(amount) OVER (
    PARTITION BY category
    ORDER BY date
  ) as category_total
FROM sales_data

結果:

date        | category | amount | category_total
2024-01-01  | A       | 100    | 100   -- カテゴリAの初日
2024-01-02  | A       | 200    | 300   -- A: 100 + 200
2024-01-03  | A       | 300    | 600   -- A: 300 + 200 + 100
2024-01-01  | B       | 150    | 150   -- カテゴリBの初日
2024-01-02  | B       | 250    | 400   -- B: 150 + 250

重要なコンポーネント

1. OVER句の要素:

   • ORDER BY: 累計を計算する順序
   • PARTITION BY: グループごとに別々の累計を計算
   • ROWS BETWEEN: 計算対象となる行の範囲を指定

2. 行の範囲指定:

   • UNBOUNDED PRECEDING: パーティションの最初から
   • CURRENT ROW: 現在の行まで
   • n PRECEDING: 現在の行から前n行

実践的な使用例

-- 月ごとの売上と累計、前年同期比を計算
SELECT
  DATE_TRUNC(date, MONTH) as month,
  SUM(amount) as monthly_sales,
  SUM(SUM(amount)) OVER (
    ORDER BY DATE_TRUNC(date, MONTH)
  ) as cumulative_sales,
  ROUND(
    SUM(amount) / LAG(SUM(amount), 12) OVER (
      ORDER BY DATE_TRUNC(date, MONTH)
    ) * 100 - 100,
    2
  ) as yoy_growth
FROM sales_data
GROUP BY DATE_TRUNC(date, MONTH)
ORDER BY month;

-- 7日間の移動平均売上を計算
SELECT
  date,
  amount,
  ROUND(
    AVG(amount) OVER (
      ORDER BY date
      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ),
    2
  ) as moving_average_7days
FROM sales_data
ORDER BY date;

パフォーマンスに関する注意点

1. パーティションサイズ：

   • 大きすぎるパーティションは処理時間とメモリ使用量に影響
   • 必要に応じて日付範囲でフィルタリング

2. インデックス：

   • ORDER BY句で使用するカラムのインデックスが重要
   • パーティションカラムのインデックスも考慮

3. メモリ使用：

   • 移動平均や移動合計の範囲を適切に設定
   • 必要最小限のカラムのみを選択

JSON関数

JSON_EXTRACT(json_string, json_path)

JSON文字列から指定したパスの値を抽出

-- JSONフィールドから値を抽出
SELECT
  JSON_EXTRACT(data, '$.user.name') as user_name,
  JSON_EXTRACT(data, '$.user.email') as email
FROM your_table;

-- ネストされたJSONの処理
SELECT
  JSON_EXTRACT(data, '$.addresses[0].city') as primary_city,
  JSON_EXTRACT(data, '$.addresses[*].city') as all_cities
FROM your_table;

JSON_EXTRACT_SCALAR(json_string, json_path)

JSON文字列から単一の値を抽出（文字列として）

-- スカラー値の抽出
SELECT
  JSON_EXTRACT_SCALAR(data, '$.age') as age,
  CAST(JSON_EXTRACT_SCALAR(data, '$.age') AS INT64) as age_number,
  JSON_EXTRACT_SCALAR(data, '$.contact.email') as email
FROM your_table;

-- タイムスタンプの処理
SELECT
  TIMESTAMP_SECONDS(
    CAST(
      JSON_EXTRACT_SCALAR(data, '$.createdAt._seconds')
    AS INT64)
  ) as created_at
FROM your_table;

JSON_EXTRACT_ARRAY(json_string, json_path)

JSON文字列から配列を抽出

-- 配列の抽出
SELECT
  JSON_EXTRACT_ARRAY(data, '$.tags') as tags,
  ARRAY_LENGTH(JSON_EXTRACT_ARRAY(data, '$.tags')) as tag_count
FROM your_table;

-- 配列要素の処理
SELECT
  JSON_EXTRACT_ARRAY(data, '$.items[*].name') as item_names,
  JSON_EXTRACT_ARRAY(data, '$.items[*].price') as item_prices
FROM your_table;

文字列関数

CONCAT(string1, string2 [, stringN])

文字列を連結

-- 基本的な文字列結合
SELECT
  CONCAT(first_name, ' ', last_name) as full_name,
  CONCAT('ID: ', CAST(id AS STRING)) as id_label
FROM your_table;

-- 複数フィールドの結合
SELECT
  CONCAT(
    'User ', 
    user_id, 
    ' created on ', 
    FORMAT_TIMESTAMP('%Y-%m-%d', created_at)
  ) as user_info
FROM your_table;

FORMAT_TIMESTAMP(format_string, timestamp)

タイムスタンプを指定したフォーマットの文字列に変換

-- タイムスタンプのフォーマット
SELECT
  timestamp_field,
  FORMAT_TIMESTAMP('%Y-%m-%d', timestamp_field) as date_only,
  FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', timestamp_field) as date_time,
  FORMAT_TIMESTAMP('%B %Y', timestamp_field) as month_year
FROM your_table;

-- ローカライズされた日付フォーマット
SELECT
  FORMAT_TIMESTAMP(
    '%Y年%m月%d日', 
    timestamp_field, 
    'Asia/Tokyo'
  ) as japanese_date
FROM your_table;

型変換関数

CAST(expression AS type)

指定した型にデータを変換

-- 基本的な型変換
SELECT
  CAST("123" AS INT64) as number,
  CAST(123 AS STRING) as string,
  CAST("2024-01-01" AS DATE) as date;

-- 複雑な変換例
SELECT
  CAST(JSON_EXTRACT_SCALAR(data, '$.amount') AS FLOAT64) as amount,
  CAST(JSON_EXTRACT_SCALAR(data, '$.timestamp') AS TIMESTAMP) as timestamp,
  CAST(ROUND(number, 2) AS STRING) as formatted_number
FROM your_table;

条件式と制御フロー

CASE

条件分岐を実行

-- 基本的な条件分岐
SELECT
  value,
  CASE
    WHEN value > 100 THEN 'High'
    WHEN value > 50 THEN 'Medium'
    ELSE 'Low'
  END as category
FROM your_table;

-- 集計での使用
SELECT
  category,
  SUM(CASE 
    WHEN status = 'completed' THEN amount 
    ELSE 0 
  END) as completed_amount,
  COUNT(CASE 
    WHEN status = 'pending' THEN 1 
  END) as pending_count
FROM your_table
GROUP BY category;

COALESCE(expression1, expression2 [, expressionN])

最初の非NULL値を返す

-- NULL値の処理
SELECT
  COALESCE(field1, field2, 'default') as non_null_value,
  COALESCE(
    JSON_EXTRACT_SCALAR(data, '$.name'),
    JSON_EXTRACT_SCALAR(data, '$.username'),
    'anonymous'
  ) as user_identifier
FROM your_table;

配列と集合関数

ARRAY_LENGTH()

配列の要素数を返す

-- 配列の長さ計算
SELECT
  ARRAY_LENGTH(['a', 'b', 'c']) as static_array_length,
  ARRAY_LENGTH(JSON_EXTRACT_ARRAY(data, '$.items')) as items_count
FROM your_table;

ARRAY_AGG()

グループ化された配列を返す

-- グループ化された配列の作成
SELECT
  category,
  ARRAY_AGG(DISTINCT user_id) as unique_users,
  ARRAY_AGG(STRUCT(user_id, amount)) as user_amounts
FROM your_table
GROUP BY category;

数学関数

ROUND()

数値を指定した小数点以下の桁数で四捨五入する

-- 数値の丸め
SELECT
  ROUND(123.456, 2) as two_decimals,  -- 123.46
  ROUND(123.456, 0) as no_decimals,   -- 123
  ROUND(123.456, -1) as tens_place;   -- 120

-- パーセンテージ計算での使用
SELECT
  category,
  count,
  total,
  ROUND(count / total * 100, 2) as percentage
FROM your_stats_table;

重要なテクニック

WITH句（Common Table Expression）

一時的な名前付きの結果セットを定義する
→複雑なクエリの可読性向上、サブクエリの再利用が可能

-- 複数のCTEの連鎖
WITH 
user_stats AS (
  SELECT
    user_id,
    COUNT(*) as activity_count,
    SUM(amount) as total_amount
  FROM activities
  GROUP BY user_id
),
user_rankings AS (
  SELECT
    *,
    ROW_NUMBER() OVER (ORDER BY activity_count DESC) as activity_rank,
    ROW_NUMBER() OVER (ORDER BY total_amount DESC) as amount_rank
  FROM user_stats
)
SELECT
  u.*,
  r.activity_rank,
  r.amount_rank
FROM users u
JOIN user_rankings r ON u.id = r.user_id;

パフォーマンス最適化テクニック

-- パーティション化されたテーブルでの効率的なクエリ
SELECT *
FROM your_partitioned_table
WHERE DATE(_PARTITIONTIME) BETWEEN 
  DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
  AND CURRENT_DATE();

-- クラスタ化されたカラムを使用した効率的なフィルタリング
SELECT *
FROM your_clustered_table
WHERE category = 'electronics'  -- クラスタ化されたカラム
  AND DATE(timestamp) >= '2024-01-01'  -- パーティションカラム
ORDER BY category;  -- クラスタ化されたカラムでのソート

高度な分析テクニック

ウィンドウ関数の組み合わせ

-- 移動平均と順位付けの組み合わせ
SELECT
  date,
  value,
  AVG(value) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) as moving_avg_7days,
  RANK() OVER (ORDER BY value DESC) as value_rank,
  DENSE_RANK() OVER (ORDER BY value DESC) as dense_value_rank
FROM your_metrics_table;

-- 累積パーセンテージの計算
SELECT
  category,
  value,
  SUM(value) OVER (ORDER BY value DESC) as running_total,
  ROUND(
    SUM(value) OVER (ORDER BY value DESC) /
    SUM(value) OVER () * 100,
    2
  ) as cumulative_percentage
FROM your_table;

時系列分析

-- 前期比較
SELECT
  current_period.date,
  current_period.value as current_value,
  previous_period.value as previous_value,
  ROUND(
    (current_period.value - previous_period.value) / 
    previous_period.value * 100,
    2
  ) as growth_percentage
FROM your_table current_period
LEFT JOIN your_table previous_period
  ON current_period.date = DATE_ADD(previous_period.date, INTERVAL 1 YEAR)
WHERE current_period.date >= '2024-01-01';

-- 期間ごとの集計
WITH daily_metrics AS (
  SELECT
    DATE(timestamp) as date,
    SUM(value) as daily_value
  FROM your_table
  GROUP BY DATE(timestamp)
)
SELECT
  date,
  daily_value,
  AVG(daily_value) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) as moving_avg_7days,
  AVG(daily_value) OVER (
    ORDER BY date
    ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
  ) as moving_avg_30days
FROM daily_metrics
ORDER BY date DESC;

コホート分析

-- ユーザーコホート分析
WITH user_first_activity AS (
  SELECT
    user_id,
    DATE_TRUNC(DATE(MIN(timestamp)), MONTH) as cohort_month,
    DATE_TRUNC(DATE(timestamp), MONTH) as activity_month
  FROM user_activities
  GROUP BY user_id, DATE_TRUNC(DATE(timestamp), MONTH)
),
cohort_size AS (
  SELECT
    cohort_month,
    COUNT(DISTINCT user_id) as users
  FROM user_first_activity
  GROUP BY cohort_month
),
cohort_retention AS (
  SELECT
    a.cohort_month,
    a.activity_month,
    COUNT(DISTINCT a.user_id) as active_users,
    DATE_DIFF(
      a.activity_month,
      a.cohort_month,
      MONTH
    ) as month_number
  FROM user_first_activity a
  GROUP BY cohort_month, activity_month
)
SELECT
  c.cohort_month,
  s.users as cohort_size,
  c.month_number,
  c.active_users,
  ROUND(c.active_users / s.users * 100, 2) as retention_rate
FROM cohort_retention c
JOIN cohort_size s ON c.cohort_month = s.cohort_month
ORDER BY c.cohort_month, c.month_number;

ファネル分析

-- イベントファネルの分析
WITH funnel_stages AS (
  SELECT
    user_id,
    MIN(CASE WHEN event = 'view' THEN timestamp END) as view_time,
    MIN(CASE WHEN event = 'add_to_cart' THEN timestamp END) as cart_time,
    MIN(CASE WHEN event = 'purchase' THEN timestamp END) as purchase_time
  FROM user_events
  WHERE DATE(timestamp) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
  GROUP BY user_id
)
SELECT
  COUNT(view_time) as views,
  COUNT(cart_time) as cart_adds,
  COUNT(purchase_time) as purchases,
  ROUND(COUNT(cart_time) / COUNT(view_time) * 100, 2) as view_to_cart_rate,
  ROUND(COUNT(purchase_time) / COUNT(cart_time) * 100, 2) as cart_to_purchase_rate,
  ROUND(COUNT(purchase_time) / COUNT(view_time) * 100, 2) as overall_conversion_rate
FROM funnel_stages;

一時関数

CREATE TEMPORARY FUNCTION

クエリ内でのみ有効な一時的な関数を定義

-- 基本的な一時関数の定義
CREATE TEMPORARY FUNCTION _START_DATE() AS (DATE_SUB(CURRENT_DATE(), INTERVAL 12 WEEK));
CREATE TEMPORARY FUNCTION _END_DATE() AS (CURRENT_DATE());

-- 一時関数を使用した期間指定の例
SELECT *
FROM your_table
WHERE date BETWEEN _START_DATE() AND _END_DATE();

時間操作関数

TIMESTAMP_TRUNC(timestamp_expression, date_part[, time_zone])

タイムスタンプを指定した精度で切り捨てる関数。
タイムゾーンを指定することで、特定のタイムゾーンでの切り捨ても可能。

基本的な使い方

-- 基本的な切り捨て
SELECT
  CURRENT_TIMESTAMP() as original_timestamp,
  TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), MICROSECOND) as trunc_microsecond,
  TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), MILLISECOND) as trunc_millisecond,
  TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), SECOND) as trunc_second,
  TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), MINUTE) as trunc_minute,
  TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), HOUR) as trunc_hour,
  TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY) as trunc_day,
  TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), WEEK) as trunc_week,
  TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), MONTH) as trunc_month,
  TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), QUARTER) as trunc_quarter,
  TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), YEAR) as trunc_year;

タイムゾーンを指定した使用例

-- 異なるタイムゾーンでの切り捨て
SELECT
  CURRENT_TIMESTAMP() as original_timestamp,
  TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY, 'UTC') as day_start_utc,
  TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY, 'Asia/Tokyo') as day_start_jst,
  TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY, 'America/Los_Angeles') as day_start_pst;

-- 週の始まりの違いを確認
SELECT
  CURRENT_TIMESTAMP() as original_timestamp,
  TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), WEEK, 'UTC') as week_start_utc,
  TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), WEEK, 'Asia/Tokyo') as week_start_jst;

実践的な使用例

-- 時間帯ごとのイベント集計
SELECT
  TIMESTAMP_TRUNC(event_timestamp, HOUR, 'Asia/Tokyo') as hour_bucket,
  COUNT(*) as event_count
FROM
  your_events_table
WHERE
  DATE(event_timestamp) = CURRENT_DATE()
GROUP BY
  hour_bucket
ORDER BY
  hour_bucket;

-- 週次レポートの生成
SELECT
  TIMESTAMP_TRUNC(TIMESTAMP_MICROS(event_timestamp), WEEK, 'Asia/Tokyo') as week_start,
  COUNT(DISTINCT user_id) as unique_users,
  COUNT(*) as total_events
FROM
  your_events_table
GROUP BY
  week_start
ORDER BY
  week_start DESC;

-- 月初からの累計値の計算
WITH daily_metrics AS (
  SELECT
    TIMESTAMP_TRUNC(event_timestamp, DAY, 'Asia/Tokyo') as day_start,
    TIMESTAMP_TRUNC(event_timestamp, MONTH, 'Asia/Tokyo') as month_start,
    COUNT(*) as daily_events
  FROM
    your_events_table
  GROUP BY
    day_start,
    month_start
)
SELECT
  day_start,
  daily_events,
  SUM(daily_events) OVER (
    PARTITION BY month_start
    ORDER BY day_start
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) as cumulative_events
FROM
  daily_metrics
ORDER BY
  day_start;

注意点と重要な特徴

1. date_partの指定

• MICROSECOND: マイクロ秒を切り捨て
• MILLISECOND: ミリ秒を切り捨て
• SECOND: 秒を切り捨て
• MINUTE: 分を切り捨て
• HOUR: 時を切り捨て
• DAY: 日を切り捨て
• WEEK: 週を切り捨て（日曜日が週の始まり）
• MONTH: 月を切り捨て
• QUARTER: 四半期を切り捨て
• YEAR: 年を切り捨て

2. タイムゾーンの影響

• タイムゾーンを指定しない場合はUTCとして処理
• タイムゾーンを指定すると、そのタイムゾーンでの切り捨てを実行
• 週の始まりはタイムゾーンによって異なる場合がある

3. パフォーマンスの考慮事項

• インデックスの効果的な利用のため、切り捨て結果でパーティショニング
• 大量データの処理時は適切な期間でフィルタリング

パフォーマンス最適化例

-- パーティション化されたテーブルでの効率的なクエリ
SELECT
  TIMESTAMP_TRUNC(event_timestamp, DAY, 'Asia/Tokyo') as day_bucket,
  COUNT(*) as event_count
FROM
  your_partitioned_events_table
WHERE
  -- パーティションキーでフィルタリング
  _PARTITIONTIME BETWEEN 
    TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY, 'Asia/Tokyo')
    AND TIMESTAMP_ADD(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY, 'Asia/Tokyo'), INTERVAL 1 DAY)
GROUP BY
  day_bucket
ORDER BY
  day_bucket;

TIMESTAMP_MICROS(int64_expression)

マイクロ秒単位のUNIXタイムスタンプをTIMESTAMP型に変換

-- マイクロ秒タイムスタンプの変換
SELECT
  TIMESTAMP_MICROS(1577836800000000) as timestamp_value,
  -- 結果: 2020-01-01 00:00:00 UTC

  -- イベントデータでの使用例
  TIMESTAMP_MICROS(event_timestamp) as event_time
FROM your_events_table;

安全な演算関数

SAFE_DIVIDE(numeric_expression1, numeric_expression2)

0除算を防ぎ、エラーの代わりにNULLを返す除算関数

-- 基本的な使用例
SELECT
  SAFE_DIVIDE(100, 2) as safe_result,  -- 50
  SAFE_DIVIDE(100, 0) as null_result;  -- NULL

-- 集計での使用例
SELECT
  category,
  total_count,
  success_count,
  ROUND(
    SAFE_DIVIDE(success_count, total_count) * 100,
    2
  ) as success_rate
FROM metrics_table;

FORMAT_DATE(format_string, date)

DATE型を指定したフォーマットの文字列に変換

-- 基本的なフォーマット
SELECT
  FORMAT_DATE('%Y%m%d', CURRENT_DATE()) as yyyymmdd,
  FORMAT_DATE('%Y-%m-%d', CURRENT_DATE()) as yyyy_mm_dd,
  FORMAT_DATE('%B %d, %Y', CURRENT_DATE()) as long_date;

-- 期間フィルタでの使用例
SELECT *
FROM your_table
WHERE 
  event_date BETWEEN 
    FORMAT_DATE('%Y%m%d', _START_DATE())
    AND FORMAT_DATE('%Y%m%d', _END_DATE());

パフォーマンス最適化関数

これらの関数を組み合わせた実践的な例：

-- 日付範囲を動的に設定し、週次のメトリクスを計算
CREATE TEMPORARY FUNCTION _START_DATE() AS (DATE_SUB(CURRENT_DATE(), INTERVAL 12 WEEK));
CREATE TEMPORARY FUNCTION _END_DATE() AS (CURRENT_DATE());

WITH period_metrics AS (
  SELECT
    FORMAT_TIMESTAMP(
      '%Y-%m-%d',
      TIMESTAMP_TRUNC(
        TIMESTAMP_MICROS(event_timestamp),
        WEEK,
        'Asia/Tokyo'
      )
    ) as metric_week,
    COUNT(DISTINCT user_id) as unique_users,
    COUNT(*) as total_events
  FROM
    `your_events_table`
  WHERE
    event_date BETWEEN
      FORMAT_DATE('%Y%m%d', _START_DATE())
      AND FORMAT_DATE('%Y%m%d', _END_DATE())
  GROUP BY
    metric_week
)
SELECT
  metric_week,
  unique_users,
  total_events,
  ROUND(
    SAFE_DIVIDE(total_events, unique_users),
    2
  ) as events_per_user
FROM
  period_metrics
ORDER BY
  metric_week DESC;

注意事項とベストプラクティス

1. パフォーマンス

• パーティション化されたカラムでフィルタリングを行う
• クラスタ化されたカラムを効果的に使用する
• 必要なカラムのみを選択する
• 適切なデータ型を使用する

2. コスト最適化

• クエリの実行前に見積もりを確認する
• 大きなテーブルではLIMIT句でテストを行う
• 中間テーブルを適切に活用する

3. メンテナンス性

• CTEを活用して複雑なクエリを整理する
• 適切なコメントを付ける
• 命名規則を統一する

4. エラー処理

• NULL値の適切な処理
• データ型の明示的な変換
• エラーが発生しやすい処理のテスト

おわりに

これらの関数と技術を組み合わせることで、効率的で保守性の高いBigQueryの実装が可能になります✌️また、具体的なユースケースに応じて、これらの基本的なパターンをカスタマイズすることで、
より複雑な分析要件にも対応できますぜ🕶️