如何在BigQuery的CASE语句中结合WHERE逻辑生成新列？

你的需求可以通过窗口函数来实现，CASE语句本身没法直接嵌入WHERE逻辑，得先提前计算好每个Y分组的关键信息，再用CASE做判断。下面给你两种可行的写法：

方法一：用FIRST_VALUE窗口函数（推荐） #

这种方法效率更高，一次扫描就能完成所有计算：

WITH processed_data AS (
  SELECT
    *,
    -- 统计每个Y对应的不同X数量
    COUNT(DISTINCT X) OVER (PARTITION BY Y) AS distinct_x_count,
    -- 直接获取当前Y分组下Z最大的那个X
    FIRST_VALUE(X) OVER (
      PARTITION BY Y
      ORDER BY Z DESC
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS x_with_latest_z
  FROM your_table_name -- 替换成你的表名
)
SELECT
  *,
  CASE
    WHEN distinct_x_count > 1 THEN x_with_latest_z
    ELSE X -- 如果Y对应的X只有一个，保留原X，也可以改成NULL
  END AS new_column_value
FROM processed_data;

方法二：用ROW_NUMBER排序定位 #

如果需要更灵活处理并列最大Z的情况（比如多个行Z相同且都是最大值），可以用ROW_NUMBER：

WITH ranked_data AS (
  SELECT
    *,
    COUNT(DISTINCT X) OVER (PARTITION BY Y) AS distinct_x_count,
    -- 按Z降序给每个Y分组的行排名，Z最大的行排第1
    ROW_NUMBER() OVER (PARTITION BY Y ORDER BY Z DESC) AS rn
  FROM your_table_name -- 替换成你的表名
)
SELECT
  t.*,
  CASE
    WHEN distinct_x_count > 1 THEN (SELECT X FROM ranked_data WHERE Y = t.Y AND rn = 1)
    ELSE X
  END AS new_column_value
FROM ranked_data t;

关键说明 #

• 你原来的代码问题在于：CASE的THEN子句只能返回一个确定的值，不能直接加WHERE筛选逻辑，必须先通过窗口函数把每个Y分组需要的“最大Z对应的X”计算出来，再在CASE里引用。
• PARTITION BY Y表示按Y分组计算窗口函数，确保每个Y的统计都是独立的。
• 如果Y对应的X只有一个，CASE里的ELSE可以根据你的需求调整，比如返回NULL或者原X值。

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