Google Cloud BigQuery分组查询报错及按periodo_esp排序方案

报错原因 #

BigQuery遵循严格的ANSI SQL标准，要求SELECT子句中的非聚合列必须直接作为分组键出现在GROUP BY中，或是与GROUP BY中的表达式完全匹配且无独立的未分组列引用。你的SQL中，SELECT里的CASE表达式直接引用了未分组的bierai_num_rut列，尽管GROUP BY里有相同的CASE表达式，BigQuery的解析器仍会判定该列未被分组或聚合，从而触发报错。而SQL Server对GROUP BY的规则更宽松，允许这种写法，这是两类SQL引擎的语法规则差异导致的。

解决方案 #

方案1：使用列位置引用分组键 #

通过SELECT子句中列的位置序号来指定分组键，让BigQuery明确关联SELECT和GROUP BY中的表达式：

SELECT 
  periodo_esp, 
  CASE WHEN bierai_num_rut < 50000000 THEN 1 ELSE 0 END AS persona_natural, 
  COUNT(DISTINCT bierai_num_rut) AS rut_unicos, 
  COUNT(bierai_num_rut) AS n_rut, 
  SUM(bierai_mon_avaluo_fiscal) AS sum_avaluo_fiscal, 
  AVG(bierai_mon_avaluo_fiscal) AS avg_avaluo_fiscal, 
  STDDEV(bierai_mon_avaluo_fiscal) AS stdev_avaluo_fiscal, 
  SUM(bierai_mon_avaluo_exento_prop) AS sum_avaluo_exento_prop, 
  AVG(bierai_mon_avaluo_exento_prop) AS avg_avaluo_exento_prop, 
  STDDEV(bierai_mon_avaluo_exento_prop) AS stdev_avaluo_exento_prop 
FROM 
  `bch-prj-bdta-datalake-pro-cec1.production_universal_cyp.sat_bien_raiz_esp` 
GROUP BY 
  1, 2  -- 对应SELECT中第1列periodo_esp和第2列persona_natural的CASE表达式
ORDER BY 
  periodo_esp DESC

方案2：用CTE预计算分组标识 #

先通过公共表表达式（CTE）生成包含分组标识persona_natural的中间结果，再对中间结果执行分组查询，避免重复编写CASE表达式：

WITH preprocessed AS (
  SELECT 
    periodo_esp,
    bierai_num_rut,
    bierai_mon_avaluo_fiscal,
    bierai_mon_avaluo_exento_prop,
    CASE WHEN bierai_num_rut < 50000000 THEN 1 ELSE 0 END AS persona_natural
  FROM 
    `bch-prj-bdta-datalake-pro-cec1.production_universal_cyp.sat_bien_raiz_esp`
)
SELECT 
  periodo_esp,
  persona_natural,
  COUNT(DISTINCT bierai_num_rut) AS rut_unicos,
  COUNT(bierai_num_rut) AS n_rut,
  SUM(bierai_mon_avaluo_fiscal) AS sum_avaluo_fiscal,
  AVG(bierai_mon_avaluo_fiscal) AS avg_avaluo_fiscal,
  STDDEV(bierai_mon_avaluo_fiscal) AS stdev_avaluo_fiscal,
  SUM(bierai_mon_avaluo_exento_prop) AS sum_avaluo_exento_prop,
  AVG(bierai_mon_avaluo_exento_prop) AS avg_avaluo_exento_prop,
  STDDEV(bierai_mon_avaluo_exento_prop) AS stdev_avaluo_exento_prop
FROM preprocessed
GROUP BY periodo_esp, persona_natural
ORDER BY periodo_esp DESC

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