在BigQuery中提取域名、子域名及子子域名的技术求助

注：你提到的“子域名”“子子域名”实际为URL的路径层级，以下按你的示例定义实现。

一、Excel公式法（适合中小批量URL） #

假设目标URL存于A列，从A2单元格开始：

1. 提取域名（协议+根域名）

=LEFT(A2,FIND("//",A2)+1)&TEXTBEFORE(TEXTAFTER(A2,"//"),"/",1)&"/"

2. 提取第一级路径（即你所说的“子域名”）

=LET(
    proto,LEFT(A2,FIND("//",A2)+1),
    dom,TEXTBEFORE(TEXTAFTER(A2,"//"),"/",1),
    path1,TEXTBEFORE(TEXTAFTER(A2,dom&"/"),"/",1),
    IF(path1<>"",proto&dom&"/"&path1,"无第一级路径")
)

3. 提取第二级路径（即你所说的“子子域名”）

=LET(
    proto,LEFT(A2,FIND("//",A2)+1),
    dom,TEXTBEFORE(TEXTAFTER(A2,"//"),"/",1),
    path1,TEXTBEFORE(TEXTAFTER(A2,dom&"/"),"/",1),
    path2,TEXTBEFORE(TEXTAFTER(A2,dom&"/"&path1&"/"),"/",1),
    IF(path2<>"",proto&dom&"/"&path1&"/"&path2,"无第二级路径")
)

二、Python脚本法（适合超大量URL） #

用脚本可快速处理上万个URL，示例代码：

import urllib.parse

def get_url_levels(url):
    parsed_url = urllib.parse.urlparse(url)
    # 生成域名（协议+根域名）
    domain = f"{parsed_url.scheme}://{parsed_url.netloc}/"
    # 拆分路径并过滤空值
    path_segments = [seg for seg in parsed_url.path.split('/') if seg]
    
    # 第一级路径
    level1 = f"{parsed_url.scheme}://{parsed_url.netloc}/{path_segments[0]}" if len(path_segments) >= 1 else "无第一级路径"
    # 第二级路径
    level2 = f"{parsed_url.scheme}://{parsed_url.netloc}/{path_segments[0]}/{path_segments[1]}" if len(path_segments) >= 2 else "无第二级路径"
    
    return {"域名": domain, "第一级路径": level1, "第二级路径": level2}

# 测试示例
test_urls = [
    "https://stackoverflow.com/users/17141604/badinmaths",
    "https://stackoverflow.com/questions/ask"
]
for url in test_urls:
    result = get_url_levels(url)
    print(f"URL: {url}")
    print(f"- 域名: {result['域名']}")
    print(f"- 第一级路径: {result['第一级路径']}")
    print(f"- 第二级路径: {result['第二级路径']}")
    print("---")

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