我想知道如何在Redshift中将逗号分隔的值转换为行。我担心我的解决方案不够优化。请给予建议。 我有一个表格，其中一个列具有逗号分隔的值。例如：
我有：
user_id|user_name|user_action
-----------------------------
1 | Shone | start,stop,cancell...
我想看见
user_id|user_name|parsed_action
-------------------------------
1 | Shone | start
1 | Shone | stop
1 | Shone | cancell
....
对现有答案的稍微改进是使用第二个“numbers”表格枚举所有可能的列表长度，然后使用“cross join”使查询更加紧凑。
Redshift没有直接创建数字表的简单方法（据我所知），但我们可以使用https://www.periscope.io/blog/generate-series-in-redshift-and-mysql.html中提到的一些技巧（使用行编号）来创建一个数字表。
具体而言，如果我们假设cmd_logs中的行数大于user_action列中逗号的最大数量，则可以通过计算行数来创建数字表。首先，让我们假设user_action列中最多有99个逗号：
select
(row_number() over (order by true))::int as n
into numbers
from cmd_logs
limit 100;
如果我们想要更加精确，我们可以从cmd_logs表中计算逗号的数量，以创建numbers中更准确的行集：
select
n::int
into numbers
from
(select
row_number() over (order by true) as n
from cmd_logs)
cross join
(select
max(regexp_count(user_action, '[,]')) as max_num
from cmd_logs)
where
n <= max_num + 1;
有了一个numbers表，我们可以执行以下操作：
select
user_id,
user_name,
split_part(user_action,',',n) as parsed_action
from
cmd_logs
cross join
numbers
where
split_part(user_action,',',n) is not null
and split_part(user_action,',',n) != '';
user_action中逗号的最大数量比cmd_logs中的行数还要多怎么办？这在我的情况下是真实存在的，我最终会丢失一些值--参考数据显示，我最多有约5,000个逗号。 - daRknight... '["' || replace( user_action, '.', '", "' ) || '"]' AS replaced
... JSON_EXTRACT_ARRAY_ELEMENT_TEXT(replaced, numbers.i) AS parsed_action
其中“numbers”是第一个答案中的表。这种方法的优点是能够使用内置的JSON功能，并且保留HTML标签。create temporary table actions
(
user_id varchar,
user_name varchar,
user_action varchar
);
insert into actions
values (1, 'Shone', 'start,stop,cancel'),
(2, 'Gregory', 'find,diagnose,taunt'),
(3, 'Robot', 'kill,destroy');
这是一个带有临时计数的附加表格
，其中包含HTML代码。create temporary table actions_with_counts
(
id varchar,
name varchar,
num_actions integer,
actions varchar
);
insert into actions_with_counts (
select user_id,
user_name,
regexp_count(user_action, ',') + 1 as num_actions,
user_action
from actions
);
select * from actions_with_counts;
| ID | 名称 | 操作数 | 操作 |
|---|---|---|---|
| 2 | 格雷戈里 | 3 | 查找、诊断、嘲讽 |
| 3 | 机器人 | 2 | 杀死、摧毁 |
| 1 | 肖恩 | 3 | 启动、停止、取消 |
再次提醒，您可以调整初始表格，因此跳过将计数作为单独表格添加。
子查询以展开操作
Here's the unnesting query:
with recursive tmp (user_id, user_name, idx, user_action) as
(
select id,
name,
1 as idx,
split_part(actions, ',', 1) as user_action
from actions_with_counts
union all
select user_id,
user_name,
idx + 1 as idx,
split_part(actions, ',', idx + 1)
from actions_with_counts
join tmp on actions_with_counts.id = tmp.user_id
where idx < num_actions
)
select user_id, user_name, user_action as parsed_action
from tmp
order by user_id;
| 用户ID | 用户名 | 已解析动作 |
|---|---|---|
| 1 | Shone | 开始 |
| 1 | Shone | 停止 |
| 1 | Shone | 取消 |
| 2 | Gregory | 查找 |
| 2 | Gregory | 诊断 |
| 2 | Gregory | 嘲讽 |
| 3 | 机器人 | 杀死 |
| 3 | 机器人 | 摧毁 |
drop table if exists #dep_tbl;
create table #dep_tbl as
select 'schema.foobar.insert_ts,schema.baz.load_ts' as dep
;
with recursive tmp (level, dep_split, to_split) as
(
select 1 as level
, split_part(dep, ',', 1) as dep_split
, regexp_count(dep, ',') as to_split
from #dep_tbl
union all
select tmp.level + 1 as level
, split_part(a.dep, ',', tmp.level + 1) as dep_split_u
, tmp.to_split
from #dep_tbl a
inner join tmp on tmp.dep_split is not null
and tmp.level <= tmp.to_split
)
select dep_split from tmp;
上述内容的结果为:
|schema.foobar.insert_ts| |schema.baz.load_ts|
CREATE OR REPLACE PROCEDURE so_test(dependencies_csv varchar(max))
LANGUAGE plpgsql
AS $$
DECLARE
dependencies_csv_vals varchar(max);
BEGIN
drop table if exists #dep_holder;
create table #dep_holder
(
avoid varchar(60000)
);
IF dependencies_csv is not null THEN
dependencies_csv_vals:='('||replace(quote_literal(regexp_replace(dependencies_csv,'\\s','')),',', '\'),(\'') ||')';
execute 'insert into #dep_holder values '||dependencies_csv_vals||';';
END IF;
END;
$$
;
call so_test('schema.foobar.insert_ts,schema.baz.load_ts')
select
*
from
#dep_holder;
以上产生：
|schema.foobar.insert_ts| |schema.baz.load_ts|
如果您只关心输入中的一个单独列（X分隔值），那么我认为存储过程更容易/更快。
但是，如果您关心其他列并希望将这些列与现在转换为行的逗号分隔值列一起保留，或者如果您想要知道参数（原始分隔值列表），那么我认为存储过程是正确的方法。在这种情况下，您可以将这些其他列添加到递归查询中选择的列中。
仅对上面的答案进行改进https://dev59.com/gV8f5IYBdhLWcg3wB-3E#31998832
使用以下SQL生成数字表 https://discourse.looker.com/t/generating-a-numbers-table-in-mysql-and-redshift/482
SELECT
p0.n
+ p1.n*2
+ p2.n * POWER(2,2)
+ p3.n * POWER(2,3)
+ p4.n * POWER(2,4)
+ p5.n * POWER(2,5)
+ p6.n * POWER(2,6)
+ p7.n * POWER(2,7)
as number
INTO numbers
FROM
(SELECT 0 as n UNION SELECT 1) p0,
(SELECT 0 as n UNION SELECT 1) p1,
(SELECT 0 as n UNION SELECT 1) p2,
(SELECT 0 as n UNION SELECT 1) p3,
(SELECT 0 as n UNION SELECT 1) p4,
(SELECT 0 as n UNION SELECT 1) p5,
(SELECT 0 as n UNION SELECT 1) p6,
(SELECT 0 as n UNION SELECT 1) p7
ORDER BY 1
LIMIT 100
如果你只想粘贴代码而不需要INTO子句并查看结果，那么"ORDER BY"就是必需的。
您可以使用以下查询语句获得预期结果。我使用了“UNION ALL”将列转换为行。
select user_id, user_name, split_part(user_action,',',1) as parsed_action from cmd_logs
union all
select user_id, user_name, split_part(user_action,',',2) as parsed_action from cmd_logs
union all
select user_id, user_name, split_part(user_action,',',3) as parsed_action from cmd_logs
虽然有点晚，但我终于让某些东西运作起来了（尽管速度非常慢）
with nums as (select n::int n
from
(select
row_number() over (order by true) as n
from table_with_enough_rows_to_cover_range)
cross join
(select
max(json_array_length(json_column)) as max_num
from table_with_json_column )
where
n <= max_num + 1)
select *, json_extract_array_element_text(json_column,nums.n-1) parsed_json
from nums, table_with_json_column
where json_extract_array_element_text(json_column,nums.n-1) != ''
and nums.n <= json_array_length(json_column)
感谢Bob Baxley的回答给予的灵感
这是我同样糟糕的答案。
我有一个users表，然后有一个events表，其中一列只是一个逗号分隔的字符串，表示参加该事件的用户。例如：
event_id | user_ids
1 | 5,18,25,99,105
LIKE和通配符函数来构建一个新表，该表表示每个事件-用户边缘。SELECT e.event_id, u.id as user_id
FROM events e
LEFT JOIN users u ON e.user_ids like '%' || u.id || '%'
这不是很好看，但我将它放在一个WITH子句中，这样我就不必每次查询都运行它了。我可能只会建立一个ETL来每晚创建那个表。
此外，这仅适用于您有第二个表，该表确实具有每个唯一可能性的一行。如果没有，您可以使用LISTAGG获取带有所有值的单个单元格，将其导出为CSV并重新上传作为表格以帮助解决问题。
就像我说的：一个可怕的、毫无用处的解决方案。
CREATE OR REPLACE PROCEDURE public.sp_string_split( "string" character varying )
AS $$
DECLARE
cnt INTEGER := 1;
no_of_parts INTEGER := (select REGEXP_COUNT ( string , ',' ));
sql VARCHAR(MAX) := '';
item character varying := '';
BEGIN
-- Create table
sql := 'CREATE TEMPORARY TABLE IF NOT EXISTS split_table (part VARCHAR(255)) ';
RAISE NOTICE 'executing sql %', sql ;
EXECUTE sql;
<<simple_loop_exit_continue>>
LOOP
item = (select split_part("string",',',cnt));
RAISE NOTICE 'item %', item ;
sql := 'INSERT INTO split_table SELECT '''||item||''' ';
EXECUTE sql;
cnt = cnt + 1;
EXIT simple_loop_exit_continue WHEN (cnt >= no_of_parts + 2);
END LOOP;
END ;
$$ LANGUAGE plpgsql;
使用示例：
call public.sp_string_split('john,smith,jones');
select *
from split_table
您可以尝试使用复制命令将文件复制到Redshift表中
copy table_name from 's3://mybucket/myfolder/my.csv' CREDENTIALS 'aws_access_key_id=my_aws_acc_key;aws_secret_access_key=my_aws_sec_key' delimiter ','
你可以使用分隔符 ','的选项。
有关复制命令选项的更多详细信息，请访问此页面。