从BigQuery表中查询域名后缀

Question

我正在尝试从我的网站表中获取域名后缀，但是，在BigQuery中没有反向功能，并且我的域名有域名，如example.example.com。因此，我无法设置'.‘的第一次/第三次出现。因为存在数量不一致的“.”

SELECT
  SUBSTR(Domain,( INSTR(Domain,'.')+1)) AS user_tld,
  COUNT(*) AS activity_count
FROM [table]
GROUP EACH BY
  user_tld
  HAVING
  user_tld IS NOT NULL AND NOT user_tld 
  IN ('')
  ORDER BY 
  user_tld DESC
  LIMIT 250;

这就是我目前所在的位置，只能列出整个域名或第一个‘’之后的域名。

Answer 1

1. BQ有一些很棒的URL函数：https://developers.google.com/bigquery/query-reference?#urlfunctions
2. if这不能完成这项工作，试着使用regexp_extract而不是substring，你可以为你的字符串定义确切的字符串结构，如果你愿意，还可以定义它以从字符串末尾开始匹配。

Answer 2

正如user2881671所说，您可以使用TLD()函数：

SELECT TLD('http://' + req_host), COUNT(*) c
FROM [httparchive:runs.2014_01_01_requests]
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1000

17130999    .com     
3106860     .net     
894779      .ru  
538917      .de  
504799      .org     
252716      .jp  
247244      .com.br  
225529      .fr  
218345      .pl  
206532      .co.uk  

请注意，TLD()“足够智能”，可以识别出TLD是“.co.uk”而不是“.uk”。

如果你只想要'.uk‘部分，正则表达式也不错：

SELECT COUNT(*) c, REGEXP_EXTRACT(req_host, r'(\.[^.:]*)\.?:?[0-9]*$')
FROM [httparchive:runs.2014_01_01_requests]
GROUP BY 2
ORDER BY 1 DESC
LIMIT 1000;

17130999    .com     
3106860     .net     
903360      .ru  
539167      .de  
504799      .org     
491532      .jp  
276205      .br  
258811      .cn  
237798      .pl  
230407      .fr