QUALIFY

BigQueryで利用可能なSQL記法の一つに「QUALIFY」というものがあります。

これはWHERE句とは異なり、ウィンドウ関数で結果を絞り込むことができるため便利なシーンがあります。

ユースケース

公開データセットのsampleを使って、Githubのtwitter organizationに属するレポジトリの中で最初にマージされたPRのタイトルを取得するケースを例に考えてみます(今はtwitterではなくXですが...)。

QUALIFYを使わないケース

SELECT
  * EXCEPT(merge_order)
FROM
(
  SELECT
    repository.name,
    payload.pull_request.title,
    payload.pull_request.merged_at,
    ROW_NUMBER() OVER(PARTITION BY repository.name ORDER BY payload.pull_request.merged_at) merge_order
  FROM
    bigquery-public-data.samples.github_nested
  WHERE
    repository.organization = "twitter"
    AND payload.pull_request.title IS NOT NULL
    AND payload.pull_request.merged_at IS NOT NULL
)
WHERE
  merge_order = 1

QUALIFYを使うケース

SELECT
  repository.name,
  payload.pull_request.title,
  payload.pull_request.merged_at,
FROM
  bigquery-public-data.samples.github_nested
WHERE
  repository.organization = "twitter"
  AND payload.pull_request.title IS NOT NULL
  AND payload.pull_request.merged_at IS NOT NULL
QUALIFY
  ROW_NUMBER() OVER(PARTITION BY repository.name ORDER BY payload.pull_request.merged_at) = 1

上記のように、QUALIFYを使うことにより、

サブクエリの削減
不要カラムの削減(EXCEPTで除外しなくていい)

の2つが実現できています。

注意点

というふうに便利なQUALIFYですが、これを含んだViewをLooker Studioでデータソースに指定する場合に注意が必要です。

WITH twitter_org AS (
    SELECT
      repository.name,
      payload.pull_request.title,
      payload.pull_request.merged_at,
    FROM
      bigquery-public-data.samples.github_nested
    WHERE
      repository.organization = "twitter"
      AND payload.pull_request.title IS NOT NULL
      AND payload.pull_request.merged_at IS NOT NULL
)
SELECT
  *
FROM
  twitter_org
QUALIFY
  ROW_NUMBER() OVER(PARTITION BY name ORDER BY merged_at) = 1

というクエリは実行できますが。これをViewとして保存して、Looker Studioのデータソースに組み込むとエラーになります。

ロギングを見ても特に情報がなく、原因が分かりづらいです。

調べた結果
WHERE句無しでQUALIFYを使うとエラーになるようです。

そのため、下記のようにすれば回避できます。

WITH twitter_org AS (
    SELECT
      repository.name,
      payload.pull_request.title,
      payload.pull_request.merged_at,
    FROM
      bigquery-public-data.samples.github_nested
    WHERE
      repository.organization = "twitter"
      AND payload.pull_request.title IS NOT NULL
      AND payload.pull_request.merged_at IS NOT NULL
)
SELECT
  *
FROM
  twitter_org
-- 回避用WHERE句
WHERE
  true
QUALIFY
  ROW_NUMBER() OVER(PARTITION BY name ORDER BY merged_at) = 1

ぼやき

同じ悩みを抱えてる人はいるみたいですがなかなか対応されない様子。
https://support.google.com/looker-studio/thread/172699750/bigquery-qualify-function-is-not-supported-by-data-studio?hl=en
https://issuetracker.google.com/issues/250581514