Open28

SQL表現やTips

Kyosuke KuboKyosuke Kubo

SQLを素早く書けるようになりたいので、基本的な表現は体に染み込ませたい。
そのため雑にだがまとめる

Kyosuke KuboKyosuke Kubo

itemsといった配列のカラムがある場合FROM句に付与することで展開できる。

FROM
   table,
   UNNEST(items) AS items
Kyosuke KuboKyosuke Kubo

GTMとGAの関係と流れの整理 📬

Geminiから引用

もう少し具体的に流れを整理すると、以下のようになります。

【準備】WebサイトにGTMを設置する
最初に一度だけ、GTMの基本コード（コンテナコードと呼ばれます）をウェブサイトの全ページに設置します。これはサイト開発者が行う作業です。これで、サイトとGTMが通信できる状態になります。

【設定】GTMで「ルール」を設定する
ここが最も重要な部分です。あなたはGTMの管理画面で、「どんなときに（トリガー）、何をする（タグ）」というルールをたくさん設定します。

例：「購入完了ページが表示されたら（トリガー）、購入情報をGA4に送信する（タグ）」

この「タグ」や「トリガー」をGTMで設定しておくのが、「コードに関連する実装を設定」するということです。サイトのHTMLコードを直接編集するわけではありません。

【実行】GAがデータを「受信」してレポート化する
準備と設定が終われば、あとは自動です。
ユーザーがサイトを訪れ、あなたがGTMで設定した「トリガー」の条件を満たす行動（例：購入完了ページを表示）をすると、GTMは自動でそれに紐づいた「タグ」を実行し、GAにデータを送信します。

GA側は、GTMから送られてきたデータをただ受信して、蓄積し、レポート画面に反映させます。GA自身が能動的に「サイトを見に行って情報を集める」わけではなく、GTMから送られてくるのを待っているイメージです。

Kyosuke KuboKyosuke Kubo

REGEXP_EXTRACTを使うことで文字列から正規表現で抽出することができる。

REGEXP_EXTRACT('https://www.example.com/page', r'https://([^/]+)')

（）がある部分を出力する

Kyosuke KuboKyosuke Kubo

正規表現で判定する場合は？

REGEXP_CONTAINS(phone, r'^090')
-- trueとなる
Kyosuke KuboKyosuke Kubo

CASTとSAFECASTの違い

SELECT CAST("123" AS INT64);  -- 123を返す
SELECT CAST("abc" AS INT64);  -- エラーになる

SELECT SAFE_CAST("123" AS INT64);  -- 123を返す
SELECT SAFE_CAST("abc" AS INT64);  -- NULLを返す（エラーではない）
Kyosuke KuboKyosuke Kubo

タイムスタンプ型から日付を取り出す場合はDATE(), 文字列などの型を変換する場合はCAST( AS DATE)を使う

SELECT DATE(TIMESTAMP "2025-08-21 12:34:56");
-- 結果: 2025-08-21

SELECT CAST("2025-08-21" AS DATE);
-- 結果: 2025-08-21
Kyosuke KuboKyosuke Kubo

CASE文

CASE
    WHEN platform = 'APP' THEN 'アプリ'
    WHEN  platform = 'WEB' THEN 'WEB'
    ELSE 'others'
END
Kyosuke KuboKyosuke Kubo

条件が一つの場合、当てはまらない場合NULLになる

SELECT
    CASE WHEN score >= 60 THEN '合格' END AS result
FROM exam;
Kyosuke KuboKyosuke Kubo

• INNER JOIN → 共通部分だけ
• LEFT JOIN → 左は必ず残る、右はなければ NULL

行が重複していればその文増えてしまう点注意

Kyosuke KuboKyosuke Kubo

• 秒単位 → TIMESTAMP_SECONDS()
• ミリ秒単位 → TIMESTAMP_MILLIS()
• マイクロ秒単位 → TIMESTAMP_MICROS()

Kyosuke KuboKyosuke Kubo

タイムスタンプとタイムゾーン指定でJSTに変換可能

DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo')
-- 結果: 2024-12-22 09:00:00
Kyosuke KuboKyosuke Kubo

WEB：page_viewでpage_locationからURLを取得可能。
APP：screen_viewでfirebase_screenからURLを取得可能。

Kyosuke KuboKyosuke Kubo

DATE_TRUNC

-- 月初に切り捨て
SELECT DATE_TRUNC(DATE '2025-08-21', MONTH);
-- 結果: 2025-08-01

-- 年初に切り捨て
SELECT DATE_TRUNC(DATE '2025-08-21', YEAR);
-- 結果: 2025-01-01
Kyosuke KuboKyosuke Kubo

MySQL: DATE_FORMAT

-- 年月で表示
SELECT DATE_FORMAT('2025-08-21', '%Y-%m');
-- 結果: '2025-08'

-- 任意フォーマット
SELECT DATE_FORMAT('2025-08-21', '%Y/%m/%d');
-- 結果: '2025/08/21'

BigQuery: FORMAT_DATE

-- 年月で表示
SELECT FORMAT_DATE('%Y-%m', DATE '2025-08-21');
-- 結果: '2025-08'

-- 任意フォーマット
SELECT FORMAT_DATE('%Y/%m/%d', DATE '2025-08-21');
-- 結果: '2025/08/21'
Kyosuke KuboKyosuke Kubo
-- 10日後
SELECT DATE_ADD(DATE '2025-08-21', INTERVAL 10 DAY);
-- 結果: 2025-08-31

-- 2か月後
SELECT DATE_ADD(DATE '2025-08-21', INTERVAL 2 MONTH);
-- 結果: 2025-10-21
Kyosuke KuboKyosuke Kubo
-- 7日前
SELECT DATE_SUB(DATE '2025-08-21', INTERVAL 7 DAY);
-- 結果: 2025-08-14

-- 1年前
SELECT DATE_SUB(DATE '2025-08-21', INTERVAL 1 YEAR);
-- 結果: 2024-08-21
Kyosuke KuboKyosuke Kubo

GAログで日付範囲指定する際

WHERE _TABLE_SUFFIX BETWEEN '20250820' AND '20250821'
Kyosuke KuboKyosuke Kubo

ウィンドウ関数とは行を潰さずに集計結果を列として追加するための記法である。

簡単な例

SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date) AS cumulative_sum,
RANK()       OVER (ORDER BY amount DESC) AS rank,
DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_rank,
ROW_NUMBER() OVER (ORDER BY amount DESC) AS row_num
Kyosuke KuboKyosuke Kubo

こんな感じの指定で

  • ユーザーIDごとに分割
  • それらをdatetimeで並び替える
  • その上で各行においてそれ以降を対象にして最小の購入日時を出力するようにしたりすることも可能
MIN(purchase_datetime) OVER (
      PARTITION BY user_pseudo_id 
      ORDER BY event_jst_datetime 
      RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    ) as next_purchase_datetime
Kyosuke KuboKyosuke Kubo
-- GA4のイベントパラメータから値を取得する一時関数
CREATE TEMP FUNCTION get_value(
  params ANY TYPE,  -- イベントパラメータ配列
  name STRING       -- パラメータ名
) AS (
  (
    SELECT 
      COALESCE(
        value.string_value,                    -- 文字列値
        CAST(value.int_value AS STRING),       -- 整数値
        CAST(value.float_value AS STRING),     -- 浮動小数点数
        CAST(value.double_value AS STRING)     -- 倍精度浮動小数点数
      )
    FROM 
      UNNEST(params) AS x
    WHERE 
      x.key = name
  )
);
Kyosuke KuboKyosuke Kubo

COALESCEは左から確認していってNULLだったら右の値を返していく。
なので以下のような感じでNULLだったらデフォルト値を出力することも可能

SELECT COALESCE(user_name, 'ゲスト') AS display_name
FROM users;
Kyosuke KuboKyosuke Kubo

QUALIFY：ウィンドウ関数で計算された結果に対して条件をかける

Kyosuke KuboKyosuke Kubo

たとえばこんな感じで、user_eventに対してwindow関数を使って各ユーザーがどのくらい閲覧しているのかのカラムを追加。
QUALIFY句はWINDOW関数の計算結果にフィルターかける際の表現

SELECT
    *,
    COUNT(DISTINCT CAST(item_id AS INT64)) OVER (PARTITION BY user_pseudo_id) as unique_item_viewed
  FROM
    user_events
  WHERE
    SAFE_CAST(item_id AS INT64) IS NOT NULL
  QUALIFY
    COUNT(DISTINCT CAST(item_id AS INT64)) OVER (PARTITION BY user_pseudo_id) >= 3