如何在Big Query中拆分文本字段提取国家代码及设备类型

前置规则说明 #

拆分的基础规则为：组合字符串由2~3位大写字母的国家代码 + 固定全大写枚举的设备类型拼接而成，常见设备类型枚举包括PHONE、DESKTOP、TABLET等。 #

不同场景下的实现方法 #

SQL 场景 #

正则提取法（支持MySQL 8.0+、PostgreSQL、Spark SQL等）

假设待拆分字段名为combined_code，目标表名为your_table，语句如下：

SELECT
  REGEXP_EXTRACT(combined_code, '^([A-Z]{2,3})(PHONE|DESKTOP|TABLET)$', 1) AS country_code,
  REGEXP_EXTRACT(combined_code, '^[A-Z]{2,3}(PHONE|DESKTOP|TABLET)$', 2) AS device_type
FROM your_table;

字符串截取法（兼容不支持正则的低版本数据库）

通过判断后缀匹配设备类型，再截取前缀作为国家代码：

SELECT
  CASE
    WHEN RIGHT(combined_code, 5) = 'PHONE' THEN LEFT(combined_code, LENGTH(combined_code) - 5)
    WHEN RIGHT(combined_code, 7) = 'DESKTOP' THEN LEFT(combined_code, LENGTH(combined_code) - 7)
    WHEN RIGHT(combined_code, 6) = 'TABLET' THEN LEFT(combined_code, LENGTH(combined_code) - 6)
    ELSE NULL
  END AS country_code,
  CASE
    WHEN RIGHT(combined_code, 5) = 'PHONE' THEN 'PHONE'
    WHEN RIGHT(combined_code, 7) = 'DESKTOP' THEN 'DESKTOP'
    WHEN RIGHT(combined_code, 6) = 'TABLET' THEN 'TABLET'
    ELSE NULL
  END AS device_type
FROM your_table;

Python Pandas 场景 #

使用str.extract直接批量生成两列：

import pandas as pd

# df为存储原始数据的DataFrame
pattern = r'^(?P<country_code>[A-Z]{2,3})(?P<device_type>PHONE|DESKTOP|TABLET)$'
df[['country_code', 'device_type']] = df['combined_code'].str.extract(pattern)

Excel 场景 #

假设待拆分内容存储在A列：

1. B列（设备类型）输入公式：
   =IF(RIGHT(A1,5)="PHONE","PHONE",IF(RIGHT(A1,7)="DESKTOP","DESKTOP",IF(RIGHT(A1,6)="TABLET","TABLET","")))
2. C列（国家代码）输入公式：
   =LEFT(A1,LEN(A1)-LEN(B1))

注意事项 #

• 若设备类型枚举有新增，需要同步更新所有匹配逻辑中的枚举列表
• 可新增校验逻辑过滤脏数据：仅保留country_code和device_type均不为空的有效记录

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