对现有答案的一个轻微改进是使用第二个“数字”表,该表枚举所有可能的列表长度,然后使用cross join
使查询更加紧凑。
据我所知,Redshift 没有一种简单的方法来创建数字表,但我们可以使用一些技巧https://www.periscope.io/blog/generate-series-in-redshift-and-mysql.html使用行号创建一个。
具体来说,如果我们假设行数cmd_logs
大于逗号的最大数量user_action
列,我们可以通过计算行数来创建一个数字表。首先,我们假设最多有 99 个逗号user_action
column:
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) != '';