如何用BigQuery的REGEXP_EXTRACT提取查询中的列名

实现代码 #

以下是针对需求编写的BigQuery查询，直接基于INFORMATION_SCHEMA.JOBS_BY_PROJECT视图提取符合要求的列名：

SELECT
  job_id,
  query,
  -- 提取并去重符合条件的列名
  ARRAY(
    SELECT DISTINCT TRIM(col)
    FROM UNNEST(REGEXP_EXTRACT_ALL(LOWER(query), r'(?:max|min|avg|sum)\(([^)]+)\)|\b([a-z0-9_]+)\b(?!\s*(?:as\s+|\s)[a-z0-9_]+)', 'x')) AS match
    -- 拆分逗号分隔的多列（比如SELECT col1, col2这种情况）
    LEFT JOIN UNNEST(SPLIT(match, ',')) AS col
    ON TRUE
    -- 过滤掉空值和COUNT(*)中的*
    WHERE TRIM(col) != ''
      AND TRIM(col) != '*'
  ) AS extracted_columns
FROM
  `your-project-id.INFORMATION_SCHEMA.JOBS_BY_PROJECT`
WHERE
  -- 按需筛选查询时间范围
  DATE(creation_time) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) AND CURRENT_DATE()
  -- 排除查询元数据的语句
  AND query NOT LIKE '%INFORMATION_SCHEMA%'
  -- 只处理成功完成的查询
  AND state = 'DONE';

关键逻辑说明 #

正则表达式解析 #

正则通过先转小写实现不区分大小写匹配，核心两部分：

1. (?:max|min|avg|sum)\(([^)]+)\)：匹配MAX()/MIN()/AVG()/SUM()这类聚合函数，捕获括号内的列名；特意排除COUNT()，以此忽略COUNT(*)的情况
2. \b([a-z0-9_]+)\b(?!\s*(?:as\s+|\s)[a-z0-9_]+)：匹配独立的列名，通过负向断言排除后面跟别名的情况（不管是用AS还是直接空格分隔的别名）

额外处理 #

• 用SPLIT()拆分逗号分隔的多列，处理SELECT col1, col2这类场景
• 用TRIM()去除列名前后的空格
• 用DISTINCT去重，避免同一列被多次提取

示例验证 #

针对你提供的示例SQL：

WITH cte_sales AS (
    SELECT 
        staff_id, 
        COUNT(*) order_count  
    FROM
        sales.orders
    WHERE 
        YEAR(order_date) = 2018
    GROUP BY
        staff_id

)
SELECT
    AVG(order_count) average_orders_by_staff
FROM 
    cte_sales;

查询会返回extracted_columns = ['staff_id']，完全符合预期：

• staff_id是无别名的普通列，被正常提取
• COUNT(*)被排除，其别名order_count不会被匹配
• AVG(order_count)中的order_count是CTE里的别名，不在原始无别名列范围内，不会被提取
• average_orders_by_staff是外层查询的别名，被负向断言排除

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