sql - 使用 ANY() 进行模式匹配

Question

我有一个包含 3 列的表格，其中一列是非均匀长度(模块)的文本数组。我想根据模块数组中是否存在与特定文本模式/短语匹配的元素来创建派生表。元素名称也可以不同，尽管它有一个共同的短语。下面给出了一个非工作示例:

    select machine_id, jobid, 
    case
    when '%charmm%' LIKE ANY(modules) then 'CHARMM'
    when '%gaussian%' LIKE ANY(modules) then 'GAUSSIAN'
    else 'OTHERS'
    end as package, modules
    from jobapps limit 50;

在这种情况下，Gaussian的模块名称可能有几个:gaussian/16b01，gaussian/09e01。但我想关注短语“gaussian”并创建一个新列，其中所有与文本模式“gaussian”匹配的条目都将被标记为“gaussian”。

这就是为什么我想像任何(模块)一样使用“%gaussian%”，但这是行不通的。

我可以使用:'gaussian/09e01' LIKE ANY(modules)，但在那种情况下我必须写下所有可能的值，如果用户创建了一个新的 Gaussian 模块，那么该数据将丢失。

有什么办法可以解决上面的问题吗？或者有什么更好的方法来实现这个目标？

    select machine_id, jobid, 
    case
    when '%charmm%' LIKE ANY(modules) then 'CHARMM'
    when '%gaussian%' LIKE ANY(modules) then 'GAUSSIAN'
    else 'OTHERS'
    end as package, modules
    from jobapps limit 50;

我想得到这样的东西:

    machine_id | jobid  | package  |         modules          
    ------------+--------+----------+--------------------------
    6 | 1884 | CHARMM   | {charmm}
    2 | 2305 | CHARMM   | {charmm}
    6 | 786  | GAUSSIAN | {gaussian/09e1}
    7 | 1956 | CHARMM   | {charmm}
    3 | 72037| NAMD     | {namd,intel/2018}

Answer 1

LIKE 运算符要求模式在右边，而 ANY() 表达式将始终使用数组成员作为右操作数，所以我不不要认为有简单的出路。

您可以创建自己的“反向类似”运算符，并将操作数反转:

create function reverse_like(text,text) returns boolean as
'select $2 like $1'
language sql immutable;

create operator <~~ (
  function = reverse_like,
  leftarg = text,
  rightarg = text
);

...然后查询您的数组:

case
when '%charmm%' <~~ ANY(modules) then 'CHARMM'
when '%gaussian%' <~~ ANY(modules) then 'GAUSSIAN'

如果你不想只为这个查询创建一个新的运算符，我认为你必须通过取消嵌套数组并检查子查询中的内容来做这件事:

case
when exists (select 1 from unnest(modules) u(v) where v like '%charmm%') then 'CHARMM'
when exists (select 1 from unnest(modules) u(v) where v like '%gaussian%') then 'GAUSSIAN'

... 或者:

case
when (select bool_or(v like '%charmm%') from unnest(modules) u(v)) then 'CHARMM'
when (select bool_or(v like '%gaussian%') from unnest(modules) u(v)) then 'GAUSSIAN'