分析SQLのコーディングではWITH句による記述を多用する。かといってサブクエリは使わないのかといえば全く使わないということもなく、サブクエリも取り入れコーディングをおこなう。つまり、どちらか片方だけが優れているということではなく、両者を使い分けてクエリを記述する。
WITH句（CTE）の概要
WITH句（Common Table Expression）について以下、公式ヘルプから日本語に訳して引用する。再帰CTEについての解説が混ざっているので少し分かりにくくなっているが、ようはWITH句とは仮想的な一時テーブルを作成できる機能と考えてよい。
WITH句には、1つ以上の共通テーブル式（CTE）が含まれます。CTEは、1つの問い合わせ式の中で参照できる一時テーブルのように動作します。各CTEは副問い合わせの結果をテーブル名に結合し、同じ問い合わせ式の他の場所で使用することができますが、ルールが適用されます。
CTE には非再帰型と再帰型があり、WITH 節にその両方を含めることができます。再帰 CTE はそれ自身を参照しますが、非再帰 CTE は参照しません。再帰 CTE を WITH 節に含める場合は、RECURSIVE キーワードも含める必要があります。
RECURSIVE キーワードは、再帰 CTE が存在しない場合でも WITH 節に含めることができます。RECURSIVE キーワードの詳細については、こちらを参照してください。
GoogleSQL は再帰 CTE の結果のみを実体化しますが、非再帰 CTE の結果を WITH 節内で実体化することはありません。非再帰型 CTE がクエリ内の複数箇所で参照されている場合、CTE は参照ごとに 1 回実行されます。非再帰型 CTE を使用した WITH 節は、主に可読性を高めるために役立ちます。
可読性
可読性を高めるのであればWITH句を採用し、可読性を高める必要がなければサブクエリを採用する。
ロジックの流れが明確になるWITH句（CTE）
WITH句とはCommon Table Expression（CTE）と同義であり、簡単にいえば論理的な一時ビューのようなもので、WITH句で定義した結果をその後のクエリで参照することができる。また、WITH句は数珠繋ぎできるため、区切りの良い結果テーブルAを作成し、その結果テーブルAをもとにWITH句を用いて作成することも可能になる。そのため、サブクエリよりもロジックの流れが読み手にとって明確になるメリットがある。
例として同一の結果を返すクエリをサブクエリとCTEで記載すると、どのくらい処理の流れが分かりやすくなるのかを以下、参考として挙げる。クエリ内容は初回訪問したユーザーに対して、初回訪問日のセッションはすべて初回訪問とみなすセグメントを作成する処理となっている。
GA4の分析において新規とリピーターを分けて分析したいケースがよくある。GA4の新規ユーザーの定義では初回訪問は新規ユーザーとしてカウントされるが、その日の2回目以降のセッションではリピーターとしてカウントされてしまう。
サブクエリを使用した記述例
CREATE TEMP FUNCTION date_from() RETURNS STRING AS ('20240401');
WITH
SEG_FirstVisitSession AS (
SELECT
DISTINCT ssid
FROM (
SELECT
ssid,
ymd,
CASE
WHEN ymd = first_visit_date THEN 'first_visit'
ELSE 'subsequent_visit'
END AS visit_type
FROM (
SELECT
CONCAT(e.user_pseudo_id, '-', CAST((SELECT value.int_value FROM UNNEST(e.event_params) WHERE key = 'ga_session_id') AS STRING)) AS ssid,
PARSE_DATE("%Y%m%d", e.event_date) AS ymd,
first_visit.ymd AS first_visit_date
FROM
`<project>.<dataset>.events_*` AS e
INNER JOIN (
SELECT
user_pseudo_id,
MIN(PARSE_DATE("%Y%m%d", event_date)) AS ymd
FROM
`<project>.<dataset>.events_*`
WHERE
_TABLE_SUFFIX BETWEEN date_from() AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
AND event_name = 'first_visit'
GROUP BY
user_pseudo_id
) AS first_visit
ON e.user_pseudo_id = first_visit.user_pseudo_id
WHERE
e._TABLE_SUFFIX BETWEEN date_from() AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
)
)
WHERE
visit_type = 'first_visit'
)
SELECT
*
FROM
SEG_FirstVisitSessionWITH句（CTE）を使用した記述例
CREATE TEMP FUNCTION date_from() RETURNS STRING AS ('20240401');
WITH
FirstVisit AS (
SELECT
user_pseudo_id,
MIN(PARSE_DATE("%Y%m%d", event_date)) AS ymd,
FROM
`<project>.<dataset>.events_*`
WHERE
_TABLE_SUFFIX BETWEEN date_from() AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
AND
event_name = 'first_visit'
GROUP BY
user_pseudo_id
),
SessionsOfTheDay AS (
SELECT
CONCAT(e.user_pseudo_id, '-', CAST((SELECT value.int_value FROM UNNEST(e.event_params) WHERE key = 'ga_session_id') AS STRING)) AS ssid,
PARSE_DATE("%Y%m%d", e.event_date) AS ymd,
f.ymd AS first_visit_date
FROM
`<project>.<dataset>.events_*` AS e
INNER JOIN
FirstVisit AS f
ON
e.user_pseudo_id = f.user_pseudo_id
WHERE
e._TABLE_SUFFIX BETWEEN date_from() AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
),
SessionJudge_01 AS (
SELECT
ssid,
ymd,
CASE
WHEN ymd = first_visit_date THEN 'first_visit'
ELSE 'subsequent_visit'
END AS visit_type
FROM
SessionsOfTheDay
),
SessionJudge_02 AS (
SELECT
DISTINCT ssid
FROM
SessionJudge_01
WHERE
visit_type = 'first_visit'
)
SELECT
*
FROM
SessionJudge_02可読性が必要ない場合はサブクエリを採用
上記のクエリ例では処理の内容を読み解く場合、WITH句で書かれたクエリの方が理解しやすい。であれば、サブクエリは必要ないのかといえば、そんなことはなく、例えば先に挙げたクエリのような新規訪問者のセグメントを作成するといった、処理の手順を読み手側に理解させる必要性があまりなく、アウトプットされた結果テーブルが返ってくればよいといった場合であれば、サブクエリを採用する。
再利用性
WITH句では作成した結果テーブルを後続のクエリから参照することができるが、サブクエリの場合は参照することができない、つまりサブクエリの1つ外側のクエリのみでしか使用できない。つまり、CTEでは先頭で生成した結果テーブルを「共通テーブル」のように見立て後続の複数のWITH句から参照することが可能になる。これは都度、必要な結果テーブルを生成する必要性がなくなるため冗長性の改善につながるといってよい。
クエリコストを削減するのであれば一時テーブルを活用
CTEで生成した結果テーブルは参照することができるが、結果テーブルの保持が保証されているわけではない。つまり、再度クエリが走る可能性もあり、その分のコストもかかることもあり得る。これを回避するためには、作成した最終的なCTEを一時テーブルとして保存し、その一時テーブルを後続のクエリから参照することがベストプラクティスと読み取れる記載がヘルプに記載があるが、BigQueryでは、CTEの結果を直接一時テーブルに保存することはできない。ヘルプの意図としては何かしらの手段を用いて一時テーブルを作成してデータを保存し、それを再利用するアプローチを推奨していると解釈すべきだろう。
ベスト プラクティス: 手続き型言語、変数、一時テーブル、自動的に期限切れになるテーブルを使用して計算を維持し、後でクエリで計算を使用します。
クエリに共通テーブル式（CTE）が含まれており、クエリ内の複数の場所で使用される場合、これらの式は参照されるたびに評価される可能性があります。クエリ オプティマイザーは、1 回しか実行できないクエリ部分を検出しようとしますが、常に検出できるとは限りません。その結果、CTE を使用しても内部クエリの複雑さとリソース消費の軽減につながらない場合があります。
『Googleアナリティクス4 完全入門』 書籍販売中！
