在 Google BigQuery 中将多个数组连接成一个字符串 [英] Concatenate mulitple arrays into a string in Google BigQuery
本文介绍了在 Google BigQuery 中将多个数组连接成一个字符串的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我的 bigquery 表数据如下所示
My bigquery table data looks like shown below
但我正在尝试连接数组值以实现如下输出.
But I am trying to concatenate the array values to achieve an output like below.
SATURDAY;12;23|WEDNESDAY;0;15 作为单列值
SATURDAY;12;23|WEDNESDAY;0;15 as a single column value
提前致谢!
推荐答案
这样的事情应该能让你朝着正确的方向前进.忽略前 2 个 CTE,因为它们只是复制您的示例数据.
Something like this should get you going in the right direction. Ignore the top 2 CTEs as they are just replicating your sample data.
with
-- recreating sample data
temp as (
select 5046528 as LineID, 'Saturday' as positions, 12 as starttime, 23 as endtime union all
select 5046528, 'Wednesday', 0, 15
),
-- formatting to recreate sample data structure
data as (
select LineID, array_agg(struct(positions)) as day_part, array_agg(struct(starttime, endtime)) as hour_part
from temp
group by 1
)
-- Logic that is relevant to the question
select
LineID,
string_agg(safe.concat(upper(d.positions),';',h.starttime,';',h.endtime),'|') as new_column
from data, unnest(day_part) d with offset as d_offset, unnest(hour_part) h with offset as h_offset
where d_offset = h_offset -- needed since you are unnesting 2 arrays, this makes sure your records "line up"
group by 1
这篇关于在 Google BigQuery 中将多个数组连接成一个字符串的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文