BigQuery SQL応用｜ウィンドウ関数・JOIN・正規表現の実践ガイド

BigQueryで高度な分析を行うためにはSQLの応用構文を理解する必要があります。ウィンドウ関数や高度なJOIN正規表現などを使うことで通常の管理画面では見えない深い分析が可能になります。本章ではブログ運営者が実際に使えるBigQuery SQL応用テクニックを体系的に紹介します。SQLの基礎は第7章で解説しています。

この記事で分かること
・BigQueryで使えるSQL応用構文の体系
・ウィンドウ関数の種類と使い方
・JOINの種類と違い
・正規表現の抽出・置換テクニック
・GA4とSearch Consoleの応用分析例

ウィンドウ関数とは？

ウィンドウ関数は「行をまたいだ計算」を行うためのSQL構文です。ランキングや移動平均などの分析に使われます。基本構文は第7章で紹介しています。

ウィンドウ関数の種類一覧

BigQueryで使える代表的なウィンドウ関数は次のとおりです。

・ROW_NUMBER（連番）
・RANK（順位）
・DENSE_RANK（欠番なし順位）
・LAG（前の行の値）
・LEAD（次の行の値）
・OVER句（計算範囲の指定）

ROW_NUMBERでランキングを作る

SELECT query, SUM(clicks) AS total_clicks,
ROW_NUMBER() OVER(ORDER BY SUM(clicks) DESC) AS rank
FROM `project.dataset.searchdata`
GROUP BY query;

LAGでページ遷移を分析する（GA4）

GA4イベントデータの構造は第5章で紹介しています。

SELECT user_pseudo_id,
LAG(page_location) OVER(PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS previous_page,
page_location AS current_page
FROM `project.dataset.events`
WHERE event_name = 'page_view';

JOINの種類と違い

JOINは複数のテーブルを結合するための構文です。Search ConsoleとGA4の結合は第4章で紹介しています。

・INNER JOIN（共通データのみ）
・LEFT JOIN（左側を優先）
・RIGHT JOIN（右側を優先）
・FULL JOIN（両方のデータをすべて）

LEFT JOINで欠損データを補完する

SELECT s.url, SUM(s.clicks) AS clicks, COUNT(g.event_name) AS events
FROM `project.dataset.searchdata` AS s
LEFT JOIN `project.dataset.events` AS g
ON s.url = g.page_location
GROUP BY s.url;

正規表現（REGEXP）でデータを抽出・置換する

正規表現はURLのパターン抽出や文字列処理に使えます。

REGEXP_EXTRACT：カテゴリ名を抽出する

SELECT REGEXP_EXTRACT(url, r'/category/(.*?)/') AS category
FROM `project.dataset.searchdata`;

REGEXP_REPLACE：URLからクエリパラメータを除去する

SELECT REGEXP_REPLACE(url, r'\\?.*$', '') AS clean_url
FROM `project.dataset.searchdata`;

GA4の応用分析例

GA4イベントデータの構造は第5章で紹介しています。

セッション継続率を分析する

SELECT COUNT(*) AS sessions,
SUM(CASE WHEN session_engaged = 1 THEN 1 ELSE 0 END) AS engaged_sessions
FROM `project.dataset.events`;

Search Consoleの応用分析例

Search Consoleのデータ構造は第4章で解説しています。

掲載順位の推移を分析する

SELECT date, AVG(position) AS avg_position
FROM `project.dataset.searchdata`
GROUP BY date
ORDER BY date ASC;

次のステップ（シリーズ総括へ）

シリーズ全体のまとめは第12章で整理しています。

BigQueryの基本的な特徴は第1章で解説しています。

料金体系は第2章で紹介しています。

BigQueryの使い方は第3章で解説しています。

Search Consoleとの連携は第4章で紹介しています。

GA4との連携は第5章で紹介しています。

分析10選は第6章で紹介しています。

SQL基礎は第7章で学べます。

シリーズ全体のまとめは第12章で整理しています。

シリーズ全体の構成は目次ページから確認できます。

---

FAQ

ウィンドウ関数は難しいですか？

基本的な使い方を覚えればランキングや移動平均などに応用できます。

正規表現はどんな時に使いますか？

URLのパターン抽出や特定キーワードの検索に使います。

GA4とSearch Consoleの結合は必須ですか？

検索→行動の流れを分析する場合に非常に役立ちます。

---

注記

本記事はGoogle BigQueryの初心者向けに構成されています。内容は2026年時点の仕様に基づいています。

---

更新履歴

2026/08/06：新規作成