Redshift将单个动态列拆分为新表中的多行

Redshift将单个动态列拆分为新表中的多行

这是Redshift答案，它将与每行多达1万个细分ID值一​​起使用。

测试数据

create table test_split (uid varchar(50),segmentids varchar(max));insert into test_split    values      ('f9b6d54b-c646-4bbb-b0ec','4454918|4455158|4455638|4455878|4455998'),      ('asd7a0s9-c646-asd7-b0ec','1265899|1265923|1265935|1266826|1266596'),      ('asd7345s9-c646-asd7-b0ec','1235935|1263456|1265675696'),      ('as345a0s9-c646-asd7-b0ec','12765899|12658883|12777935|144466826|1266226|12345');

代码

with ten_numbers as (select 1 as num union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 0)  , generted_numbers AS(    SELECT (1000 * t1.num) + (100 * t2.num) + (10 * t3.num) + t4.num AS gen_num    FROM ten_numbers AS t1      JOIN ten_numbers AS t2 ON 1 = 1      JOIN ten_numbers AS t3 ON 1 = 1      JOIN ten_numbers AS t4 ON 1 = 1)  , splitter AS(    SELECt *    FROM generted_numbers    WHERe gen_num BETWEEN 1 AND (SELECt max(REGEXP_COUNT(segmentids, '\|') + 1)FROM test_split))  --select * from splitter;  , expanded_input AS(    SELECt      uid,      split_part(segmentids, '|', s.gen_num) AS segment    FROM test_split AS ts      JOIN splitter AS s ON 1 = 1    WHERe split_part(segmentids, '|', s.gen_num) <> '')SELECt * FROM expanded_input;

前两个cte步骤（ten_numbers和generate_numbers）用于生成许多行，这是必需的，因为不支持generate_series

下一步（拆分器）仅占用等于定界符最大数量+1（即段的最大数量）的行数

最后，我们将输入数据与交叉分割器交叉，使用split_part获取相关值，然后排除空白部分（这是因为该行的行数小于最大段数）