请求协助编写BigQuery SQL：解析逗号分隔IP字符串为带序号列

方案1：静态列写法（已知最大IP数量） #

如果能确定数据中最多包含的IP个数，直接通过数组索引提取对应位置的IP作为列：

WITH sample_data AS (
  SELECT '1.1.1.1, 12.12.12.12' AS ip_str UNION ALL
  SELECT '1.1.1.1, 12.12.12.12, 3.3.3.3' AS ip_str
)
SELECT
  ip_str,
  -- 拆分并清理IP数组，处理逗号前后的空格
  ARRAY(SELECT SAFE_TRIM(ip_item) FROM UNNEST(SPLIT(ip_str, ',')) ip_item) AS ip_array,
  -- 按位置提取IP生成对应列
  ip_array[ORDINAL(1)] AS ip_1,
  ip_array[ORDINAL(2)] AS ip_2,
  ip_array[ORDINAL(3)] AS ip_3 -- 若最大IP数更多，继续添加对应行
FROM sample_data

方案2：动态列写法（自动适配IP数量） #

如果IP字符串长度不固定，可通过动态SQL自动生成对应数量的列：

DECLARE max_ip_count INT64;

-- 第一步：统计数据中最多包含的IP数量
SET max_ip_count = (
  SELECT MAX(ARRAY_LENGTH(ARRAY(SELECT SAFE_TRIM(ip_item) FROM UNNEST(SPLIT(ip_str, ',')) ip_item)))
  FROM your_table_name -- 替换为实际表名
);

-- 第二步：动态生成查询语句，自动创建ip_1、ip_2...列
EXECUTE IMMEDIATE FORMAT("""
WITH processed_data AS (
  SELECT
    *,
    ARRAY(SELECT SAFE_TRIM(ip_item) FROM UNNEST(SPLIT(ip_str, ',')) ip_item) AS ip_array
  FROM your_table_name -- 替换为实际表名
)
SELECT
  %s
FROM processed_data
""", STRING_AGG(FORMAT("ip_array[ORDINAL(%d)] AS ip_%d", idx, idx), ', ')
FROM UNNEST(GENERATE_ARRAY(1, max_ip_count)) idx);

关键说明 #

• SAFE_TRIM用于清理IP元素前后的空格，适配逗号后带空格或不带空格的字符串格式
• ORDINAL(n)用于按1-based索引提取数组元素，对应IP的位置序号
• 动态写法需确保有足够权限执行EXECUTE IMMEDIATE语句

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