BigQuery

BigQueryのテーブル関数(TVF)で複雑な分析SQLを構築

2024.09.20 2024.09.23

GA4のデータソースを始めとした分析SQLのコーディングにおいて、テーブル関数（Table-Valued Functions）は必須の機能となる。テーブル関数を使わなければ可読性の低下や極めて冗長なクエリとなりミスの根源となり得る。テーブル関数はUDF（User-Defined Function）の一種であるが、「テーブル」を戻り値として返すことができるため、スカラー値を戻すUDFと大きな違いがある。

テーブル関数の概要
- スカラーUDFとテーブル関数の違い
テーブル関数のメリット
テーブル関数を活用したクエリ例
- テーブル関数を使わない新規セッション率を求めるクエリ
- テーブル関数を用いた新規セッション率を求めるクエリ
テーブル関数の作成と呼び出し
- 作成
- 呼び出し

テーブル関数の概要

テーブル関数は列と行からなる結果テーブルを返すことができる。つまり1つのカプセル化された結果セットとなるため、複数のテーブル関数を呼び出し、その結果テーブルを結合させることで、複雑なデータ処理を簡潔化させることが可能になる。つまり、1つのクエリの中で処理を書かずに、既に結果として正しい値が返ってくることが保証されたテーブル関数を用意しておき、それを呼び出し組み合わせることで、複雑なクエリを都度書かずとも、欲しいテーブルを瞬時に構築することができるのがテーブル関数の最大のメリットとなる。

参照：テーブル関数 – Google Cloud

スカラーUDFとテーブル関数の違い

戻り値の形式
・スカラーUDF：単一の値（例えば、文字列や数値など）を返す。関数は特定の行の値に対して処理を行い、結果としてその行ごとに1つの値を返すのが通常となる。
・テーブル関数（TVF）：テーブル全体を返す。つまり、複数の行と列から成る結果セットを返すことができる。これはテーブルそのものをクエリの一部として扱う場合に活用できる。
用途
・スカラーUDF：列の値に対する変換やカスタムロジックを適用するのに使用される。例えば、文字列を操作する関数や、数値の計算を行う関数を定義する。
・テーブル関数（TVF）: より大規模なデータ処理やフィルタリング、集計、結合などを行い、結果をテーブル形式で返す用途に使われる。
実行される場所
・スカラーUDF：基本的に行ごとに適用されるため、SELECT文の列の一部として使われることが多い。
・テーブル関数（TVF）： FROM句で使用され、クエリ全体の結果としてテーブルを返すため、通常のテーブルと同様に扱われる。

テーブル関数のメリット

GA4のデータ抽出を始めとした分析SQLにおいてテーブル関数は必須となる。テーブル関数を使わなければ極めて冗長なクエリ・可読性の低下・車輪の再発明など、保守の観点からもテーブル関数を使わず業務を行うことは非常率の極みといってよい。

コードの再利用性
テーブル関数を一度定義してしまえば、再度同じ処理を書く必要がなくなるため、「車輪の再発明」を防ぐことができる。
保守性の向上
テーブル関数を呼び出すことで実際の処理内容を隠すことができるため、ロジックの流れが追いやすくなる。仮に処理内容に修正が入った場合でも、大元のテーブル関数の1ヵ所を修正することで対応が完了する。
動的なクエリの実行
分析SQLではよくあるケースであるが、複数のURLに対して同一の処理を施したい場合、テーブル関数を使わなければ、その分のクエリを書く必要があり非常に冗長なクエリになってしまう。しかし、テーブル関数では引数を取ることができるため、パラメータの値が変動する処理であっても、テーブル関数の呼び出し時に引数にパラメータを渡すことで動的に結果テーブルを得ることが可能になる。

テーブル関数を活用したクエリ例

共通化できる処理はテーブル関数として定義することで上記に挙げたメリットを享受することができる。汎用性が高くテーブルとして返すことができるのであればテーブル関数として定義できないか検討する。例えば、日次のセッション数を返す処理は汎用性が高いため予めテーブル関数として定義しておくことで、日次のセッション数が必要な場面ではそのテーブル関数を呼び出せば都度セッション数を求める処理を書く必要がなくなる。新規セッション数も同様。

テーブル関数を使わない新規セッション率を求めるクエリ

CREATE TEMP FUNCTION date_from() RETURNS STRING AS ('20240401');

WITH
  SessionDaily AS (
    SELECT
      ymd,
      COUNT(DISTINCT ssid) AS _ssuu
    FROM (
      SELECT
        ymd,
        CONCAT(user_pseudo_id, '-', CAST(ga_session_id AS STRING)) AS ssid
      FROM (
        SELECT
          PARSE_DATE("%Y%m%d", event_date) AS ymd,
          user_pseudo_id,
          (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id
        FROM
          `<project>.<dataset>.events_*`
        WHERE
          _TABLE_SUFFIX BETWEEN date_from() AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(),INTERVAL 1 DAY))
      )
  )
  GROUP BY
    ymd
  ),
  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'
  ),
  SessionID AS (
    SELECT
      ymd,
      CONCAT(user_pseudo_id, '-', CAST(ga_session_id AS STRING)) AS ssid
    FROM (
      SELECT
        PARSE_DATE("%Y%m%d", event_date) AS ymd,
        user_pseudo_id,
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id
      FROM
        `<project>.<dataset>.events_*`
      WHERE
        _TABLE_SUFFIX BETWEEN date_from() AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(),INTERVAL 1 DAY))
    )
    ORDER BY
      ymd
  ),
  NewSessionID AS (
    SELECT
      ymd,
      ssid
    FROM (
      SELECT
        ymd,
        ssid
      FROM
        SessionID
    ) AS event_data
    WHERE
      event_data.ssid IN (SELECT ssid FROM SEG_FirstVisitSession)
  ),
  NewSessionDaily AS (
    SELECT
      ymd,
      COUNT(DISTINCT ssid) AS _ssuu
    FROM
     NewSessionID
    GROUP BY
      ymd
  ),
  JoinSession AS (
    SELECT
      ymd,
      _ssuu,
      _new_ssuu,
      ROUND(_rate_new_ssuu,2) AS _rate_new_ssuu
    FROM (
      SELECT
        t.ymd,
        t._ssuu AS _ssuu,
        n._ssuu AS _new_ssuu,
        n._ssuu / t._ssuu AS _rate_new_ssuu
      FROM
        SessionDaily AS t
      LEFT JOIN
        NewSessionDaily AS n
      ON
        t.ymd = n.ymd
    )
    ORDER BY
      ymd ASC
  )

SELECT
  *
FROM
  JoinSession

テーブル関数を用いた新規セッション率を求めるクエリ

CREATE TEMP FUNCTION date_from() RETURNS STRING AS ('20240401');

WITH
  SessionDaily AS (
    SELECT * FROM `<project>.<dataset>.<テーブル関数名_SessionDaily>`(date_from())
  ),
  NewSessionDaily AS (
    SELECT * FROM `<project>.<dataset>.<テーブル関数名_NewSessionDaily>`(date_from())
  ),
  JoinSession AS (
    SELECT
      ymd,
      _ssuu,
      _new_ssuu,
      ROUND(_rate_new_ssuu,2) AS _rate_new_ssuu
    FROM (
      SELECT
        t.ymd,
        t._ssuu AS _ssuu,
        n._ssuu AS _new_ssuu,
        n._ssuu / t._ssuu AS _rate_new_ssuu
      FROM
        SessionDaily AS t
      LEFT JOIN
        NewSessionDaily AS n
      ON
        t.ymd = n.ymd
    )
    ORDER BY
      ymd ASC
  )

SELECT
  *
FROM
  JoinSession

日次のセッション数・新規セッション数を返すテーブル関数をそれぞれ定義しておき、FROM句の中で呼び出している。これにより、130行であったSQLを36行まで縮めることができた。

テーブル関数の作成と呼び出し

以下、テーブル関数の基本的な作成と呼び出しの構文を明記する。

作成

CREATE OR REPLACE TABLE FUNCTION `<project>.<dataset>.<テーブル関数名>`(date_from STRING) AS (

WITH
  Result AS (
    SELECT
      -- テーブル関数の処理
    FROM
      `<project>.<dataset>.events_*`
    WHERE
      _TABLE_SUFFIX BETWEEN date_from AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(),INTERVAL 1 DAY))
  )

SELECT
  *
FROM
  Result
);

呼び出し

CREATE TEMP FUNCTION date_from() RETURNS STRING AS ('20240401');

WITH
  Result AS (
    SELECT
      *
    FROM
      `<project>.<dataset>.<テーブル関数名>`(date_from())
  )

SELECT
  *
FROM
  Result