BigQuery：SPLIT（）仅返回一个值。

Question

我有一个页面URL列，其中的组件由/分隔。我尝试在BigQuery中运行SPLIT()函数，但它只给出第一个值。我想要特定列中的所有值。

我不理解在Split string into multiple columns with bigquery中提到的Regexp_extract()示例的用法。

我需要类似于REGEX_SPLIT_TO_TABLE(<String>, <DELIMITER>)的东西，它将单个字符串转换为多个列。

查询：

SELECT PK, 
DATE(TIMESTAMP(CONCAT(SUBSTR(date,1,4),'-',SUBSTR(date,5,2),'-',SUBSTR(date,7,2),' 00:00:00'))) as visit_date,
hits_page_pagePath,
split(hits_page_pagePath,'/')
FROM [Intent.All2mon] limit 100

- Saumil Agrawal

---

数据长什么样？ - Felipe Hoffa

嗨，菲利普，你能帮我理解如何准备正则表达式以从每个实体中提取位于“/”符号内的信息吗？ - Saumil Agrawal

在BigQuery中，您无法生成可变数量的列，只能生成常量数量的列，因此您无法完全按照所需进行操作。如果您可以在URL组件数量上设置上限，则可以生成相应数量的列，并在特定URL具有较少组件时将其设置为NULL。 - sprocket

Answer 1

2018标准SQL更新：

#standardSQL
SELECT SPLIT(path, '/')[OFFSET(0)] part1,
       SPLIT(path, '/')[OFFSET(1)] part2,
       SPLIT(path, '/')[OFFSET(2)] part3
FROM (SELECT "/a/b/aaaa?c" path)

---

现在我明白您想要它们分别放在不同的列中。

一个替代您提供的查询的方法：

SELECT FIRST(SPLIT(path, '/')) part1,
       NTH(2, SPLIT(path, '/')) part2,
       NTH(3, SPLIT(path, '/')) part3
FROM (SELECT "/a/b/aaaa?c" path)

NTH(X, SPLIT(s)) 将从 SPLIT(s) 的结果中提供第 X 个值。FIRST(s) 和 NTH(1, s) 是相同的。

- Felipe Hoffa

---

你能提供一个检查，以防在某些情况下[OFFSET(2)]不存在而导致溢出的方法吗？类似这样：IF(SPLIT(path, '/')[OFFSET(2)].doesnt_exist), Null, SPLIT(path, '/')[OFFSET(2)]？ - Lev

加上"SAFE"前缀，或者发布一个新问题 ;) - Felipe Hoffa

@FelipeHoffa，第二个查询在BigQuery中似乎无法正常工作。但第一个查询很好用！ - Haipeng Su

Answer 2

您也可以尝试使用SPLIT函数，但是需要知道您的URL将有多少个“/”，或者进行足够的条目输入，以便如果您的URL包含更多“/”，则仍然可以在单独的列中获取这些值。

  SPLIT(`url`, '/')[safe_ordinal(1)] AS `Col1`, 
  SPLIT(`url`, '/')[safe_ordinal(2)] AS `Col2`,
  SPLIT(`url`, '/')[safe_ordinal(3)] AS `Col3`, 
  SPLIT(`url`, '/')[safe_ordinal(4)] AS `Col4`,
  .
  .
  SPLIT(`url`, '/')[safe_ordinal(N)] AS `ColN`

- LondonUK

Answer 3

在标准 SQL 中，您可以使用以下函数：

array[OFFSET(zero_based_offset)]
array[ORDINAL(one_based_ordinal)]

so

SELECT SPLIT(path, '/')[OFFSET(1)] part2,
       SPLIT(path, '/')[ORDINAL(2)] part2_again,
       SPLIT(path, '/')[ORDINAL(3)] part3
FROM (SELECT "/a/b/aaaa?c" path)

part2   part2_again part3    
a       a           b

在这种情况下，part1是空字符串（即第一个斜杠之前）。

- marengaz

Answer 4

这对我有效：

SELECT SPLIT(path, '/') part
FROM (SELECT "/a/b/aaaa?c" path)

Row part     
1   a    
2   b    
3   aaaa?c

不确定为什么它对你不起作用。你的数据是什么样子？

- Felipe Hoffa

---

SPLIT()函数将列值拆分为行，而不会添加行号。 - Saumil Agrawal

我需要的是一种方法，将每个在“/”之间的字符串实例分隔到单独的列中...而不需要为每个可能的实例运行单独的split()查询... - Saumil Agrawal

我已经构建了这个查询：SELECT 日期，点击时间，fullVisitorId，visitNumber，hits_hitNumber， hits_page_pagePath， REGEXP_EXTRACT(hits_page_pagePath,r'/(\w*)/') as one, REGEXP_EXTRACT(hits_page_pagePath,r'/\w/(\w)') as two, REGEXP_EXTRACT(hits_page_pagePath,r'/.*/./(.)/') as three from [Intent.All2mon] limit 1000 但它仍然没有分离出字符串... - Saumil Agrawal

那应该可以工作 - 你能分享一个样本数据集，这样我就可以在你的数据上运行查询吗？我没有访问[Intent.All2mon]。 - Felipe Hoffa

注意：如果您选择一个目标表并取消“展开结果”，则会得到一行带有重复部件列的单个行。 - Michael Sheldon

Answer 5

已经以一种方式解决了它。

   SELECT
   date, 
   hits_time, 
   fullVisitorId, 
   visitNumber, 
   hits_hitNumber,
   X.page_path,
   REGEXP_EXTRACT(X.page_path,r'/(\w*)\/') as one,
   REGEXP_EXTRACT(X.page_path,r'/\w*\/(\w*)') as two,
   REGEXP_EXTRACT(X.page_path,r'/\w*\/\w*\/(\w*)') as three,
   REGEXP_EXTRACT(X.page_path,r'/\w*/\w*/\w*\/(\w*)\/.*') as four
   from
   (
   select 
   date, hits_time, fullVisitorId, visitNumber, hits_hitNumber,
   REGEXP_REPLACE (hits_page_pagePath, '-', '') as page_path
   from
   [Intent.All2mon]
   ) X 
   limit 1000

- Saumil Agrawal