问红移。将逗号分隔的值转换为具有所有组合的行

EN

create or replace function get_unique_combinations(list varchar(max))
returns varchar(max)
stable as $$

from itertools import combinations

arr = list.split(',')

response = []

for L in range(1, len(arr)+1):
    for subset in combinations(arr, L):
        response.append(','.join(subset))

return ';'.join(response)

$$ language plpythonu;

WITH unique_combinations as (
    SELECT 
     user_id
    ,user_name
    ,get_unique_combinations(user_actions) as action_combinations
    FROM your_table
)
,unwrap_lists as (
    SELECT 
     user_id
    ,user_name
    ,split_part(action_combinations,';',1) as parsed_action
    FROM unique_combinations
    UNION ALL
    SELECT 
     user_id
    ,user_name
    ,split_part(action_combinations,';',2) as parsed_action
    FROM unique_combinations
    -- as much UNIONS as possible combinations you have for a single element, with the 3rd parameter (1-based array index) increasing by 1
    )
SELECT *
FROM unwrap_lists
WHERE parsed_action is not null