BigQuery查询GA数据时SELECT未在GROUP BY的列报错及解决方法

报错根因 #

• 你的报错本质是BigQuery严格遵循SQL标准语法校验规则：只要出现在SELECT列表里的非聚合列，必须全部纳入GROUP BY子句，否则会直接判定为逻辑不合法。
• 传统部分数据库支持“SELECT未纳入GROUP BY的列”，属于非标准的兼容模式：引擎会随机取分组内任意一行的目标列值，结果完全不确定——因为一个分组下如果存在多行不同的device_category、user_type值，引擎根本没有规则判断要返回哪一行的对应值，BigQuery从设计层面直接禁止这种无确定性的取数逻辑。

适配需求的可实现方案 #

你的核心目标是：查询2020-11-01至2021-01-31期间，Organic Search渠道下按月度聚合的营收Top产品，不需要按设备、用户类型维度拆分分组，仅需带出对应关联的设备、用户类型信息。根据对维度值的精度要求，可以选择两种实现方式：

方案1：使用内置聚合函数ANY_VALUE()快速实现 #

• 原理：BigQuery提供的ANY_VALUE(col)聚合函数，可在指定分组范围内返回任意一行的目标列值，无需将该列纳入GROUP BY子句，完全匹配“不按维度分组、但需要带出列值”的需求。
• 调整后的可运行查询：

SELECT
  month, 
  year, 
  ANY_VALUE(device_category) AS device_category, 
  ANY_VALUE(user_type) AS user_type, 
  product_name, 
  ROUND(SUM(item_revenue),2) AS item_revenue 
FROM 
  `ProjectName.DatasetName.GA_REPORT_3_*` 
WHERE 
  _table_suffix BETWEEN '20201101' AND '20210131' 
  AND channel_grouping = 'Organic Search' 
GROUP BY 
  month, 
  year, 
  product_name 
ORDER BY 
  item_revenue DESC;

• 适用场景：如果你的GA表拆分逻辑下，同一个「年-月-渠道-产品」组合仅对应唯一的device_category、user_type值，或者你仅需要该分组下的维度参考值、不需要精准匹配最高营收对应的维度组合，这个方案可以直接替换原有不生效的查询。

方案2：窗口函数精准匹配最高营收对应的维度值 #

• 原理：先按细分维度计算各组合的营收值，通过窗口函数定位每个「年-月-产品」下贡献营收最高的device_category+user_type组合，再按月度+产品维度聚合总营收，关联上精准匹配的维度值，最终筛选每个月的Top营收产品。
• 可运行查询：

WITH split_dim_revenue AS (
  SELECT
    month,
    year,
    product_name,
    device_category,
    user_type,
    SUM(item_revenue) AS split_revenue,
    -- 对每个月+产品下的维度拆分按营收降序排序
    ROW_NUMBER() OVER(PARTITION BY year, month, product_name ORDER BY split_revenue DESC) AS rank_in_product
  FROM `ProjectName.DatasetName.GA_REPORT_3_*`
  WHERE 
    _table_suffix BETWEEN '20201101' AND '20210131'
    AND channel_grouping = 'Organic Search'
  GROUP BY month, year, product_name, device_category, user_type
),
monthly_total_revenue AS (
  SELECT
    month,
    year,
    product_name,
    ROUND(SUM(split_revenue),2) AS item_revenue
  FROM split_dim_revenue
  GROUP BY month, year, product_name
)
SELECT 
  t.month,
  t.year,
  d.device_category,
  d.user_type,
  t.product_name,
  t.item_revenue
FROM monthly_total_revenue t
-- 关联取对应产品下营收最高的维度组合
LEFT JOIN split_dim_revenue d
  ON t.year = d.year
  AND t.month = d.month
  AND t.product_name = d.product_name
  AND d.rank_in_product = 1
-- 筛选每个月总营收最高的Top1产品
QUALIFY ROW_NUMBER() OVER(PARTITION BY t.year, t.month ORDER BY t.item_revenue DESC) = 1
ORDER BY t.item_revenue DESC;

• 适用场景：需要保证带出的device_category、user_type值，是对应产品下贡献最高营收的维度组合，不存在取值偏差的场景。

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