谷歌BigQuery字符串类型的Replace函数

7

我正在尝试替换数据中的某些客户名称。 我能够使用Google BigQuery语言执行SQL来通过replace函数将字符串的一部分转换为另一个特定字符串。

Replace(CustomerName, 'ABC', 'XYZ')

然而,我还有一些需要使用替换函数的内容,以便更好地表达。

Replace(CustomerName, 'PLO', 'Rustic')
Replace(CustomerName, 'Kix', 'BowWow')

等等看。

我已经尝试过使用以下语句:

Replace(CustomerName, 'ABC', 'XYZ') OR Replace(CustomerName, 'PLO', 'Rustic') OR Replace(CustomerName, 'Kix', 'BowWow')

但是这使我得到了一个错误信息。

我也尝试过使用以下语句:

Replace(CustomerName, 'ABC', 'XYZ') AND Replace(CustomerName, 'PLO', 'Rustic') AND Replace(CustomerName, 'Kix', 'BowWow')

但是这也使我得到了一个错误信息。

我可以只使用"case when语句",然后对每个值进行硬编码,但我想知道是否有更好/更快的方法仅使用替换语句。

谢谢您的帮助。

3个回答

7
< p > CASE WHEN选项非常合理。另一个选项是将它们链接在一起:

REPLACE(
  REPLACE(
    REPLACE(
      CustomerName,
      'ABC',
      'XYZ'),
    'PLO',
    'Rustic'),
  'Kix',
  'BowWow')

你选择哪种方法取决于具体情况。串联的 REPLACE 调用可能更快,但它们可能以奇怪的方式重叠(例如,如果一个替换的输出与后续替换的输入匹配)。CASE WHEN 方法避免了这个问题,但它可能更昂贵,因为你需要执行一次操作来查找子字符串,另一次操作来实际替换它。
请注意,在使用 ANDOR 时,你试图将 REPLACE 的字符串输出作为布尔值组合,这就是为什么它失败的原因。

这个运行得非常好!非常感谢您,并感谢解释为什么它失败了。非常感激。 - Anya

0
在某些情况下,如果您需要进行大量替换,则 REPLACE 的链接可能变得不切实际且需要手动操作,这可能会让人感到烦恼。
以下解决了这个潜在问题(假设您维护具有一对一的查找表:单词,替换)。
SELECT CustomerName, fixedCustomerName FROM JS(
// input table
(
  SELECT
    CustomerName, Replacements
  FROM YourTable
  CROSS JOIN (
    SELECT 
      GROUP_CONCAT_UNQUOTED(CONCAT(Word, ',', Replacement), ';') AS Replacements
    FROM ReplacementLookup
) ,
// input columns
CustomerName, Replacements,
// output schema
"[
{name: 'CustomerName', type: 'string'},
{name: 'fixedCustomerName', type: 'string'}
]",
// function
"function(r, emit){
  var Replacements = r.Replacements.split(';');
  var fixedCustomerName = r.CustomerName;
  for (var i = 0; i < Replacements.length; i++) {
    var pat = new RegExp(Replacements[i].split(',')[0],'gi')
    fixedCustomerName = fixedCustomerName.replace(pat, Replacements[i].split(',')[1]);
  }
  emit({CustomerName: r.CustomerName,fixedCustomerName: fixedCustomerName});
 }"
)

您可以使用以下示例进行测试

SELECT CustomerName, fixedCustomerName FROM JS(
// input table
(
  SELECT
    CustomerName, Replacements
  FROM (
    SELECT CustomerName FROM
      (SELECT '1234ABC567' AS CustomerName),
      (SELECT '12 34 PLO 56' AS CustomerName),
      (SELECT 'Kix' AS CustomerName),
      (SELECT '98 ABC PLO Kix ABC 76 XYZ 54' AS CustomerName),
      (SELECT 'ABCQweKIX' AS CustomerName)
  ) YourTable
  CROSS JOIN (
    SELECT 
      GROUP_CONCAT_UNQUOTED(CONCAT(Word, ',', Replacement), ';') AS Replacements
    FROM (
      SELECT Word, Replacement FROM
        (SELECT 'XYZ' AS Word, 'QWE' AS Replacement),
        (SELECT 'ABC' AS Word, 'XYZ' AS Replacement),
        (SELECT 'PLO' AS Word, 'Rustic' AS Replacement),
        (SELECT 'Kix' AS Word, 'BowWow' AS Replacement)
    )
  ) ReplacementLookup
) ,
// input columns
CustomerName, Replacements,
// output schema
"[
{name: 'CustomerName', type: 'string'},
{name: 'fixedCustomerName', type: 'string'}
]",
// function
"function(r, emit){
  var Replacements = r.Replacements.split(';');
  var fixedCustomerName = r.CustomerName;
  for (var i = 0; i < Replacements.length; i++) {
    var pat = new RegExp(Replacements[i].split(',')[0],'gi')
    fixedCustomerName = fixedCustomerName.replace(pat, Replacements[i].split(',')[1]);
  }
  emit({CustomerName: r.CustomerName,fixedCustomerName: fixedCustomerName});
 }"
)

请注意:如果一个替换的结果匹配了后续替换的输入,仍然会存在问题。

谢谢!感谢您在此方面的帮助。 - Anya
这看起来非常不错!如何在标准SQL中实现呢? :) - Jens86

0

我相信解决这个问题有多种方法,这取决于您的数据集大小、手动制作指导表并将其上传到BigQuery的实用性以及您想要替换的数据粒度。

如果您的值非常细粒度,您可以创建一个带有不同列上的“from”和“to”值的表,并将该表与主表连接,从而非常清晰地检索这些值。

# Replace the support_table table with your actual table
WITH support_table AS (
    SELECT "ABC" AS OldValue, "XYZ" AS NewValue
)
SELECT main_table.OldValue, support_table.NewValue FROM main_table
JOIN support_table ON main_table.old_value = support_table.old_value

现在,如果您想要用某个东西替换一个大的不同值列表,您可以使用包含所有可能值的字符串的REGEXP_REPLACE

如果您有一个非常大的项目列表,您可以在包含您想要替换的所有值的表中使用STRING_AGG,或者跳过STRING_AGG步骤并手动创建该字符串。 以下两个代码片段都会生成“item1|item2|item3”。选择哪个对您来说更快。

# Replace the values_to_replace table with your actual table
WITH values_to_replace AS (
   SELECT "item1" AS ColumnWithItemsToReplace
   UNION ALL
   SELECT "item2"
   UNION ALL
   SELECT "item3"
)
SELECT STRING_AGG(ColumnsWithItemsToReplace,"|") FROM values_to_replace

SELECT r"item1|item2|item3"

STRING_AGG 可以从表格或查询中检索所有值,并使用所选分隔符将它们连接起来。如果您使用管道分隔符,您将能够创建一个字符串,如 "item1|item2|item3|..."。

对于正则表达式,管道符号被视为“或”,这意味着正则表达式将把字符串解释为“item1或item2或item3”。因此,如果您将生成的字符串作为要替换的值传递给 REGEXP_REPLACE,它将被视为有效。

以下是示例代码:

REGEXP_REPLACE(
 column_to_replace
,(SELECT STRING_AGG(ColumnWithItemsToReplace,"|") FROM `YourTable`)
,"Replacer"
)

希望能有所帮助。


网页内容由stack overflow 提供, 点击上面的
可以查看英文原文,
原文链接