如何找出BigQuery中可用作通配符的表前缀？

以下是几种无需依赖外部工具、仅通过BigQuery SQL即可提取动态表前缀的方案，适配只读权限场景：

方法1：正则匹配提取带数字后缀的前缀 #

针对「前缀+数字串」的动态表命名规则（如events_122022），用正则直接提取前缀部分，再筛选出对应多个表的有效前缀：

WITH table_prefixes AS (
  SELECT
    -- 匹配最后下划线后的数字序列，提取下划线前的前缀
    REGEXP_EXTRACT(table_name, r'^(.*?)_\d+$') AS prefix
  FROM
    dataset.INFORMATION_SCHEMA.TABLES
  WHERE
    -- 仅保留符合「前缀_数字」格式的表
    REGEXP_CONTAINS(table_name, r'^.*_\d+$')
)
SELECT DISTINCT prefix
FROM table_prefixes
WHERE prefix IS NOT NULL
-- 可选：仅保留对应多个表的前缀，排除单个表的孤立情况
AND (SELECT COUNT(*) FROM table_prefixes tp WHERE tp.prefix = table_prefixes.prefix) > 1;

方法2：结合DDL分组+SQL内置最长公共前缀计算 #

延续你之前按DDL分组的思路，直接在SQL内对每组表名计算最长公共前缀，无需外部算法工具：

WITH grouped_tables AS (
  SELECT
    SUBSTR(ddl, STRPOS(ddl, '(')) AS common_ddl,
    ARRAY_AGG(table_name) AS table_names
  FROM
    dataset.INFORMATION_SCHEMA.TABLES
  GROUP BY SUBSTR(ddl, STRPOS(ddl, '('))
),
prefix_calculations AS (
  SELECT
    common_ddl,
    -- 递归比对字符，计算组内表名的最长公共前缀
    (SELECT 
       STRING_AGG(SUBSTR(tn, 1, pos), '') 
       FROM (
         SELECT
           tn,
           MIN(pos) OVER() AS pos
         FROM UNNEST(table_names) tn,
              UNNEST(GENERATE_ARRAY(1, LENGTH(MIN(tn) OVER()))) pos
         WHERE ALL(SUBSTR(tn, 1, pos) = SUBSTR(MIN(tn) OVER(), 1, pos))
       )
       GROUP BY pos
       LIMIT 1
    ) AS longest_common_prefix
  FROM grouped_tables
  WHERE ARRAY_LENGTH(table_names) > 1
)
SELECT DISTINCT longest_common_prefix
FROM prefix_calculations
WHERE longest_common_prefix LIKE '%_'; -- 筛选以_结尾的前缀，匹配你的需求格式

方法3：字符位置逐步比对法 #

从表名的第一个字符开始，逐步增加比对长度，找到所有表名的最长公共前缀：

WITH table_list AS (
  SELECT table_name FROM dataset.INFORMATION_SCHEMA.TABLES
),
max_check_length AS (
  SELECT MIN(LENGTH(table_name)) AS min_table_len FROM table_list
),
prefix_validation AS (
  SELECT
    pos,
    SUBSTR(table_name, 1, pos) AS prefix
  FROM table_list, max_check_length, GENERATE_ARRAY(1, min_table_len) pos
  GROUP BY pos, prefix
  HAVING COUNT(*) = (SELECT COUNT(*) FROM table_list WHERE SUBSTR(table_name,1,pos) = prefix)
),
longest_valid_prefix AS (
  SELECT prefix FROM prefix_validation ORDER BY pos DESC LIMIT 1
)
SELECT prefix FROM longest_valid_prefix
WHERE prefix LIKE '%_'; -- 确保前缀以_结尾，符合动态表命名特征

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