BigQuery/SQL 如何从右数第二个点分割字符串提取核心域名

核心逻辑是从右往左匹配分隔符取对应位数的域名段，针对有.co.za这类特殊二级国别后缀的场景，优先匹配特殊后缀再做截取，具体实现如下：

数据库SQL实现（以MySQL为例，其他数据库函数逻辑一致仅语法略有差异） #

用MySQL内置的SUBSTRING_INDEX函数即可实现反向截取，该函数第三个参数为负数时，会从右往左数第N个点之后的所有内容：

SELECT 
    CASE
        -- 优先匹配需要保留3段的特殊后缀
        WHEN website LIKE '%.co.za' THEN SUBSTRING_INDEX(website, '.', -3)
        -- 普通通用域名保留最后2段即可
        ELSE SUBSTRING_INDEX(website, '.', -2)
    END AS website
FROM 你的数据表名;

如果后续有其他特殊后缀（比如.co.uk、.com.hk等），直接在CASE WHEN中新增对应判断条件即可。

Python代码实现 #

如果是用Python批量处理该类数据，可以参考以下代码：

# 提前定义需要保留3段的特殊后缀列表
special_suffix_list = [".co.za", ".co.uk", ".com.hk"]
original_domains = ["www.abc.google.com", "www.bcd.google.com", "wwww.efd.google.co.za"]
processed_domains = []

for domain in original_domains:
    processed = None
    for suffix in special_suffix_list:
        if domain.endswith(suffix):
            # 匹配到特殊后缀，取分割后的最后3段
            processed = ".".join(domain.split(".")[-3:])
            break
    if not processed:
        # 普通通用域名，取分割后的最后2段
        processed = ".".join(domain.split(".")[-2:])
    processed_domains.append(processed)

# 输出结果：['google.com', 'google.com', 'google.co.za']
print(processed_domains)

如果你的场景涉及大量不同类型的顶级域名、国别域名，可直接基于公共后缀列表的对应解析库做处理，不需要手动维护后缀列表。

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