Google Data Studio连接含QUALIFY的BigQuery视图失败，求SQL改写方案

核心思路 #

把原本用QUALIFY过滤窗口函数结果的逻辑拆分为两步：

1. 通过子查询或CTE（公共表表达式）计算出窗口函数（如ROW_NUMBER()）的结果，作为临时列
2. 在外层查询中用WHERE子句过滤这个临时列，实现和QUALIFY完全一致的筛选效果

具体改写示例 #

原带QUALIFY的查询结构 #

假设你的原视图SQL类似：

SELECT
  sheet_data.id,
  sheet_data.name,
  bq_calculated.metric_value
FROM
  `project.dataset.sheet_data` AS sheet_data
JOIN
  `project.dataset.bq_calculated` AS bq_calculated
  ON sheet_data.id = bq_calculated.id
QUALIFY
  ROW_NUMBER() OVER (PARTITION BY sheet_data.id ORDER BY bq_calculated.update_time DESC) = 1

改写方案1：使用CTE（可读性更强） #

WITH ranked_data AS (
  SELECT
    sheet_data.id,
    sheet_data.name,
    bq_calculated.metric_value,
    -- 完整保留原窗口函数的分区、排序逻辑
    ROW_NUMBER() OVER (PARTITION BY sheet_data.id ORDER BY bq_calculated.update_time DESC) AS row_num
  FROM
    `project.dataset.sheet_data` AS sheet_data
  JOIN
    `project.dataset.bq_calculated` AS bq_calculated
    ON sheet_data.id = bq_calculated.id
)
-- 外层过滤行号为1的记录，等价于原QUALIFY的效果
SELECT
  id,
  name,
  metric_value
FROM ranked_data
WHERE row_num = 1

改写方案2：使用子查询 #

如果不习惯CTE，也可以用子查询实现相同逻辑：

SELECT
  id,
  name,
  metric_value
FROM (
  SELECT
    sheet_data.id,
    sheet_data.name,
    bq_calculated.metric_value,
    ROW_NUMBER() OVER (PARTITION BY sheet_data.id ORDER BY bq_calculated.update_time DESC) AS row_num
  FROM
    `project.dataset.sheet_data` AS sheet_data
  JOIN
    `project.dataset.bq_calculated` AS bq_calculated
    ON sheet_data.id = bq_calculated.id
) AS ranked_data
WHERE row_num = 1

注意事项 #

• 严格保留原PARTITION BY和ORDER BY的逻辑，确保筛选结果和原视图完全一致
• 如果原查询中有多个窗口函数配合QUALIFY，可以在子查询/CTE中同时计算多个临时列，再在外层用WHERE组合过滤
• 对于复杂的多表关联场景，CTE的可读性更强，推荐优先使用

内容的提问来源于stack exchange，提问作者mister_b