BigQueryのパイプ構文でGA4のテーブルにクエリを投げてみた
BigQuery, SQL

BigQueryのパイプ構文でGA4のテーブルにクエリを投げてみた

2024年10月8日付けのBigQueryのリリースノートに以下の投稿がありました。『プレビュー（正式ローンチ前）のステータスだけど、BigQueryで「パイプ構文（pipe syntax）」が使えるようになったよ。』という内容です。ちなみに、ちゃんと訳すと以下となります。

GoogleSQLを記述する場所であればどこでもパイプ構文を使用できるようになりました。パイプ構文は、クエリを読みやすく、書きやすく、保守しやすいように設計された線形構造をサポートします。この機能は現在プレビュー段階にあります。

このパイプ構文（pipe syntax）はBigQueryで使えるSQLの新しい記述体系です。従来の構文（Standard SQL）ももちろんこれまでどおりに使えます。なんならパイプ構文での記述と従来の構文を混在させて使うこともできます。従来の構文で成立していたSQLに、Googleがあえてパイプ構文を加えてきたということは、パイプ構文に従来の構文にはないなんらかのメリットがあるということでもあります。

では、実際にパイプ構文に従ってSQL文を記述してみて、そのメリットを確認して行きましょう。GA4のデータを記録したテーブルを対象とします。具体的には、このブログサイトにおける「ページタイトルに”GA4″を含むページのページ別ページビュー数」を求める例で話を進めていきます。対象期間は2024年9月とします。

パイプ文法のメリット1：頭で考えた順番に記述できる

BigQueryのコンソールを開いて、「さぁ、SQL文を書いてほしい結果を得るぞ」というときに、従来の構文では「SELECT文」から記述を始める必要があります。SELECT文は、結果テーブルに表示したい列を指定する命令です。ということは、SQLを書く人が、最初からどんな列が必要かを分かっていなければいけません。

「従来の構文」と「対象とするデータ」の両方に精通した人は、「まず、元データをこういう形で整形しておいて」、「次に、●●でグループ化してSUMで集計すればほしい結果が得られるな」と頭の中で組み立てることができます。そのため、構文上SELECTで記述を始めなければいけないという制約がそれほど苦になりません。一方、従来の構文のSQLが得意でなかったり、対象のデータに馴染みが薄い人は「最初からどの列が必要かなんてわからないよ」という気持ちになります。

ところが、パイプ構文では最初にFROM句から記述することができます。そして、ページビューを求めたいのであれば、「WHERE event_name = “page_view」が必要ということは分かるので、それは記述できるでしょう。ここまでの頭の働きをSQL文に落とし込むと、以下となるでしょう。で、このSQL文は成立しています。

  • ①パイプ文法で記述したSQL文
  • ②クエリが完了したことを知らせるメッセージ
  • ③パイプ文法に従って得た結果テーブル

　

次に、今知りたいのはページタイトル別のページビュー数ですから、1行が1ページビューになっている結果テーブルを対象として「集計」する必要に思い至ります。そこでSQL文の次の行に集計を行う命令を記述します。パイプ文法では、AGGREGATEという命令と、それに続いて集計関数を記述します。具体的には以下の記述をします。

AGGREGATE COUNT(*) AS pageviews

ここまで記述すると、さらに「ページタイトルごとの」という要素が必要なことが分かるでしょう。そこで、GROPU BYに引き続いてページタイトルを取得するSQL文を記述します。

ページタイトルはevent_paramsという名前の「レコード中の表」の中に格納されています。（正確に書くと、配列化された構造体の一つの要素としてページタイトルが格納されている。となりますが、それでは分かりづらいのでイメージとして「レコードの中の表」と表現しています）その「レコードの中の表」からページタイトルを取得するには、event_paramsを直接SQL文を実行できる状態に変換してから「keyがpage_titleに等しい」という条件で、値（value.string_value）を取得する必要があります。少々複雑ですが、SQL文を記述するときには以下の通りに考えます。

  • event_paramsを直接SQL文を実行できる状態に変換する
  • WHERE句で「keyがpage_titleに等しい」の条件で絞り込む
  • それに対しSELECT文を使って、明示的にvalue.string_valueを取得する

ここまでで、以下のSQL文が記述できます。以下の画面コピーのSQL文の5行目から8行目で、上記で考えた流れに沿って、event_parmsに格納されている page_titleを取得しています。

　

人間の言葉で表現すると、以下となります。

  • 5行目：event_paramsを、UNNEST関数でフラット化した表から（FROM）
  • 6行目：keyが “page_title”に一致する行（WHERE)だけに絞り込んで
  • 7行目：その行の value.string_valueを選んで（SELECTして）返してください
  • 8行目：返した値には、page_titleという名前をつけてください

人間の言葉で説明できるということは、人間の思考ががたどる通りの順番でSQL文が記述されている。ということになります。

最後にページタイトルに「GA4」が含まれているページだけを対象にするために、|> WHERE STRPOS(page_title, “GA4”) >0を、そしてpage_viewの多い順に出力するために|> ORDER BY pageviews DESCの2行を追記します。すると、パイプ文法で記述したSQL文の全体像は以下の通りとなります。

FROM `bigquerytableauoct.analytics_323400862.events_202409*`
|> WHERE event_name="page_view"
|> AGGREGATE COUNT(*) AS pageviews
   GROUP BY (
   FROM UNNEST(event_params)
   |> WHERE key="page_title"
   |> SELECT value.string_value
   ) AS page_title
|> WHERE STRPOS(page_title, "GA4") >0
|> ORDER BY pageviews DESC

　

パイプ文法のメリット2：寛容な命令の記述順序

2番目のメリットは、SQL文の記述順序が柔軟なことです。前述の通り、上記のパイプ構文のSQL文では、最後の2行として|> WHERE STRPOS(page_title, “GA4”) >0　と、|> ORDER BY pageviews DESCを記述しています。たまたまWHERE句を先に、ORDER BY句をあとに記述していますが、その2行の順番はどちらでも成立します。つまり、上記のSQL文が成立したの同様に、以下のSQL文も成立します。

FROM `bigquerytableauoct.analytics_323400862.events_202409*`
|> WHERE event_name="page_view"
|> AGGREGATE COUNT(*) AS pageviews
   GROUP BY (
   FROM UNNEST(event_params)
   |> WHERE key="page_title"
   |> SELECT value.string_value
   ) AS page_title
|> ORDER BY pageviews DESC -- ORDER BY句が先
|> WHERE STRPOS(page_title, "GA4") >0 --WHERE句が後

　

パイプ構文では、記述した順に「上から」処理されていきますので、3行目で pageviewsを定義した後であれば、どこでもpageviewsが利用できますし、8行目で page_titleを定義した後であれば、どこでも page_titleが記述できます。従来の構文でSQL文を学んだ方は、「命令の記述順序が間違っている」という趣旨のエラーを何度も目にしていると思います。パイプ文法は命令の記述順序に寛容なため、そうしたエラーがでにくいというメリットがあります。

　

パイプ文法のメリット3：サブクエリ利用の回避

パイプ文法のメリットの3つめは、サブクエリを使わなくて良いということです。パイプ文法で記述した前述のSQL文と同じ結果を得るための従来文法のSQL文は以下の通りです。構造として、3行目から8行目が「内側」のSQL文、残りが外側のSQL文です。内側のSQL文が外側のSQL文のFROM句にすっぽり入り、入れ子状態ができていることがわかります。入れ子になった内側のSQL文のことを、「副問い合わせ」あるいは「サブクエリ」と呼びます。

SELECT page_title, COUNT(*) AS pageviews
FROM
  (　--サブクエリ開始
  SELECT 
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key="page_title") AS page_title
  FROM `bigquerytableauoct.analytics_323400862.events_202409*`
  WHERE event_name="page_view"
  )  -- サブクエリ終了
GROUP BY page_title
ORDER BY pageviews DESC

　

一方、今一度パイプ文法のSQL文を見ていただくと、サブクエリに相当する記述はありません。「サブクエリ」は一旦習得してしまえば便利ではあるのですが、初心者の方にはSQLを学習するうえでの一つのハードルになっているのも事実だと思います。というのは、サブクエリを利用する場合、SQL文を記述する場所が、あっちにいったり、こっちにいったりするからです。（FROM句でサブクエリを使った戻り値を集計する場合、SELECT文はサブクエリの上に、GROUP BY句はサブクエリの下に記述する必要があります。）パイプ構文では、従来構文のときに頻用されるサブクエリを利用しなくてよいというメリットがあります。

　

パイプ文法のメリット4：集計するときの重複した記述の回避

4番目のメリットは集計するときの重複した記述の回避です。たとえば、GA4のテーブルを対象として、「日別のユーザー数」を取得する場合、従来の文法では、以下のように記述します。1行目にも、3行目にも、event_date を記述する必要があります。

また、結果テーブルをevent_dateの昇順、あるいは降順で表示させる場合、3行目にも、4行目にも even_dateを記述する必要があります。

SELECT event_date, COUNT(DISTICT user_pseudo_id) AS users
FROM `bigquerytableauoct.analytics_323400862.events_202409*`
GROUP BY event_date
ORDER BY event_date ASC

一方、同じ結果を得るためのパイプ構文でのSQL文は以下の通りとなります。上記の従来の構文でのSQL文同様、event_date でグループ化をしています。結果テーブルを見ると1列目が event_dateになっています。つまり、パイプ構文ではグループ化に利用したフィールドは自動的に結果テーブルに含まれます。そのため、event_date を2箇所に記述する必要がありません。また、4行目の記述を見てください。GROUP BY句で結果テーブルのソート順を指定するしています。つまり、ORDER BY句が不要になります。

また、もし、event_dateではなく、usersで並び順を指定したい場合には、2行目の末尾に、ASC、あるいはDESCを記述することで実現できます。結果として、パイプ構文ではより簡潔な記述でほしい結果を求めることができると言えます。

　

まとめ

BigQueryに登場したパイプ構文について、そのメリットを実例とともに紹介しました。いかがでしたでしょうか？パイプ構文の今一度メリットをまとめると以下の通りとなります。

  1. 頭で考えた順番に記述できる
  2. 寛容な命令の記述順序
  3. サブクエリ利用の回避
  4. 集計するときの重複した記述の回避

　

この記事に関連した学習リソース

というわけで、SQLのパイプ構文を紹介しました。メリットがあるのは分かったが、まずは、従来の構文を勉強したいという方もいると思います。そんな方に向けて学習用の良い動画講座があるんですよ。たくさんのレビューを頂き、5点満点中4.6点と、お陰様で評判も良いようです。よろしければどうぞ。（画像をクリックするとUdemy.comにジャンプします。）

※講座作成後に登場したのでパイプ構文については触れていません。

　

え？SQLは本で勉強したい？なるほど、では、こちらをどうぞ。（アマゾンのアフィリエイトリンクです。画像をクリックするとアマゾンにジャンプします）