BigQuery パイプ構文を試す - 直感的で読みやすい新しいSQLクエリの書き方

2024 年 10 月 9 日の Google Cloud Blog にて、BigQuery と Cloud Logging における「パイプ構文」導入の記事がアップされました。

Introducing pipe syntax in BigQuery and Cloud Logging | Google Cloud Blog

SQL のクエリといえば、長らく同じ記法で歴史を刻んできた古に伝わる呪文ですが、今回の「パイプ構文」は、それに一石を投じる新しい書き方になっています。まさに黒船。ペリー来航です。（といいつつ、今回紹介するパイプ構文はあくまでも GoogleSQL としての機能です。MySQL や PostgreSQL などでは現状パイプ構文をサポートしていません）

そんなパイプ構文を、BigQuery で試してみたいと思います。

パイプ構文

BigQuery のパイプ構文は、SQL クエリをより直感的で読みやすくするための記法です。パイプ演算子 (|>) を使って処理を順番にチェーンすることで、クエリの各操作をステップごとに記述できるのが特徴です。 これにより、クエリの記述量の削減、可読性の向上が期待できます。

従来の SQL のようにすべての操作を 1 つのブロックでまとめるのではなく、フィルタリング、選択、集約などの操作をそれぞれの行で分けて記述します。詳細な記法は公式ドキュメントをご確認ください。

• Pipe syntax | BigQuery | Google Cloud
• Pipe query syntax | BigQuery | Google Cloud

パイプ構文を体験

では、実際にデータを用意してクエリを書いてみましょう。

今回用意したのは、e-sports のゲームをプレイした履歴のデータです。

esports_play_sessions テーブル（n=30,000）

player_id	esports_genre	session_date	match_result
31	sports_games	2024/01/01	win
133	moba	2024/01/01	lose
32	sports_games	2024/01/01	win

どのプレイヤーが、いつ、どのジャンルのゲームをプレイして、勝敗がどうだったか。を収録したデータです。

players テーブル（n=163）

id	name
1	Caspian
2	Kamryn
3	Charmaine

こちらはプレイヤーを収録したテーブルです。

さて、こんなオーダーが来ました。

「sports_games と、card_games をプレイしたプレイヤーの、最終プレイ日を抽出したい」

このとき、どんなクエリを書くでしょうか。通常の SQL なら、以下のようなクエリになると思います。

SELECT
    p.id as player_id,
    p.name,
    s.esports_genre,
    MAX(s.session_date) AS session_date
FROM `sample.players` p
INNER JOIN `sample.esports_play_sessions` s ON p.id=s.player_id
WHERE s.esports_genre IN ('sports_games', 'card_games')
GROUP BY p.id, p.name, s.esports_genre
ORDER BY p.id, MAX(s.session_date)

ジャンルを絞り込み、それに対して集計。ユーザーごとの sports_games, card_games をプレイした最新の日時を抽出します。

これをパイプ構文を使用してクエリを組み立てると、以下になります。

FROM `sample.players` AS p
|> JOIN `sample.esports_play_sessions` AS s ON p.id = s.player_id
|> WHERE s.esports_genre IN ('sports_games', 'card_games')
|> AGGREGATE MAX(s.session_date) AS session_date GROUP BY p.id, p.name, s.esports_genre
|> SELECT id as player_id, name, esports_genre, session_date
|> ORDER BY player_id, session_date;

1. [|> JOIN] players テーブルに esports_play_sessions テーブルを結合
2. [|> WHERE] ジャンルを絞り込む
3. [|> AGGREGATE] 集計し最新日を算出
4. [|> SELECT] 出力するカラムを指定
5. [|> ORDER BY] 並び替え

上から順番に読めるので、可読性は確かに向上するかもしれません。一方で、記述量はあまり変わってはいない印象を受けます。

もう少し複雑なクエリで試してみます。 各ジャンルにおける、一年間のプレイ数トップ 3 を出してみます。まずは通常の SQL です。

WITH ranked_sessions AS (
    SELECT
        esports_genre, player_id, COUNT(*) AS session_count,
        RANK() OVER (PARTITION BY esports_genre ORDER BY COUNT(*) DESC) AS session_rank
    FROM `sample.esports_play_sessions`
    WHERE session_date BETWEEN '2024-01-01' AND '2024-12-31'
    GROUP BY esports_genre, player_id
)
SELECT
    s.esports_genre, s.session_rank, p.id, p.name, s.session_count
FROM `sample.players` p
INNER JOIN ranked_sessions s ON p.id=s.player_id
WHERE s.session_rank <= 3
ORDER BY s.esports_genre, s.session_rank;

ランキングで絞りトップ 3 としたいため、一度 CTE を定義し session_rank を付与、それから絞り込んでいます。

対して、このクエリをパイプ構文で記述すると以下になります。

FROM `sample.esports_play_sessions` AS s
|> WHERE session_date BETWEEN '2024-01-01' AND '2024-12-31'
|> AGGREGATE COUNT(*) AS session_count GROUP BY esports_genre, player_id
|> WINDOW RANK() OVER (PARTITION BY esports_genre ORDER BY session_count DESC) AS session_rank
|> WHERE session_rank <= 3
|> JOIN `sample.players` AS p ON player_id=p.id
|> SELECT esports_genre, session_rank, p.id AS player_id, p.name, session_count
|> ORDER BY esports_genre, session_rank;

1. [|> WHERE] 日付を絞り込む
2. [|> AGGREGATE] 集計し各ジャンルのプレイ数を算出
3. [|> WINDOW RANK()] プレイ数によってジャンルごとにユーザーをランク付け
4. [|> WHERE] トップ 3 に絞り込み
5. [|> JOIN] esports_play_sessions テーブルに players テーブルを結合
6. [|> SELECT] 出力するカラムを指定
7. [|> ORDER BY] 並び替え

抽出の順は確かに追いやすいですね。ただ、記述量は減ったか？と言われればそうでもない気がします。

最後に、もう少し複雑なクエリで試してみます。各ジャンルにおいて、勝率が最も高いユーザートップ 3 を抽出してみます。

まずは、通常の SQL です。

WITH player_stats AS ( -- 各プレイヤーの各ジャンルにおける総試合数と勝利数を算出
    SELECT
        e.player_id,
        p.name as player_name,
        e.esports_genre,
        COUNT(*) AS total_matches,
        SUM(CASE WHEN e.match_result = 'win' THEN 1 ELSE 0 END) AS wins
    FROM `sample.esports_play_sessions` e
    JOIN `sample.players` p ON e.player_id = p.id
    GROUP BY e.player_id, p.name, e.esports_genre
),
ranked_players AS ( -- 勝率を算出し、各ジャンルごとに勝率の高い順にランク付け
    SELECT
        esports_genre,
        player_id,
        player_name,
        ROUND(SAFE_DIVIDE(wins, total_matches), 2) AS win_rate,
        ROW_NUMBER() OVER (PARTITION BY esports_genre ORDER BY SAFE_DIVIDE(wins, total_matches) DESC) AS rank
    FROM player_stats
)
SELECT
    esports_genre,
    rank,
    player_id,
    player_name,
    win_rate
FROM ranked_players
WHERE rank <= 3
ORDER BY esports_genre, rank;

これをパイプ構文で記述すると以下になります。

FROM `sample.esports_play_sessions` AS s
|> JOIN `sample.players` AS p ON s.player_id = p.id
|> AGGREGATE COUNT(*) AS total_matches, SUM(CASE WHEN s.match_result = 'win' THEN 1 ELSE 0 END) AS wins GROUP BY s.player_id, p.name, s.esports_genre
|> EXTEND ROUND(SAFE_DIVIDE(wins, total_matches) * 100, 2) AS win_rate
|> WINDOW ROW_NUMBER() OVER (PARTITION BY esports_genre ORDER BY win_rate DESC) AS rank
|> RENAME name as player_name
|> WHERE rank <= 3
|> SELECT esports_genre, rank, player_id, player_name, win_rate
|> ORDER BY esports_genre, rank;

1. [|> JOIN] esports_play_sessions テーブルに players テーブルを結合
2. [|> AGGREGATE] 各プレイヤーの各ジャンルにおける総試合数と勝利数を算出
3. [|> EXTEND] 勝率を算出
4. [|> WINDOW] 勝率によってランク付け
5. [|> RENAME] カラム名 name を player_name に変更
6. [|> WHERE] トップ 3 に絞り込み
7. [|> SELECT] 出力するカラムを指定
8. [|> ORDER BY] 並び替え

追いやすさはこれまで通りポジティブですが、今回は記述量が大分減りました。CTE で切り出し、ないしはサブクエリの記述が無くなった分、SELECT 文分の記述量が主に削減されています。パイプ構文では、SELECT や集計でカラムが絞られない限りはそのまま次の行に持ち越されるため、追加したい新たなカラムだけを記述すればよい点が削減に寄与しています。

もう一点、通常の SQL では、ranked_players 定義時に　win_rate と rank を算出していますが、その両方で SAFE_DIVIDE(wins, total_matches) を行っています。対してパイプ構文だと同じ計算は 1 回済んでおり、冗長さが解消されています。こういった点は地味にうれしいポイントです。

直感的で追いやすくクエリ冗長に成り難し

2024 年 10 月 16 日現在、パイプ構文のリリースステージは「プレビュー」であり、一般提供はされていません。

BigQuery でパイプ構文を使ってみたい場合は、BigQuery パイプ構文登録フォームに記入し、パイプ構文プレビューにプロジェクトを登録する必要があります。

また、今回紹介したパイプ構文は、GoogleSQL としての機能です。MySQL や PostgreSQL などでは現状パイプ構文をサポートしていません。

さて、パイプ構文を使ってみましたが、読みやすさの向上、記述量の削減など、複雑なクエリほどパイプ構文の恩恵を受けやすくて良い機能でした。パイプ構文で記述したクエリも CTE として切り出せました。

「直感的で追いやすくクエリ冗長に成り難し」

BigQuery のパイプ構文、是非使ってみてください。

271_sample_data - Google スプレッドシート

（今回使用したサンプルデータはスプレッドシートで公開しています。CSV ダウンロードしてご自身の BigQuery にインポートするなど、ご自由にお使いください。）