PostgreSQL在递归查询中找到所有可能的组合（排列） [英] PostgreSQL find all possible combinations (permutations) in recursive query

问题描述

输入是长度为n的数组。我需要生成所有可能的数组元素组合，包括输入数组中元素较少的所有组合。

Input is an array of 'n' length. I need to generate all possible combinations of array elements, including all combinations with fewer elements from the input array.

IN: j='{A, B, C ..}'
OUT: k='{A, AB, AC, ABC, ACB, B, BA, BC, BAC, BCA..}' 

带有重复项，因此 AB BA ..

With repetitions, so with AB BA..

我尝试过类似的操作：

WITH RECURSIVE t(i) AS (SELECT * FROM unnest('{A,B,C}'::text[])) 
,cte AS (
    SELECT i AS combo, i, 1 AS ct 
    FROM t 
  UNION ALL 
    SELECT cte.combo || t.i, t.i, ct + 1 
    FROM cte 
    JOIN t ON t.i > cte.i
) 
SELECT ARRAY(SELECT combo FROM cte ORDER BY ct, combo ) AS result;

它正在生成没有重复的组合...所以我需要以某种方式进行修改。

It is generating combinations without repetitions... so I need to modify that somehow.

推荐答案

在递归查询中，将删除迭代中使用的搜索表中的术语，然后对其余记录重复查询。在您的情况下，这意味着一旦处理完第一个数组元素（ A），就不再可用于数组元素的进一步排列。要重新获得那些使用过的元素，您需要与递归查询中的数组元素表交叉联接，然后过滤掉当前排列中已使用的数组元素（ position（ti in cte .combo）= 0 ）和停止迭代的条件（ ct< = 3 ）。

In a recursive query the terms in the search table that are used in an iteration are removed and then the query repeats with the remaining records. In your case that means that as soon as you have processed the first array element ("A") it is no longer available for further permutations of the array elements. To get those "used" elements back in, you need to cross-join with the table of array elements in the recursive query and then filter out array elements already used in the current permutation (position(t.i in cte.combo) = 0) and a condition to stop the iterations (ct <= 3).

WITH RECURSIVE t(i) AS (
  SELECT * FROM unnest('{A,B,C}'::char[])
), cte AS (
     SELECT i AS combo, i, 1 AS ct 
     FROM t 
   UNION ALL 
     SELECT cte.combo || t.i, t.i, ct + 1 
     FROM cte, t
     WHERE ct <= 3
       AND position(t.i in cte.combo) = 0
) 
SELECT ARRAY(SELECT combo FROM cte ORDER BY ct, combo) AS result;

这篇关于PostgreSQL在递归查询中找到所有可能的组合（排列）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！