BigQuery:SPLIT()只返回一个值 [英] BigQuery: SPLIT() returns only one value
问题描述
我有一个页面URL列组成部分由 /
分隔。我尝试在BigQuery中运行 SPLIT()
函数,但它只给出第一个值。我想要在特定列中的所有值。
我不明白如何使用 Regexp_extract()
示例在用bigquery将字符串拆分成多个列。
我需要类似于 REGEX_SPLIT_TO_TABLE(< String>,< DELIMITER>)
>将单个字符串转换为多列。 / p>
查询:
$ b
SELECT PK,
DATE(TIMESTAMP(CONCAT(SUBSTR(date,1,4),' - ',SUBSTR(date,5,2),' - ',SUBSTR(date,7,2),'00:00: 00')))作为visit_date,
hits_page_pagePath,
split(hits_page_pagePath,'/')
从[Intent.All2mon]限制100
现在我明白你希望他们在不同的栏目中。
您提供的查询的替代方法:
SELECT FIRST SPLIT(path,'/'))part1,
NTH(2,SPLIT(path,'/'))part2,
NTH(3,SPLIT(path,'/'))part3
FROM(SELECT/ a / b / aaaa?cpath)
NTH(X,SPLIT(s))
将提供SPLIT中的第X个值。 FIRST(s)
与 NTH(1,s)
相同
I have a page URL column components of which are delimited by /
. I tried to run the SPLIT()
function in BigQuery but it only gives the first value. I want all values in specific columns.
I don't understand how to use the Regexp_extract()
example mentioned in Split string into multiple columns with bigquery.
I need something similar to REGEX_SPLIT_TO_TABLE(<String>, <DELIMITER>)
which converts a single string into multiple columns.
Query:
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
Now I understand you want them in different columns.
An alternative to the query you provided:
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))
will provide the Xth value from the SPLIT. FIRST(s)
is the same as NTH(1, s)
这篇关于BigQuery:SPLIT()只返回一个值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!