SQL以不同顺序选择和分组具有相同单词的记录

Question

我在表中有以下值:

餐 table 水果

id | name                        | price
-----------------------------------------
 1 | 'APPLE BANANA ORANGE'       | 12.00
 2 | 'BANANA ORANGE APPLE'       |  4.00
 3 | 'ORANGE APPLE BANANA'       | 10.00
 4 | 'LEMON APPLE BANANA ORANGE' |  7.00
 5 | 'APPLE LEMON BANANA ORANGE' |  8.00

我想选择前 3 行的所有值，我只有一个包含“APPLE BANANA ORANGE”的文本

示例

SELECT * 
FROM fruits 
WHERE name IN 
('APPLE BANANA ORANGE','BANANA ORANGE APPLE','ORANGE APPLE BANANA')

问题是这些值来自另一个表，我正在寻找一种方法来生成可能值的不同组合

也许使用一个函数:

SELECT * 
FROM fruits f 
INNER JOIN order o ON o.name IN some_function(f.name)

也许使用一些正则表达式:

SELECT * 
FROM fruits f 
INNER JOIN order o ON o.name ~ '(?=' || f.name || ')'

我尝试使用在互联网上找到的一些使用环视的正则表达式，它们带来了包含这三个词但也包含更多词的所有值。喜欢'LEMON APPLE BANANA ORANGE'和'APPLE LEMON BANANA ORANGE'

另一件事是我想对这些结果进行分组，而不管它们出现的顺序如何

SELECT sum(price) 
FROM fruits f 
INNER JOIN order o ON o.name ~ '(?=' || f.name || ')'
GROUP BY somefunction(name);

谢谢你的帮助

马塞尔艾萨克

更新

我创建了一个函数来拆分字符串的内容，对单词进行排序并返回一个包含有序单词的新字符串

CREATE OR REPLACE FUNCTION order_words(txt character varying) 
returns character varying as
$BODY$
DECLARE 
    tmp character varying;
BEGIN
    SELECT string_agg(t,' ') 
    INTO tmp
    FROM (
      SELECT rstt 
      FROM regexp_split_to_table(txt, ' ') rstt 
      ORDER BY rstt) t; 

    RETURN tmp;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

当对值执行时，它总是返回相同的字符串

select order_words('APPLE BANANA ORANGE');
    order_words     
---------------------
APPLE BANANA ORANGE
(1 row)

select order_words('BANANA ORANGE APPLE');
    order_words     
---------------------
APPLE BANANA ORANGE
(1 row)

select order_words('ORANGE APPLE BANANA');
    order_words     
---------------------
APPLE BANANA ORANGE
(1 row)

现在我可以写我的代码了

SELECT order_words(name),sum(price) 
FROM fruits f 
INNER JOIN order o ON order_words(o.name) = order_words(f.name)
GROUP BY order_words(name);

我会测试性能

Answer 1

不确定这是否比您的解决方案更快:

select f.*
from fruits f
  join orders o 
    on string_to_array(f.name, ' ') @> string_to_array(o.name, ' ')
   and cardinality(string_to_array(f.name, ' ')) = cardinality(string_to_array(o.name, ' '));

想法是将两个值拆分为数组并检查它们是否重叠。但是因为“重叠”并不意味着所有 元素都相等，所以我也在比较数组的长度。如果它们的长度相等，和重叠，则所有元素都相等。

表达式 string_to_array(f.name, ' ') 可以被索引，GIN 索引甚至可以被 @> 运算符使用。