时间戳

首页 > 解决方案 > 如何使用正则表达式提取来抓取特定字符之间的文本？

google-bigquery - 如何使用正则表达式提取来抓取特定字符之间的文本？

问题描述

我目前正在尝试 REGEX EXTRACT 的不同类型的公式，试图玩弄并完全理解它。下面将是我正在使用的数据的示例以及我用来获取所需内容的当前代码。（请批评我的代码，如果它可以写得更好，因为我还在学习 REGEX EXTRACT）

Sample_Data
AAAA;BBBB;CCCC;A1=1234;DDDD;EEEE
FFFF;GGGG;A1=2345;A2=4567,2345;RRRR;KKKK
SSSS;TTTT;UUUU;VVVV;A1=3456;GGGG;UUUU
UUUU:WWWW;QQQQ;IIII;A1=9876;A2=7654,7890;UUUU

我拥有的当前代码是：

SELECT
 REGEXP_EXTRACT(Sample_Data, r'(?:^|;)A1=(\d*)') AS A1,
 REGEXP_EXTRACT(Sample_Date, r'(?:^|;)A2=(\d*)(?:;)') AS A2,
 SPLIT(REGEXP_EXTRACT(Sample_Data, r'(?:^|;)A2=(\d*\,\d*)(?:;)'), ",")[offset(1)] AS A2_v1
FROM
 db.Sample

我得到的输出是：

  A1    |   A2    |  A2_v1
1234    |  NULL   |  NULL
2345    |  4567   |  2345
3456    |  NULL   |  NULL
9876    |  7654   |  7890

有了输出，这就是我所期望的。但是，我有几个不同的问题，正如您在输出第 2 行中看到的那样：

2345  |  4567  |  2345

它有2345两次，有没有办法让它只显示2345一次，例如：

2345  |  4567  |  NULL

我的思考过程是拥有 aCASE WHEN并让它检查REGEXP_EXTRACT公式以查看它们是否匹配以及是否确实抛出 a NULL。有没有更好的方法来做到这一点，或者这会是最好的结果吗？

我的第二个问题是，假设我们有以下示例数据：

AAAA;GGGG;DDDD;A1=1234;A2=7890,1234,3456;DDDD
BBBB;DDDD;CCCC;FFFF;A1=2345;A2=8907,1234,4567,8976;WWWW;GGGG
CCCC;EEEE;A1=6789;A2=34567,8901,3456,12345;TTTT

使用我拥有的当前公式，它只能获得A1和一部分A2。但是，我如何将公式转换为能够获取所有由 分隔的数字,？我正在寻找的最终结果如下：

  A1  |  A2  |  A2_v1 | A2_v2 | A2_v3
 1234 | 7890 |  1234  | 3456  | NULL
 2345 | 8907 |  1234  | 4567  | 8976
 6789 | 34567|  8901  | 3456  | 12345

我将如何使这项工作正常进行？会不会是以下内容的变体：

SPLIT(REGEXP_EXTRACT(Sample_Data, r'(?:^|;)A2=(\d*\,\d*)(?:;)'), ",")[offset(1)] AS A2_v1

和有什么不同offset？或者是否有一种不同的公式可以做到这一点？

任何帮助将非常感激！！

标签： google-bigquery

解决方案

为了避免重复数字，我认为您的想法CASE ... WHEN是一个好方法。在这种情况下，IF条件可以用作简写。通过制作原始查询，子查询更容易比较值。

对于 A2，在 REGEXP_EXTRACT 中，您不能使用多个匹配组，因此可以通过在正则表达式中更加宽容来捕获完整的数字。例如，使用的正则表达式：

'A2=([\d,]*)'

还将匹配以下表达式：A2=1,2,3,4,5在您的场景中可能允许也可能不允许。可以改进正则表达式以完全匹配您正在寻找的内容；但是，它需要更长的时间，或者需要使用多个匹配组。例子：

'A2=((\d{4},?)+)'

此正则表达式将匹配一个或多个由四个数字组成的序列，后跟零个或一个逗号。要使用此正则表达式，您可以REGEXP_REPLACE改用，并保留所需的部分，同时删除其他所有内容。但是，这种方法似乎使事情变得复杂而不是简化。

最后，由于数组中值的数量可能会发生变化，我建议使用SAFE_OFFSET来访问数组值，因为只要出现索引超出范围错误，这将返回空值。

您可以使用以下 SQL 查询作为参考：

SELECT
  A1,
  IF(A2[SAFE_OFFSET(0)] = A1, NULL, A2[SAFE_OFFSET(0)]) AS A2,
  IF(A2[SAFE_OFFSET(1)] = A1, NULL, A2[SAFE_OFFSET(1)]) AS A2_V1,
  IF(A2[SAFE_OFFSET(2)] = A1, NULL, A2[SAFE_OFFSET(2)]) AS A2_v2,
  IF(A2[SAFE_OFFSET(3)] = A1, NULL, A2[SAFE_OFFSET(3)]) AS A2_v3
FROM (
  SELECT
    REGEXP_EXTRACT(Sample_Data, r'A1=(\d{4})') as A1,
    SPLIT(REGEXP_EXTRACT(Sample_Data, r'A2=([\d,]*)'), ",") AS A2
  FROM
   (
    SELECT 'BBBB;DDDD;CCCC;FFFF;A1=2345;A2=8907,1234,4567,2345;WWWW;GGGG' AS Sample_Data
    UNION ALL
    SELECT 'CCCC;EEEE;A1=6789;TTTT' AS Sample_Data)
)

推荐阅读