迁移至Amazon Redshift：unnest(array[])函数替代方案咨询

从Azure数据库迁移到Amazon Redshift时，原查询中使用的unnest(array[])并行展开多数组的写法需要适配Redshift语法。以下是两种可行的替代方案，均可保留原查询中的field_name列并生成预期结果：

方案一：使用 UNION ALL 逐列展开 #

你之前尝试UNION未达预期，通常是因为未正确编写每个字段对应的查询分支。正确的做法是为每个目标字段单独编写SELECT语句，通过UNION ALL合并所有结果，确保每个分支输出对应的field_name和field_date，同时关联原表的id。

样本数据适配示例 #

select
    rp.id as project_id,
    'colo_l2_submitted_telstra_nbn_vha_f__c' as field_name,
    rot.colo_l2_submitted_telstra_nbn_vha_f__c as field_date
from
    s.project rp
left join s.otherproject rot on rp.id = rot.project__c
union all
select
    rp.id as project_id,
    'colo_l2_submitted_telstra_nbn_vha_a__c' as field_name,
    rot.colo_l2_submitted_telstra_nbn_vha_a__c as field_date
from
    s.project rp
left join s.otherproject rot on rp.id = rot.project__c

完整查询（对应原15个字段） #

-- 第一个字段分支
select
    rp.id,
    'colo_l2_submitted_telstra_nbn_vha_f__c' as field_name,
    rot.colo_l2_submitted_telstra_nbn_vha_f__c as field_date
from
    s.project rp
left join s.otherproject rot on rp.id = rot.project__c
union all
-- 第二个字段分支
select
    rp.id,
    'colo_l2_submitted_telstra_nbn_vha_a__c' as field_name,
    rot.colo_l2_submitted_telstra_nbn_vha_a__c as field_date
from
    s.project rp
left join s.otherproject rot on rp.id = rot.project__c
union all
-- 第三个字段分支
select
    rp.id,
    'colo_l2_approved_telstra_nbn_vha_f__c' as field_name,
    rot.colo_l2_approved_telstra_nbn_vha_f__c as field_date
from
    s.project rp
left join s.otherproject rot on rp.id = rot.project__c
union all
-- 第四个字段分支
select
    rp.id,
    'colo_l2_approved_telstra_nbn_vha_a__c' as field_name,
    rot.colo_l2_approved_telstra_nbn_vha_a__c as field_date
from
    s.project rp
left join s.otherproject rot on rp.id = rot.project__c
union all
-- 第五个字段分支
select
    rp.id,
    'colo_l3_submitted_telstra_nbn_vha_f__c' as field_name,
    rot.colo_l3_submitted_telstra_nbn_vha_f__c as field_date
from
    s.project rp
left join s.otherproject rot on rp.id = rot.project__c
union all
-- 第六个字段分支
select
    rp.id,
    'colo_l3_submitted_telstra_nbn_vha_a__c' as field_name,
    rot.colo_l3_submitted_telstra_nbn_vha_a__c as field_date
from
    s.project rp
left join s.otherproject rot on rp.id = rot.project__c
union all
-- 第七个字段分支
select
    rp.id,
    'colo_l3_approved_telstra_nbn_vha_f__c' as field_name,
    rot.colo_l3_approved_telstra_nbn_vha_f__c as field_date
from
    s.project rp
left join s.otherproject rot on rp.id = rot.project__c
union all
-- 第八个字段分支
select
    rp.id,
    'colo_l3_approved_telstra_nbn_vha_a__c' as field_name,
    rot.colo_l3_approved_telstra_nbn_vha_a__c as field_date
from
    s.project rp
left join s.otherproject rot on rp.id = rot.project__c
union all
-- 第九个字段分支
select
    rp.id,
    'colo_l6_submitted_telstra_nbn_vha_f__c' as field_name,
    rot.colo_l6_submitted_telstra_nbn_vha_f__c as field_date
from
    s.project rp
left join s.otherproject rot on rp.id = rot.project__c
union all
-- 第十个字段分支
select
    rp.id,
    'colo_l6_submitted_telstra_nbn_vha_a__c' as field_name,
    rot.colo_l6_submitted_telstra_nbn_vha_a__c as field_date
from
    s.project rp
left join s.otherproject rot on rp.id = rot.project__c
union all
-- 第十一个字段分支
select
    rp.id,
    'colo_l6_approved_telstra_nbn_vha_f__c' as field_name,
    rot.colo_l6_approved_telstra_nbn_vha_f__c as field_date
from
    s.project rp
left join s.otherproject rot on rp.id = rot.project__c
union all
-- 第十二个字段分支
select
    rp.id,
    'colo_l6_approved_telstra_nbn_vha_a__c' as field_name,
    rot.colo_l6_approved_telstra_nbn_vha_a__c as field_date
from
    s.project rp
left join s.otherproject rot on rp.id = rot.project__c
union all
-- 第十三个字段分支
select
    rp.id,
    'in_service_rfs_complete_f__c' as field_name,
    rot.in_service_rfs_complete_f__c as field_date
from
    s.project rp
left join s.otherproject rot on rp.id = rot.project__c
union all
-- 第十四个字段分支
select
    rp.id,
    'in_service_rfs_complete_a__c' as field_name,
    rot.in_service_rfs_complete_a__c as field_date
from
    s.project rp
left join s.otherproject rot on rp.id = rot.project__c
union all
-- 第十五个字段分支
select
    rp.id,
    'as_built_completed__c' as field_name,
    rot.as_built_completed__c as field_date
from
    s.project rp
left join s.otherproject rot on rp.id = rot.project__c

优缺点：逻辑直观，容易验证结果正确性；但字段较多时代码量偏大，可通过脚本批量生成分支代码来简化操作。

方案二：使用 Redshift UNNEST 结合序号关联 #

Redshift支持UNNEST函数，但直接并行展开两个数组会产生笛卡尔积，无法实现原查询中数组元素的位置配对。可以通过WITH ORDINALITY为每个数组元素添加序号，再通过序号关联两个展开结果，还原原查询的配对逻辑。

改写后的完整查询 #

with project_data as (
    -- 先将字段名和对应日期打包为数组
    select
        rp.id,
        array['colo_l2_submitted_telstra_nbn_vha_f__c',
              'colo_l2_submitted_telstra_nbn_vha_a__c',
              'colo_l2_approved_telstra_nbn_vha_f__c',
              'colo_l2_approved_telstra_nbn_vha_a__c',
              'colo_l3_submitted_telstra_nbn_vha_f__c',
              'colo_l3_submitted_telstra_nbn_vha_a__c',
              'colo_l3_approved_telstra_nbn_vha_f__c',
              'colo_l3_approved_telstra_nbn_vha_a__c',
              'colo_l6_submitted_telstra_nbn_vha_f__c',
              'colo_l6_submitted_telstra_nbn_vha_a__c',
              'colo_l6_approved_telstra_nbn_vha_f__c',
              'colo_l6_approved_telstra_nbn_vha_a__c',
              'in_service_rfs_complete_f__c',
              'in_service_rfs_complete_a__c',
              'as_built_completed__c'] as field_names,
        array[rot.colo_l2_submitted_telstra_nbn_vha_f__c,
              rot.colo_l2_submitted_telstra_nbn_vha_a__c,
              rot.colo_l2_approved_telstra_nbn_vha_f__c,
              rot.colo_l2_approved_telstra_nbn_vha_a__c,
              rot.colo_l3_submitted_telstra_nbn_vha_f__c,
              rot.colo_l3_submitted_telstra_nbn_vha_a__c,
              rot.colo_l3_approved_telstra_nbn_vha_f__c,
              rot.colo_l3_approved_telstra_nbn_vha_a__c,
              rot.colo_l6_submitted_telstra_nbn_vha_f__c,
              rot.colo_l6_submitted_telstra_nbn_vha_a__c,
              rot.colo_l6_approved_telstra_nbn_vha_f__c,
              rot.colo_l6_approved_telstra_nbn_vha_a__c,
              rot.in_service_rfs_complete_f__c,
              rot.in_service_rfs_complete_a__c,
              rot.as_built_completed__c] as field_dates
    from
        s.project rp
    left join s.otherproject rot on rp.id = rot.project__c
),
unnest_names as (
    -- 展开字段名数组并添加序号
    select
        id,
        unnest(field_names) as field_name,
        ordinality as idx
    from project_data
    cross join unnest(field_names) with ordinality
),
unnest_dates as (
    -- 展开日期数组并添加序号
    select
        id,
        unnest(field_dates) as field_date,
        ordinality as idx
    from project_data
    cross join unnest(field_dates) with ordinality
)
-- 通过id和序号关联，得到配对结果
select
    un.id,
    un.field_name,
    ud.field_date
from unnest_names un
join unnest_dates ud on un.id = ud.id and un.idx = ud.idx

注意事项：两个数组的长度必须完全一致，否则会出现数据不匹配，这和原Azure查询的要求一致。

结果验证 #

两种方案的输出均与原unnest查询结果一致，以样本数据为例，输出如下：

内容的提问来源于stack exchange，提问作者SWL