如何通过BigQuery SQL从User Agent字符串提取设备、浏览器、OS等信息？

生产环境首选：使用内置UA解析函数 #

User Agent字符串格式碎片化极强，手动维护正则的成本极高，BigQuery 内置了标准化的NET.PARSE_USER_AGENT函数，基于通用开源UA解析规则库持续更新，不需要手动维护匹配逻辑，就能直接输出结构化的解析结果。
针对给出的示例UA，可直接运行如下SQL完成字段提取：

WITH sample_ua AS (
  SELECT 'Mozilla/5.0 (Linux; Android 10; SM-A307G Build/QP1A.190711.020; wv) AppleWebKit/537.36 (KHTML, like Gecko) Version/4.0 Chrome/87.0.4280.141 Mobile Safari/537.36 Instagram 170.2.0.30.474 Android (29/10; 280dpi; 720x1423; samsung; SM-A307G; a30s; exynos7904; es_US; 267925733)' AS user_agent_str
)
SELECT
  parsed.user_agent_family AS browser_or_app_name,
  parsed.user_agent_major AS browser_or_app_major_version,
  parsed.os_family AS os_name,
  parsed.os_major AS os_major_version,
  parsed.os_minor AS os_minor_version,
  parsed.device_type AS device_category,
  parsed.device_family AS device_model,
  parsed.device_brand AS device_brand
FROM sample_ua,
UNNEST([NET.PARSE_USER_AGENT(user_agent_str)]) AS parsed

上述SQL针对示例UA的返回结果为：

• browser_or_app_name: Instagram
• browser_or_app_major_version: 170
• os_name: Android
• os_major_version: 10
• os_minor_version: NULL
• device_category: Mobile
• device_brand: Samsung
• device_model: SM-A307G
  函数返回的结构可覆盖绝大多数统计场景的分类需求，无需手动维护规则匹配逻辑。

特殊场景：自定义正则兜底匹配 #

如果有特定的自定义匹配规则，可通过正则表达式抽取UA中的固定标识字段，针对本次给出的示例UA，对应的提取逻辑如下：

WITH sample_ua AS (
  SELECT 'Mozilla/5.0 (Linux; Android 10; SM-A307G Build/QP1A.190711.020; wv) AppleWebKit/537.36 (KHTML, like Gecko) Version/4.0 Chrome/87.0.4280.141 Mobile Safari/537.36 Instagram 170.2.0.30.474 Android (29/10; 280dpi; 720x1423; samsung; SM-A307G; a30s; exynos7904; es_US; 267925733)' AS user_agent_str
)
SELECT
  'Android' AS os_name,
  -- 匹配Android后的大版本号
  REGEXP_EXTRACT(user_agent_str, r'Android (\d+)') AS os_major_version,
  -- 优先匹配App标识，其次匹配Chrome内核标识
  COALESCE(
    REGEXP_EXTRACT(user_agent_str, r'Instagram (\d+\.\d+\.\d+\.\d+)'),
    REGEXP_EXTRACT(user_agent_str, r'Chrome/(\d+\.\d+\.\d+\.\d+)')
  ) AS browser_or_app_version,
  -- 匹配UA末尾括号段内的品牌字段
  REGEXP_EXTRACT(user_agent_str, r'; (samsung|Xiaomi|Huawei|OPPO|vivo);') AS device_brand,
  -- 匹配开头括号内的设备型号
  REGEXP_EXTRACT(user_agent_str, r'Android \d+; ([^;]+) Build/') AS device_model
FROM sample_ua

需要注意：自定义正则的覆盖范围有限，遇到未预设格式的UA会返回NULL，仅适合做特定场景的补充匹配，不建议作为全量UA解析的主力方案。

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