BigQuery中group by与partition by共用列的查询问题

报错原因 #

报错信息翻译为：PARTITION BY子句引用的列col3既未被分组也未被聚合
出现这个错误的核心原因有两点：

1. 在GROUP BY之后的窗口函数中，直接使用DATE(col3)引用原始列表达式，虽然GROUP BY中包含该逻辑，但BigQuery要求窗口函数的分区字段必须是GROUP BY中明确列出的字段或聚合结果；
2. 窗口函数的逻辑错误：COUNTIF(NULLIF(TRIM(col2),"")) IS NULL是判断当前分组是否没有非空的col2值，而非统计col1+date分组下col2的空值总数。

同时原查询中统计col4、col5空值的逻辑也存在偏差：COUNTIF(NULLIF(TRIM(col4),"")) IS NULL仅判断分组内是否无有效col4值，而非统计空值的具体数量。

正确查询语句 #

WITH TAB AS 
(SELECT 'AAA' col1, null col2, TIMESTAMP '2020-01-01 00:00:00' col3, 'A1A' col4, 'A2A' col5    
UNION ALL   
SELECT 'AAA', null, TIMESTAMP '2020-01-01 00:00:00', null, 'A2A'  
UNION ALL   
SELECT 'AAA', 'XYZ', TIMESTAMP '2020-01-01 00:00:00', null, 'A2A' 
UNION ALL  
SELECT 'BBB', 'XYZ', TIMESTAMP '2020-01-02 00:00:00', 'B1B', null ),
-- 预处理：先计算col1+date维度下的col2空值总数
preprocessed AS (
  SELECT
    col1,
    col2,
    DATE(col3) AS date,
    col4,
    col5,
    -- 统计当前col1+date分组中，col2为空或空字符串的行数
    COUNTIF(col2 IS NULL OR TRIM(col2) = '') OVER (PARTITION BY col1, DATE(col3)) AS col2_isnull_total
  FROM TAB
  WHERE DATE(col3) BETWEEN '2020-01-01' AND '2020-01-31'
)
-- 按col1、col2、date分组，统计col4、col5的空值数量
SELECT
  col1,
  col2,
  date,
  -- 统计分组内col4为空或空字符串的数量
  COUNTIF(col4 IS NULL OR TRIM(col4) = '') AS col4_isnull_total,
  -- 统计分组内col5为空或空字符串的数量
  COUNTIF(col5 IS NULL OR TRIM(col5) = '') AS col5_isnull_total,
  -- 同一col1+date分组下col2空值总数一致，用MAX取统一值
  MAX(col2_isnull_total) AS col2_isnull_total
FROM preprocessed
GROUP BY col1, col2, date, col2_isnull_total;

预期查询结果 #

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