sql - 以有效的方式从数组元素中获取所有可能的组合

Question

数组中最多有 5 个元素，例如给定:{1,2,3,4,5}，我需要从这个数组中获取所有可能的唯一组合，预期结果是:

 {1}
 {1,2}
 {1,2,3}
 {1,2,3,4}
 {1,2,3,4,5}
 {1,2,3,5}
 {1,2,4}
 {1,2,4,5}
 {1,2,5}
 {1,3}
 {1,3,4}
 {1,3,4,5}
 {1,3,5}
 {1,4}
 {1,4,5}
 {1,5}
 {2}
 {2,3}
 {2,3,4}
 {2,3,4,5}
 {2,3,5}
 {2,4}
 {2,4,5}
 {2,5}
 {3}
 {3,4}
 {3,4,5}
 {3,5}
 {4}
 {4,5}
 {5}

我有这个解决方案:

create table temp_all_possible_cards (
    card_ids int[]
);

create or replace function test(cards_in_hands INT[] )
returns void
as $$
begin 
    with all_possible_cards(ids) as(
        select ARRAY_APPEND('{}'::int[], t1.card_ids)||ARRAY_APPEND('{}'::int[], t2.card_ids)||ARRAY_APPEND('{}'::int[], t3.card_ids)||ARRAY_APPEND('{}'::int[], t4.card_ids)||ARRAY_APPEND('{}'::int[], t5.card_ids)
        from (
            select unnest(cards_in_hands) as card_ids
        ) t1
        cross join (
            select unnest(cards_in_hands) as card_ids
        ) t2
        cross join (
            select unnest(cards_in_hands) as card_ids
        ) t3
        cross join (
            select unnest(cards_in_hands) as card_ids
        ) t4
        cross join (
            select unnest(cards_in_hands) as card_ids
        ) t5
    )
    INSERT INTO temp_all_possible_cards
    SELECT DISTINCT uniq( sort(ids) ) from all_possible_cards;  
end;
$$ language plpgsql

这行得通，但有一个大问题，有时我需要运行这个函数 5000 次

do $$
begin
    for i in 1..5000 loop
        perform test('{1,2,3,4,5}');
    end loop;
end;
$$ language plpgsql

循环的执行时间为 55-60 秒。

问题:如何高效地从数组中获取所有可能的唯一组合？如何优化此解决方案，以便即使调用 5000 次也比 60 秒快得多？

Answer 1

;WITH NOS AS (SELECT 1 aval 
             UNION ALL
             SELECT aval + 1 FROM NOS WHERE aval < 2 * 2 * 2 * 2 * 2 - 1
             )
    SELECT LEFT(IQ.x, LEN(IQ.x) - 1) + '}' FROM (
            SELECT RTRIM('{' 
                                + CASE WHEN aval & 1 != 0 THEN '1, ' ELSE '' END        
                                + CASE WHEN aval & 2 != 0 THEN '2, ' ELSE '' END        
                                + CASE WHEN aval & 4 != 0 THEN '3, ' ELSE '' END        
                                + CASE WHEN aval & 8 != 0 THEN '4, ' ELSE '' END        
                                + CASE WHEN aval & 16 != 0 THEN '5, ' ELSE '' END) AS X     
                        FROM NOS) IQ

显示基准

create table #test (x nvarchar(50))

declare @i int = 0; 

    declare @s datetime2 = sysutcdatetime();

    while @i < 5000
    begin

    ;WITH NOS AS (SELECT 1 aval 
                 UNION ALL
                 SELECT aval + 1 FROM NOS WHERE aval < 2 * 2 * 2 * 2 * 2 - 1
                 )
        insert #test SELECT LEFT(IQ.x, LEN(IQ.x) - 1) + '}' FROM (
                SELECT RTRIM('{' 
                                    + CASE WHEN aval & 1 != 0 THEN '1, ' ELSE '' END        
                                    + CASE WHEN aval & 2 != 0 THEN '2, ' ELSE '' END        
                                    + CASE WHEN aval & 4 != 0 THEN '3, ' ELSE '' END        
                                    + CASE WHEN aval & 8 != 0 THEN '4, ' ELSE '' END        
                                    + CASE WHEN aval & 16 != 0 THEN '5, ' ELSE '' END) AS X     
                            FROM NOS) IQ


        set @i = @i + 1;

        end 

        DECLARe @usTiming BIGINT = datediff(MICROSECOND, @s ,sysutcdatetime())

        select CAST(@usTiming as nvarchar(19)) + 'us = ' + CAST(CAST(@usTiming/1000000.000000000000 as dec(10,3)) as nvarchar(20)) + ' seconds';

        drop table #test

我有 2.5 秒