使用like搜索数组的每个元素 [英] Searching each element of array using like

问题描述

关于我的问题，我需要一些专业的建议。

I would like some professional advice regarding my problem.

您看到我们的数据库正在使用ETL来检索大量数据。

You see our database is using an ETL to retrieve massive data. Some or rather most of these data are aggregated.

问题是我需要根据所选站点检索数据

Problem is I need to retrieve a data, base on the selected Stations

例如。我选择了 MNL 。

现在，在汇总列中，我们有如下数组：

Now, in aggregated column we have arrays like:

{MNL-CEB,CEB-MNL,DVO-MNL,MNL-DVO,DVO-CEB,CEB-DVO}

现在，使用我选择的代码（MNL），我应该只能从数组中选择以下元素。

Now, given with my selected code (MNL), I should only be able to pick the following elements from the array.

MNL-CEB,
CEB-MNL,
DVO-MNL,
MNL-DVO

我一直在尝试各种情况，但都没有成功。希望你们能帮助我。谢谢！

I've been trying various where conditions with no success. Hope you guys can help me out. Thanks!

下面是我一直在使用的一段代码-

Here's a piece of code I've been using below -

select distinct
    unnest(fpd.segment_agg) segments
 from daylightreport.f_dailysales_agg fpd

$ b $中选择不同的
unnest（fpd.segment_agg）细分
b

数据太大而无法嵌套，导致脚本加载更多内容。

The data is too big to unnest, causing the script to load more.

编辑-我还使用了1个站号。例如
fpd.segment_agg IN（'MNL'，'CEB'）或与此类似的内容。

Edit - I'm also using more than 1 station code. For instance Where fpd.segment_agg IN ('MNL','CEB') or something similar to this.

预先感谢！

推荐答案

避免嵌套的一种方法是将数组转换为字符串，然后进行正则表达式匹配该字符串：

One way that avoids unnesting is to convert the array to a string then do a regex match on that string:

select *
from daylightreport.f_dailysales_agg fpd
where array_to_string(fpd.segment_agg, ',') ~ '(-){0,1}(MNL)(-){0,1}';

正则表达式确保搜索字符串不会出现在另一个字符串中。如果仍然无法做到这一点，那么简单的操作也可能会做到。这也假设数据永远不会包含，

The regex makes sure the search string doesn't appear as part of another string. If that can't happen anyway, a simple like would probably do as well. This also assumes that the data never contains a ,

这不虽然速度很快，但是避免了多行嵌套。

This is not going to be fast though, but avoids the unnesting in multiple rows.

另一个选择（虽然可能不是真的快得多）是编写一个遍历数组元素并使用LIKE运算符的函数：

Another option (although probably not really that much faster) is to write a function that loops through the array elements and uses the LIKE operator:

create or replace function any_element_like(p_elements text[], p_pattern text)
  returns boolean
as
$$
declare
   l_word text;
begin
  foreach l_word in array p_elements 
  loop
    if l_word like p_pattern then 
      return true;
    end if;
  end loop;
  return false;
end;
$$
language plpgsql;

可以这样使用：

select *
from daylightreport.f_dailysales_agg fpd
where any_element_like(fpd.segment_agg, '%MNL%');

这篇关于使用like搜索数组的每个元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！