如何在PostgreSQL中查找任意大小数组的所有组合(子集) [英] How to find all combinations (subset) of any size of an array in postgresql

问题描述

给出一个数组，如何在postgresql中查找某个大小的元素的所有组合(子集).例如，给定数组[1、2、3、4]，大小为3的所有组合都是

Given an array, how to find all combinations (subsets) of elements of a certain size in postgresql. For example, given an array [1, 2, 3, 4] all combinations of size 3 would be

[1, 2, 3],
[1, 2, 4],
[1, 3, 4],
[2, 3, 4]

顺序在组合中并不重要，因此[1、2、3]和[3、2、1]被视为同一组合.

Order is not important in combinations and therefore [1, 2, 3] and [3, 2, 1] are considered the same combination.

更新: 必须在运行时将所需组合的大小指定为参数，以便可以使用相同的函数/查询来查找n <=数组大小的任何大小的组合. 现有解决方案仅适用于大小为3的组合，并且每次增加大小都需要一个额外的交叉联接，这显然是不现实的.

Update: The size of the combinations required must be specified at run-time as a parameter, so that the same function/query can be used to find combinations of any size n <= size of the array. The existing solution works only for combinations of size 3 and needs one additional cross join for every increase in the size, which is clearly not practical.

推荐答案

以下函数将所请求大小的所有组合生成为一组行，每行一个组合:

The following function produces all the combinations of the requested size as a set of rows with one combination per row:

create or replace function get_combinations(source anyarray, size int) returns setof anyarray as $$
 with recursive combinations(combination, indices) as (
   select source[i:i], array[i] from generate_subscripts(source, 1) i
   union all
   select c.combination || source[j], c.indices || j
   from   combinations c, generate_subscripts(source, 1) j
   where  j > all(c.indices) and
          array_length(c.combination, 1) < size
 )
 select combination from combinations
 where  array_length(combination, 1) = size;
$$ language sql;

此函数在数组类型中是多态的.

This function is polymorphic in the array type.

这篇关于如何在PostgreSQL中查找任意大小数组的所有组合(子集)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！