使用GROUP BY时遇到BigQuery语法错误，请求技术支持

错误原因 #

SQL分组查询的核心规则：SELECT列表中的字段要么出现在GROUP BY子句中，要么被聚合函数（如MIN/MAX/ARRAY_AGG）包裹。你的查询中：

• action 和 time_acton 字段既未加入GROUP BY，也未使用聚合函数
• 派生字段visa依赖原始字段jsonPayload.message，但该原始字段未被分组或聚合，因此BigQuery抛出非法引用错误

解决方案 #

根据你“按visa分组展示指定格式结果”的需求，分三种场景给出具体实现：

场景1：聚合每个visa的相关数据（如所有操作、最早/最晚时间） #

先用CTE提取派生字段，再对分组后的字段做聚合操作：

WITH parsed_logs AS (
  SELECT 
    SPLIT(jsonPayload.message, ",")[OFFSET(1)] AS visa, 
    SPLIT(jsonPayload.message, ",")[OFFSET(3)] AS action, 
    timestamp AS time_acton 
  FROM `ops-center-axe-dev-9561.rstudio_vms_logs.WORKBENCH_SESSION_AUDIT_LOGS_20230109` 
  WHERE jsonPayload.message LIKE '%session_file%'
)
SELECT 
  visa,
  ARRAY_AGG(DISTINCT action) AS all_actions, -- 收集该visa的所有操作类型
  MIN(time_acton) AS first_action_time,      -- 最早操作时间
  MAX(time_acton) AS last_action_time        -- 最晚操作时间
FROM parsed_logs
GROUP BY visa

场景2：保留每个visa的所有明细记录，按分组排序 #

如果不需要聚合，只是想按visa分组展示所有明细，可以用窗口函数替代GROUP BY：

SELECT 
  SPLIT(jsonPayload.message, ",")[OFFSET(1)] AS visa, 
  SPLIT(jsonPayload.message, ",")[OFFSET(3)] AS action, 
  timestamp AS time_acton,
  ROW_NUMBER() OVER(PARTITION BY visa ORDER BY timestamp) AS group_row_num -- 给每个visa组内的记录编号
FROM `ops-center-axe-dev-9561.rstudio_vms_logs.WORKBENCH_SESSION_AUDIT_LOGS_20230109` 
WHERE jsonPayload.message LIKE '%session_file%'

场景3：每个visa只显示一条记录（如最新操作） #

如果需要每个visa仅保留最新的操作记录，可结合窗口函数过滤：

WITH parsed_logs AS (
  SELECT 
    SPLIT(jsonPayload.message, ",")[OFFSET(1)] AS visa, 
    SPLIT(jsonPayload.message, ",")[OFFSET(3)] AS action, 
    timestamp AS time_acton 
  FROM `ops-center-axe-dev-9561.rstudio_vms_logs.WORKBENCH_SESSION_AUDIT_LOGS_20230109` 
  WHERE jsonPayload.message LIKE '%session_file%'
),
ranked_logs AS (
  SELECT *,
    ROW_NUMBER() OVER(PARTITION BY visa ORDER BY time_acton DESC) AS rn
  FROM parsed_logs
)
SELECT visa, action, time_acton
FROM ranked_logs
WHERE rn = 1 -- 仅保留每个visa的最新操作

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