Google Data Studio中的REGEXP_EXTRACT；持续获取"Null" [英] REGEXP_EXTRACT in Google Data Studio; Keep Getting 'Null'

问题描述

我正在Google的Data Studio中创建报表，过去我已经使用Keyword维度上的REGEXP_MATCH结合CASE语句成功创建了多个自定义维度，以创建所需的维度.这个让我感到难过.

I am creating reports in Google's Data Studio, and I have successfully created several custom dimensions in the past using REGEXP_MATCH on the Keyword dimension combined with CASE statements to create the dimensions I need. This one has me stumped.

我有通过Keyword维度输入的数据，其中包含一个我想提取并显示为自定义维度的子字符串.

I have data coming in through the Keyword dimension that contains a substring that I would like to extract and display as a custom dimension.

通过的关键字数据的一部分看起来像这样:

A subset of the keyword data coming through looks like this:

09172018_rp_ws_1_og_
img s4_ac_p_act_
img s5_ws_5_m_
img s4_ws_5_m_

我正在尝试使用REGEXP_EXTRACT创建一个名为Image type的新计算字段，该字段将所有条目以img开头，后跟一个空格，然后是任何字母数字，然后以下划线结尾，将所有条目分组.因此，所有带有img s4的条目将被分组在一起，而img s5将会被分组在一起.关键字维度中没有该模式的所有内容都可以完全排除在数据集中.

I am trying to use REGEXP_EXTRACT to create a new calculated field called Image type that is a dimension that groups all entries with starting with img, followed by a space, and then any alphanumeric afterwards ending with an underscore. So all entries with img s4 would be grouped together, img s5 would be grouped together. Anything in the keyword dimension without that pattern can be left out of the dataset entirely.

使用REGEXP_EXTRACT不能获得除null以外的任何结果.

I am not able to get any results except null using REGEXP_EXTRACT.

即使只是尝试REGEXP_EXTRACT(Keyword, '.*img.*')，在为新的计算字段输入公式时，也会产生null.

Even just trying REGEXP_EXTRACT(Keyword, '.*img.*') yields null when entering in the formula for the new calculated field.

让我感到困扰的是，我尝试以下操作只是为了查看我的语法是否已关闭，并且该公式确实返回了结果(由于图像类型未聚合，因此不是我想要的结果).

What is stumping me is I tried the following just to see if my syntax was off, and this formula does return results (just not what I want as the image types are not aggregated).

CASE
  WHEN (REGEXP_MATCH(Keyword, '.*img.*')) THEN Keyword
  ELSE "Not Set"
END

知道我要去哪里错了吗?无论输入什么，都无法从REGEXP_EXTRACT(Keyword, 'your reg expression here')中获取任何输出.

Any idea where I am going wrong? I can't get any output out of REGEXP_EXTRACT(Keyword, 'your reg expression here') no matter what I enter.

推荐答案

请注意，为了从REGEXP_EXTRACT中提取任何文本，您应该在正则表达式模式中定义一个捕获组.简而言之，请用一对未转义的括号将要提取的部分括起来.

Mind that in order to extract any text from REGEXP_EXTRACT, you should define a capturing group inside the regex pattern. In short, enclose the part you need to extract with a pair of unescaped parentheses.

现在，要在字符串的开头匹配img，您需要使用^锚，它与字符串位置的开头匹配.

Now, to match img at the start of the string you need to use ^ anchor, it matches the start of a string position.

要匹配1个或多个字符，请使用+.

To match 1 or more chars, use +.

因此，您可以根据实际规则使用以下任何一种方法:

So, you may use any of the following depending on your actual rules:

REGEXP_EXTRACT(Keyword, '^img ([a-zA-Z0-9_]+)')
REGEXP_EXTRACT(Keyword, '^img\\s+(\\w+)')
REGEXP_EXTRACT(Keyword, '^img\\s+(.+)')

详细信息

• ^-字符串开头
• img -文字子字符串
• ([a-zA-Z0-9_]+)-捕获组1:一个或多个字母，数字或_
• \s+-1个或多个空格
• \w+-1个或多个单词字符:字母，数字或_
• .+-除换行符以外的1个或更多字符.

• ^ - start of string
• img - a literal substring
• ([a-zA-Z0-9_]+) - Capturing group 1: one or more letters, digits or _
• \s+ - 1 or more whitespaces
• \w+ - 1 or more word chars: letters, digits or _
• .+ - 1 or more chars other than line break chars.

这篇关于Google Data Studio中的REGEXP_EXTRACT；持续获取"Null"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！