PostgreSQL-利用递归的方法获得一个数组的所有子数组

create or replace function subarrays(inarray anyarray)
returns table (inarr anyarray)
as $$
WITH RECURSIVE r AS (
 SELECT inarray ar1
 UNION ALL
 SELECT array_remove(ar1 ,sub)
 FROM (
 SELECT ar1, unnest(ar1) sub FROM r
 ) x
 WHERE array_length(ar1, 1) > 1
)
SELECT distinct ar1 FROM r ;
 $$language sql;

例子,

select * from subarrays('{a,b,c,d}'::text[]);
-----------------
inarr
{d}
{b,d}
{a,b,d}
{a,d}
{c}
{b,c}
{a,b,c}
{a,c}
{b}
{c,d}
{a,b,c,d}
{a,c,d}
{a,b}
{a}
{b,c,d}

实际测试下来,存在性能问题,不知道是否仍然有优化空间,
另外一个实现

create or replace function subarrays(inarray anyarray, len int)
returns table (inarr anyarray)
as $$
with  a as (select unnest(inarray) aa)
	select array_remove(inarray, aa) from a 
	union select subarrays(array_remove(inarray, aa), len-1) from a where len>2;
$$LANGUAGE SQL;

select  subarrays(array['a','b','c','d'], 4) ;

回头比较下两个性能差异(目前看来两个都满足我做关联规则时实现多推多的情况)。

 

P.S.

PL/Python 版本,

CREATE or replace FUNCTION subsets1(itemset text[])
  RETURNS table(a text[])
AS $$
	from itertools import chain, combinations
	#import numpy as np
	#return array
	#return np.array(list(chain(*[combinations(itemset, i + 1) for i, a in enumerate(itemset)])))
	#return list/table
	return list(chain(*[combinations(itemset, i + 1) for i, a in enumerate(itemset)]))
$$ LANGUAGE plpythonu;