抽象
我正在为一个数据分析工具编写代码，该工具与PostgreSQL数据库接口，并构造一个 SQL 查询以根据用户输入过滤到一组行。从广义上讲，每一行都是包含一组输入数据和关联的输出/结果的记录。我正在开发的实用程序允许用户通过对输入和输出值应用过滤器来查看此数据的不同视图。
此表中有一个字段，其中包含一个整数数组，这些整数表示一组实体的"类"，这是"输入"的一部分。这些类对输出有最直接的影响，因此此字段中的特定值分类对系统用户特别重要。有二十个唯一的"类"值，数组通常不超过六个元素。在某些情况下，单个记录中可以有两个这样的数组，它们可以单独查询，也可以组合成一组最多 12 个值。
我的系统提供了一个自由格式输入，用户可以在其中编写专门用于根据此字段的内容筛选结果的筛选条件。它允许用户指定他们希望包含在过滤器子句中的类名称列表，以及他们希望明确排除的任何类名称。此自由格式输入的语法基于预先存在的社区定义的语法，该语法在此系统外部用于表示相关数据，并在此处进行了调整以进行过滤。
给定记录中的多个实体可能具有相同的"类"，因此相同的值可以在数组中多次出现，并且用户可以指定对每个类值的实例数的约束。此数组的长度也可以变化，但用户可能只对特定项目感兴趣，因此用户可以指定通配符并对数组长度进行约束。
数组是未排序的，因为特定的顺序(最值得注意的是，第一个位置的值)有时可能很重要。
例子
存储在数据库列中的数据是一个整数数组，但出于演示目的，我将在下面的示例中使用文本类指定。用户在查询中输入这些文本名称，然后由系统将其转换为数字 ID。
示例字段数据：[A, B, B, E, B, D]
成功匹配上述内容的示例用户输入：
A B B B D E//显式编写，筛选出与此确切的项目列表匹配的行。顺序无关紧要，除非用户还选择一个选项来显式匹配第一个条目。
6*//具有长度约束的数组通配符;筛选到数组长度为 6 的任何行。
2-3B *//筛选到包含 B 的两个到三个(包括)实例以及零个或多个其他非 B 项(不受约束的数组通配符 *)的任何行。
A 2B 3XX//筛选到包含任何类(也可能是 A 和/或 B)的至少一个 A、两个 B 和三个其他项目(类通配符 XX)的任何行
所有这些目前都有效。我目前的方法是确定所有指定类的实例计数(或缺乏实例计数)的潜在上限/下限，以及数组长度本身的上限/下限，并构造一个查询来检查这些实例计数和数组长度并返回成功满足这些条件的行。
问题...
目前所有当前的语法都运行良好。然而，它纯粹是"AND"时尚 - 并且该系统的#1请求功能是引入"OR"语法，该语法通常在社区中用于表示某些类集何时被认为是可互换的。
例如：
A B|C将匹配[A,B]和[A,C].
3(B|C)会匹配[B,B,B]、[C,C,C]、[B,C,B]等。
这些类型的查询通常更复杂，2(A|B) 2(B|C|D) 2E之类的事情并不少见。这种增加复杂性的可能性是我的大脑在试图找到解决方案时开始崩溃的地方。
我相信我目前跟踪每个值的预期实例计数的解决方案本质上与此不兼容(除非我只是将事情过于复杂化或忽略某些内容)，但我一直不知道如何更好地处理它，更糟糕的是我不知道这种类型的问题甚至叫什么。我相信这会被认为是一种无序模式/规则匹配的形式，但这是一个相当广泛的保护伞，到目前为止我的搜索都没有结果。
我并不是真的希望得到一个解决方案，但如果有人认识到我正在处理的问题类型，并且知道我可以研究哪些主题来自己解决它(特别是在SQL查询的上下文中)，这将是非常有帮助的。
数据库注释
执行典型查询的数据池为 30 天，数据子集平均跨越约 300,000 行。此窗口可以增加，用户执行跨越数百万行的长期查询的情况并不少见。性能非常重要。
有问题的 SQL 数据库是外部合作伙伴数据库的副本。它通过二进制复制操作定期复制，因此在很大程度上保留了表的原始格式。可以添加其他字段以优化对某些类型数据的访问，但这必须在复制过程中的单独步骤中完成，如果可能的话，我宁愿避免这种情况。
所述问题与正则表达式非常相似，即使查询的无序性质使正则表达式不完全合适。但这可以通过定义依赖于正则表达式的AGGREGATE函数来解决。 考虑到：
-
要计算的整数数组可以转换为以"{"开头的文本，以"}"结尾，并以"，"作为分隔符
-
您的查询可以转换为文本，它是一组带有空格作为分隔符的元素。每个元素都是任何类型的正则表达式，特别是：元素可以是表示整数的简单数字字符串，元素可以像'(A|B|C)' 其中 A、B、C 是数字刺痛，以便在这些整数等之间实现"OR"运算符
您的查询可以是有序的，也可以是非有序的：有序意味着根据查询 中元素的顺序计算整数数组，无序意味着元素数组正在计算查询的每个元素，而这些元素之间没有任何顺序考虑
您的查询可能是严格或非严格的：strict意味着整数数组与查询中的元素集完全匹配，即数组中不存在与查询元素不匹配的其他整数，非严格意味着整数数组可能包含一些与查询的任何元素不匹配的整数
查询的有序和严格参数是相互独立的，即用户可能需要有序和非严格查询，或者非有序和严格查询等
下面定义的函数check_function应该涵盖您的大多数用例，包括"OR"语法：
CREATE OR REPLACE FUNCTION deduct
( str1 text
, str2 text
, reg text
) RETURNS text LANGUAGE sql IMMUTABLE AS
$$
SELECT CASE
WHEN res = COALESCE(str1,str2)
THEN NULL
ELSE res
END
FROM regexp_replace( COALESCE(str1,str2)
, reg
, ','
) AS res
$$ ;
DROP AGGREGATE IF EXISTS deduct_agg(text, text);
CREATE AGGREGATE deduct_agg
( str text
, reg text
)
( sfunc = deduct
, stype = text
) ;
-- this function returns true when init_string matches with the reg_string expression according to the parameters ordered_match and strict_match
CREATE OR REPLACE FUNCTION check_function
( init_string text -- string to be checked against the reg_string; in case of an array of integer, it must be converted into text before being passed to the function
, reg_string text -- set of elements separated by a space and individually used for checking the init_string iteratively
, ordered_match boolean -- true = the order of the elements in reg_string must be respected in init_string, false = every element in reg_string is individually checked in init_string without any matching order in init_string
, strict_match boolean -- true = the init_string mut exactly match the reg_string, false = the init_string must match all the elements of the reg_string but with some extra substrings which don't match
) RETURNS boolean LANGUAGE plpgsql IMMUTABLE AS
$$
DECLARE res boolean ;
BEGIN
CASE
WHEN ordered_match AND strict_match
THEN SELECT deduct_agg(init_string, '(,|{)' || r.reg || '(,|})$' ORDER BY r.id DESC) IS NOT DISTINCT FROM ','
INTO res
FROM regexp_split_to_table(reg_string,' ') WITH ORDINALITY AS r(reg,id) ;
WHEN NOT ordered_match AND strict_match
THEN SELECT deduct_agg(init_string, '(,|{)' || r.reg || '(,|})') IS NOT DISTINCT FROM ','
INTO res
FROM regexp_split_to_table(reg_string,' ') AS r(reg) ;
WHEN ordered_match AND NOT strict_match
THEN SELECT deduct_agg(init_string, '(,|{)' || r.reg || '(,|})') IS DISTINCT FROM NULL
INTO res
FROM regexp_replace(reg_string,' ', '.*','g') AS r(reg) ;
ELSE SELECT deduct_agg(init_string, '(,|{)' || r.reg || '(,|})') IS DISTINCT FROM NULL
INTO res
FROM regexp_split_to_table(reg_string,' ') AS r(reg) ;
END CASE ;
RETURN res ;
END ;
$$ ;
应支持以下用例：
- "A B B B D E//明确编写，筛选到与此匹配的行 确切的项目列表。顺序无关紧要" ==> 实现为
SELECT check_function(your_array_of_integers :: text, 'A B B B D E', true, true) - "6*//具有长度约束的数组通配符;过滤到数组长度为 6 的任何行。" ==> 实现为
SELECT check_function(your_array_of_integers :: text,'([0-9]+,){5}([0-9]+)',true,true)。这个用例可以通过将"6*"替换为"n*"并'{5}'reg_string中的'{' || n-1 || '}'来推广，其中n是1>的任何整数 - 具有任何顺序和严格 ==> 的"A 3B"按
SELECT check_function(your_array_of_integers :: text, 'A B B B', false, true)实现 - "A (B|C)"没有顺序，不严格==>实现为
SELECT check_function(your_array_of_integers :: text, 'A (B|C)', false, false) - "3(B|C)"没有顺序和严格 ==> 实现为
SELECT check_function(your_array_of_integers :: text, '(B|C) (B|C) (B|C)', false, true) - "2(A|B) 2(B|C|D) 2E"，没有顺序，也不严格 ==> 按
SELECT check_function(your_array_of_integers :: text, '(A|B) (A|B) (B|C|D) (B|C|D) E E', false, false)实现 - 等
尚未实现的用例：
- "2-3B"，但一些额外的家庭作业可能会实现它，我没有看到任何阻塞点。一个想法是调用函数check_function两次
SELECT check_function (..., 'B B', ..., ...) AND NOT check_function (..., 'B B B B', ..., ...)： - "2-3B *"和"A 2B 3XX"，因为在这种情况下我不清楚通配符 * 和 XX。
PS ：我是正则表达式的基本用户，因为我没有使用手册中提供的所有功能。在正则表达式中听取有经验的用户的建议可以在您的上下文中带来很多价值。