在 Google BigQuery 中将多个数组连接成一个字符串 [英] Concatenate mulitple arrays into a string in 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屋！