时间戳

首页 > 解决方案 > 如何根据 POSTGRESQL 中 LIKE 运算符的大量可能值过滤文本？

sql - 如何根据 POSTGRESQL 中 LIKE 运算符的大量可能值过滤文本？

问题描述

编辑：我稍微改变了我的例子，因为它不正确且具有误导性。这是一个更正确的（我希望如此）。
这是一个需要解释的复杂问题，所以我会尽量说清楚。
我有一个 CASE，它通过 LIKE 运算符根据文本过滤器返回一个值。
我需要生成 1 列（class_of_event），其中包含 N 个可能的值，将一个给定的字符串分类为 N 个可能的类别。LIKE 运算符搜索到的这组值将在脚本中一次又一次地使用，并且会不时更新。
脚本或多或少是这样的：

SELECT
event,
CASE
  WHEN
    event LIKE '%MURDER%' or
    event LIKE '%KILL%' or
    ... --and so on with many other possible values...
    event LIKE '%WAR%'
  THEN 'VIOLENCE'
  WHEN
    event LIKE '%MARRIAGE%' or
    event LIKE '%MARRIED%' or
    ... --and so on with many other possible values...
    event LIKE '%WIFE%'
  THEN 'RELATIONSHIP'
  ELSE NULL 
  END class_of_event
FROM TABLE history_facts

我知道我可以使用管道 | 而不是 OR 运算符，因此编写

CASE WHEN event LIKE '%MARRIAGE%|%MARRIED%|%WIFE%' THEN 'RELATIONSHIP' ELSE null END class_of_event

而不是一长串 OR 运算符。
无论如何，这可能会变成一个非常长的字符串，因为我可能愿意扩大要查找的值集。

 此外，这组值将在（长）脚本中再次使用，如果有一天我必须连贯地重写它们，那将是一个问题。所以我尝试将这些值放在函数的返回值中：

CREATE OR REPLACE FUNCTION relationship_event()
  RETURNS text AS
$$SELECT text '%MARRIAGE%|%MARRIED%|%WIFE%'$$ LANGUAGE sql IMMUTABLE PARALLEL SAFE;

然后使用以下内容：

CASE WHEN event LIKE relationship_event() THEN 'RELATIONSHIP' ELSE null END class_of_event

这似乎是一个很好的解决方案，因为我可以在脚本开头定义或更新一次函数，然后在需要它的任何地方使用它。问题是这种方法在某些情况下表现得很好，而在其他情况下表现得非常糟糕。

那么，有没有办法：
1）编写event LIKE 'a' OR event LIKE 'b' OR event LIKE 'c' OR...
2）的合成版本并将我正在寻找的字符串存储在一些“全局变量”中，我只能重写一次并在脚本中的任何地方重复使用？

谢谢大家，这让我发疯了。我想我可以用 SAS 或 Python 轻松做到这一点，但无法在 POSTGRESQL 上实现

标签： sqlpostgresqlsql-likecase-whensql-function

解决方案

我知道我可以使用管道 | 而不是 OR 运算符，因此编写

你不能。LIKE 不支持将管道作为“或”运算符。

您可以使用数组简化表达式：

SELECT event,
       CASE
         WHEN event ilike any (array['%MURDER%','%KILL%','%WAR%']) 
           then 'VIOLENCE'
         WHEN event ilike any (array['%MARRIAGE%','%MARRIED%','%WIFE%']) 
           then 'RELATIONSHIP' 
       END as class_of_event,
class_of_event
FROM history_facts;

您可以将其放入一个函数中：

create or replace function map_event(p_input text)
  returns text
as
$$
  select CASE
           WHEN event ilike any (array['%MURDER%','%KILL%','%WAR%']) 
             then 'VIOLENCE'
           WHEN ilike any (array['%MARRIAGE%','%MARRIED%','%WIFE%']) 
             then 'RELATIONSHIP' 
         END;
$$
language sql
immutable;

然后你只需要调用函数，而不是使用 CASE 表达式：

select event, 
       map_event(event) as class_of_event
from history_facts;

推荐阅读