BigQuery GCP成本查询分组优化及报错解决咨询

报错原因 #

报错SELECT list expression references column labels which is neither grouped nor aggregated是因为查询中SELECT子句里的labels和system_labels两个字段，既没有被加入GROUP BY分组列表，也没有使用聚合函数处理，违反了BigQuery的分组查询规则——所有非聚合的SELECT字段必须出现在GROUP BY中。

优化后的查询语句 #

SELECT
  billing_account_id,
  service.id AS service_id,
  service.description AS service_des,
  sku.id AS sku_id,
  sku.description AS sku_des,
  DATE(usage_start_time) AS usage_date,
  MIN(usage_start_time) AS first_usage_start_time,
  DATE(MAX(usage_end_time)) AS usage_end_date,
  location.location AS location,
  location.region AS region,
  location.country AS country,
  location.zone AS zone,
  MAX(export_time) AS export_time,
  CAST(invoice.month AS STRING) AS invoice_mon,  
  currency,
  currency_conversion_rate,
  cost_type,
  resource.name AS resource_name,
  resource.global_name AS res_global_name,
  SUM(usage.amount_in_pricing_units) AS usage_amount_pricing_units,
  usage.pricing_unit,
  SUM(cost) AS total_cost,
  SUM(cost_at_list) AS total_cost_at_list, 
  transaction_type,
  seller_name,
  adjustment_info.id AS adjustment_info_id,
  adjustment_info.description AS adjustment_des,
  adjustment_info.type AS adjustment_type,
  adjustment_info.mode AS adjustment_mode,
  price.effective_price,
  price.pricing_unit_quantity,
  project.id AS project_id,
  project.number AS project_number,
  project.name AS project_name,
  SUM(IFNULL(credits.amount, 0)) AS total_credit_amount,
  credits.id AS credit_id,  -- 补充需求中的credit_id字段
  CAST(credits.type AS STRING) AS credit_type,  
  credits.full_name AS credit_full_name,
  ANY_VALUE(labels) AS labels,  -- 用ANY_VALUE聚合标签字段
  ANY_VALUE(system_labels) AS system_labels
FROM `TABLE_NAME`
LEFT JOIN UNNEST(credits) AS credits
WHERE (cost != 0 OR IFNULL(credits.amount, 0) != 0) AND  -- 处理credits为NULL的情况
  usage_start_time >= TIMESTAMP_MILLIS(1714176000000) AND
  usage_end_time <= TIMESTAMP_MILLIS(1714262400000)
GROUP BY
  -- 严格按照需求指定的分组字段整理
  billing_account_id,
  service.id,
  sku.id,
  location.location,
  location.region,
  location.country,
  location.zone,
  CAST(invoice.month AS STRING),
  cost_type,
  resource.name,
  adjustment_info.id,
  project.id,
  credits.id,
  CAST(credits.type AS STRING),
  adjustment_info.type,
  transaction_type,
  -- 保留其他与分组字段强关联或不影响分组维度的字段（确保SELECT非聚合字段都在GROUP BY中）
  service.description,
  sku.description,
  DATE(usage_start_time),
  currency,
  currency_conversion_rate,
  resource.global_name,
  usage.pricing_unit,
  seller_name,
  adjustment_info.description,
  adjustment_info.mode,
  price.effective_price,
  price.pricing_unit_quantity,
  project.number,
  project.name,
  credits.full_name
ORDER BY
  usage_date

关键调整点 #

1. 修复报错：对labels和system_labels使用ANY_VALUE()聚合函数，同一分组维度下这些标签字段通常具有一致性，该函数不会改变原有值，同时满足BigQuery的分组规则。
2. 匹配需求分组：严格按照指定的分组字段调整GROUP BY列表，补充了需求中提到的credit_id（对应credits.id）字段。
3. 优化WHERE条件：将credits.amount != 0改为IFNULL(credits.amount, 0) != 0，避免LEFT JOIN后credits为NULL时的判断错误。
4. 日级聚合逻辑：通过DATE(usage_start_time)实现日级分组，用MIN(usage_start_time)获取当日首次使用时间，DATE(MAX(usage_end_time))获取当日末次使用时间的日期，符合日级聚合需求。
5. 冗余分组清理：移除了原查询中不必要的重复分组项，同时确保所有SELECT中的非聚合字段都存在于GROUP BY中。

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